Re: [PERFORM] Background writer configuration
[EMAIL PROTECTED]:~$ 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, dynamically linked (uses shared libs), stripped On Fri, 17 Mar 2006 18:56:32 +0100 11 <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Background writer configuration
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Background writer configuration
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/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Background writer configuration
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Background writer configuration
>>> On Fri, Mar 17, 2006 at 6:24 am, in message <[EMAIL PROTECTED]>, Evgeny Gridasov <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Background writer configuration
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 ? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Background writer configuration
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 <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Background writer configuration
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 ? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Background writer configuration
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 sobibo incs us sy id wa 1 25528 14992 22884 787673600 457 383 7783 1 0 94 5 0 7632 14728 22892 78757800 88 4412 9456 1594 21623 9 5 8 78 1 19796 16904 22928 78727120 16 3536 9053 1559 19717 9 4 12 75 0 4872 14928 22936 78742080 36 3036 9092 1574 20874 9 4 2 85 0 24912 16292 22964 78720680 44 3020 9316 1581 19922 9 4 9 78 0 1912 17800 22980 786987600 2596 8700 1560 19926 9 4 4 84 4 23996 18284 22996 7868292 320 3396 11048 1657 22802 11 5 3 81 0 22960 14728 23020 7871448 520 3020 9648 1613 21641 9 4 5 82 0 28 1008 15440 23028 78696240 48 2992 10052 1608 21430 9 5 5 82 1 16 1088 17328 23044 786719600 2460 7884 1530 16536 8 3 9 79 0 23 1088 18440 23052 786555600 3256 10128 1635 22587 10 4 4 81 1 29 1076 14728 23076 786860400 2968 9860 1597 21518 10 5 7 79 1 24 1136 15952 23084 78667000 40 2696 8900 1560 19311 9 4 5 81 0 14 1208 17200 23112 78647360 16 2888 9508 1603 20634 10 4 6 80 0 21 1220 18520 23120 78628280 72 2816 9487 1572 19888 10 4 7 79 1 21 1220 14792 23144 786600000 2960 9536 1599 20331 9 5 5 81 1 24 1220 16392 23152 786408800 2860 8932 1583 19288 9 4 3 84 0 18 1276 18000 23168 786204800 2792 8592 1553 18843 9 4 9 78 1 17 1348 19144 23176 78601320 16 2840 9604 1583 20654 10 4 6 80 0 22 64 15112 23200 7864264 5280 3280 8785 1582 19339 9 4 7 80 0 25 16 16008 23212 786266440 2764 8964 1605 18471 9 4 8 79 0 26 16 17544 23236 786087200 3008 9848 1590 20527 10 4 7 79 1 7 16 18704 23244 785896000 2756 8760 1564 19875 9 4 4 84 1 25 16 15120 23268 786199600 2768 8512 1550 18518 9 3 12 75 1 25 16 18076 23276 785981200 2484 8580 1536 18391 8 4 8 80 0 3 16 17832 23300 786291600 2888 8864 1586 21450 9 4 4 83 0 14 16 24280 23308 786603600 2816 9140 1537 20655 9 4 7 81 1 1 16 54452 23348 786796800 1808 6988 1440 14235 6 9 24 61 0 1 16 51988 23348 78680360060 4180 1344 885 1 10 72 16 0 2 16 51988 23348 786803600 0 3560 143350 0 0 75 25 0 2 16 51988 23348 786803600 0 2848 136446 0 0 75 25 0 2 16 51988 23348 786803600 0 2560 135044 0 0 75 25 0 4 16 51996 23360 786809200 0 2603 132860 0 0
Re: [PERFORM] Background writer configuration
>>> On Thu, Mar 16, 2006 at 12:15 pm, in message <[EMAIL PROTECTED]>, Evgeny Gridasov <[EMAIL PROTECTED]> 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 = 2 # min 16 or max_connections*2, 8KB each work_mem = 10240# min 64, size in KB max_fsm_pages = 140 # 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Background writer configuration
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" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Background writer configuration
>>> On Wed, Mar 15, 2006 at 1:54 pm, in message <[EMAIL PROTECTED]>, "Joshua D. Drake" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Background writer configuration
> 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 [EMAIL PROTECTED] 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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
