Re: [GENERAL] Perfomance decreasing
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 <[EMAIL PROTECTED]> on 08/14/2001 04:38:59 AM To: [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Perfomance decreasing
On Mon, Aug 20, 2001 at 02:41:05PM -0400, [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> on 08/14/2001 04:38:59 AM > > To: [EMAIL PROTECTED] > 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" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Perfomance decreasing
Tom Lane wrote: > > Hiroshi Inoue <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Perfomance decreasing
Hiroshi Inoue <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Perfomance decreasing
Tom Lane wrote: > > Hiroshi Inoue <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Perfomance decreasing
Hiroshi Inoue <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Perfomance decreasing
Tom Lane wrote: > > Hiroshi Inoue <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Perfomance decreasing
Hiroshi Inoue <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Perfomance decreasing
>> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
