pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVery high effective_cache_size == worse performance?
23+ messages / 9 participants
[nested] [flat]
* Very high effective_cache_size == worse performance?
@ 2010-04-20 17:39 David Kerr <dmk@mr-paradox.net>
2010-04-20 17:41 ` Re: Very high effective_cache_size == worse performance? Joshua D. Drake <jd@commandprompt.com>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:15 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 3 replies; 23+ messages in thread
From: David Kerr @ 2010-04-20 17:39 UTC (permalink / raw)
To: pgsql-performance
Howdy all,
I've got a huge server running just postgres. It's got 48 cores and 256GB of ram. Redhat 5.4, Postgres 8.3.9.
64bit OS. No users currently.
I've got a J2EE app that loads data into the DB, it's got logic behind it so it's not a simple bulk load, so
i don't think we can use copy.
Based on the tuning guides, it set my effective_cache_size to 128GB (1/2 the available memory) on the box.
When I ran my load, it took aproximately 15 hours to do load 20 million records. I thought this was odd because
on a much smaller machine I was able to do that same amount of records in 6 hours.
My initial thought was hardware issues so we got sar, vmstat, etc all running on the box and they didn't give
any indication that we had resource issues.
So I decided to just make the 2 PG config files look the same. (the only change was dropping effective_cache_size
from 128GB to 2GB).
Now the large box performs the same as the smaller box. (which is fine).
incidentally, both tests were starting from a blank database.
Is this expected?
Thanks!
Dave
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
@ 2010-04-20 17:41 ` Joshua D. Drake <jd@commandprompt.com>
2 siblings, 0 replies; 23+ messages in thread
From: Joshua D. Drake @ 2010-04-20 17:41 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: pgsql-performance
On Tue, 2010-04-20 at 10:39 -0700, David Kerr wrote:
> Howdy all,
>
> I've got a huge server running just postgres. It's got 48 cores and 256GB of ram. Redhat 5.4, Postgres 8.3.9.
> 64bit OS. No users currently.
>
> I've got a J2EE app that loads data into the DB, it's got logic behind it so it's not a simple bulk load, so
> i don't think we can use copy.
>
> Based on the tuning guides, it set my effective_cache_size to 128GB (1/2 the available memory) on the box.
>
> When I ran my load, it took aproximately 15 hours to do load 20 million records. I thought this was odd because
> on a much smaller machine I was able to do that same amount of records in 6 hours.
>
> My initial thought was hardware issues so we got sar, vmstat, etc all running on the box and they didn't give
> any indication that we had resource issues.
>
> So I decided to just make the 2 PG config files look the same. (the only change was dropping effective_cache_size
> from 128GB to 2GB).
>
> Now the large box performs the same as the smaller box. (which is fine).
>
> incidentally, both tests were starting from a blank database.
>
> Is this expected?
Without a more complete picture of the configuration, this post doesn't
mean a whole lot. Further, effective_cash_size is not likely to effect a
bulk load at all.
Joshua D. Drake
>
> Thanks!
>
> Dave
>
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
@ 2010-04-20 17:44 ` Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2 siblings, 1 reply; 23+ messages in thread
From: Robert Haas @ 2010-04-20 17:44 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: pgsql-performance
On Tue, Apr 20, 2010 at 1:39 PM, David Kerr <dmk@mr-paradox.net> wrote:
> Howdy all,
>
> I've got a huge server running just postgres. It's got 48 cores and 256GB of ram. Redhat 5.4, Postgres 8.3.9.
> 64bit OS. No users currently.
>
> I've got a J2EE app that loads data into the DB, it's got logic behind it so it's not a simple bulk load, so
> i don't think we can use copy.
>
> Based on the tuning guides, it set my effective_cache_size to 128GB (1/2 the available memory) on the box.
>
> When I ran my load, it took aproximately 15 hours to do load 20 million records. I thought this was odd because
> on a much smaller machine I was able to do that same amount of records in 6 hours.
>
> My initial thought was hardware issues so we got sar, vmstat, etc all running on the box and they didn't give
> any indication that we had resource issues.
>
> So I decided to just make the 2 PG config files look the same. (the only change was dropping effective_cache_size
> from 128GB to 2GB).
>
> Now the large box performs the same as the smaller box. (which is fine).
>
> incidentally, both tests were starting from a blank database.
>
> Is this expected?
Lowering effective_cache_size tends to discourage the planner from
using a nested-loop-with-inner-indexscan plan - that's it.
What may be happening is that you may be loading data into some tables
and then running a query against those tables before the autovacuum
daemon has a chance to analyze them. I suspect that if you enable
some logging you'll find that one of those queries is really, really
slow, and that (by happy coincidence) discouraging it from using the
index it thinks it should use happens to produce a better plan. What
you should probably do is, for each table that you bulk load and then
query, insert a manual ANALYZE between the two.
...Robert
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
@ 2010-04-20 18:03 ` David Kerr <dmk@mr-paradox.net>
2010-04-20 18:12 ` Re: Very high effective_cache_size == worse performance? Nikolas Everett <nik9000@gmail.com>
2010-04-20 18:15 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:17 ` Re: Very high effective_cache_size == worse performance? Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 3 replies; 23+ messages in thread
From: David Kerr @ 2010-04-20 18:03 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-performance
On Tue, Apr 20, 2010 at 01:44:18PM -0400, Robert Haas wrote:
- On Tue, Apr 20, 2010 at 1:39 PM, David Kerr <dmk@mr-paradox.net> wrote:
- > My initial thought was hardware issues so we got sar, vmstat, etc all running on the box and they didn't give
- > any indication that we had resource issues.
- >
- > So I decided to just make the 2 PG config files look the same. (the only change was dropping effective_cache_size
- > from 128GB to 2GB).
- >
- > Now the large box performs the same as the smaller box. (which is fine).
- >
- > incidentally, both tests were starting from a blank database.
- >
- > Is this expected?
-
- Lowering effective_cache_size tends to discourage the planner from
- using a nested-loop-with-inner-indexscan plan - that's it.
-
- What may be happening is that you may be loading data into some tables
- and then running a query against those tables before the autovacuum
- daemon has a chance to analyze them. I suspect that if you enable
- some logging you'll find that one of those queries is really, really
- slow, and that (by happy coincidence) discouraging it from using the
- index it thinks it should use happens to produce a better plan. What
- you should probably do is, for each table that you bulk load and then
- query, insert a manual ANALYZE between the two.
-
- ...Robert
-
that thought occured to me while I was testing this. I ran a vacuumdb -z
on my database during the load and it didn't impact performance at all.
Incidentally the code is written to work like this :
while (read X lines in file){
Process those lines.
write lines to DB.
}
So i would generally expect to get the benefits of the updated staticis
once the loop ended. no? (would prepared statements affect that possibly?)
Also, while I was debugging the problem, I did load a 2nd file into the DB
ontop of one that had been loaded. So the statistics almost certinaly should
have been decent at that point.
I did turn on log_min_duration_statement but that caused performance to be unbearable,
but i could turn it on again if it would help.
Dave
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
@ 2010-04-20 18:12 ` Nikolas Everett <nik9000@gmail.com>
2010-04-20 18:19 ` Re: Very high effective_cache_size == worse performance? Kris Jurka <books@ejurka.com>
2010-04-20 18:20 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2 siblings, 2 replies; 23+ messages in thread
From: Nikolas Everett @ 2010-04-20 18:12 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Tue, Apr 20, 2010 at 2:03 PM, David Kerr <dmk@mr-paradox.net> wrote:
> that thought occured to me while I was testing this. I ran a vacuumdb -z
> on my database during the load and it didn't impact performance at all.
>
> Incidentally the code is written to work like this :
>
> while (read X lines in file){
> Process those lines.
> write lines to DB.
> }
>
> So i would generally expect to get the benefits of the updated staticis
> once the loop ended. no? (would prepared statements affect that possibly?)
>
> Also, while I was debugging the problem, I did load a 2nd file into the DB
> ontop of one that had been loaded. So the statistics almost certinaly
> should
> have been decent at that point.
>
> I did turn on log_min_duration_statement but that caused performance to be
> unbearable,
> but i could turn it on again if it would help.
>
> Dave
You can absolutely use copy if you like but you need to use a non-standard
jdbc driver: kato.iki.fi/sw/db/postgresql/jdbc/copy/. I've used it in the
past and it worked.
Is the whole thing going in in one transaction? I'm reasonably sure
statistics aren't kept for uncommited transactions.
For inserts the prepared statements can only help. For selects they can
hurt because eventually the JDBC driver will turn them into back end
prepared statements that are only planned once. The price here is that that
plan may not be the best plan for the data that you throw at it.
What was log_min_duration_statement logging that it killed performance?
--Nik
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:12 ` Re: Very high effective_cache_size == worse performance? Nikolas Everett <nik9000@gmail.com>
@ 2010-04-20 18:19 ` Kris Jurka <books@ejurka.com>
1 sibling, 0 replies; 23+ messages in thread
From: Kris Jurka @ 2010-04-20 18:19 UTC (permalink / raw)
To: Nikolas Everett <nik9000@gmail.com>; +Cc: David Kerr <dmk@mr-paradox.net>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Tue, 20 Apr 2010, Nikolas Everett wrote:
> You can absolutely use copy if you like but you need to use a non-standard
> jdbc driver: kato.iki.fi/sw/db/postgresql/jdbc/copy/. I've used it in the
> past and it worked.
Copy support has been added to the 8.4 driver.
Kris Jurka
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:12 ` Re: Very high effective_cache_size == worse performance? Nikolas Everett <nik9000@gmail.com>
@ 2010-04-20 18:20 ` David Kerr <dmk@mr-paradox.net>
2010-04-20 18:23 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: David Kerr @ 2010-04-20 18:20 UTC (permalink / raw)
To: Nikolas Everett <nik9000@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Tue, Apr 20, 2010 at 02:12:15PM -0400, Nikolas Everett wrote:
- On Tue, Apr 20, 2010 at 2:03 PM, David Kerr <dmk@mr-paradox.net> wrote:
-
- > that thought occured to me while I was testing this. I ran a vacuumdb -z
- > on my database during the load and it didn't impact performance at all.
- >
- > Incidentally the code is written to work like this :
- >
- > while (read X lines in file){
- > Process those lines.
- > write lines to DB.
- > }
- >
- > So i would generally expect to get the benefits of the updated staticis
- > once the loop ended. no? (would prepared statements affect that possibly?)
- >
- > Also, while I was debugging the problem, I did load a 2nd file into the DB
- > ontop of one that had been loaded. So the statistics almost certinaly
- > should
- > have been decent at that point.
- >
- > I did turn on log_min_duration_statement but that caused performance to be
- > unbearable,
- > but i could turn it on again if it would help.
- >
- > Dave
-
-
- You can absolutely use copy if you like but you need to use a non-standard
- jdbc driver: kato.iki.fi/sw/db/postgresql/jdbc/copy/. I've used it in the
- past and it worked.
-
- Is the whole thing going in in one transaction? I'm reasonably sure
- statistics aren't kept for uncommited transactions.
-
- For inserts the prepared statements can only help. For selects they can
- hurt because eventually the JDBC driver will turn them into back end
- prepared statements that are only planned once. The price here is that that
- plan may not be the best plan for the data that you throw at it.
-
- What was log_min_duration_statement logging that it killed performance?
-
- --Nik
Good to know about the jdbc-copy. but this is a huge project and the load is
just one very very tiny component, I don't think we could introduce anything
new to assist that.
It's not all in one tx. I don't have visibility to the code to determine how
it's broken down, but most likely each while loop is a tx.
I set it to log all statements (i.e., = 0.). that doubled the load time from
~15 to ~30 hours. I could, of course, be more granular if it would be helpful.
Dave
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:12 ` Re: Very high effective_cache_size == worse performance? Nikolas Everett <nik9000@gmail.com>
2010-04-20 18:20 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
@ 2010-04-20 18:23 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 18:28 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
0 siblings, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2010-04-20 18:23 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: Nikolas Everett <nik9000@gmail.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Tue, Apr 20, 2010 at 12:20 PM, David Kerr <dmk@mr-paradox.net> wrote:
> On Tue, Apr 20, 2010 at 02:12:15PM -0400, Nikolas Everett wrote:
> - On Tue, Apr 20, 2010 at 2:03 PM, David Kerr <dmk@mr-paradox.net> wrote:
> -
> - > that thought occured to me while I was testing this. I ran a vacuumdb -z
> - > on my database during the load and it didn't impact performance at all.
> - >
> - > Incidentally the code is written to work like this :
> - >
> - > while (read X lines in file){
> - > Process those lines.
> - > write lines to DB.
> - > }
> - >
> - > So i would generally expect to get the benefits of the updated staticis
> - > once the loop ended. no? (would prepared statements affect that possibly?)
> - >
> - > Also, while I was debugging the problem, I did load a 2nd file into the DB
> - > ontop of one that had been loaded. So the statistics almost certinaly
> - > should
> - > have been decent at that point.
> - >
> - > I did turn on log_min_duration_statement but that caused performance to be
> - > unbearable,
> - > but i could turn it on again if it would help.
> - >
> - > Dave
> -
> -
> - You can absolutely use copy if you like but you need to use a non-standard
> - jdbc driver: kato.iki.fi/sw/db/postgresql/jdbc/copy/. I've used it in the
> - past and it worked.
> -
> - Is the whole thing going in in one transaction? I'm reasonably sure
> - statistics aren't kept for uncommited transactions.
> -
> - For inserts the prepared statements can only help. For selects they can
> - hurt because eventually the JDBC driver will turn them into back end
> - prepared statements that are only planned once. The price here is that that
> - plan may not be the best plan for the data that you throw at it.
> -
> - What was log_min_duration_statement logging that it killed performance?
> -
> - --Nik
>
> Good to know about the jdbc-copy. but this is a huge project and the load is
> just one very very tiny component, I don't think we could introduce anything
> new to assist that.
>
> It's not all in one tx. I don't have visibility to the code to determine how
> it's broken down, but most likely each while loop is a tx.
>
> I set it to log all statements (i.e., = 0.). that doubled the load time from
> ~15 to ~30 hours. I could, of course, be more granular if it would be helpful.
So are you logging to the same drive that has pg_xlog and your
data/base directory on this machine?
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:12 ` Re: Very high effective_cache_size == worse performance? Nikolas Everett <nik9000@gmail.com>
2010-04-20 18:20 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:23 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-04-20 18:28 ` David Kerr <dmk@mr-paradox.net>
2010-04-20 18:30 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 19:24 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 20:26 ` Re: Very high effective_cache_size == worse performance? Greg Smith <greg@2ndquadrant.com>
0 siblings, 3 replies; 23+ messages in thread
From: David Kerr @ 2010-04-20 18:28 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Nikolas Everett <nik9000@gmail.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Tue, Apr 20, 2010 at 12:23:51PM -0600, Scott Marlowe wrote:
- On Tue, Apr 20, 2010 at 12:20 PM, David Kerr <dmk@mr-paradox.net> wrote:
- > On Tue, Apr 20, 2010 at 02:12:15PM -0400, Nikolas Everett wrote:
- > - On Tue, Apr 20, 2010 at 2:03 PM, David Kerr <dmk@mr-paradox.net> wrote:
- > -
- > - You can absolutely use copy if you like but you need to use a non-standard
- > - jdbc driver: kato.iki.fi/sw/db/postgresql/jdbc/copy/. I've used it in the
- > - past and it worked.
- > -
- > - Is the whole thing going in in one transaction? I'm reasonably sure
- > - statistics aren't kept for uncommited transactions.
- > -
- > - For inserts the prepared statements can only help. For selects they can
- > - hurt because eventually the JDBC driver will turn them into back end
- > - prepared statements that are only planned once. The price here is that that
- > - plan may not be the best plan for the data that you throw at it.
- > -
- > - What was log_min_duration_statement logging that it killed performance?
- > -
- > - --Nik
- >
- > Good to know about the jdbc-copy. but this is a huge project and the load is
- > just one very very tiny component, I don't think we could introduce anything
- > new to assist that.
- >
- > It's not all in one tx. I don't have visibility to the code to determine how
- > it's broken down, but most likely each while loop is a tx.
- >
- > I set it to log all statements (i.e., = 0.). that doubled the load time from
- > ~15 to ~30 hours. I could, of course, be more granular if it would be helpful.
-
- So are you logging to the same drive that has pg_xlog and your
- data/base directory on this machine?
-
the db, xlog and logs are all on separate areas of the SAN.
separate I/O controllers, etc on the SAN. it's setup well, I wouldn't expect
contention there.
I'm logging via syslog, I've had trouble with that before. when i moved to syslog-ng
on my dev environments that mostly resoved the probelm for me. but these machines
still have vanilla syslog.
Dave
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:12 ` Re: Very high effective_cache_size == worse performance? Nikolas Everett <nik9000@gmail.com>
2010-04-20 18:20 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:23 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 18:28 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
@ 2010-04-20 18:30 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 18:32 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2 siblings, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2010-04-20 18:30 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: Nikolas Everett <nik9000@gmail.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Tue, Apr 20, 2010 at 12:28 PM, David Kerr <dmk@mr-paradox.net> wrote:
>
> I'm logging via syslog, I've had trouble with that before. when i moved to syslog-ng
> on my dev environments that mostly resoved the probelm for me. but these machines
> still have vanilla syslog.
Yea, I almost always log directly via stdout on production machines
because of that.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:12 ` Re: Very high effective_cache_size == worse performance? Nikolas Everett <nik9000@gmail.com>
2010-04-20 18:20 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:23 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 18:28 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:30 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-04-20 18:32 ` David Kerr <dmk@mr-paradox.net>
0 siblings, 0 replies; 23+ messages in thread
From: David Kerr @ 2010-04-20 18:32 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Nikolas Everett <nik9000@gmail.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Tue, Apr 20, 2010 at 12:30:14PM -0600, Scott Marlowe wrote:
- On Tue, Apr 20, 2010 at 12:28 PM, David Kerr <dmk@mr-paradox.net> wrote:
- >
- > I'm logging via syslog, I've had trouble with that before. when i moved to syslog-ng
- > on my dev environments that mostly resoved the probelm for me. but these machines
- > still have vanilla syslog.
-
- Yea, I almost always log directly via stdout on production machines
- because of that.
-
Ah well good to know i'm not the only one =)
I'll get the query info. I've got a twin system that I can use and abuse.
Dave
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:12 ` Re: Very high effective_cache_size == worse performance? Nikolas Everett <nik9000@gmail.com>
2010-04-20 18:20 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:23 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 18:28 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
@ 2010-04-20 19:24 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 23+ messages in thread
From: Scott Marlowe @ 2010-04-20 19:24 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: Nikolas Everett <nik9000@gmail.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Tue, Apr 20, 2010 at 12:28 PM, David Kerr <dmk@mr-paradox.net> wrote:
> On Tue, Apr 20, 2010 at 12:23:51PM -0600, Scott Marlowe wrote:
> - So are you logging to the same drive that has pg_xlog and your
> - data/base directory on this machine?
> -
>
> the db, xlog and logs are all on separate areas of the SAN.
>
> separate I/O controllers, etc on the SAN. it's setup well, I wouldn't expect
> contention there.
Same xkb/s gigabit connection?
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:12 ` Re: Very high effective_cache_size == worse performance? Nikolas Everett <nik9000@gmail.com>
2010-04-20 18:20 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:23 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 18:28 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
@ 2010-04-20 20:26 ` Greg Smith <greg@2ndquadrant.com>
2010-04-20 20:39 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2 siblings, 1 reply; 23+ messages in thread
From: Greg Smith @ 2010-04-20 20:26 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Nikolas Everett <nik9000@gmail.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
David Kerr wrote:
> the db, xlog and logs are all on separate areas of the SAN.
> separate I/O controllers, etc on the SAN. it's setup well, I wouldn't expect
> contention there.
>
Just because you don't expect it doesn't mean it's not there.
Particularly something as complicated as a SAN setup, presuming anything
without actually benchmarking it is a recipe for fuzzy diagnostics when
problems pop up. If you took anyone's word that your SAN has good
performance without confirming it yourself, that's a path that's lead
many to trouble.
Anyway, as Robert already stated, effective_cache_size only impacts how
some very specific types of queries are executed; that's it. If there's
some sort of query behavior involved in your load, maybe that has
something to do with your slowdown, but it doesn't explain general slow
performance. Other possibilities include that something else changed
when you reloaded the server as part of that, or it's a complete
coincidence--perhaps autoanalyze happened to finish at around the same
time and it lead to new plans.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:12 ` Re: Very high effective_cache_size == worse performance? Nikolas Everett <nik9000@gmail.com>
2010-04-20 18:20 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:23 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 18:28 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 20:26 ` Re: Very high effective_cache_size == worse performance? Greg Smith <greg@2ndquadrant.com>
@ 2010-04-20 20:39 ` David Kerr <dmk@mr-paradox.net>
2010-04-21 06:45 ` Re: Very high effective_cache_size == worse performance? Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 23+ messages in thread
From: David Kerr @ 2010-04-20 20:39 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Nikolas Everett <nik9000@gmail.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Tue, Apr 20, 2010 at 04:26:52PM -0400, Greg Smith wrote:
- David Kerr wrote:
- >the db, xlog and logs are all on separate areas of the SAN.
- >separate I/O controllers, etc on the SAN. it's setup well, I wouldn't
- >expect
- >contention there.
- >
-
- Just because you don't expect it doesn't mean it's not there.
- Particularly something as complicated as a SAN setup, presuming anything
- without actually benchmarking it is a recipe for fuzzy diagnostics when
- problems pop up. If you took anyone's word that your SAN has good
- performance without confirming it yourself, that's a path that's lead
- many to trouble.
that's actually what I'm doing, performance testing this environment.
everything's on the table for me at this point.
- Anyway, as Robert already stated, effective_cache_size only impacts how
- some very specific types of queries are executed; that's it. If there's
- some sort of query behavior involved in your load, maybe that has
- something to do with your slowdown, but it doesn't explain general slow
- performance. Other possibilities include that something else changed
- when you reloaded the server as part of that, or it's a complete
- coincidence--perhaps autoanalyze happened to finish at around the same
- time and it lead to new plans.
Ok that's good to know. I didn't think it would have any impact, and was
surprised when it appeared to.
I just finished running the test on another machine and wasn't able to
reproduce the problem, so that's good news in some ways. But now i'm back
to the drawing board.
I don't think it's anything in the Db that's causing it. ( drop and re-create
the db between tests) I actually suspect a hardware issue somewhere.
Dave
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:12 ` Re: Very high effective_cache_size == worse performance? Nikolas Everett <nik9000@gmail.com>
2010-04-20 18:20 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:23 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 18:28 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 20:26 ` Re: Very high effective_cache_size == worse performance? Greg Smith <greg@2ndquadrant.com>
2010-04-20 20:39 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
@ 2010-04-21 06:45 ` Greg Smith <greg@2ndquadrant.com>
0 siblings, 0 replies; 23+ messages in thread
From: Greg Smith @ 2010-04-21 06:45 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Nikolas Everett <nik9000@gmail.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
David Kerr wrote:
> I don't think it's anything in the Db that's causing it. ( drop and re-create
> the db between tests) I actually suspect a hardware issue somewhere.
>
You might find my "Database Hardware Benchmarking" talk, available at
http://projects.2ndquadrant.com/talks , useful to help sort out what's
good and bad on each server, and correspondingly what'd different
between the two. Many of the ideas there came from fighting with SAN
hardware that didn't do what I expected.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
@ 2010-04-20 18:15 ` Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:47 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2 siblings, 1 reply; 23+ messages in thread
From: Robert Haas @ 2010-04-20 18:15 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: pgsql-performance
On Tue, Apr 20, 2010 at 2:03 PM, David Kerr <dmk@mr-paradox.net> wrote:
> that thought occured to me while I was testing this. I ran a vacuumdb -z
> on my database during the load and it didn't impact performance at all.
The window to run ANALYZE usefully is pretty short. If you run it
before the load is complete, your stats will be wrong. If you run it
after the select statements that hit the table are planned, the
updated stats won't arrive in time to do any good.
> I did turn on log_min_duration_statement but that caused performance to be unbearable,
> but i could turn it on again if it would help.
I think you need to find a way to identify exactly which query is
running slowly. You could sit there and run "select * from
pg_stat_activity", or turn on log_min_duration_statement, or have your
application print out timestamps at key points, or some other
method...
...Robert
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:15 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
@ 2010-04-20 18:47 ` David Kerr <dmk@mr-paradox.net>
2010-04-20 19:22 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: David Kerr @ 2010-04-20 18:47 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-performance
On Tue, Apr 20, 2010 at 02:15:19PM -0400, Robert Haas wrote:
- On Tue, Apr 20, 2010 at 2:03 PM, David Kerr <dmk@mr-paradox.net> wrote:
- > that thought occured to me while I was testing this. I ran a vacuumdb -z
- > on my database during the load and it didn't impact performance at all.
-
- The window to run ANALYZE usefully is pretty short. If you run it
- before the load is complete, your stats will be wrong. If you run it
- after the select statements that hit the table are planned, the
- updated stats won't arrive in time to do any good.
right, but i'm loading 20 million records in 1000 record increments. so
the analyze should affect all subsequent increments, no?
- > I did turn on log_min_duration_statement but that caused performance to be unbearable,
- > but i could turn it on again if it would help.
-
- I think you need to find a way to identify exactly which query is
- running slowly. You could sit there and run "select * from
- pg_stat_activity", or turn on log_min_duration_statement, or have your
- application print out timestamps at key points, or some other
- method...
I'm on it.
Dave
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:15 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:47 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
@ 2010-04-20 19:22 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-22 01:21 ` Re: Very high effective_cache_size == worse performance? Scott Carey <scott@richrelevance.com>
0 siblings, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2010-04-20 19:22 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Tue, Apr 20, 2010 at 12:47 PM, David Kerr <dmk@mr-paradox.net> wrote:
> On Tue, Apr 20, 2010 at 02:15:19PM -0400, Robert Haas wrote:
> - On Tue, Apr 20, 2010 at 2:03 PM, David Kerr <dmk@mr-paradox.net> wrote:
> - > that thought occured to me while I was testing this. I ran a vacuumdb -z
> - > on my database during the load and it didn't impact performance at all.
> -
> - The window to run ANALYZE usefully is pretty short. If you run it
> - before the load is complete, your stats will be wrong. If you run it
> - after the select statements that hit the table are planned, the
> - updated stats won't arrive in time to do any good.
>
> right, but i'm loading 20 million records in 1000 record increments. so
> the analyze should affect all subsequent increments, no?
I keep thinking FK checks are taking a long time because they aren't
cached because in import they went through the ring buffer in pg or
some other way aren't in a buffer but large effective cache size says
it's 99.99% chance or better that it's in cache, and chooses a poor
plan to look them up. Just a guess.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:15 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:47 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 19:22 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-04-22 01:21 ` Scott Carey <scott@richrelevance.com>
0 siblings, 0 replies; 23+ messages in thread
From: Scott Carey @ 2010-04-22 01:21 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: David Kerr <dmk@mr-paradox.net>; Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Apr 20, 2010, at 12:22 PM, Scott Marlowe wrote:
> On Tue, Apr 20, 2010 at 12:47 PM, David Kerr <dmk@mr-paradox.net> wrote:
>> On Tue, Apr 20, 2010 at 02:15:19PM -0400, Robert Haas wrote:
>> - On Tue, Apr 20, 2010 at 2:03 PM, David Kerr <dmk@mr-paradox.net> wrote:
>> - > that thought occured to me while I was testing this. I ran a vacuumdb -z
>> - > on my database during the load and it didn't impact performance at all.
>> -
>> - The window to run ANALYZE usefully is pretty short. If you run it
>> - before the load is complete, your stats will be wrong. If you run it
>> - after the select statements that hit the table are planned, the
>> - updated stats won't arrive in time to do any good.
>>
>> right, but i'm loading 20 million records in 1000 record increments. so
>> the analyze should affect all subsequent increments, no?
>
> I keep thinking FK checks are taking a long time because they aren't
> cached because in import they went through the ring buffer in pg or
> some other way aren't in a buffer but large effective cache size says
> it's 99.99% chance or better that it's in cache, and chooses a poor
> plan to look them up. Just a guess.
>
Yeah, I was thinking the same thing.
If possible make sure the table either has no indexes and FK's or only the minimum required (PK?) while doing the load, then add the indexes and FK's later.
Whether this is possible depends on what the schema is and what must be known by the app to load the data, but if you can do it its a huge win.
Of course, if its not all in one transaction and there is any other concurrency going on that could be a bad idea. Or, if this is not a load on a fresh table but an append/update it may not be possible to drop some of the indexes first.
Generally speaking, a load on a table without an index followed by index creation is at least twice as fast, and often 5x as fast or more. This is less true if each row is an individual insert and batching or 'insert into foo values (a, b, c, ...), (a2, b2, c2, ...)' multiple row syntax is not used.
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
@ 2010-04-20 18:17 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-04-20 18:46 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2 siblings, 1 reply; 23+ messages in thread
From: Kevin Grittner @ 2010-04-20 18:17 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; David Kerr <dmk@mr-paradox.net>; +Cc: pgsql-performance
David Kerr <dmk@mr-paradox.net> wrote:
> Incidentally the code is written to work like this :
>
> while (read X lines in file){
> Process those lines.
> write lines to DB.
> }
Unless you're selecting from multiple database tables in one query,
effective_cache_size shouldn't make any difference. There's
probably some other reason for the difference.
A couple wild shots in the dark:
Any chance the source files were cached the second time, but not the
first?
Do you have a large checkpoint_segments setting, and did the second
run without a new initdb?
-Kevin
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:44 ` Re: Very high effective_cache_size == worse performance? Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` Re: Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:17 ` Re: Very high effective_cache_size == worse performance? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-04-20 18:46 ` David Kerr <dmk@mr-paradox.net>
0 siblings, 0 replies; 23+ messages in thread
From: David Kerr @ 2010-04-20 18:46 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Tue, Apr 20, 2010 at 01:17:02PM -0500, Kevin Grittner wrote:
- David Kerr <dmk@mr-paradox.net> wrote:
-
- > Incidentally the code is written to work like this :
- >
- > while (read X lines in file){
- > Process those lines.
- > write lines to DB.
- > }
-
- Unless you're selecting from multiple database tables in one query,
- effective_cache_size shouldn't make any difference. There's
- probably some other reason for the difference.
-
- A couple wild shots in the dark:
-
- Any chance the source files were cached the second time, but not the
- first?
-
- Do you have a large checkpoint_segments setting, and did the second
- run without a new initdb?
-
- -Kevin
no i don't think the files would be cached the 2nd time. I ran it multiple times
and got the same performance each time. It wasn't until i changed the parameter
that performance got better.
I've got checkpoint_segments = 300
Dave
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
@ 2010-04-20 18:15 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 18:20 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2010-04-20 18:15 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: pgsql-performance
On Tue, Apr 20, 2010 at 11:39 AM, David Kerr <dmk@mr-paradox.net> wrote:
> Howdy all,
>
> I've got a huge server running just postgres. It's got 48 cores and 256GB of ram. Redhat 5.4, Postgres 8.3.9.
> 64bit OS. No users currently.
What's your IO subsystem look like? What did vmstat actually say?
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Very high effective_cache_size == worse performance?
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 18:15 ` Re: Very high effective_cache_size == worse performance? Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-04-20 18:20 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Scott Marlowe @ 2010-04-20 18:20 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: pgsql-performance
On Tue, Apr 20, 2010 at 12:15 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Apr 20, 2010 at 11:39 AM, David Kerr <dmk@mr-paradox.net> wrote:
>> Howdy all,
>>
>> I've got a huge server running just postgres. It's got 48 cores and 256GB of ram. Redhat 5.4, Postgres 8.3.9.
>> 64bit OS. No users currently.
>
> What's your IO subsystem look like? What did vmstat actually say?
Note that on a 48 core machine, if vmstat shows 2% wait and 98% idle
then you'd be 100% io bound, because it's % of total CPU. iostat -x
10 will give a better view of how hard your disks are working, and if
they're the issue.
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2010-04-22 01:21 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-20 17:39 Very high effective_cache_size == worse performance? David Kerr <dmk@mr-paradox.net>
2010-04-20 17:41 ` Joshua D. Drake <jd@commandprompt.com>
2010-04-20 17:44 ` Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:03 ` David Kerr <dmk@mr-paradox.net>
2010-04-20 18:12 ` Nikolas Everett <nik9000@gmail.com>
2010-04-20 18:19 ` Kris Jurka <books@ejurka.com>
2010-04-20 18:20 ` David Kerr <dmk@mr-paradox.net>
2010-04-20 18:23 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 18:28 ` David Kerr <dmk@mr-paradox.net>
2010-04-20 18:30 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 18:32 ` David Kerr <dmk@mr-paradox.net>
2010-04-20 19:24 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 20:26 ` Greg Smith <greg@2ndquadrant.com>
2010-04-20 20:39 ` David Kerr <dmk@mr-paradox.net>
2010-04-21 06:45 ` Greg Smith <greg@2ndquadrant.com>
2010-04-20 18:15 ` Robert Haas <robertmhaas@gmail.com>
2010-04-20 18:47 ` David Kerr <dmk@mr-paradox.net>
2010-04-20 19:22 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-22 01:21 ` Scott Carey <scott@richrelevance.com>
2010-04-20 18:17 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-04-20 18:46 ` David Kerr <dmk@mr-paradox.net>
2010-04-20 18:15 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 18:20 ` Scott Marlowe <scott.marlowe@gmail.com>
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