Re: Postgres not using indexes - Mailing list pgsql-bugs
From | Lawrence Cohan |
---|---|
Subject | Re: Postgres not using indexes |
Date | |
Msg-id | 965AA5440EAC094E9F722519E285ACEDB368961C31@WWCEXCHANGE.web.web.com Whole thread Raw |
In response to | Re: Postgres not using indexes ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
List | pgsql-bugs |
Many thanks for all your advice and we will use Greg Smith's book on perfor= mance to incrementaly tune our environment. Regards, Lawrence Cohan. -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: April-21-11 3:38 PM To: pgsql-bugs@postgresql.org; Lawrence Cohan Subject: RE: [BUGS] Postgres not using indexes Lawrence Cohan <LCohan@web.com> wrote: > We managed to put together a new test server running PG 9.0.2 on > 2socketsx6cores =3D 12CPU with 64 GB RAM against a 3PAR 10TB SAN. We > kept the settings I submitted already (and enclosed below) and > after 12 hours of pounding the box with PGBENCH running 8 scripts > to perform all of INSERT/UPDATE/DELETE/SELECT statements we wanted > we got a pretty good picture of what can do with those settings. > We got a load average of 60 with CPU up and around that 60% mark, > pushing through about 1400 transactions per second for 12 hours. > We made the changes as suggested and listed below but the > throughput dropped from 1400 t/s to 400 t/s and I suspect are the > "stalled" transactions you mentioned about. > > Here's what we changed: > > Current Settings Test Settings > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > shared_buffers =3D 500MB shared_buffers =3D 8GB > effective_cache_size =3D 2GB effective_cache_size =3D 32GB To make sure I understand, are the "Current Settings" the ones which performed better? > Just to be 100% accurate we ask you what do you mean by: > > 1) "Make the background writer more aggressive and/or back > off on shared_memory, so that there isn't such a glut of dirty > pages to Write during a checkpoint." > > By aggressive does he mean changing any of the following? > # - Background Writer - > > #bgwriter_delay =3D 200ms > #bgwriter_lru_maxpages =3D 100 > #bgwriter_lru_multiplier =3D 2.0 We use these overrides: bgwriter_lru_maxpages =3D 1000 bgwriter_lru_multiplier =3D 4.0 > Or we should be better of by the checkpoint segment handling - any > of the below: > # - Checkpoints - > > checkpoint_segments =3D 64 > #checkpoint_timeout =3D 5min > #checkpoint_completion_target =3D 0.5 > checkpoint_warning =3D 60s You might consider increasing checkpoint_timeout if you can tolerate the increased recovery time if there is a crash. You should probably boost checkpoint_completion_target to 0.8 or 0.9. Really, if you don't already have it, you should get a copy of Greg Smith's recent book on performance: http://www.postgresql.org/docs/books/ It gives good descriptions of all of these parameters and advice on incremental tuning to find you best settings. The fact that you listed shared_buffers and effective_cache_size together at least suggests that you don't yet grasp the role of these settings. One affects how much memory PostgreSQL allocates; the other has absolutely nothing to do with that. effective_cache_size affects costs assigned to various plans, thereby affecting plan choice. While a high shared_buffers setting might lead to a glut of writes around commit time, setting effective_cache_size incorrectly might lead to plans which don't read the data efficiently. Seeing what vmstat or iostat say during a slow episode, and seeing whether the episodes correspond to checkpoints, will give you a better indication of where the problem lies. -Kevin Attention: The information contained in this message and or attachments is intended on= ly for the person or entity to which it is addressed and may contain confid= ential and/or privileged material. Any review, retransmission, disseminati= on or other use of, or taking of any action in reliance upon, this informat= ion by persons or entities other than the intended recipient is prohibited.= If you received this in error, please contact the sender and delete the ma= terial from any system and destroy any copies.
pgsql-bugs by date: