Thread: postgreSQL performance 8.2.6 vs 8.3.3

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.

===== postgresql.conf begins =====

port = 5432                # (change requires restart)
max_connections = 180            # (change requires restart)
superuser_reserved_connections = 5    # (change requires restart)
unix_socket_directory = '/var/run/postgresql'        # (change requires restart)
ssl = off                # (change requires restart)

shared_buffers = 512MB            # min 128kB or max_connections*16kB

temp_buffers = 8MB            # min 800kB
max_prepared_transactions = 5        # can be 0 or more

work_mem = 16MB                # min 64kB
maintenance_work_mem = 512MB        # min 1MB
max_stack_depth = 2MB            # min 100kB

# - Free Space Map -

max_fsm_pages = 2400000            # min max_fsm_relations*16, 6 bytes each

vacuum_cost_delay = 0            # 0-1000 milliseconds
vacuum_cost_page_hit = 1        # 0-10000 credits
vacuum_cost_page_miss = 10        # 0-10000 credits
vacuum_cost_page_dirty = 20        # 0-10000 credits
vacuum_cost_limit = 200            # 1-10000 credits


fsync = off                # turns forced synchronization on or off

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

seq_page_cost = 1.0            # measured on an arbitrary scale
random_page_cost = 3.0            # same scale as above
effective_cache_size = 1024MB
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

autovacuum = on            # Enable autovacuum subprocess?  'on'
autovacuum_naptime = 1min        # time between autovacuum runs
autovacuum_vacuum_threshold = 500    # min number of row updates before
autovacuum_analyze_threshold = 250    # min number of row updates before
autovacuum_vacuum_scale_factor = 0.2    # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.1    # fraction of table size before analyze
autovacuum_vacuum_cost_delay = 0    # default vacuum cost delay for
autovacuum_vacuum_cost_limit = 200    # default vacuum cost limit for


#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
datestyle = 'iso, mdy'
timezone = UTC                # actually, defaults to TZ environment
lc_messages = 'en_US.UTF-8'        # locale for system error message
                    # strings
lc_monetary = 'en_US.UTF-8'        # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'        # locale for number formatting
lc_time = 'en_US.UTF-8'            # locale for time formatting


#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------

escape_string_warning = off



===== postgresql.conf ends =====

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

On Fri, Feb 20, 2009 at 1:34 PM, Battle Mage <battlemage@gmail.com> wrote:
> 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?

Why is it bad?  High load can mean a number of things.

The only way to reduce the load is to get the client to submit
requests slower.  I don't think you'll be successful in tuning the
database to run slower.  I think you're headed in the wrong direction.

-Dave

On Fri, Feb 20, 2009 at 2:34 PM, Battle Mage <battlemage@gmail.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?

On Fri, 20 Feb 2009, David Rees wrote:

> On Fri, Feb 20, 2009 at 1:34 PM, Battle Mage <battlemage@gmail.com> wrote:
>> 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?
>
> Why is it bad?  High load can mean a number of things.
>
> The only way to reduce the load is to get the client to submit
> requests slower.  I don't think you'll be successful in tuning the
> database to run slower.  I think you're headed in the wrong direction.

note that on linux the loadave includes processes that are stalled waiting
for I/O to complete. as a result loadave isn't the entire picture. you
need to also look to see what the cpu idle time looks like.

that being said, I am generally very happy with loadave <= # cores and
consider loadave <= 2x # cores to be acceptable

it's nowhere near perfect, but it seems to serve me well as a rule of
thumb.

David Lang

On Mon, Feb 23, 2009 at 2:02 PM,  <david@lang.hm> wrote:
> On Fri, 20 Feb 2009, David Rees wrote:
>
>> On Fri, Feb 20, 2009 at 1:34 PM, Battle Mage <battlemage@gmail.com> wrote:
>>>
>>> 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?
>>
>> Why is it bad?  High load can mean a number of things.
>>
>> The only way to reduce the load is to get the client to submit
>> requests slower.  I don't think you'll be successful in tuning the
>> database to run slower.  I think you're headed in the wrong direction.
>
> note that on linux the loadave includes processes that are stalled waiting
> for I/O to complete. as a result loadave isn't the entire picture. you need
> to also look to see what the cpu idle time looks like.
>
> that being said, I am generally very happy with loadave <= # cores and
> consider loadave <= 2x # cores to be acceptable
>
> it's nowhere near perfect, but it seems to serve me well as a rule of thumb.

And it's very dependent on type of load.  For our primary customer
data database a load of 80 to 120 is not uncommon during certain
operations (like adding a slave back to the fark and it gets a ton of
requests while it's loading up its cache) and it stays responsive.
OTOH, a load of 20 on a reporting server doing tons of sequential
scans and allocating a lot of memory is way overloaded for the same
server type.

I had responsive behaviour into the 300 or 400 load range running
pgbench in "destroy all servers mode (-c 500 -t 10000000 or something
like that) on that machine.  Sure, it wasn't exactly peppy or
anything, but most small queries were still running in well under a
second.