From: tv@fuzzy.cz
To: John Williams <jwilliams@42nddesign.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Set of related slow queries
Date: Wed, 8 Jun 2011 13:30:38 +0200
Message-ID: <0c5ef0e27a29aeec40e8cbd988a1dbaf.squirrel@sq.gransy.com> (raw)
In-Reply-To: <BANLkTik-SdA2+-VKgBYhOpfNTXM0mgWEig@mail.gmail.com>
References: <BANLkTik-SdA2+-VKgBYhOpfNTXM0mgWEig@mail.gmail.com>
------------------------------+---------------------------------------------
> shared_buffers | 16MB
> work_mem | 250MB
This seems a bit suspicious. Are you sure you want to keep the
shared_buffers so small and work_mem so large at the same time? There
probably are workloads where this is the right thing to do, but I doubt
this is the case. Why have you set it like this?
I don't have much experience with running Pg on AWS, but I'd try to
increase the shared buffers to say 512MB and decrease the work_mem to 16MB
(or something like that).
Undersized shared_buffers might actually be part of the problem - to
access a row, the page needs to be loaded into shared_buffers. Even though
the I/O is very fast (or the page is already in the filesystem page
cache), there's some locking etc. that needs to be done. When the cache is
small (e.g. 16MB) then the pages need to be removed and read again
frequently. This might be one of the reasons why the CPU is 100% utilized.
> SELECT "logparser_entry"."id" ,
> "logparser_entry"."log_id" ,
> "logparser_entry"."encounter_id" ,
> "logparser_entry"."entry_order" ,
> "logparser_entry"."timestamp" ,
> "logparser_entry"."seconds_since_start" ,
> "logparser_entry"."event_type" ,
> "logparser_entry"."actor_id" ,
> "logparser_entry"."actor_relation" ,
> "logparser_entry"."target_id" ,
> "logparser_entry"."target_relation" ,
> "logparser_entry"."pet_owner_id" ,
> "logparser_entry"."pet_owner_relation" ,
> "logparser_entry"."pet_target_owner_id" ,
> "logparser_entry"."pet_target_owner_relation",
> "logparser_entry"."ability_id" ,
> "logparser_entry"."effective_value" ,
> "logparser_entry"."blocked" ,
> "logparser_entry"."absorbed" ,
> "logparser_entry"."overkill" ,
> "logparser_entry"."overheal" ,
> "logparser_entry"."total_value"
> FROM "logparser_entry"
> WHERE (
> "logparser_entry"."log_id" = 2
> AND NOT
> (
> (
> "logparser_entry"."actor_relation"
> IN (E'Other',
>
> E'N/A')
> AND "logparser_entry"."actor_relation"
> IS NOT NULL
> )
> )
> AND "logparser_entry"."event_type" IN (E'Attack' ,
> E'DoT Tick',
> E'Critical Attack')
> )
> ORDER BY "logparser_entry"."entry_order" ASC
> LIMIT 1
> http://explain.depesz.com/s/vEx
Well, the problem with this is that it needs to evaluate the whole result
set, sort it by "entry_order" and then get the 1st row. And there's no
index on entry_order, so it has to evaluate the whole result set and then
perform a traditional sort.
Try to create an index on the "entry_order" column - that might push it
towards index scan (to be honest I don't know if PostgreSQL knows it can
do it this way, so maybe it won't work).
> SELECT (ROUND(logparser_entry.seconds_since_start / 42)) AS "interval",
> SUM("logparser_entry"."effective_value") AS
> "effective_value__sum"
> FROM "logparser_entry"
> WHERE (
> "logparser_entry"."log_id" = 2
> AND NOT
> (
> (
> "logparser_entry"."actor_relation"
> IN (E'Other',
>
> E'N/A')
> AND "logparser_entry"."actor_relation"
> IS NOT NULL
> )
> )
> AND "logparser_entry"."event_type" IN (E'Attack' ,
> E'DoT Tick',
> E'Critical Attack')
> )
> GROUP BY (ROUND(logparser_entry.seconds_since_start / 42)),
> ROUND(logparser_entry.seconds_since_start / 42)
> ORDER BY "interval" ASC
> http://explain.depesz.com/s/Rhb
Hm, this is probably the best plan possible - not sure how to make it
faster. I'd expect a better performance with larger shared_buffers.
> http://explain.depesz.com/s/JUo
Same as above. Good plan, maybe increase shared_buffers?
> http://explain.depesz.com/s/VZA
Same as above. Good plan, maybe increase shared_buffers.
regards
Tomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, jwilliams@42nddesign.com
Subject: Re: Set of related slow queries
In-Reply-To: <0c5ef0e27a29aeec40e8cbd988a1dbaf.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox