Re: [PERFORM] The results of my PostgreSQL/filesystem performance tests
I'm likely going to make this the default for PostgreSQL on FreeBSD starting with 7.4 (just posted something to -hackers about this)f. If you'd like to do this in your testing, just apply the following patch. Right now PostgreSQL defaults to 8K blocks, but FreeBSD uses 16K blocks which means that currently, reading two blocks of data in PG is two read calls to the OS, one reads 16K of data off disk and returns the 1st page, the 2nd call pulls the 2nd block from the FS cache. In making things 16K, it avoids the need for the 2nd system call which is where the performance difference is coming from, afaikt. -sc Are you _sure_ this won't cause any atomicity problems? Can FreeBSD write 16k as an atomic unit? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] The results of my PostgreSQL/filesystem performance tests
Hey all. I said I was going to do it, and I finally did it. As with all performance tests/benchmarks, there are probably dozens or more reasons why these results aren't as accurate or wonderful as they should be. Take them for what they are and hopefully everyone can learn a few things from them. Intelligent feedback is welcome. http://www.potentialtech.com/wmoran/postgresql.php -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] The results of my PostgreSQL/filesystem performance tests
Bill, Very interesting results. I'd like to command you on your honesty. Having started out with the intentions of proving that FreeBSD is faster than Linux only to find that the opposite is true must not have been rewarding for you. However, these unexpected results serve only to reinforce the integrity of your tests. Thanks for all the work. Balazs -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Moran Sent: Tuesday, August 26, 2003 6:48 PM To: [EMAIL PROTECTED] Subject: [PERFORM] The results of my PostgreSQL/filesystem performance tests Hey all. I said I was going to do it, and I finally did it. As with all performance tests/benchmarks, there are probably dozens or more reasons why these results aren't as accurate or wonderful as they should be. Take them for what they are and hopefully everyone can learn a few things from them. Intelligent feedback is welcome. http://www.potentialtech.com/wmoran/postgresql.php -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] The results of my PostgreSQL/filesystem performance tests
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Balazs Wellisch) wrote: Very interesting results. I'd like to command you on your honesty. Having started out with the intentions of proving that FreeBSD is faster than Linux only to find that the opposite is true must not have been rewarding for you. However, these unexpected results serve only to reinforce the integrity of your tests. Well put. To see a result that the tester didn't really want to see/present does suggest good things about the tester's honesty. There was incentive to hide unfavorable results. What it still leaves quite open is just what happens when the OS has more than one disk drive or CPU to play with. It's not clear what happens in such cases, whether FreeBSD would catch up, or be left further in the dust. The traditional propaganda has been that there are all sorts of reasons to expect PostgreSQL on FreeBSD to run a bit faster than on Linux; it is a bit unexpected for the opposite to seem true. -- output = reverse(gro.mca @ enworbbc) http://www3.sympatico.ca/cbbrowne/sap.html I am aware of the benefits of a micro kernel approach. However, the fact remains that Linux is here, and GNU isn't --- and people have been working on Hurd for a lot longer than Linus has been working on Linux. -- Ted T'so, 1992. ---(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] The results of my PostgreSQL/filesystem performance tests
Couple of questions: What was the postgresql.conf configuration used? Default? How many threads of the script ran? Looks like a single user only. I assume there was nothing else running at the time (cron, sendmail, etc. were all off?) Do you know whether the machines were disk or I/O bound? Was PostgreSQL compiled the same for each OS or did you use the rpm, deb, tgz that were available? On Tue, 2003-08-26 at 21:47, Bill Moran wrote: Hey all. I said I was going to do it, and I finally did it. As with all performance tests/benchmarks, there are probably dozens or more reasons why these results aren't as accurate or wonderful as they should be. Take them for what they are and hopefully everyone can learn a few things from them. Intelligent feedback is welcome. http://www.potentialtech.com/wmoran/postgresql.php signature.asc Description: This is a digitally signed message part
Re: [PERFORM] The results of my PostgreSQL/filesystem performance tests
http://www.potentialtech.com/wmoran/postgresql.php -- Bill Moran Potential Technologies http://www.potentialtech.com Adding my voice to the many, thanks for sharing your results Bill. Very instructive. -- Best, Al Hulaton| Sr. Account Engineer | Command Prompt, Inc. 503.222.2783 | [EMAIL PROTECTED] Home of Mammoth PostgreSQL and 'Practical PostgreSQL' Managed PostgreSQL, Linux services and consulting Read and Search O'Reilly's 'Practical PostgreSQL' at http://www.commandprompt.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] The results of my PostgreSQL/filesystem performance tests
What it still leaves quite open is just what happens when the OS has more than one disk drive or CPU to play with. It's not clear what happens in such cases, whether FreeBSD would catch up, or be left further in the dust. The traditional propaganda has been that there are all sorts of reasons to expect PostgreSQL on FreeBSD to run a bit faster than on Linux; it is a bit unexpected for the opposite to seem true. Let me nip this in the butt before people run away with ideas that aren't correct. When the tests were performed in FreeBSD 5.1 and Linux, the hard drives were running UDMA. When running 4.8, for some reason his drives settled in on PIO mode: ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) retrying ad0s1a: UDMA ICRC error writing fsbn 1458368 of 729184-729215 (ad0s1 bn 1458368; cn 241 tn 12 sn 44) falling back to PIO mode The benchmarks were hardly conclusive as UDMA runs vastly faster than PIO. Until we hear back as to whether cables were jarred loose between the tests or hearing if something else changed, I'd hardly consider these conclusive tests given PIO/UDMA is apples to oranges in terms of speed and I fully expect that FreeBSD 4.8 will perform at least faster than 5.1 (5.x is still being unwound from Giant), but should out perform Linux as well if industry experience iss any indicator. -sc -- Sean Chittenden ---(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] The results of my PostgreSQL/filesystem performance tests
I need to step in and do 2 things: Thanks for posting that. Let me know if you have any questions while doing your testing. I've found that using 16K blocks on FreeBSD results in about an 8% speedup in writes to the database, fwiw. I'm likely going to make this the default for PostgreSQL on FreeBSD starting with 7.4 (just posted something to -hackers about this)f. If you'd like to do this in your testing, just apply the following patch. Right now PostgreSQL defaults to 8K blocks, but FreeBSD uses 16K blocks which means that currently, reading two blocks of data in PG is two read calls to the OS, one reads 16K of data off disk and returns the 1st page, the 2nd call pulls the 2nd block from the FS cache. In making things 16K, it avoids the need for the 2nd system call which is where the performance difference is coming from, afaikt. -sc -- Sean Chittenden Index: src/include/pg_config_manual.h === RCS file: /home/ncvs/pgsql/pgsql-server/src/include/pg_config_manual.h,v retrieving revision 1.5 diff -u -r1.5 pg_config_manual.h --- src/include/pg_config_manual.h 4 Aug 2003 00:43:29 - 1.5 +++ src/include/pg_config_manual.h 27 Aug 2003 17:40:12 - @@ -23,7 +23,7 @@ * * Changing BLCKSZ requires an initdb. */ -#define BLCKSZ 8192 +#define BLCKSZ 16384 /* * RELSEG_SIZE is the maximum number of blocks allowed in one disk ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] The results of my PostgreSQL/filesystem performance 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. Where/how does one set this? In postgresql.conf or on the file system or during compilation of postgres? I'm on FreeBSD 4.8 still. I've got a box right now on which I'm comparing the speed merits of hardware RAID10, RAID5, and RAID50 using a filesystem benchmark utility (bonnie++). If I have time I'm gonna try different striping block sizes. Right now I'm using 32k byte stripe size. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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] The results of my PostgreSQL/filesystem performance 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. ok.. ignore my prior request about how to set that... i missed you had included a patch. Any recommendations on newfs parameters for an overly large file system used solely for Postgres? Over 100Gb (with raid 10) or over 200Gb (with raid 5)? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] The results of my PostgreSQL/filesystem performance tests
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. ok.. ignore my prior request about how to set that... i missed you had included a patch. Any recommendations on newfs parameters for an overly large file system used solely for Postgres? Over 100Gb (with raid 10) or over 200Gb (with raid 5)? Nope, you'll have to test and see. If you find something that works, however, let me know. -sc -- Sean Chittenden ---(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
