User-Agent: Microsoft-Entourage/11.2.5.060620
Date: Mon, 11 Dec 2006 14:31:21 -0800
Subject: Re: New to PostgreSQL, performance considerations
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Daniel van Ham Colchete" <daniel.colchete@gmail.com>,
	pgsql-performance@postgresql.org
Message-ID: <C1A31C39.15548%llonergan@greenplum.com>
Thread-Topic: [PERFORM] New to PostgreSQL, performance considerations
Thread-Index: AccddBUOU4JD+olnEdu/8AAWy4o9DA==
In-Reply-To: <8a0c7af10612111422x6bdcc0c2p7cbfeb5a7b0e0374@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

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.colchete@gmail.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
>