Thread: 7.1 performance

7.1 performance

From
"Thomas Heller"
Date:
Hi there,

i just upgraded my database system from 7.0.3 to 7.1 ... i did a dumpall and
psql -f dump.sql to the new db. the data is in place correctly and its
running fine. but as soon as i put the connections back on to the db the
load raised to 6. prior to the upgrade the system load was an 0.3 ... now it
is at 6.

is this normal for 7.1 to need so much more hardware? i use the same
settings for sort_mem, buffers, etc. just no fsync. I thought 7.1 wouldn't
need that anymore.

Any suggestions that i should do? the performance is really bad now :(

btw: the db is a dedicated p3-800 + 1gig ram + uw scsi drives running
redhat6.2

thanks cu,
thomas



Re: 7.1 performance

From
Tom Lane
Date:
"Thomas Heller" <maniax@comtron.net> writes:
> is this normal for 7.1 to need so much more hardware?

No...

> i use the same
> settings for sort_mem, buffers, etc. just no fsync. I thought 7.1 wouldn't
> need that anymore.

Does the behavior change if you turn off fsync again?

            regards, tom lane

RE: 7.1 performance

From
"Mikheev, Vadim"
Date:
> i just upgraded my database system from 7.0.3 to 7.1 ... i
> did a dumpall and psql -f dump.sql to the new db. the data
> is in place correctly and its running fine. but as soon as
> i put the connections back on to the db the load raised to
> 6. prior to the upgrade the system load was an 0.3 ...
> now it is at 6.
>
> is this normal for 7.1 to need so much more hardware? i use
> the same settings for sort_mem, buffers, etc. just no fsync.
> I thought 7.1 wouldn't need that anymore.

7.1 requires fsync! fsync-s are just much more optimized there.

> Any suggestions that i should do? the performance is really bad now :(

What kind of queries have bad performance now?
For insert/update/delete queries you could consider increasing
wal_buffers (~256) and wal_files (~16).

Vadim

Re: 7.1 performance

From
"Thomas Heller"
Date:
Hi,

> > i use the same
> > settings for sort_mem, buffers, etc. just no fsync. I thought 7.1
wouldn't
> > need that anymore.
>
> Does the behavior change if you turn off fsync again?

No.

The Database is loaded with up to 25req/s ... most of them are just simple
SELECTS (no ORDER BY, no JOINS) ... i use indexes on all important fields.
Some queries just return 1 row ... some even 1.000 ... when i run these
queries in psql they seem fast and not problematic. (i did no query
optimizing yet) Note: its a backend database to a webpage (php). There are
arround 8 postgres processes running on the machine. Through the mass of
Queries i can't really say WHO is causing the load to the machine. As the
load raised to about 17 after 1 hour online, i switched back to 7.0.3 ...
now the machine is at load 0,3 again and everything is fine.

Here are some setting from my postgresql.conf

log_connections = off
fsync = on (also off tested)
tcpip_socket = true
max_connections = 32
sort_mem = 4096
shared_buffers = 128

wal_buffers = 8
wal_files = 8
wal_sync_method = fsync
wal_debug = 0
commit_delay = 0
commit_siblings = 5
checkpoint_segments = 3
checkpoint_timeout = 300

i would really be interested in using 7.1 cause i set it up for a new
project (running a dual-p3-1ghz + 2 gig ram) and this machine really needs
good performance and i use (binary &|) which wheren't available in <7.1
afaik.

thanks,
thomas



Re: 7.1 performance

From
Anuradha Ratnaweera
Date:
I had to run a script which did few thousand queries on 7.1 few times and
after that even the queries that are done entirely based on indexes were
also _VERY_ slow. But I did a "vacuumdb dbname" as postgres user and after
that thigs were back to normal - very fast.

But I could _NOT_ reproduce this.

Does the number of WAL files have to do anything with this?

Anuradha

On Wed, 25 Apr 2001, Thomas Heller wrote:

> Hi there,
>
> i just upgraded my database system from 7.0.3 to 7.1 ... i did a
> dumpall and psql -f dump.sql to the new db. the data is in place
> correctly and its running fine. but as soon as i put the connections
> back on to the db the load raised to 6. prior to the upgrade the
> system load was an 0.3 ... now it is at 6.
>
> is this normal for 7.1 to need so much more hardware? i use the same
> settings for sort_mem, buffers, etc. just no fsync. I thought 7.1
> wouldn't need that anymore.
>
> Any suggestions that i should do? the performance is really bad now :(
>
> btw: the db is a dedicated p3-800 + 1gig ram + uw scsi drives running
> redhat6.2
>
> thanks cu,
> thomas
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


RE: Re: 7.1 performance

From
"Blake, Stephen"
Date:
Two things you should check.

1) Are you using persistent connections in your PHP scripts?
2) Are you certain your queries are utilizing the indices you have set up
for them.

For the answer to 1) check your php configuration.
For the answer to 2) do an explain on each query that is slow, making sure
it's hitting the index you think it should be.

Stephen Blake
s t e p h e n <dot/> b l a k e <at/> v e r i t e c t <dot/> c o m

-----Original Message-----
From: Thomas Heller [mailto:maniax@comtron.net]
Sent: Thursday, April 26, 2001 12:56 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Re: 7.1 performance


Hi,

> > i use the same
> > settings for sort_mem, buffers, etc. just no fsync. I thought 7.1
wouldn't
> > need that anymore.
>
> Does the behavior change if you turn off fsync again?

No.

The Database is loaded with up to 25req/s ... most of them are just simple
SELECTS (no ORDER BY, no JOINS) ... i use indexes on all important fields.
Some queries just return 1 row ... some even 1.000 ... when i run these
queries in psql they seem fast and not problematic. (i did no query
optimizing yet) Note: its a backend database to a webpage (php). There are
arround 8 postgres processes running on the machine. Through the mass of
Queries i can't really say WHO is causing the load to the machine. As the
load raised to about 17 after 1 hour online, i switched back to 7.0.3 ...
now the machine is at load 0,3 again and everything is fine.

Here are some setting from my postgresql.conf

log_connections = off
fsync = on (also off tested)
tcpip_socket = true
max_connections = 32
sort_mem = 4096
shared_buffers = 128

wal_buffers = 8
wal_files = 8
wal_sync_method = fsync
wal_debug = 0
commit_delay = 0
commit_siblings = 5
checkpoint_segments = 3
checkpoint_timeout = 300

i would really be interested in using 7.1 cause i set it up for a new
project (running a dual-p3-1ghz + 2 gig ram) and this machine really needs
good performance and i use (binary &|) which wheren't available in <7.1
afaik.

thanks,
thomas



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Re: 7.1 performance

From
"Thomas Heller"
Date:
Hi,

> Two things you should check.
>
> 1) Are you using persistent connections in your PHP scripts?

not that good for 250+ apache httpd sessions running (only serving php ...
images/static content is balanced to another server). ;) definitly no.

> 2) Are you certain your queries are utilizing the indices you have set up
> for them.

they use indices .. i'm pretty sure with it cause i didn't change any
indices as i pg_dumpall'd the db and restored it to 7.1. As said .. the
7.0.3 is laughing about the traffic (load 0.3) and the 7.1 is about to break
(load 16). No change in php conf ... the only change is the upgrade to 7.1.

hmpf (i hope the next dual p3-1ghz won't break with 7.1, i need it there
...)

cu
thomas