Skip site navigation (1) Skip section navigation (2)

Re: Weird 8.2.4 performance

From: Kurt Overberg <kurt(at)hotdogrecords(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Weird 8.2.4 performance
Date: 2007-06-07 11:18:22
Message-ID: C040B179-DBCF-441F-9677-E03AE663CEF9@hotdogrecords.com (view raw or flat)
Thread:
Lists: pgsql-performance
Thank you everyone for the replies.  I'll try to answer everyone's  
questions in one post.

* Regarding production/mac memory and cache usage.  This query HAS  
been running on 8.0 on my Mac, I just got that particular query  
explain from our production system because I had to nuke my local 8.0  
database before installing  8.2.4 due to disk space limitations.  The  
query that this sample query is part of run in under 5 seconds when I  
was running 8.0 locally on my mac, and it did a bunch of agregations  
based on task instance.

* work_mem is set to 1 megabyte (the default) on both 8.0 and 8.2.4.

* setting enable_bitmapscan = false on 8.2.4

0605=# explain analyze  select id from taskinstance where taskid in  
(select id from task where campaignid = 76);
                                                                         
                                          QUERY PLAN
------------------------------------------------------------------------ 
------------------------------------------------------------------------ 
-------
Nested Loop  (cost=16.94..15484.61 rows=2309 width=4) (actual  
time=44.751..8498.689 rows=1117 loops=1)
    ->  HashAggregate  (cost=16.94..17.01 rows=7 width=4) (actual  
time=0.144..0.194 rows=10 loops=1)
          ->  Index Scan using "Task_campaignId_key" on task   
(cost=0.00..16.93 rows=7 width=4) (actual time=0.069..0.116 rows=10  
loops=1)
                Index Cond: (campaignid = 51)
    ->  Index Scan using taskid_taskinstance_key on taskinstance   
(cost=0.00..2202.73 rows=554 width=8) (actual time=20.305..849.640  
rows=112 loops=10)
          Index Cond: (taskinstance.taskid = task.id)
Total runtime: 8499.599 ms

...FWIW, this query returns about 900 rows.  TaskInstance is a fairly  
large table in width (20 columns, about 15 are varchar, 3 timestamps  
and a few ints)
and height (650,000) rows.  I can't really run the same query  
multiple times due to caching, so I change up "campaignid".  Is there  
a way to flush that cache?  Turning off bitmap scans definitely seems  
to help things, but I'm concerned that when/if I flip my production  
machine, I'm going to run into who-knows-what.  I don't really have a  
set of SQL acceptance tests to test jumping from rev to rev (I know I  
should- BAD DEVELOPER, BAD!).

* Configuration

- My production environment is running RedHat 2.6.9.ELsmp on a server  
with 16GB of memory

-  My old 8.0 database on my mac only had this modified from default:

shared_buffers =  100
work_mem = 1024

- 8.2.4 database seemed to go through some sort of auto-config when I  
installed it, settings I think are different are as follows:

shared_buffers = 128MB                  # min 128kB or  
max_connections*16kB
work_mem = 100MB                        # when I ran the original  
query, this was set to 1MB, increased on Mark Kirkwood's advice,  
seemed to help a bit but not really

8.2.4 Database size- 25 GB (from du -sh on the directory 'base')

* Richard Huxton

Thanks for the kind words- I'm glad I was able to 'ask a good  
question'.  I'm very new to this mailing list, but I'm on many Java/ 
Struts/Perl mailing lists and have seen enough poorly worded/spelled/ 
asked questions to last a lifetime.  My situation is: I'm the senior  
(read: first) developer at a small but growing startup.  Everything I  
know about PostgreSQL I've learned over the past 4 years in which our  
tiny little DB grew from one database with 100 users to over a 4 node  
Slony setup 300,000 users.  Somehow, I'm not sure why, but I find  
myself in the awkward position of being the 'go-to guy' for all  
database related stuff at my company.  What I don't know could fill  
volumes, but I've been able to keep the durn database running for  
over 4 years (which is mostly a testament to how awesome PostgreSQL  
is)- so when I hit something that makes no sense, I KNOW that if I  
have any hope of getting one of ye postgresql gods to help me with an  
obscure, non-sensical problem such as this one, I'd better include as  
much context as possible.  :-)   FWIW- we're looking to hire a  
PostgreSQL hired gun to help me with this and many other things.   
Ideally, that person would be in Boston, MA, USA and be able to come  
into the office, but we'd consider remote people too.  If you're  
interested, drop me a line.

Thanks again for the replies, gang.  Have there been many reported  
performance related problems regarding people upgrading from 8.0->8.2?

Is there a primer somewhere on how to read EXPLAIN output?

Thanks again for helping me with this...

/kurt



On Jun 7, 2007, at 5:23 AM, Richard Huxton wrote:

> Mark Kirkwood wrote:
>> 8.2 is deciding to use a bitmap index scan on  
>> taskid_taskinstance_key, which seems to be slower (!) than a plain  
>> old index scan that 8.0 is using. A dirty work around is to  
>> disable bitmap scans via:
>
> I'm having difficulty figuring out why it's doing this at all.  
> There's only one index involved, and it's over the primary-key to  
> boot!
>
> An EXPLAIN ANALYSE with enable_bitmapscan off should say why PG  
> thinks the costs are cheaper than they actually are.
>
> PS - well worded question Kurt. All the relevant information neatly  
> laid out, explain analyse on both platforms - you should be  
> charging to let people help ;-)
>
> -- 
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo(at)postgresql(dot)org so that  
> your
>       message can get through to the mailing list cleanly


In response to

Responses

pgsql-performance by date

Next:From: Steinar H. GundersonDate: 2007-06-07 11:28:07
Subject: Re: Weird 8.2.4 performance
Previous:From: Mark KirkwoodDate: 2007-06-07 11:12:52
Subject: Re: Weird 8.2.4 performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group