headers
John Mendenhall | 1 Jul 2005 01:58

Re: ported application having performance issues

pgsql performance gurus,

I truly appreciate the suggestions provided.

I have tried each one separately to determine the
best fit.  I have included results for each suggestion.
I have also included my entire postgresql.conf file so
you can see our base configuration.
Each result is based on an in-session variable setting,
so it only affected the current session.

(1) Increase the default_statistics_target,
run vacuum, analyze on each table for each setting

The default setting is 10.

I tried the following settings, with the corresponding
results:

default_statistics_target =   10     time approximately 4500ms
default_statistics_target =  100     time approximately 3900ms
default_statistics_target =  500     time approximately 3900ms
default_statistics_target = 1000     time approximately 3900ms

So, this option does not quite get us there.

(2) Increase sort_mem value

The current setting for sort_mem is 2048.
(Continue reading)

Permalink | Reply | 11 comments |
headers
Dennis Bjorklund | 1 Jul 2005 10:57

Re: ported application having performance issues

On Thu, 30 Jun 2005, John Mendenhall wrote:

> Our setting for effective_cache_size is 2048.
> 
> random_page_cost = 4, effective_cache_size = 2048   time approximately 4500ms
> random_page_cost = 3, effective_cache_size = 2048   time approximately 1050ms
> random_page_cost = 3, effective_cache_size = 4096   time approximately 1025ms
> 
> The decrease of random_page_cost to 3 caused the plan
> to work properly, using the lead_requests table as a
> join starting point and using the contacts index.

The effective_cache_size still looks small. As a rule of tumb you might
want effective_cache_size to be something like 1/2 or 2/3 of your total
memory. I don't know how much you had, but effective_cache_size = 4096 is
only 32M.

shared_buffers and effective_cache_size is normally the two most important 
settings in my experience.

--

-- 
/Dennis Björklund

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Permalink | Reply | 1 comment |
headers
Sam Mason | 1 Jul 2005 15:33
Picon

planner picking more expensive plan

Hi,

I've just been referred here after a conversion on IRC and everybody
seemed to think I've stumbled upon some strangeness.

The planner (in PG version 8.0.2) is choosing what it thinks is a more
expensive plan.  I've got a table of animals (about 3M rows) and their
movements (about 16M rows), and I'm trying to execute this query:

  SELECT a.birthlocnid, m.locnid
  FROM animals a
    LEFT JOIN movements m ON (a.animalid = m.animalid AND m.mtypeid=0)
  LIMIT 10;

If I have "work_mem" set to something small (1000) it uses this plan:

    QUERY PLAN

 Limit  (cost=0.00..202.52 rows=10 width=8) (actual time=0.221..0.600 rows=10 loops=1)
   ->  Merge Left Join  (cost=0.00..66888828.30 rows=3302780 width=8) (actual time=0.211..0.576 rows=10 loops=1)
         Merge Cond: ("outer".animalid = "inner".animalid)
         ->  Index Scan using animals_pkey on animals a  (cost=0.00..10198983.91 rows=3302780 width=8) (actual
time=0.112..0.276 rows=10 loops=1)
         ->  Index Scan using movement_animal on movements m  (cost=0.00..56642740.73 rows=3107737 width=8)
(actual time=0.088..0.235 rows=10 loops=1)
               Filter: (mtypeid = 0)
 Total runtime: 0.413 ms

But if I increase "work_mem" to 10000 it uses this plan:
(Continue reading)

Permalink | Reply | 5 comments |
headers
Tom Lane | 1 Jul 2005 16:22
Picon

Re: planner picking more expensive plan

Sam Mason <sam <at> samason.me.uk> writes:
> The planner (in PG version 8.0.2) is choosing what it thinks is a more
> expensive plan.

I fooled around trying to duplicate this behavior, without success.
Can you create a self-contained test case?

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Permalink | Reply | 1 comment |
headers
Sam Mason | 1 Jul 2005 16:58
Picon

Re: planner picking more expensive plan

Tom Lane wrote:
>I fooled around trying to duplicate this behavior, without success.
>Can you create a self-contained test case?

I'll try and see if I can put something together, it's probably
going to be early next week though.  I wont be able to give you our
data, so I'll be a bit of a headscratching exercise generating
something that'll provoke the same behaviour.

Not sure if it'll help, but here's what the database schema looks
like at the moment:

             Table "public.animals"
   Column    |         Type          | Modifiers 
-------------+-----------------------+-----------
 animalid    | integer               | not null
 sex         | character(1)          | not null
 dob         | date                  | not null
 birthlocnid | integer               | 
 breedid     | character varying(8)  | 
 eartag_1    | character varying(20) | 
 eartag_2    | character varying(20) | 
 eartag_3    | character varying(20) | 
Indexes:
    "animals_pkey" primary key, btree (animalid)
    "animal_birthlocn" btree (birthlocnid)
    "animal_breed" btree (breedid)
    "animal_eartag" btree (eartag_1)
Check constraints:
    "animal_sex" CHECK (sex = 'M'::bpchar OR sex = 'F'::bpchar)
(Continue reading)

Permalink | Reply | 0 comments |
headers
John A Meinel | 1 Jul 2005 17:17

Re: planner picking more expensive plan

Sam Mason wrote:

>Hi,
>
>I've just been referred here after a conversion on IRC and everybody
>seemed to think I've stumbled upon some strangeness.
>
>The planner (in PG version 8.0.2) is choosing what it thinks is a more
>expensive plan.  I've got a table of animals (about 3M rows) and their
>movements (about 16M rows), and I'm trying to execute this query:
>
>  SELECT a.birthlocnid, m.locnid
>  FROM animals a
>    LEFT JOIN movements m ON (a.animalid = m.animalid AND m.mtypeid=0)
>  LIMIT 10;
>
>
>
Why are you using LIMIT without having an ORDER BY?
What are actually trying to get out of this query? Is it just trying to
determine where the 'home' locations are?
It just seems like this query isn't very useful. As it doesn't restrict
by animal id, and it just gets 10 randomly selected animals where
m.mtypeid=0.
And why a LEFT JOIN instead of a normal join?
Anyway, the general constraints you are applying seem kind of confusing.
What happens if you change the plan to:

  SELECT a.birthlocnid, m.locnid
  FROM animals a
(Continue reading)

Permalink | Reply | 1 comment |
headers
Sam Mason | 1 Jul 2005 17:58
Picon

Re: planner picking more expensive plan

John A Meinel wrote:
>Why are you using LIMIT without having an ORDER BY?

I'm just exploring the data, trying to figure out what it's like.

>It just seems like this query isn't very useful. As it doesn't restrict
>by animal id, and it just gets 10 randomly selected animals where
>m.mtypeid=0.

Yup, that's the point.  Check to see if the animals were born where
they say they were.  The data's come from an external source and
I'm just trying to figure out how good it is before I do too much
with it

>And why a LEFT JOIN instead of a normal join?

I'm not sure if some animals will have missing data!

>Anyway, the general constraints you are applying seem kind of confusing.

This was a slightly cut down query in an attempt to reduce general
confusion -- I guess I failed.  Sorry!

>I would guess that this would help the planner realize it should try to
>use an index, since it can realize that it wants only a few rows by
>a.animalid in order.

This seems to work the appropiate magic.  It always seems to prefer
index scans now.
(Continue reading)

Permalink | Reply | 0 comments |
headers
John Mendenhall | 2 Jul 2005 02:52

Re: ported application having performance issues

Dennis,

On Fri, 01 Jul 2005, Dennis Bjorklund wrote:

> On Thu, 30 Jun 2005, John Mendenhall wrote:
> 
> > Our setting for effective_cache_size is 2048.
> > 
> > random_page_cost = 4, effective_cache_size = 2048   time approximately 4500ms
> > random_page_cost = 3, effective_cache_size = 2048   time approximately 1050ms
> > random_page_cost = 3, effective_cache_size = 4096   time approximately 1025ms
> 
> The effective_cache_size still looks small. As a rule of tumb you might
> want effective_cache_size to be something like 1/2 or 2/3 of your total
> memory. I don't know how much you had, but effective_cache_size = 4096 is
> only 32M.
> 
> shared_buffers and effective_cache_size is normally the two most important 
> settings in my experience.

I have increased the effective_cache_size to 16384 (128M).  I have kept
random_page_cost at 3 for now.  This appears to give me the performance
I need at this time.

In the future, we'll look at other methods of increasing the
performance.

Thank you all for all your suggestions.

JohnM
(Continue reading)

Permalink | Reply | 0 comments |
headers
Emil Briggs | 2 Jul 2005 03:59

Planner constants for RAM resident databases


I'm working with an application where the database is entirely resident in RAM 
(the server is a quad opteron with 16GBytes of memory). It's a web 
application and handles a high volume of queries. The planner seems to be 
generating poor  plans for some of our queries which I can fix by raising 
cpu_tuple_cost. I have seen some other comments in the archives saying that 
this is a bad idea  but is that necessarily the case when the database is 
entirely resident in RAM?

Emil

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Permalink | Reply | 2 comments |
headers
Steve Atkins | 2 Jul 2005 04:08

Re: Planner constants for RAM resident databases

On Fri, Jul 01, 2005 at 09:59:38PM -0400, Emil Briggs wrote:

> I'm working with an application where the database is entirely resident in RAM 
> (the server is a quad opteron with 16GBytes of memory). It's a web 
> application and handles a high volume of queries. The planner seems to be 
> generating poor  plans for some of our queries which I can fix by raising 
> cpu_tuple_cost. I have seen some other comments in the archives saying that 
> this is a bad idea  but is that necessarily the case when the database is 
> entirely resident in RAM?

If I'm understanding correctly that'll mostly increase the estimated
cost of handling a row relative to a sequential page fetch, which
sure sounds like it'll push plans in the right direction, but it
doesn't sound like the right knob to twiddle.

What do you have random_page_cost set to?

Cheers,
  Steve

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Permalink | Reply | 0 comments |

Gmane