Thread: Perfomance decreasing
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?
Best regards,
Alexander mailto:sas@mplik.ru
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
usefulfor 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
limitationsof (the current version of) PostgreSQL. Don't use inheritance, don't use on 24x7 systems, whatever. It
doesn'thave 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'ttried 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 > > regards, tom lane
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
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"
> 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
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 ----------------------------------
On Mon, Aug 20, 2001 at 02:41:05PM -0400, wsheldah@lexmark.com wrote: > > > 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. I reduced the number of times vacuum was run with analyze, and run a normal vacuum twice a day. The files are no longer growing since. I'm leaving for hollidays now, I'll investigate more when I return next week. /erwin > > > > > 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 > > > -- Erwin Lansing -- http://droso.org "You've got mail"
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