From: | "Daniel van Ham Colchete" <daniel(dot)colchete(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: New to PostgreSQL, performance considerations |
Date: | 2006-12-11 22:22:42 |
Message-ID: | 8a0c7af10612111422x6bdcc0c2p7cbfeb5a7b0e0374@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Luke Lonergan | 2006-12-11 22:31:21 | Re: New to PostgreSQL, performance considerations |
Previous Message | Mark Kirkwood | 2006-12-11 22:13:30 | Re: New to PostgreSQL, performance considerations |