Thanks for your help everyone. I set: shared_buffers = 4GB effective_cache_size = 72GB work_mem = 128MB maintenance_work_mem = 4GB checkpoint_segments = 64 checkpoint_completion_target = 0.9 random_page_cost = 3.5 cpu_tuple_cost = 0.05
Where can I get the values for random_page_cost and for cpu_tuple_cost where they depend on hardware? I know that for SSDs random_page_cost should be 1.0, but I have no idea what value this should be for different types of drives. I also set: vm.swappiness = 0 vm.overcommit_memory = 2 vm.overcommit_ratio = 50 But I don't understand why do I need to set overcommit_memory, since I only have postgres running, nothing else would allocate memory anyway? I will set readahead later, first I want to see how is this working. Strahinja Kustudić | System Engineer | Nordeus On Wed, Oct 10, 2012 at 10:52 AM, Julien Cigar <jci...@ulb.ac.be> wrote: > On 10/10/2012 10:30, Strahinja Kustudić wrote: > > Thanks for very fast replies everyone :) > > @Laurenz I know that effective cache size is only used for the query > planner, what I was saying is that if I tell it that it can have 90GB > cached items, that is not trues, since the OS and Postgres process itself > can take more than 6GB, which would mean 90GB is not the correct value, but > if effective_cache size should be shared_buffers+page cache as Tomas said, > than 90GB, won't be a problem. > > > @Tomas here are the values: > > # cat /proc/sys/vm/swappiness > 60 > # cat /proc/sys/vm/overcommit_memory > 0 > # cat /proc/sys/vm/overcommit_ratio > 50 > > I will turn of swappiness, I was meaning to do that, but I don't know much > about the overcommit settings, I will read what they do. > > > @Julien thanks for the suggestions, I will tweak them like you suggested. > > > also with 15k SCSI you can reduce random_page_cost to 3.5 (instead of 4.0) > I also recommend to raise cpu_tuple_cost to 0.05 (instead of 0.01), set > vm.swappiness to 0, vm.overcommit_memory to 2, and finally raise the > read-ahead (something like 8192) > > > Strahinja Kustudić | System Engineer | Nordeus > > > > On Wed, Oct 10, 2012 at 10:11 AM, Julien Cigar <jci...@ulb.ac.be> wrote: > >> On 10/10/2012 09:12, Strahinja Kustudić wrote: >> >>> Hi everyone, >>> >> >> Hello, >> >> >> >>> 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 >>> >> >> Generally going over 4GB for shared_buffers doesn't help.. some of the >> overhead of bgwriter and checkpoints is more or less linear in the size of >> shared_buffers .. >> >> effective_cache_size = 90GB >>> >> >> effective_cache_size should be ~75% of the RAM (if it's a dedicated >> server) >> >> work_mem = 32MB >>> >> >> with 96GB of RAM I would raise default work_mem to something like 128MB >> >> maintenance_work_mem = 512MB >>> >> >> again, with 96GB of ram you can raise maintenance_work_mem to something >> like 4GB >> >> >> 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. >>> >>> Thanks in advance, >>> Strahinja >>> >> >> Julien >> >> >> -- >> No trees were killed in the creation of this message. >> However, many electrons were terribly inconvenienced. >> >> >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org >> ) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> >> > > > -- > No trees were killed in the creation of this message. > However, many electrons were terribly inconvenienced. > >
