pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Option shared_buffers in PostgreSQL 3+ messages / 3 participants [nested] [flat]
* Option shared_buffers in PostgreSQL @ 2011-05-01 11:20 Javier Reyes <c.javier.reyes.e@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Javier Reyes @ 2011-05-01 11:20 UTC (permalink / raw) To: pgsql-admin Hello. I have a server with 4GB of RAM and PostgreSQL 9.0.3 on Centos 5. I'm using pgbench and pgbench-tools to measure performance, using two pgbench-tools queries: select and tpc-b. With default settings of postgresql.conf and select query, I get the following results: Scale: 1, 10, 100, 1000. Transactions per second: 10000, 8800, 7500, 100. (the number of records of the table is scale*100000) I've only increased the option shared_buffer to 256MB (previously had 32 MB) and I get the following results: Scale: 1, 10, 100, 1000. Transactions per second: 10000, 8000, 3200, 30 I don't understand why when the scale is 100 or more in the second benchmark, the performance is so low compared to the first test. The only thing I have done was increase the memory. I've thrown every test twice and the results were similar. In an earlier test, the memory configuration was: shared_buffers = 512 MB maintenance_work_mem = 8MB effective_cache_size = 1GB work_mem = 2MB And the results were similar, even slightly worse. For that reason, I did tests changing options one by one, to know what was the cause of poor performance. And "the winner" is shared_buffers... Thanks. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Option shared_buffers in PostgreSQL @ 2011-05-01 14:11 Lou Picciano <loupicciano@comcast.net> 0 siblings, 0 replies; 3+ messages in thread From: Lou Picciano @ 2011-05-01 14:11 UTC (permalink / raw) To: Javier Reyes <c.javier.reyes.e@gmail.com>; +Cc: pgsql-admin Javier, There has been much discussion of this. Generally - and though it's a bit counter-intuitive - wholesale increase in PostgreSQL's shared buffer won't give you the outcomes you might expect. In short, once there's enough, there's enough. Workmem is another option which behaves along these lines. Reason for this is that PostgreSQL makes _very_ good use of its surrounding memory environment; IE, after a certain point, it's better to focus on tweaking your OS and operating environment. Now, there will probably be some 'optimal' shared memory and workmem settings, given the specifics of your query and execution environment; here's where mileage will vary. But you shouldn't expect a direct and/or linear increase in performance with doubling/quadrupling of shared memory. You'll get answers here from folks well above my paygrade, but I think that's a fair rough outline. (!!) Lou Picciano ----- Original Message ----- From: "Javier Reyes" <c.javier.reyes.e@gmail.com> To: pgsql-admin@postgresql.org Sent: Sunday, May 1, 2011 7:20:15 AM Subject: [ADMIN] Option shared_buffers in PostgreSQL Hello. I have a server with 4GB of RAM and PostgreSQL 9.0.3 on Centos 5. I'm using pgbench and pgbench-tools to measure performance, using two pgbench-tools queries: select and tpc-b. With default settings of postgresql.conf and select query, I get the following results: Scale: 1, 10, 100, 1000. Transactions per second: 10000, 8800, 7500, 100. (the number of records of the table is scale*100000) I've only increased the option shared_buffer to 256MB (previously had 32 MB) and I get the following results: Scale: 1, 10, 100, 1000. Transactions per second: 10000, 8000, 3200, 30 I don't understand why when the scale is 100 or more in the second benchmark, the performance is so low compared to the first test. The only thing I have done was increase the memory. I've thrown every test twice and the results were similar. In an earlier test, the memory configuration was: shared_buffers = 512 MB maintenance_work_mem = 8MB effective_cache_size = 1GB work_mem = 2MB And the results were similar, even slightly worse. For that reason, I did tests changing options one by one, to know what was the cause of poor performance. And "the winner" is shared_buffers... Thanks. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Option shared_buffers in PostgreSQL @ 2011-05-01 16:17 Tom Lane <tgl@sss.pgh.pa.us> parent: Javier Reyes <c.javier.reyes.e@gmail.com> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2011-05-01 16:17 UTC (permalink / raw) To: Javier Reyes <c.javier.reyes.e@gmail.com>; +Cc: pgsql-admin Javier Reyes <c.javier.reyes.e@gmail.com> writes: > I have a server with 4GB of RAM and PostgreSQL 9.0.3 on Centos 5. I'm using > pgbench and pgbench-tools to measure performance, using two pgbench-tools > queries: select and tpc-b. > With default settings of postgresql.conf and select query, I get the > following results: > Scale: 1, 10, 100, 1000. > Transactions per second: 10000, 8800, 7500, 100. These numbers look like complete junk from here. I suspect you were using a number-of-transactions parameter that's too small to give stable answers. If you don't have the value set high enough so that each pgbench run lasts at least a couple of minutes, I wouldn't put any credence in the results. See also the other "good practices" mentioned at the bottom of the pgbench documentation page. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-05-01 16:17 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2011-05-01 11:20 Option shared_buffers in PostgreSQL Javier Reyes <c.javier.reyes.e@gmail.com> 2011-05-01 16:17 ` Tom Lane <tgl@sss.pgh.pa.us> 2011-05-01 14:11 Re: Option shared_buffers in PostgreSQL Lou Picciano <loupicciano@comcast.net>
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