Thread
-
Re: Slow vacuum performance
Andrew McMillan <andrew@catalyst.net.nz> — 2004-06-21T10:11:49Z
On Fri, 2004-06-18 at 19:51 -0700, Patrick Hatcher wrote: > > Thanks! > > My effective_cache_size = 625000 > > I thought that having the shared_buffers above 2k or 3k didn't gain > any performance and may in fact degrade it? Hi Patrick, Quoting from: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html shared_buffers Sets the size of PostgreSQL's' memory buffer where queries are held before being fed into the Kernel buffer of the host system. It's very important to remember that this is only a holding area, and not the total memory available for the server. As such, resist the urge to set this number to a large portion of your RAM, as this will actually degrade performance on many operating systems. Members of the pgsql-performance mailing list have found useful values in the range of 1000-6000, depending on available RAM, database size, and number of concurrent queries. For servers with very large amounts of available RAM (more than 1 GB) increasing this setting to 6-15% or available RAM has worked well for some users. The real analysis of the precise best setting is not fully understood and is more readily determined through testing than calculation. As a rule of thumb, observe shared memory usage of PostgreSQL with tools like ipcs and determine the setting. Remember that this is only half the story. You also need to set effective_cache_size so that postgreSQL will use available memory optimally. Using this conservatively, on an 8G system, 6% would be roughly 60,000 pages - considerably higher than 2-3000... One day when I wasn't timid (well, OK, I was desperate :-), I did see a _dramatic_ performance improvement in a single very narrow activity by setting shared_buffers to 300000 on a 4G RAM system (I was rolling back a transaction involving an update to 2.8 million rows) , but afterwards I set shared_buffers back to 10000, which I have now increased to 20000 on that system. You may also want to look at: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Or indeed, peruse the articles regularly as they appear: http://www.varlena.com/varlena/GeneralBits/ Regards, Andrew McMillan ------------------------------------------------------------------------- Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 Tomorrow will be cancelled due to lack of interest. ------------------------------------------------------------------------- -
Re: Slow vacuum performance
Patrick Hatcher <phatcher@macys.com> — 2004-06-21T15:24:14Z
Thanks! Patrick Hatcher Andrew McMillan <andrew@catalyst.net.nz> Sent by: pgsql-performance-owner@postgresql.org 06/21/04 03:11 AM To Patrick Hatcher <PHatcher@macys.com> cc pgsql-performance@postgresql.org Subject Re: [PERFORM] Slow vacuum performance On Fri, 2004-06-18 at 19:51 -0700, Patrick Hatcher wrote: > > Thanks! > > My effective_cache_size = 625000 > > I thought that having the shared_buffers above 2k or 3k didn't gain > any performance and may in fact degrade it? Hi Patrick, Quoting from: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html shared_buffers Sets the size of PostgreSQL's' memory buffer where queries are held before being fed into the Kernel buffer of the host system. It's very important to remember that this is only a holding area, and not the total memory available for the server. As such, resist the urge to set this number to a large portion of your RAM, as this will actually degrade performance on many operating systems. Members of the pgsql-performance mailing list have found useful values in the range of 1000-6000, depending on available RAM, database size, and number of concurrent queries. For servers with very large amounts of available RAM (more than 1 GB) increasing this setting to 6-15% or available RAM has worked well for some users. The real analysis of the precise best setting is not fully understood and is more readily determined through testing than calculation. As a rule of thumb, observe shared memory usage of PostgreSQL with tools like ipcs and determine the setting. Remember that this is only half the story. You also need to set effective_cache_size so that postgreSQL will use available memory optimally. Using this conservatively, on an 8G system, 6% would be roughly 60,000 pages - considerably higher than 2-3000... One day when I wasn't timid (well, OK, I was desperate :-), I did see a _dramatic_ performance improvement in a single very narrow activity by setting shared_buffers to 300000 on a 4G RAM system (I was rolling back a transaction involving an update to 2.8 million rows) , but afterwards I set shared_buffers back to 10000, which I have now increased to 20000 on that system. You may also want to look at: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Or indeed, peruse the articles regularly as they appear: http://www.varlena.com/varlena/GeneralBits/ Regards, Andrew McMillan ------------------------------------------------------------------------- Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 Tomorrow will be cancelled due to lack of interest. -------------------------------------------------------------------------