| Lists: | sfpug |
|---|
| From: | Brian Ghidinelli <brian(at)pukkasoft(dot)com> |
|---|---|
| To: | SF Postgres <sfpug(at)postgresql(dot)org> |
| Subject: | Slow 1st queries and RAM size |
| Date: | 2014-01-08 22:23:03 |
| Message-ID: | 52CDCFC7.6010907@pukkasoft.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | sfpug |
Looking at pgfouine, I have slow queries each day, here's one that took
7s for which I just ran explain analyze:
QUERY PLAN
---------------------------------------------------
Sort (cost=32920.99..32922.31 rows=530 width=399) (actual
time=7133.321..7133.423 rows=637 loops=1)
Then, I run it again a min later and I get:
QUERY PLAN
--------------------------------------------------
Sort (cost=32920.99..32922.31 rows=530 width=399) (actual
time=236.774..236.864 rows=637 loops=1)
Is this the data not being in the filesystem cache on the first try and
it being so on the second?
How do I improve this? Right now, the database is just a little bit
bigger than RAM, so I could add more RAM, but that won't help with the
"first hit", will it?
I can improve my query, but will that help the order of magnitude
initial hit or is that purely a disk/hardware issue? The machine is a
dedicated Dell R410 with quad-core processors and 2 x 300GB RAID1
arrays, logs on one, data on the other, behind a Perc H700.
Brian
| From: | David Fetter <david(at)fetter(dot)org> |
|---|---|
| To: | sfpug(at)postgresql(dot)org |
| Subject: | Re: Slow 1st queries and RAM size |
| Date: | 2014-01-08 22:39:11 |
| Message-ID: | 20140108223911.GB22688@fetter.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | sfpug |
On Wed, Jan 08, 2014 at 02:23:03PM -0800, Brian Ghidinelli wrote:
>
> Looking at pgfouine, I have slow queries each day, here's one that
> took 7s for which I just ran explain analyze:
>
> QUERY PLAN
> ---------------------------------------------------
> Sort (cost=32920.99..32922.31 rows=530 width=399) (actual
> time=7133.321..7133.423 rows=637 loops=1)
>
> Then, I run it again a min later and I get:
>
> QUERY PLAN
> --------------------------------------------------
> Sort (cost=32920.99..32922.31 rows=530 width=399) (actual
> time=236.774..236.864 rows=637 loops=1)
>
> Is this the data not being in the filesystem cache on the first try
> and it being so on the second?
Yes.
One tool you might use to mitigate this is pgfincore.
https://github.com/klando/pgfincore
http://villemain.org/projects/pgfincore
Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate