Hi yall, I made some preliminary tests. Before the results, I would like to make some acknowledgments: 1 - I didn't show any prove to any of the things I said until now. 2 - It really is a waste of everyone's time to say one thing when I can't prove it. But all I said, is the knowledge I have been acumulating over the past few years working on a project where optimization is important. After algorithmic optimizations, compiler options is the second on my list and with my software they show measurable improvement. With the other software I use, they seen to run faster, but I didn't measure it. TEST PROCEDURE ================ I ran this test at a Gentoo test machine I have here. It's a Pentium 4 3.0GHz (I don't know witch P4) with 1 GB of RAM memory. It only uses SATA drives. I didn't changed my glibc (or any other lib) during the test. I used GCC 3.4.6. I ran each test three times. So we can get an idea about average values and standard deviation. Each time I ran the test with the following commands: dropdb mydb createdb mydb pgbench -i -s 10 mydb 2> /dev/null psql -c 'vacuum analyze' mydb psql -c 'checkpoint' mydb sync pgbench -v -n -t 600 -c 5 mydb My postgresql.conf was the default one, except for: fsync = <depends on the test> shared_buffers = 10000 work_mem = 10240 Every test results should begin the above, but I removed it because it's always the same: transaction type: TPC-B (sort of) scaling factor: 10 number of clients: 5 number of transactions per client: 600 number of transactions actually processed: 3000/3000 TESTS RESULTS ============== TEST 01: CFLAGS="-O2 -march=i686" fsync=false tps = 734.948620 (including connections establishing) tps = 736.866642 (excluding connections establishing) tps = 713.225047 (including connections establishing) tps = 715.039059 (excluding connections establishing) tps = 721.769259 (including connections establishing) tps = 723.631065 (excluding connections establishing) TEST 02: CFLAGS="-O2 -march=i686" fsync=true tps = 75.466058 (including connections establishing) tps = 75.485675 (excluding connections establishing) tps = 75.115797 (including connections establishing) tps = 75.135311 (excluding connections establishing) tps = 73.883113 (including connections establishing) tps = 73.901997 (excluding connections establishing) TEST 03: CFLAGS="-O2 -march=pentium4" fsync=false tps = 846.337784 (including connections establishing) tps = 849.067017 (excluding connections establishing) tps = 829.476269 (including connections establishing) tps = 832.008129 (excluding connections establishing) tps = 831.416457 (including connections establishing) tps = 835.300001 (excluding connections establishing) TEST 04 CFLAGS="-O2 -march=pentium4" fsync=true tps = 83.224016 (including connections establishing) tps = 83.248157 (excluding connections establishing) tps = 80.811892 (including connections establishing) tps = 80.834525 (excluding connections establishing) tps = 80.671406 (including connections establishing) tps = 80.693975 (excluding connections establishing) CONCLUSIONS Everyone can get their own conclusion. Mine is: 1 - You have improvement when you compile your postgresql using processor specific tecnologies. With the fsync the you have an improvement of 9% at the tps rate. Without the fsync, the improvement is of 15,6%. 2 - You can still improve your indexes, sqls and everythingelse, this only adds another possible improvment. 3 - I can't prove this but I *think* that this is related to the fact that GCC knows how to do the same thing better on each processor. 4 - I'm still using source-based distros. WHAT NOW There are other things I wish to test: 1 - What efect an optimized glibc has on PostgreSQL? 2 - How much improvement can I get playing with my postgresql.conf. 3 - What efect optimizations have with concurrency? 4 - What if I used Intel C++ Compiler instead of GCC? 5 - What if I use GCC 4.1.1 instead of GCC 3.4.6? I'm thinking about writing a script to make all the tests (more than 3 times each), get the data and plot some graphs. I don't have the time right now to do it, maybe next week I'll have. I invite everyone to comment/sugest on the procedure or the results. Best regards, Daniel Colchete
