Re: New to PostgreSQL, performance considerations

Daniel,

Good stuff.

Can you try this with just "-O3" versus "-O2"?

- Luke

On 12/11/06 2:22 PM, "Daniel van Ham Colchete" <daniel(dot)colchete(at)gmail(dot)com>
wrote:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>