Thread: vacuum error
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. Any pointers? -- Eric Cholet
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 12 March 2003 6:27 am, 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? > > Thanks and greetings, > > Stefan Sturmn > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--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