Re: New to PostgreSQL, performance considerations - Mailing list pgsql-performance

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