Re: [PERFORM] FreeBSD page size
Vivek Khera wrote: > > "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: > > > BM> The 32k number is from Tatsuo testing a few years ago. > > Can you verify for me that these parameters in postgresql.conf are > based on the BLCKSZ (ie one buffer is the size of the BLCKSIZ macro): > > shared_buffers > effective_cache_size > > Logically it makes sense, but I want to be sure I'm adjusting my > postgresql.conf parameters accordingly when I try different block > sizes. Also, to check, you can use ipcs to see the shared memory sizes allocated. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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] FreeBSD page size
Vivek Khera wrote: > > "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: > > > BM> The 32k number is from Tatsuo testing a few years ago. > > Can you verify for me that these parameters in postgresql.conf are > based on the BLCKSZ (ie one buffer is the size of the BLCKSIZ macro): > > shared_buffers > effective_cache_size > > Logically it makes sense, but I want to be sure I'm adjusting my > postgresql.conf parameters accordingly when I try different block > sizes. Uh, yes, I think they have to be the same because they are pages in the shared buffer cache, not disk blocks. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] FreeBSD page size (was Re: The results of my
On Wed, 3 Sep 2003, Vivek Khera wrote: > > "SC" == Sean Chittenden <[EMAIL PROTECTED]> writes: > > >> I need to step in and do 2 things: > SC> Thanks for posting that. Let me know if you have any questions while > SC> doing your testing. I've found that using 16K blocks on FreeBSD > SC> results in about an 8% speedup in writes to the database, fwiw. > > Just double checking: if I do this, then I need to halve the > parameters in postgresql.conf that involve buffers, specifically, > max_fsm_pages and shared_buffers. I think max_fsm_pages should be > adjusted since the number of pages in the system overall has been > halved. > > Anything else that should be re-tuned for this? Yes, effective_cache_size as well is measured in pgsql blocks. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] FreeBSD page size
> "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: BM> The 32k number is from Tatsuo testing a few years ago. Can you verify for me that these parameters in postgresql.conf are based on the BLCKSZ (ie one buffer is the size of the BLCKSIZ macro): shared_buffers effective_cache_size Logically it makes sense, but I want to be sure I'm adjusting my postgresql.conf parameters accordingly when I try different block sizes. Thanks. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] FreeBSD page size
Vivek Khera wrote: > > "MGF" == Marc G Fournier <[EMAIL PROTECTED]> writes: > > MGF> Just curious, but Bruce(?) mentioned that apparently a 32k block size was > MGF> found to show a 15% improvement ... care to run one more test? :) > > > Well, it is hard to tell based on my quick and dirty test: The 32k number is from Tatsuo testing a few years ago. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] FreeBSD page size
> "MGF" == Marc G Fournier <[EMAIL PROTECTED]> writes: MGF> Just curious, but Bruce(?) mentioned that apparently a 32k block size was MGF> found to show a 15% improvement ... care to run one more test? :) Well, it is hard to tell based on my quick and dirty test: 16k page size: restore time: 11322 seconds vacuum analyze time: 1663 seconds (27 minutes) select count(*) from user_list where owner_id=315; 5.64 ms 32k page size: restore time: 11430 seconds vacuum analyze time: 1346 seconds select count(*) from user_list where owner_id=315; 63275.73 ms one anomaly I note is that if I re-run the select count(*) query above, the large the page size, the longer the query takes. In the standard 8k page size, it was on the order of 306ms, with 16k page size it was over 1400, and with 32k page size nearly 3000ms. Another anomaly I note is that for the larger indexes, the relpages doesn't scale as expected. ie, I'd expect roughly half the relpages per index for 32k page size as for 16k page size, but this is not always the case... some are about the same size and some are about 2/3 and some are about 1/2. The smaller indexes are often the same number of pages (when under 20 pages). I think I'm going to write a synthetic load generator that does a bunch of inserts to some linked tables with several indexes, then goes thru and pounds on it (update/select) from multiple children with occasional vacuum's thrown in. That's the only way to get 'real' numbers, it seems. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 3: 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] FreeBSD page size
Marc G. Fournier wrote: > On Wed, 3 Sep 2003, Bruce Momjian wrote: > > > Vivek Khera wrote: > > > the restore complained often about checkpoints occurring every few > > > seconds: > > > > > > Sep 2 11:57:14 d02 postgres[49721]: [5-1] LOG: checkpoints are occurring too > > > frequently (15 seconds apart) > > > Sep 2 11:57:14 d02 postgres[49721]: [5-2] HINT: Consider increasing > > > CHECKPOINT_SEGMENTS. > > > > > > The HINT threw me off since I had to set checkpoint_segments in > > > postgresql.conf, where as CHECKPOINT_SEGMENTS implied to me a > > > compile-time constant. > > > > Woo hoo, my warning worked. Great. > > > > I uppercased it because config parameters are uppercased in the > > documentation. Do we mention config parameters in any other error > > messages? Should it be lowercased? > > k, to me upper case denotes a compiler #define, so I would have been > confused ... I'd go with lower case and single quotes around it to denote > its a variable to be changed ... Done. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] FreeBSD page size
On Wed, 3 Sep 2003, Bruce Momjian wrote: > Vivek Khera wrote: > > the restore complained often about checkpoints occurring every few > > seconds: > > > > Sep 2 11:57:14 d02 postgres[49721]: [5-1] LOG: checkpoints are occurring too > > frequently (15 seconds apart) > > Sep 2 11:57:14 d02 postgres[49721]: [5-2] HINT: Consider increasing > > CHECKPOINT_SEGMENTS. > > > > The HINT threw me off since I had to set checkpoint_segments in > > postgresql.conf, where as CHECKPOINT_SEGMENTS implied to me a > > compile-time constant. > > Woo hoo, my warning worked. Great. > > I uppercased it because config parameters are uppercased in the > documentation. Do we mention config parameters in any other error > messages? Should it be lowercased? k, to me upper case denotes a compiler #define, so I would have been confused ... I'd go with lower case and single quotes around it to denote its a variable to be changed ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] FreeBSD page size
> I uppercased it because config parameters are uppercased in the > documentation. Do we mention config parameters in any other error > messages? Should it be lowercased? How about changing the hint? Consider increasing CHECKPOINT_SEGMENTS in your postgresql.conf signature.asc Description: This is a digitally signed message part
Re: [PERFORM] FreeBSD page size
Vivek Khera wrote: > the restore complained often about checkpoints occurring every few > seconds: > > Sep 2 11:57:14 d02 postgres[49721]: [5-1] LOG: checkpoints are occurring too > frequently (15 seconds apart) > Sep 2 11:57:14 d02 postgres[49721]: [5-2] HINT: Consider increasing > CHECKPOINT_SEGMENTS. > > The HINT threw me off since I had to set checkpoint_segments in > postgresql.conf, where as CHECKPOINT_SEGMENTS implied to me a > compile-time constant. Woo hoo, my warning worked. Great. I uppercased it because config parameters are uppercased in the documentation. Do we mention config parameters in any other error messages? Should it be lowercased? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] FreeBSD page size
Just curious, but Bruce(?) mentioned that apparently a 32k block size was found to show a 15% improvement ... care to run one more test? :) On Wed, 3 Sep 2003, Vivek Khera wrote: > Ok... simple tests have completed. Here are some numbers. > > FreeBSD 4.8 > PG 7.4b2 > 4GB Ram > Dual Xeon 2.4GHz processors > 14 U320 SCSI disks attached to Dell PERC3/DC RAID controller in RAID 5 > config with 32k stripe size > > Dump file: > -rw-r--r-- 1 vivek wheel 1646633745 Aug 28 11:01 19-Aug-2003.dump > > When restored (after deleting one index that took up ~1Gb -- turned > out it was redundant to another multi-column index): > > % df -k /u/d02 > Filesystem1K-blocks Used Avail Capacity Mounted on > /dev/amrd1s1e 226408360 18067260 190228432 9%/u/d02 > > > > postgresql.conf alterations from standard: > shared_buffers = 6 > sort_mem = 8192 > vacuum_mem=131702 > max_fsm_pages=100 > effective_cache_size=25600 > random_page-cost = 2 > > > restore time: 14777 seconds > vacuum analyze time: 30 minutes > select count(*) from user_list where owner_id=315; 50388.64 ms > > > the restore complained often about checkpoints occurring every few > seconds: > > Sep 2 11:57:14 d02 postgres[49721]: [5-1] LOG: checkpoints are occurring too > frequently (15 seconds apart) > Sep 2 11:57:14 d02 postgres[49721]: [5-2] HINT: Consider increasing > CHECKPOINT_SEGMENTS. > > The HINT threw me off since I had to set checkpoint_segments in > postgresql.conf, where as CHECKPOINT_SEGMENTS implied to me a > compile-time constant. > > Anyhow, so I deleted the PG data directory, and made these two > changes: > > checkpoint_segments=50 > sort_mem = 131702 > > This *really* improved the time for the restore: > > restore time: 11594 seconds > > then I reset the checkpoint_segments and sort_mem back to old > values... > > vacuum analyze time is still 30 minutes > select count(*) from user_list where owner_id=315; 51363.98 ms > > so the select appears a bit slower but it is hard to say why. the > system is otherwise idle as it is not in production yet. > > > Then I took the suggestion to update PG's page size to 16k and did the > same increase on sort_mem and checkpoint_segments as above. I also > halved the shared_buffers and max_fsm_pages (probably should have > halved the effective_cache_size too...) > > restore time: 11322 seconds > vacuum analyze time: 27 minutes > select count(*) from user_list where owner_id=315; 48267.66 ms > > > Granted, given this simple test it is hard to say whether the 16k > blocks will make an improvement under live load, but I'm gonna give it > a shot. The 16k block size shows me roughly 2-6% improvement on these > tests. > > So throw in my vote for 16k blocks on FreeBSD (and annotate the docs > to tell which parameters need to be halved to account for it). > > > -- > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Vivek Khera, Ph.D.Khera Communications, Inc. > Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 > AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 3: 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] FreeBSD page size
> "SC" == Sean Chittenden <[EMAIL PROTECTED]> writes: SC> hardware setup, Vivek, would it be possible for you to run a test with SC> 32K blocks? Will do. What's another 4 hours... ;-) I guess I'll halve the buffer size parameters again... SC> I've started writing a threaded benchmarking program called pg_crush SC> that I hope to post here in a few days that'll time connection startup Ok. Please post it when it is ready. I've decided to wait until 7.4 is final before going to production so I've got this very expensive very fast box doing not much of anything for a little while... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] FreeBSD page size
> Ok... simple tests have completed. Here are some numbers. > > FreeBSD 4.8 > PG 7.4b2 > 4GB Ram > Dual Xeon 2.4GHz processors > 14 U320 SCSI disks attached to Dell PERC3/DC RAID controller in RAID 5 > config with 32k stripe size [snip] > Then I took the suggestion to update PG's page size to 16k and did the > same increase on sort_mem and checkpoint_segments as above. I also > halved the shared_buffers and max_fsm_pages (probably should have > halved the effective_cache_size too...) > > restore time: 11322 seconds > vacuum analyze time: 27 minutes > select count(*) from user_list where owner_id=315; 48267.66 ms > > > Granted, given this simple test it is hard to say whether the 16k > blocks will make an improvement under live load, but I'm gonna give it > a shot. The 16k block size shows me roughly 2-6% improvement on these > tests. > > So throw in my vote for 16k blocks on FreeBSD (and annotate the docs > to tell which parameters need to be halved to account for it). I haven't had a chance to run any tests yet (ELIFE), but there was a suggestion that 32K blocks was a better performer than 16K blocks (!!??!!??). I'm not sure why this is and my only guess is that it relies more heavily on the disk cache to ease IO. Since you have the hardware setup, Vivek, would it be possible for you to run a test with 32K blocks? I've started writing a threaded benchmarking program called pg_crush that I hope to post here in a few days that'll time connection startup times, INSERTs, DELETEs, UPDATEs, and both sequential scans as well as index scans for random and sequentially ordered tuples. It's similar to pgbench, except it generates its own data, uses pthreads (chears on KSE!), and returns more fine grained timing information for the various activities. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] FreeBSD page size
Ok... simple tests have completed. Here are some numbers. FreeBSD 4.8 PG 7.4b2 4GB Ram Dual Xeon 2.4GHz processors 14 U320 SCSI disks attached to Dell PERC3/DC RAID controller in RAID 5 config with 32k stripe size Dump file: -rw-r--r-- 1 vivek wheel 1646633745 Aug 28 11:01 19-Aug-2003.dump When restored (after deleting one index that took up ~1Gb -- turned out it was redundant to another multi-column index): % df -k /u/d02 Filesystem1K-blocks Used Avail Capacity Mounted on /dev/amrd1s1e 226408360 18067260 190228432 9%/u/d02 postgresql.conf alterations from standard: shared_buffers = 6 sort_mem = 8192 vacuum_mem=131702 max_fsm_pages=100 effective_cache_size=25600 random_page-cost = 2 restore time: 14777 seconds vacuum analyze time: 30 minutes select count(*) from user_list where owner_id=315; 50388.64 ms the restore complained often about checkpoints occurring every few seconds: Sep 2 11:57:14 d02 postgres[49721]: [5-1] LOG: checkpoints are occurring too frequently (15 seconds apart) Sep 2 11:57:14 d02 postgres[49721]: [5-2] HINT: Consider increasing CHECKPOINT_SEGMENTS. The HINT threw me off since I had to set checkpoint_segments in postgresql.conf, where as CHECKPOINT_SEGMENTS implied to me a compile-time constant. Anyhow, so I deleted the PG data directory, and made these two changes: checkpoint_segments=50 sort_mem = 131702 This *really* improved the time for the restore: restore time: 11594 seconds then I reset the checkpoint_segments and sort_mem back to old values... vacuum analyze time is still 30 minutes select count(*) from user_list where owner_id=315; 51363.98 ms so the select appears a bit slower but it is hard to say why. the system is otherwise idle as it is not in production yet. Then I took the suggestion to update PG's page size to 16k and did the same increase on sort_mem and checkpoint_segments as above. I also halved the shared_buffers and max_fsm_pages (probably should have halved the effective_cache_size too...) restore time: 11322 seconds vacuum analyze time: 27 minutes select count(*) from user_list where owner_id=315; 48267.66 ms Granted, given this simple test it is hard to say whether the 16k blocks will make an improvement under live load, but I'm gonna give it a shot. The 16k block size shows me roughly 2-6% improvement on these tests. So throw in my vote for 16k blocks on FreeBSD (and annotate the docs to tell which parameters need to be halved to account for it). -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] FreeBSD page size (was Re: The results of my PostgreSQL/filesystemperformance tests)
> "SC" == Sean Chittenden <[EMAIL PROTECTED]> writes: >> I need to step in and do 2 things: SC> Thanks for posting that. Let me know if you have any questions while SC> doing your testing. I've found that using 16K blocks on FreeBSD SC> results in about an 8% speedup in writes to the database, fwiw. Just double checking: if I do this, then I need to halve the parameters in postgresql.conf that involve buffers, specifically, max_fsm_pages and shared_buffers. I think max_fsm_pages should be adjusted since the number of pages in the system overall has been halved. Anything else that should be re-tuned for this? My tests are still running so I don't have numbers yet. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
