Re: postgreSQL performance 8.2.6 vs 8.3.3

On Fri, Feb 20, 2009 at 04:34:23PM -0500, Battle Mage wrote:
> I have a server box that has 4GB of RAM, Quad core CPU AMD Opteron 200.152
> Mhz (1024 KB cache size each) with plenty of hard drive space.
>
> I installed both postgresql 8.2.6 and 8.3.3 on it. I've created a basic
> test db and used
> pgbench -i -s 1 -U test -h localhost test
> to create a sample test db.
>
> Then, to benchmark the postgreSQLs, I executed this separately on each of
> them:
> pg_bench -h localhost -d test -t 2000 -c 50 -s 50 -U test
> (2000 transactions per client, 50 clients, scalability factor of 50)
>
> Using the above,
> I get on postgreSQL 8.2.6:
> Load average: Between 3.4 and 4.3
> tps = 589 (including connections establishing)
> tps = 590 (excluding connections establishing)
>
> I get on postgreSQL 8.3.3
> Load: Between 4.5 and 5.6
> tps = 949 (including connections establishing)
> tps = 951 (excluding connections establishing)
>
> The amount of tps almost doubled, which is good, but i'm worried about the
> load. For my application, a load increase is bad and I'd like to keep it
> just like in 8.2.6 (a load average between 3.4 and 4.3). What parameters
> should I work with to decrease the resulting load average at the expense of
> tps?
>
> Down below is my 8.3.3 configuration file. I removed everything that is
> commented since if it's commented, it's default value. I also removed from
> the sample below parameters related to logging.

Please evaluate your load on the 8.3.3 box at 590 tps. If the load is
proportional to the tps than the scaled load will be: 2.8 to 3.5 for
an equivalent tps. There is no free lunch but 8.3 performs much better than
8.2 and I suspect that this trend will continue. :)

Cheers,
Ken