Perfomance decreasing

On Tue, Aug 14, 2001 at 02:06:40PM +0600, Alexander Loginov wrote:

Hello.

I have a question about perfomance.
I'm running PostgreSQL 7.1.2 at FreeBSD 4.3.

For the first 1-2 days of running perfomance is excellent. But
after that, speed began to decrease. And after a week of
operation, perfomance falls 8-10 times, than at first day of
using.

I'm doing vacuum periodically (once a hour), but perfomance
still falls down.

After that I dump database as text file, make dropdb & createdb
and after that, restore database from dump -> Perfomance is
excellent again (for 1-2 days).

Why this situation occures? May be I must use "VACUUM ANALYSE"
instead of VACUUM?

I have actually the same problem, also FreeBSD 4.3, pgsql 7.1.2. I do
use VACUUM ANALYSE quite often. The problem in the end gets that bad
that perl-jobs cannot perform any SELECTs, or at least they stop
returning results before dbi times out. So far I have tracked the
problem down to the size of the database in the filesystem, where
problems start occurring when it exceeds 1,4 Gb. A
dump/drop/create/restore reduces files size to approx. 350 Mb.

Any pointers would be helpful as a weekly dump/restore is not quite
optimal :)

/erwin

--
Erwin Lansing -- http://droso.org
"You've got mail"

On Tue, Aug 14, 2001 at 02:06:40PM +0600, Alexander Loginov wrote:

Hello.

I have a question about perfomance.
I'm running PostgreSQL 7.1.2 at FreeBSD 4.3.

For the first 1-2 days of running perfomance is excellent. But
after that, speed began to decrease. And after a week of
operation, perfomance falls 8-10 times, than at first day of
using.

I'm doing vacuum periodically (once a hour), but perfomance
still falls down.

After that I dump database as text file, make dropdb & createdb
and after that, restore database from dump -> Perfomance is
excellent again (for 1-2 days).

Why this situation occures? May be I must use "VACUUM ANALYSE"
instead of VACUUM?

I have actually the same problem, also FreeBSD 4.3, pgsql 7.1.2. I do
use VACUUM ANALYSE quite often. The problem in the end gets that bad
that perl-jobs cannot perform any SELECTs, or at least they stop
returning results before dbi times out. So far I have tracked the
problem down to the size of the database in the filesystem, where
problems start occurring when it exceeds 1,4 Gb. A
dump/drop/create/restore reduces files size to approx. 350 Mb.

Any pointers would be helpful as a weekly dump/restore is not quite
optimal :)

/erwin

--
Erwin Lansing -- http://droso.org
"You've got mail"

For the first 1-2 days of running perfomance is excellent. But
after that, speed began to decrease. And after a week of
operation, perfomance falls 8-10 times, than at first day of
using.

I'm doing vacuum periodically (once a hour), but perfomance
still falls down.

It sounds to me like you may be running into index growth problems.
VACUUM is presently not good about shrinking indexes. If you drop
and recreate the indexes used by your most important queries, does
the performance go back to where it was?

Why this situation occures? May be I must use "VACUUM ANALYSE"
instead of VACUUM?

Occasional VACUUM ANALYZEs are a good idea.

regards, tom lane

Tom Lane wrote:

I'm doing vacuum periodically (once a hour), but perfomance
still falls down.

It sounds to me like you may be running into index growth problems.
VACUUM is presently not good about shrinking indexes.

I always enjoy Tom's comments - he is the master of understatement and always helpful.

In this case, however, I think he may be understating too much. I read the original question as "PostgreSQL is not useful for production systems." Call me melodramatic if you like: you are probably right.

The point, I guess, is this: it would be really useful to have a document somewhere that honestly described the limitations of (the current version of) PostgreSQL. Don't use inheritance, don't use on 24x7 systems, whatever. It doesn't have to be fancy formatting, a brain-dump to a text file would be excellent.... (This is a hint, Tom et al!! :-))

If you drop
and recreate the indexes used by your most important queries, does
the performance go back to where it was?

For what it's worth: I observed a similar issue and found that a dump and restore of all the databases helped. I haven't tried just recreating the index. I'll try it out and maybe post a test script to reproduce the issue..... (where?)

    --- Allan.

Hello,

I'm doing vacuum periodically (once a hour), but perfomance
still falls down.

TL> It sounds to me like you may be running into index growth problems.
TL> VACUUM is presently not good about shrinking indexes. If you drop
TL> and recreate the indexes used by your most important queries, does
TL> the performance go back to where it was?

Thanks for solution. After REINDEX command for most significant
tables, perfomance was restored. I will do this procedure every
two days.

I have only one small question. Can I do REINDEX during inserting
of information into tables. Or I must block somehow updating of
tables.

Best regards,
Alexander Loginov mailto:sas@mplik.ru

Alexander Loginov <sas@mplik.ru> writes:

I have only one small question. Can I do REINDEX during inserting
of information into tables. Or I must block somehow updating of
tables.

Hmmm ... it looks like REINDEX only grabs AccessShareLock on the target
relation, which seems very wrong. Hiroshi, doesn't it need to get a
stronger lock to prevent concurrent insertions? For that matter,
shouldn't the lock be obtained a lot earlier, to ensure the table isn't
dropped partway through? There's a lot of processing here that seems
to be executed while holding no lock at all :-(

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

Alexander Loginov <sas@mplik.ru> writes:

I have only one small question. Can I do REINDEX during inserting
of information into tables. Or I must block somehow updating of
tables.

Hmmm ... it looks like REINDEX only grabs AccessShareLock on the target
relation, which seems very wrong.

Sorry I couldn't find where AccessShareLock is grabbed now.
AccessExclusiveLock is acquired in reindex_index but do you
mean it's too late ?

regards,
Hiroshi Inoue

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Hmmm ... it looks like REINDEX only grabs AccessShareLock on the target
relation, which seems very wrong.

Sorry I couldn't find where AccessShareLock is grabbed now.
AccessExclusiveLock is acquired in reindex_index but do you
mean it's too late ?

I was looking at reindex_relation, which doesn't seem to grab anything
higher than AccessShareLock at all. But in any case, I think you should
be holding a lock on the parent table a lot sooner than you do, and
should not release it until transaction commit. reindex_relation ought
to be holding some lock on the table from the very start, IMHO. Maybe
AccessExclusiveLock is more than is needed, but I'd want to see a good
argument that it's safe not to hold an exclusive lock while you're doing
this stuff. What happens if two backends try to REINDEX at the same time?

regards, tom lane

Tom Lane wrote:

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Hmmm ... it looks like REINDEX only grabs AccessShareLock on the target
relation, which seems very wrong.

Sorry I couldn't find where AccessShareLock is grabbed now.
AccessExclusiveLock is acquired in reindex_index but do you
mean it's too late ?

I was looking at reindex_relation, which doesn't seem to grab anything
higher than AccessShareLock at all. But in any case, I think you should
be holding a lock on the parent table a lot sooner than you do, and
should not release it until transaction commit. reindex_relation ought
to be holding some lock on the table from the very start, IMHO.

OK I don't object to it.

Maybe
AccessExclusiveLock is more than is needed, but I'd want to see a good
argument that it's safe not to hold an exclusive lock while you're doing
this stuff. What happens if two backends try to REINDEX at the same time?

One backend would be blocked by another one because reindex_relation
calls reindex_index and reindex_index grabs an ExclusiveLock on the
relation.
Am I missing anything ?

regards,
Hiroshi Inoue

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

One backend would be blocked by another one because reindex_relation
calls reindex_index and reindex_index grabs an ExclusiveLock on the
relation.
Am I missing anything ?

It'd be okay if you *held* the lock throughout. Grabbing and releasing
it isn't safe IMHO. Just for one problem, what if someone else tries
to drop the relation in one of those intervals where you're not holding
a lock?

regards, tom lane

Tom Lane wrote:

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

One backend would be blocked by another one because reindex_relation
calls reindex_index and reindex_index grabs an ExclusiveLock on the
relation.
Am I missing anything ?

It'd be okay if you *held* the lock throughout. Grabbing and releasing
it isn't safe IMHO.

Maybe I'm slow on the uptake because I've just finished a
short vacation. Where do I release the lock ?

regards,
Hiroshi Inoue

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

Where do I release the lock ?

I'd say you shouldn't release it at all. Let it be held until end of
transaction.

rel = heap_open(relid, AccessExclusiveLock);
...
heap_close(rel, NoLock); /* close rel, keep lock till end of xact */

regards, tom lane

Tom Lane wrote:

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

Where do I release the lock ?

I'd say you shouldn't release it at all.

As far as I see I'm not releasing it.

regards,
Hiroshi Inoue

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

I'd say you shouldn't release it at all.

As far as I see I'm not releasing it.

Oh, I hadn't looked closely at reindex_index. Hmm... okay, you are
holding the lock acquired there. But you're still acquiring it way too
late for my taste. All of the setup work in reindex_relation seems to
be done without any lock.

regards, tom lane

Tom Lane wrote:

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

I'd say you shouldn't release it at all.

As far as I see I'm not releasing it.

Oh, I hadn't looked closely at reindex_index. Hmm... okay, you are
holding the lock acquired there.

Yes reindex_index is guarding itself.

But you're still acquiring it way too
late for my taste. All of the setup work in reindex_relation seems to
be done without any lock.

OK I would add a heap_open() to reindex_relation.
Please wait a while. I've not reorganized my local
source yet.

regards,
Hiroshi Inoue

In this case, however, I think he may be understating too much. I read

the original question as "PostgreSQL is not useful for production systems."
Call me melodramatic if you like: you are probably right.

The point, I guess, is this: it would be really useful to have a document

somewhere that honestly described the limitations of (the current version
of) PostgreSQL.

Do you mean Postgres becomes very weak when the size of a database achieves
1.5Gb or something close to it?

Maybe this is one of typical questions, but I have heard people complaining
that Postgres is just for quite small bases. Now we have to choose a free
database for then inexpensive branch of our project and Interbase looks
better at capability to work with quite big bases (up to 10-20Gb). I am not
sure now that Postgres will work with bases greater than 10Gb, what does All
think?

Thanks in advance, Ivan Babikoff.

On Fri, Aug 17, 2001 at 02:09:33PM +0600, Ivan Babikov wrote:

In this case, however, I think he may be understating too much. I read

the original question as "PostgreSQL is not useful for production systems."
Call me melodramatic if you like: you are probably right.

The point, I guess, is this: it would be really useful to have a document

somewhere that honestly described the limitations of (the current version
of) PostgreSQL.

Do you mean Postgres becomes very weak when the size of a database achieves
1.5Gb or something close to it?

Maybe this is one of typical questions, but I have heard people complaining
that Postgres is just for quite small bases. Now we have to choose a free
database for then inexpensive branch of our project and Interbase looks
better at capability to work with quite big bases (up to 10-20Gb). I am not
sure now that Postgres will work with bases greater than 10Gb, what does All
think?

Thanks in advance, Ivan Babikoff.

In my experience postgresql has no problems with big databases. I have had several
problems but they had to do with the os and hardware not the db.

- Einar Karttunen

Does it help if you drop and recreate the indexes, in addition to the vacuuming
you're doing now? I think this was suggested not long ago on this list.

Erwin Lansing <erwin%lansing.dk@interlock.lexmark.com> on 08/14/2001 04:38:59 AM

To: pgsql-general%postgresql.org@interlock.lexmark.com
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: Re: [GENERAL] Perfomance decreasing

On Tue, Aug 14, 2001 at 02:06:40PM +0600, Alexander Loginov wrote:

Hello.

I have a question about perfomance.
I'm running PostgreSQL 7.1.2 at FreeBSD 4.3.

For the first 1-2 days of running perfomance is excellent. But
after that, speed began to decrease. And after a week of
operation, perfomance falls 8-10 times, than at first day of
using.

I'm doing vacuum periodically (once a hour), but perfomance
still falls down.

After that I dump database as text file, make dropdb & createdb
and after that, restore database from dump -> Perfomance is
excellent again (for 1-2 days).

Why this situation occures? May be I must use "VACUUM ANALYSE"
instead of VACUUM?

I have actually the same problem, also FreeBSD 4.3, pgsql 7.1.2. I do
use VACUUM ANALYSE quite often. The problem in the end gets that bad
that perl-jobs cannot perform any SELECTs, or at least they stop
returning results before dbi times out. So far I have tracked the
problem down to the size of the database in the filesystem, where
problems start occurring when it exceeds 1,4 Gb. A
dump/drop/create/restore reduces files size to approx. 350 Mb.

Any pointers would be helpful as a weekly dump/restore is not quite
optimal :)

/erwin

--
Erwin Lansing -- http://droso.org
"You've got mail"

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Friday 17 August 2001 15:09, Ivan Babikov wrote:

In this case, however, I think he may be understating too much. I read

the original question as "PostgreSQL is not useful for production systems."
Call me melodramatic if you like: you are probably right.

The point, I guess, is this: it would be really useful to have a document

somewhere that honestly described the limitations of (the current version
of) PostgreSQL.

Do you mean Postgres becomes very weak when the size of a database achieves
1.5Gb or something close to it?

Maybe this is one of typical questions, but I have heard people complaining
that Postgres is just for quite small bases. Now we have to choose a free
database for then inexpensive branch of our project and Interbase looks
better at capability to work with quite big bases (up to 10-20Gb). I am not
sure now that Postgres will work with bases greater than 10Gb, what does
All think?

I do not see any problems. It works for me, and I have no problems. The only
problem you could have is with vacuum. It is solvable anyway. But if you have
not so much updates it is not an issue too (I mean if do not update more than
25% of DB each day).

Actually for anyone listening for such advices I would recommend to create a
test installation, and stress test it before go to production. Interbase has
its own problems.

--
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------