Обсуждение: confused of buffers and memory settings
Hi, we're quite new to Postgres and we're going to start using Postgres8.3 on Opensuse10.3 64 bit. We have a dedicated dbserver with 8GB RAM, and now I'm not sure how to deal with the memory/buffer settings. The db will be accessed heavily (~20 requests/sec.) with a read/write ration of 50:50, yes, a lot of write activity. I thought of setting "shared_buffers" to 750000 (~6GB) but how depends this on the kernel buffer setting in /etc/sysctl.conf (what is the interaction between these two settings?). I know the variable "shmmax" can be defined, but currently there's no such entry. The meaning of "work_mem" / "maintenance_work_mem" and "wal_buffers" is also not clear. The maintenance_work_mem influences the size of the WAL logs..?!? What else are "top performance related" options for the usage scenario I described earlier ? any help appreciated....GERD...
Hi, Gerd König a écrit : > we're quite new to Postgres and we're going to start using Postgres8.3 > on Opensuse10.3 64 bit. We have a dedicated dbserver with 8GB RAM, and > now I'm not sure how to deal with the memory/buffer settings. The db > will be accessed heavily (~20 requests/sec.) with a read/write ration of > 50:50, yes, a lot of write activity. > > I thought of setting "shared_buffers" to 750000 (~6GB) but how depends > this on the kernel buffer setting in /etc/sysctl.conf (what is the > interaction between these two settings?). > I know the variable "shmmax" can be defined, but currently there's no > such entry. > shared_buffers should be set to 1/4 of your available RAM. It's a start value, you can tweak it after that, but you would probably be better advised to use 2GB to begin with. > The meaning of "work_mem" / "maintenance_work_mem" and "wal_buffers" is > also not clear. The maintenance_work_mem influences the size of the WAL > logs..?!? > work_mem is used for sort and group operations (for example, ORDER BY). It's not part of the shared_buffers memory and it will be used by every postgres process. So you better have small values, something between 1 MB and 64 MB. maintenance_work_mem is used for maintenance operations (VACUUM, CREATE INDEX and another one I don't remember now). You can use bigger values because you won't have many operations of this kind that will happen at the same time. Probably 256 MB would be a good start value. > What else are "top performance related" options for the usage scenario I > described earlier ? > Checkpoint and WAL settings are important ones. FSM size is another one. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
On Mittwoch, 23. April 2008 Gerd König wrote: > The db will be accessed heavily (~20 requests/sec.) > with a read/write ration of 50:50, yes, a lot of write activity. Not really heavy, I would say, but maybe your transactions are very big and produce a lot of I/O. > I thought of setting "shared_buffers" to 750000 (~6GB) but how > depends this on the kernel buffer setting in /etc/sysctl.conf (what > is the interaction between these two settings?). > I know the variable "shmmax" can be defined, but currently there's no > such entry. # Shared Mem Maximum example: kernel.shmmax = 950123456 # do not allow memory overcommit to prevent database crashes vm.overcommit_memory=2 > The meaning of "work_mem" / "maintenance_work_mem" and "wal_buffers" > is also not clear. The maintenance_work_mem influences the size of > the WAL logs..?!? > What else are "top performance related" options for the usage > scenario I described earlier ? http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0676/846 914 666 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: www.keyserver.net Key-ID: 1C1209B4
Вложения
Le mercredi 23 avril 2008, Gerd König a écrit : > we're quite new to Postgres and we're going to start using Postgres8.3 [...] > What else are "top performance related" options for the usage scenario I > described earlier ? Did you read this document already? http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm -- dim