Re: postgreSQL performance 8.2.6 vs 8.3.3

On Fri, Feb 20, 2009 at 2:34 PM, Battle Mage <battlemage(at)gmail(dot)com> 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)

If you're goint to test with -c50 you should initialize with -s50. -s
50 after initialization doesn't mean anything. It's the first pgbench
-i -s nnn where you need to set nnn to 50 (or higher) if you're gonna
test with it.

> 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)

Nice improvement.

> 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?

I agree with the other poster. Why is a load increase bad? What does
it mean here. I've got one load that runs smoothly with a load factor
of 60 to 150 on a server, while the same server with a different load
starts to bog down with load factors between 10 and 15. It's a very
broad measurement. Don't try to tune to your load factor, try to tune
to the real load being applied, and opimtize there.

> 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.
>
> ===== postgresql.conf begins =====
> fsync = off # turns forced synchronization on or off

So, I assume either your data is easily reproduceable, unimportant, or
replicated in such a way that you can survive sudden power loss /
kernel crash?

Also, is there are reason you're running two different out of date
releases of postgresql?