Thread: Background writer configuration
We were seeing clusters of query timeouts with our web site, which were corrected by adjusting the configuration of the background writer. I'm posting just to provide information which others might find useful -- I don't have any problem I'm trying to solve in this regard. The web site gets 1 to 2 million hits per day, with about the same number of select queries run to provide data for the web pages. The load is distributed across multiple databases. (We have four, but the load is easily handled by any two of them, and we often take one or two out of web use for maintenance or special statistical runs.) Each database gets the same stream of modification requests -- about 2.7 million database transactions per day. Each transaction can contain multiple inserts, updates, or deletes. The peak times for both the web requests and the data modifications are in the afternoon on business days. Most web queries run under a timeout limit of 20 seconds. During peak times, we would see clusters of timeouts (where queries exceeded the 20 second limit) on very simple queries which normally run in a few milliseconds. The pattern suggested that checkpoints were at fault. I boosted the settings for the background writer from the defaults to the values below, and we saw a dramatic reduction in these timeouts. We also happened to have one machine which had been out of the replication mix which was in "catch up" mode, processing the transaction stream as fast as the database could handle it, without any web load. We saw the transaction application rate go up by a factor of four when I applied these changes: bgwriter_lru_percent = 2.0 bgwriter_lru_maxpages = 250 bgwriter_all_percent = 1.0 bgwriter_all_maxpages = 250 This was with shared_buffers = 20000, so that last value was effectively limited to 200 by the percentage. I then did some calculations, based on the sustained write speed of our drive array (as measured by copying big files to it), and we tried this: bgwriter_lru_percent = 20.0 bgwriter_lru_maxpages = 200 bgwriter_all_percent = 10.0 bgwriter_all_maxpages = 600 This almost totally eliminated the clusters of timeouts, and caused the transaction application rate to increase by a factor of eight over the already-improved speed. (That is, we were running 30 to 35 times as many transactions per minute into the database, compared to the default background writer configuration.) I'm going to let these settings settle in for a week or two before we try adjusting them further (to see if we can eliminate those last few timeouts of this type). I guess my point is that people shouldn't be shy about boosting these numbers by a couple orders of magnitude from the default values. It may also be worth considering whether the defaults should be something more aggressive. -Kevin
> I then did some calculations, based on the sustained write speed of our > drive array (as measured by copying big files to it), and we tried > this: > > bgwriter_lru_percent = 20.0 > bgwriter_lru_maxpages = 200 > bgwriter_all_percent = 10.0 > bgwriter_all_maxpages = 600 > > This almost totally eliminated the clusters of timeouts, and caused the > transaction application rate to increase by a factor of eight over the > already-improved speed. (That is, we were running 30 to 35 times as > many transactions per minute into the database, compared to the default > background writer configuration.) I'm going to let these settings > settle in for a week or two before we try adjusting them further (to see > if we can eliminate those last few timeouts of this type). Can you tell us what type of array you have? Joshua D. Drake > > I guess my point is that people shouldn't be shy about boosting these > numbers by a couple orders of magnitude from the default values. It may > also be worth considering whether the defaults should be something more > aggressive. > > -Kevin > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/
>>> On Wed, Mar 15, 2006 at 1:54 pm, in message
<200603151154.33504.jd@commandprompt.com>, "Joshua D. Drake"
<jd@commandprompt.com> wrote:
>> I then did some calculations, based on the sustained write speed of
our
>> drive array (as measured by copying big files to it), and we tried
>> this:
>>
>> bgwriter_lru_percent = 20.0
>> bgwriter_lru_maxpages = 200
>> bgwriter_all_percent = 10.0
>> bgwriter_all_maxpages = 600
>>
>> This almost totally eliminated the clusters of timeouts, and caused
the
>> transaction application rate to increase by a factor of eight over
the
>> already- improved speed. (That is, we were running 30 to 35 times
as
>> many transactions per minute into the database, compared to the
default
>> background writer configuration.) I'm going to let these settings
>> settle in for a week or two before we try adjusting them further (to
see
>> if we can eliminate those last few timeouts of this type).
>
>
> Can you tell us what type of array you have?
Each machine has a RAID5 array of 13 (plus one hot spare)
15,000 RPM Ultra 320 SCSI drives
2 machines using IBM ServRaid6M battery backed caching controllers
2 machines using IBM ServRaid4MX battery backed caching controllers
Kevin, please, could you post other settings from your postgresql.conf? interested in: bgwriter_delay shared_buffers checkpoint_segments checkpoint_timeout wal_buffers On Wed, 15 Mar 2006 13:43:45 -0600 "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote: > We were seeing clusters of query timeouts with our web site, which were > corrected by adjusting the configuration of the background writer. I'm > posting just to provide information which others might find useful -- I > don't have any problem I'm trying to solve in this regard. > -- Evgeny Gridasov Software Engineer I-Free, Russia
>>> On Thu, Mar 16, 2006 at 12:15 pm, in message <20060316211523.73343cee.eugrid@fpm.kubsu.ru>, Evgeny Gridasov <eugrid@fpm.kubsu.ru> wrote: > > please, could you post other settings from your postgresql.conf? Everything in postgresql.conf which is not commented out: listen_addresses = '*' # what IP interface(s) to listen on; max_connections = 600 # note: increasing max_connections costs shared_buffers = 20000 # min 16 or max_connections*2, 8KB each work_mem = 10240 # min 64, size in KB max_fsm_pages = 1400000 # min max_fsm_relations*16, 6 bytes each bgwriter_lru_percent = 20.0 # 0-100% of LRU buffers scanned/round bgwriter_lru_maxpages = 200 # 0-1000 buffers max written/round bgwriter_all_percent = 10.0 # 0-100% of all buffers scanned/round bgwriter_all_maxpages = 600 # 0-1000 buffers max written/round full_page_writes = off # recover from partial page writes wal_buffers = 20 # min 4, 8KB each checkpoint_segments = 10 # in logfile segments, min 1, 16MB each effective_cache_size = 524288 # typically 8KB each random_page_cost = 2 # units are one sequential page fetch redirect_stderr = on # Enable capturing of stderr into log log_line_prefix = '[%m] %p %q<%u %d %r> ' # Special values: stats_start_collector = on stats_block_level = on stats_row_level = on autovacuum = true # enable autovacuum subprocess? autovacuum_naptime = 10 # time between autovacuum runs, in secs autovacuum_vacuum_threshold = 1 # min # of tuple updates before autovacuum_analyze_threshold = 1 # min # of tuple updates before autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before lc_messages = 'C' # locale for system error message lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C' # locale for number formatting lc_time = 'C' # locale for time formatting sql_inheritance = off standard_conforming_strings = on
Yesterday we recieved a new server 2xAMD64(2core x 2chips = 4 cores) 8GB RAM and RAID-1 (LSI megaraid) I've maid some tests with pgbench (scaling 1000, database size ~ 16Gb) First of all, I'd like to mention that it was strange to see that the server performance degraded by 1-2% when we changed kernel/userland to x86_64 from default installed i386 userland/amd64 kernel. The operating system was Debian Linux, filesystem ext3. bg_writer_*_percent/maxpages setting did not dramatically increase performance, but setting bg_writer_delay to values x10 original setting (2000-4000) increased transaction rate by 4-7 times. I've tried shared buffers 32768, 65536, performance was almost equal. for all tests: checkpoint_segments = 16 checkpoint_timeout = 900 shared_buffers=65536 wal_buffers=128: bgwriter_delay = 200 bgwriter_lru_percent = 10.0 bgwriter_lru_maxpages = 100 bgwriter_all_percent = 5.0 bgwriter_all_maxpages = 50 result: ./pgbench -c 32 -t 500 -U postgres regression starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1000 number of clients: 32 number of transactions per client: 500 number of transactions actually processed: 16000/16000 tps = 112.740903 (including connections establishing) tps = 112.814327 (excluding connections establishing) (disk activity about 2-4mb/sec writing) bgwriter_delay = 4000 bgwriter_lru_percent = 10.0 bgwriter_lru_maxpages = 100 bgwriter_all_percent = 5.0 bgwriter_all_maxpages = 50 result: ./pgbench -c 32 -t 500 -U postgres regression starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1000 number of clients: 32 number of transactions per client: 500 number of transactions actually processed: 16000/16000 tps = 508.637831 (including connections establishing) tps = 510.107981 (excluding connections establishing) (disk activity about 20-40 mb/sec writing) Setting bgwriter_delay to higher values leads to slower postgresql shutdown time (I see postgresql writer process writing to disk). Sometimes postgresql didn't shutdown correctly (doesn't complete background writing ?). I've found some settings with which system behaves strange: ./pgbench -c 32 -t 3000 -U postgres regression vmstat 1: procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 1 25 528 14992 22884 7876736 0 0 457 383 77 83 1 0 94 5 0 7 632 14728 22892 7875780 0 88 4412 9456 1594 21623 9 5 8 78 1 19 796 16904 22928 7872712 0 16 3536 9053 1559 19717 9 4 12 75 0 4 872 14928 22936 7874208 0 36 3036 9092 1574 20874 9 4 2 85 0 24 912 16292 22964 7872068 0 44 3020 9316 1581 19922 9 4 9 78 0 1 912 17800 22980 7869876 0 0 2596 8700 1560 19926 9 4 4 84 4 23 996 18284 22996 7868292 32 0 3396 11048 1657 22802 11 5 3 81 0 22 960 14728 23020 7871448 52 0 3020 9648 1613 21641 9 4 5 82 0 28 1008 15440 23028 7869624 0 48 2992 10052 1608 21430 9 5 5 82 1 16 1088 17328 23044 7867196 0 0 2460 7884 1530 16536 8 3 9 79 0 23 1088 18440 23052 7865556 0 0 3256 10128 1635 22587 10 4 4 81 1 29 1076 14728 23076 7868604 0 0 2968 9860 1597 21518 10 5 7 79 1 24 1136 15952 23084 7866700 0 40 2696 8900 1560 19311 9 4 5 81 0 14 1208 17200 23112 7864736 0 16 2888 9508 1603 20634 10 4 6 80 0 21 1220 18520 23120 7862828 0 72 2816 9487 1572 19888 10 4 7 79 1 21 1220 14792 23144 7866000 0 0 2960 9536 1599 20331 9 5 5 81 1 24 1220 16392 23152 7864088 0 0 2860 8932 1583 19288 9 4 3 84 0 18 1276 18000 23168 7862048 0 0 2792 8592 1553 18843 9 4 9 78 1 17 1348 19144 23176 7860132 0 16 2840 9604 1583 20654 10 4 6 80 0 22 64 15112 23200 7864264 528 0 3280 8785 1582 19339 9 4 7 80 0 25 16 16008 23212 7862664 4 0 2764 8964 1605 18471 9 4 8 79 0 26 16 17544 23236 7860872 0 0 3008 9848 1590 20527 10 4 7 79 1 7 16 18704 23244 7858960 0 0 2756 8760 1564 19875 9 4 4 84 1 25 16 15120 23268 7861996 0 0 2768 8512 1550 18518 9 3 12 75 1 25 16 18076 23276 7859812 0 0 2484 8580 1536 18391 8 4 8 80 0 3 16 17832 23300 7862916 0 0 2888 8864 1586 21450 9 4 4 83 0 14 16 24280 23308 7866036 0 0 2816 9140 1537 20655 9 4 7 81 1 1 16 54452 23348 7867968 0 0 1808 6988 1440 14235 6 9 24 61 0 1 16 51988 23348 7868036 0 0 60 4180 1344 885 1 10 72 16 0 2 16 51988 23348 7868036 0 0 0 3560 1433 50 0 0 75 25 0 2 16 51988 23348 7868036 0 0 0 2848 1364 46 0 0 75 25 0 2 16 51988 23348 7868036 0 0 0 2560 1350 44 0 0 75 25 0 4 16 51996 23360 7868092 0 0 0 2603 1328 60 0 0 72 28 0 4 16 52060 23360 7868092 0 0 0 2304 1306 46 0 0 75 25 0 4 16 52140 23360 7868092 0 0 0 2080 1288 40 0 0 75 25 0 2 16 52140 23360 7868092 0 0 0 2552 1321 48 0 0 75 25 0 2 16 52220 23360 7868092 0 0 0 2560 1335 44 0 0 75 25 0 2 16 52220 23360 7868092 0 0 0 2560 1340 48 0 0 75 25 0 2 16 52284 23360 7868092 0 0 0 2560 1338 48 0 0 75 25 ... continued during the time with zero read io and write io about 2500 I see many hanging postgresql processes executing UPDATE or COMMIT. This lasts for a minute or so, after that I see the same IO which was during benchmark start. What happens during this period? On Thu, 16 Mar 2006 15:58:53 -0600 "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote: > >>> On Thu, Mar 16, 2006 at 12:15 pm, in message > <20060316211523.73343cee.eugrid@fpm.kubsu.ru>, Evgeny Gridasov > <eugrid@fpm.kubsu.ru> wrote: > > > > please, could you post other settings from your postgresql.conf? > > Everything in postgresql.conf which is not commented out: > > listen_addresses = '*' # what IP interface(s) to listen on; > max_connections = 600 # note: increasing > max_connections costs > shared_buffers = 20000 # min 16 or max_connections*2, > 8KB each > work_mem = 10240 # min 64, size in KB > max_fsm_pages = 1400000 # min max_fsm_relations*16, 6 > bytes each > bgwriter_lru_percent = 20.0 # 0-100% of LRU buffers > scanned/round > bgwriter_lru_maxpages = 200 # 0-1000 buffers max > written/round > bgwriter_all_percent = 10.0 # 0-100% of all buffers > scanned/round > bgwriter_all_maxpages = 600 # 0-1000 buffers max > written/round > full_page_writes = off # recover from partial page > writes -- Evgeny Gridasov Software Engineer I-Free, Russia
> First of all, I'd like to mention that it was strange to see that
> the server performance degraded by 1-2% when we changed kernel/userland
> to x86_64
> from default installed i386 userland/amd64 kernel. The operating system
> was Debian Linux,
> filesystem ext3.
Did you use postgres compiled for AMD64 with the 64 kernel, or did you
use a 32 bit postgres in emulation mode ?
template1=# select version();
version
---------------------------------------------------------------------------------------------
PostgreSQL 8.1.3 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13)
(1 row)
On Fri, 17 Mar 2006 14:35:15 +0100
PFC <lists@peufeu.com> wrote:
>
> > First of all, I'd like to mention that it was strange to see that
> > the server performance degraded by 1-2% when we changed kernel/userland
> > to x86_64
> > from default installed i386 userland/amd64 kernel. The operating system
> > was Debian Linux,
> > filesystem ext3.
>
> Did you use postgres compiled for AMD64 with the 64 kernel, or did you
> use a 32 bit postgres in emulation mode ?
>
--
Evgeny Gridasov
Software Engineer
I-Free, Russia
I got this :
template1=# select version();
version
------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.2 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (GCC) 3.4.4 (Gentoo 3.4.4-r1, ssp-3.4.4-1.0,
pie-8.7.8)
(1 ligne)
Normally you should get a noticeable performance boost by using userland
executables compiled for the 64 platform... strange...
On Fri, 17 Mar 2006 15:50:17 +0100, Evgeny Gridasov <eugrid@fpm.kubsu.ru>
wrote:
> template1=# select version();
> version
> ---------------------------------------------------------------------------------------------
> PostgreSQL 8.1.3 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5
> (Debian 1:3.3.5-13)
> (1 row)
>
>
> On Fri, 17 Mar 2006 14:35:15 +0100
> PFC <lists@peufeu.com> wrote:
>
>>
>> > First of all, I'd like to mention that it was strange to see that
>> > the server performance degraded by 1-2% when we changed
>> kernel/userland
>> > to x86_64
>> > from default installed i386 userland/amd64 kernel. The operating
>> system
>> > was Debian Linux,
>> > filesystem ext3.
>>
>> Did you use postgres compiled for AMD64 with the 64 kernel, or did you
>> use a 32 bit postgres in emulation mode ?
>>
>
>>> On Fri, Mar 17, 2006 at 6:24 am, in message <20060317152448.452e4854.eugrid@fpm.kubsu.ru>, Evgeny Gridasov <eugrid@fpm.kubsu.ru> wrote: > > I've maid some tests with pgbench If possible, tune the background writer with your actual application code under normal load. Optimal tuning is going to vary based on usage patterns. You can change these settings on the fly by editing the postgresql.conf file and running pg_ctl reload. This is very nice, as it allowed us to try various settings in our production environment while two machines dealt with normal update and web traffic and another was in a saturated update process. For us, the key seems to be to get the dirty blocks pushed out to the OS level cache as soon as possible, so that the OS can deal with them before the checkpoint comes along. > for all tests: > checkpoint_segments = 16 > checkpoint_timeout = 900 > shared_buffers=65536 > wal_buffers=128: > ./pgbench - c 32 - t 500 - U postgres regression Unless you are going to be running in short bursts of activity, be sure that the testing is sustained long enough to get through several checkpoints and settle into a "steady state" with any caching controller, etc. On the face of it, it doesn't seem like this test shows anything except how it would behave with a relatively short burst of activity sandwiched between big blocks of idle time. I think your second test may look so good because it is just timing how fast it can push a few rows into cache space. > Setting bgwriter_delay to higher values leads to slower postgresql shutdown time > (I see postgresql writer process writing to disk). Sometimes postgresql didn't > shutdown correctly (doesn't complete background writing ?). Yeah, here's where it gets to trying to finish all the work you avoided measuring in your benchmark. -Kevin
On Mar 17, 2006, at 4:24 AM, Evgeny Gridasov wrote: > Yesterday we recieved a new server 2xAMD64(2core x 2chips = 4 cores) > 8GB RAM and RAID-1 (LSI megaraid) > I've maid some tests with pgbench (scaling 1000, database size ~ 16Gb) > > First of all, I'd like to mention that it was strange to see that > the server performance degraded by 1-2% when we changed kernel/ > userland to x86_64 > from default installed i386 userland/amd64 kernel. The operating > system was Debian Linux, > filesystem ext3. 64 bit binaries usually run marginally slower than 32 bit binaries. AIUI the main reason is that they're marginally bigger, so fit less well in cache, have to haul themselves over the memory channels and so on. They're couch potato binaries. I've seen over 10% performance loss in compute-intensive code, so a couple of percent isn't too bad at all. If that 64 bit addressing gets you cheap access to lots of RAM, and your main applications can make good use of that then that can easily outweigh the overall loss in performance Cheers, Steve
On Fri, Mar 17, 2006 at 08:56:58AM -0800, Steve Atkins wrote: > 64 bit binaries usually run marginally slower than 32 bit binaries. This depends a bit on the application, and what you mean by "64 bit" (ie. what architecture). Some specialized applications actually benefit from having a 64-bit native data type (especially stuff working with a small amount of bitfields -- think an anagram program), but Postgres is probably not among them unless you do lots of arithmetic on bigints. amd64 has the added benefit that you get twice as many registers available in 64-bit mode (16 vs. 8 -- the benefit gets even bigger when you consider that a few of those go to stack pointers etc.), so in some code you might get a few percent extra from that, too. /* Steinar */ -- Homepage: http://www.sesse.net/
On 2006-03-17, at 15:50, Evgeny Gridasov wrote: > template1=# select version(); > version > ---------------------------------------------------------------------- > ----------------------- > PostgreSQL 8.1.3 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) > 3.3.5 (Debian 1:3.3.5-13) > (1 row) How about something like: $ file /usr/lib/postgresql/bin/postgres (or whatever directory postmaster binary is in) instead? -- 11.
eugene@test:~$ file /usr/lib/postgresql/8.1/bin/postgres /usr/lib/postgresql/8.1/bin/postgres: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.0, dynamicallylinked (uses shared libs), stripped On Fri, 17 Mar 2006 18:56:32 +0100 11 <eleven@ludojad.itpp.pl> wrote: > On 2006-03-17, at 15:50, Evgeny Gridasov wrote: > > > template1=# select version(); > > version > > ---------------------------------------------------------------------- > > ----------------------- > > PostgreSQL 8.1.3 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) > > 3.3.5 (Debian 1:3.3.5-13) > > (1 row) > > How about something like: > $ file /usr/lib/postgresql/bin/postgres > (or whatever directory postmaster binary is in) instead? -- Evgeny Gridasov Software Engineer I-Free, Russia