On Thu, Jan 21, 2010 at 7:46 PM, Craig Ringer
<cr...@postnewspapers.com.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

Reply via email to