Strahinja Kustudic wrote: >> I have a Postgresql 9.1 dedicated server with 16 cores, 96GB RAM and RAID10 15K SCSI drives >> which is runing Centos 6.2 x64. This server is mainly used for inserting/updating large amounts of >> data via copy/insert/update commands, and seldom for running select queries. >> >> Here are the relevant configuration parameters I changed: >> >> shared_buffers = 10GB >> effective_cache_size = 90GB >> work_mem = 32MB >> maintenance_work_mem = 512MB >> checkpoint_segments = 64 >> checkpoint_completion_target = 0.8 >> >> My biggest concern are shared_buffers and effective_cache_size, should I increase shared_buffers >> and decrease effective_cache_size? I read that values above 10GB for shared_buffers give lower >> performance, than smaller amounts? >> >> free is currently reporting (during the loading of data): >> >> $ free -m >> total used free shared buffers cached >> Mem: 96730 96418 311 0 71 93120 >> -/+ buffers/cache: 3227 93502 >> Swap: 21000 51 20949 >> >> So it did a little swapping, but only minor, still I should probably decrease shared_buffers so >> there is no swapping at all. > Hm, I just notices that shared_buffers + effective_cache_size = 100 > 96GB, which can't be right. > effective_cache_size should probably be 80GB. I think you misunderstood effective_cache_size. It does not influence memory usage, but query planning. It gives the planner an idea of how much memory there is for caching data, including the filesystem cache. So a good value for effective_cache_size would be total memory minus what the OS and others need minus what private memory the PostgreSQL backends need. The latter can be estimated as work_mem times max_connections. To avoid swapping, consider setting vm.swappiness to 0 in /etc/sysctl.conf. 10GB of shared_buffers is quite a lot. If you can run realistic performance tests, start with a lower value and increase until you cannot see a notable improvement. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
