On Thu, Jan 21, 2010 at 7:46 PM, Craig Ringer
> > 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*
* "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
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?
In response to
pgsql-performance by date
|Next:||From: Tory M Blue||Date: 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 Stonebanks||Date: 2010-01-22 17:37:56|
|Subject: Re: New server to improve performance on our large and busy DB - advice?|