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

Re: Data Set Growth causing 26+hour runtime, on what we believe to be very simple SQL

From: Tory M Blue <tmblue(at)gmail(dot)com>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Data Set Growth causing 26+hour runtime, on what we believe to be very simple SQL
Date: 2010-01-22 17:59:34
Message-ID: 8a547c841001220959s77c8c28arf56a5db8c66958c1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Jan 21, 2010 at 7:46 PM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au>wrote:

>
> > Any assistance would be appreciated, don't worry about slapping me
> > around I need to figure this out. Otherwise I'm buying new hardware
> > where it may not be required.
>
> What is the reporting query that takes 26 hours? You didn't seem to
> include it, or any query plan information for it (EXPLAIN or EXPLAIN
> ANALYZE results).
>

It's this query, run 6000 times with a diff makeid's  *
*

*SELECT COUNT(DISTINCT uid )  AS active_users FROM pixelpool.userstats
WHERE makeid ='bmw-ferman' AND tagged =true*

*                Plan*

*                "Aggregate  (cost=49467.00..49467.01 rows=1 width=8)"*

*                "  ->  Bitmap Heap Scan on userstats
(cost=363.49..49434.06 rows=13175 width=8)"*

*                "        Recheck Cond: (makeid = 'b1mw-ferman'::text)"*

*                "        Filter: tagged"*

*                "        ->  Bitmap Index Scan on
idx_retargetuserstats_makeidtag  (cost=0.00..360.20 rows=13175 width=0)"*

*                "              Index Cond: ((makeid = 'b1mw-ferman'::text)
AND (tagged = true))"*


> What sort of activity is happening on the db concurrently with your
> tests? What's your max connection limit?
>

50 max and there is nothing, usually one person connected if that, otherwise
it's a cron job that bulk inserts and than jobs later on run that generate
the reports off the static data. No deletes or updates happening.


>
> What're your shared_buffers and effective_cache_size settings?
>

shared_buffers = 1028MB  (Had this set at 128 and 256 and just recently
bumped it higher, didn't buy me anything)
maintenance_work_mem = 128MB
fsync=on
random_page_cost = 4.0
effective_cache_size = 7GB
default vac settings


>
> Could sorts be spilling to disk? Check work_mem size and enable logging
> of tempfiles (see the manual).
>

work_mem = 100MB                                # min 64kB

Will do and I guess it's possible but during the queries, reports I don't
see a ton of writes, mostly reads

>
> Does an explicit ANALYZE of the problem table(s) help?
>

It didn't.

Thanks
Tory

In response to

Responses

pgsql-performance by date

Next:From: Tory M BlueDate: 2010-01-22 18:03:35
Subject: Re: Data Set Growth causing 26+hour runtime, on what we believe to be very simple SQL
Previous:From: Carlo StonebanksDate: 2010-01-22 17:37:56
Subject: Re: New server to improve performance on our large and busy DB - advice?

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