vacuum error

Hello,

we have a performance problem with PostgreSQL 7.2.4. But first I try to
explain our situation:

We have an online game. This game is a fantasy game, where you can buy
and sell players for your team.
All players for the teams are stored in one table. So in this table we
have just one primary key and to foreign keys.
When we have around 500 Users at one time on the maschine, the game is
slowing down(Time for one spage up to 1minute and more). And I think
the problem is this table. There are a lot of reading and writing
connections to this table.

So now my question: What can I do to increase the performance?

Thanks and greetings,

Stefan Sturmn

On Wed, 12 Mar 2003, Stefan Sturm wrote:

we have a performance problem with PostgreSQL 7.2.4. But first I try to
explain our situation:

We have an online game. This game is a fantasy game, where you can buy
and sell players for your team.
All players for the teams are stored in one table. So in this table we
have just one primary key and to foreign keys.
When we have around 500 Users at one time on the maschine, the game is
slowing down(Time for one spage up to 1minute and more). And I think
the problem is this table. There are a lot of reading and writing
connections to this table.

So now my question: What can I do to increase the performance?

We'll need more information, like what kind of queries you're doing, to
give detailed answers, but as general questions:

Is that 500 simultaneous queries to the database? What sort of real db
traffic are you seeing? Are you analyzing frequently enough? Are you
vacuuming enough? What are the postgresql.conf settings (specifically
shared_buffers and sort_mem as a starting point)? What kind of query plans
are you getting for your queries (see explain analyze)?

It's possible that if you're doing lots of updates that you may be running
into concurrency problems with the foreign keys, but without knowing the
query mix (and examples) you're running, it's hard to say.

Eric Cholet <cholet@logilune.com> writes:

I get this error when vacuuming a table:
PANIC: open of /usr/local/pgsql/data/pg_clog/0005 failed: No such file or
directory
using 7.3.2.

What file names are actually present in pg_clog/ ?

regards, tom lane

Are you using the default settings in postgresql.conf? If so you will need to
tinker with the memory settings such as shared_buffers (I've currently set
mine to 1000 but am still learning and testing).

The default configuration is designed to ensure that postgres will start up
on as many installations as possible. It is not configured for performance -
that is left as an exercise for the DBA based on his/her individual
requirements. Search the archives for lots of tips.

Cheers,
Steve

--On Wednesday, March 12, 2003 11:24:43 -0500 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

Eric Cholet <cholet@logilune.com> writes:

I get this error when vacuuming a table:
PANIC: open of /usr/local/pgsql/data/pg_clog/0005 failed: No such file
or directory
using 7.3.2.

What file names are actually present in pg_clog/ ?

0000 0001 0002 0003 0004

Thanks,

--
Eric Cholet

Eric Cholet <cholet@logilune.com> writes:

I get this error when vacuuming a table:
PANIC: open of /usr/local/pgsql/data/pg_clog/0005 failed: No such file
or directory
using 7.3.2.

What file names are actually present in pg_clog/ ?

0000 0001 0002 0003 0004

Hm, interesting. You had any crashes recently? Could you show us an
"ls -l" listing of those clog files (I want to know their sizes and
mod dates...)

regards, tom lane

On Wed, 12 Mar 2003, Stefan Sturm wrote:

Hello,

we have a performance problem with PostgreSQL 7.2.4. But first I try to
explain our situation:

We have an online game. This game is a fantasy game, where you can buy
and sell players for your team.
All players for the teams are stored in one table. So in this table we
have just one primary key and to foreign keys.
When we have around 500 Users at one time on the maschine, the game is
slowing down(Time for one spage up to 1minute and more). And I think
the problem is this table. There are a lot of reading and writing
connections to this table.

So now my question: What can I do to increase the performance?

Hi Stefan, the first step to increasing performance is surveying the
current situation. When the machine starts to slow down, what does the
output of top look like? Is the machine running out of any resources like
file handles or memory? Note that most flavors of unix will not show a
lot of memory free they will show it being used as cache. If your machine
shows 400 megs of system cache and postgresql is using 4 megs of shared
memory, you aren't giving enough to postgresql. It'll go slow.

If you allocate too much memory for certain things (sort_mem is a
notorious gotcha for performance tuners) then you might allocate all your
memory and start using swap. It'll go slow then too.

So it's about finding the sweet spot.

What's in your postgresql.conf file?
What's your kernel's shm and max files / inodes type stuff set to?
How much memory does your machine have?
What kinds of queries are you running?
Are you reconnecting in your script or using a single connection?
Are you pooling connections?
Are you running lots of updates in autocommit that belong together
inside one transaction? It's much faster to put em together.

--On Wednesday, March 12, 2003 11:40:39 -0500 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

Eric Cholet <cholet@logilune.com> writes:

I get this error when vacuuming a table:
PANIC: open of /usr/local/pgsql/data/pg_clog/0005 failed: No such file
or directory
using 7.3.2.

What file names are actually present in pg_clog/ ?

0000 0001 0002 0003 0004

Hm, interesting. You had any crashes recently?

Yes, I've had many crashes. Always when vacuuming a largish (500 Mb) table.
I suspected faulty hardware, so I dropped and recreated the tables
several times.

Could you show us an
"ls -l" listing of those clog files (I want to know their sizes and
mod dates...)

-rw------- 1 postgres wheel 262144 Dec 30 03:49 0000
-rw------- 1 postgres wheel 262144 Jan 2 19:12 0001
-rw------- 1 postgres wheel 262144 Feb 12 12:30 0002
-rw------- 1 postgres wheel 262144 Mar 10 06:51 0003
-rw------- 1 postgres wheel 253952 Mar 12 17:53 0004

Thanks,

--
Eric Cholet

Eric Cholet <cholet@logilune.com> writes:

I get this error when vacuuming a table:
PANIC: open of /usr/local/pgsql/data/pg_clog/0005 failed: No such file
or directory
using 7.3.2.

Hm, interesting. You had any crashes recently?

Yes, I've had many crashes. Always when vacuuming a largish (500 Mb) table.
I suspected faulty hardware, so I dropped and recreated the tables
several times.

Did that help? What were the crash symptoms exactly --- are you talking
about previous occurrences of this same error message, or other things?
Anything interesting in the postmaster's stderr log?

Could you show us an
"ls -l" listing of those clog files (I want to know their sizes and
mod dates...)

-rw------- 1 postgres wheel 262144 Dec 30 03:49 0000
-rw------- 1 postgres wheel 262144 Jan 2 19:12 0001
-rw------- 1 postgres wheel 262144 Feb 12 12:30 0002
-rw------- 1 postgres wheel 262144 Mar 10 06:51 0003
-rw------- 1 postgres wheel 253952 Mar 12 17:53 0004

You seem to be still at least several tens of thousands of transactions
away from actually needing an 0005 clog segment. (It'd be worth your
time to run pg_controldata and verify that the next transaction ID
counter is still short of 5meg, ie 5242880.)

I'm guessing that the problem is data corruption in the table that you
are vacuuming when you get the error. If you're lucky it's just one row
broken with a bogus xmin (or xmax) transaction ID.

What you can do is manually create an 0005 segment file. Make sure it
contains exactly 262144 zero bytes (dd from /dev/zero may help here).
Give it the same ownership and permissions as the existing files. Then,
when you vacuum, the broken row will look like it came from a failed
transaction, and it should disappear automatically.

But you'd better look into the root cause of the problem. Have you run
memory and disk diagnostics lately?

regards, tom lane

On Thu, 2003-03-13 at 04:28, scott.marlowe wrote:

On Wed, 12 Mar 2003, Stefan Sturm wrote:

Hello,

we have a performance problem with PostgreSQL 7.2.4. But first I try to
explain our situation:

We have an online game. This game is a fantasy game, where you can buy
and sell players for your team.
All players for the teams are stored in one table. So in this table we
have just one primary key and to foreign keys.
When we have around 500 Users at one time on the maschine, the game is
slowing down(Time for one spage up to 1minute and more). And I think
the problem is this table. There are a lot of reading and writing
connections to this table.

So now my question: What can I do to increase the performance?

Hi Stefan, the first step to increasing performance is surveying the
current situation. When the machine starts to slow down, what does the
output of top look like? Is the machine running out of any resources like
file handles or memory? Note that most flavors of unix will not show a
lot of memory free they will show it being used as cache. If your machine
shows 400 megs of system cache and postgresql is using 4 megs of shared
memory, you aren't giving enough to postgresql. It'll go slow.

To add to what Scott says, what does vmstat report (are you swapping?).
If you're not already, install sar and see how much I/O and CPU you're
consuming - one important issue is whether you're running out of I/O
capacity or CPU or RAM.

Stephen