Re: [PERFORM] postgresql performance tuning
On Dec 6, 2005, at 5:03 PM, Ameet Kini wrote: table with only 1 index, the time to do a vacuum (without full) went down from 45 minutes to under 3 minutes. Maybe thats not bloat but thats surely surprising. And this was after running vacuum periodically. I'll bet either your FSM settings are too low and/or you don't vacuum often enough for your data churn rate. Without more data, it is hard to solve the right problem. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] postgresql performance tuning
On Tue, Dec 06, 2005 at 04:03:22PM -0600, Ameet Kini wrote: I'm running postgresql v8.0 and my problem is that running vacuum on my indices are blazing fast (upto 10x faster) AFTER running reindex. For a table with only 1 index, the time to do a vacuum (without full) went down from 45 minutes to under 3 minutes. I've also noticed a fairly large increase in vacuum speed after a reindex. (To the point where the reindex + vacuum was faster than just a vacuum.) Mike Stone ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] postgresql performance tuning
> what evidence do you have that you are suffering index bloat? or are > you just looking for solutions to problems that don't exist as an > academic exercise? :-) Well, firstly, its not an academic exercise - Its very much of a real problem that needs a real solution :) I'm running postgresql v8.0 and my problem is that running vacuum on my indices are blazing fast (upto 10x faster) AFTER running reindex. For a table with only 1 index, the time to do a vacuum (without full) went down from 45 minutes to under 3 minutes. Maybe thats not bloat but thats surely surprising. And this was after running vacuum periodically. Ameet > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > Ameet ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] postgresql performance tuning
Tom Lane wrote: Alan Stange <[EMAIL PROTECTED]> writes: Vivek Khera wrote: what evidence do you have that you are suffering index bloat? The files for the two indices on a single table used 7.8GB of space before a reindex, and 4.4GB after. That's not bloat ... that's pretty nearly in line with the normal expectation for a btree index, which is about 2/3rds fill factor. If the compacted index were 10X smaller then I'd agree that you have a bloat problem. I wrote "I don't think this counts as bloat...". I still don't. -- Alan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] postgresql performance tuning
Alan Stange <[EMAIL PROTECTED]> writes: > Vivek Khera wrote: >> what evidence do you have that you are suffering index bloat? > The files for the two indices on a single table used 7.8GB of space > before a reindex, and 4.4GB after. That's not bloat ... that's pretty nearly in line with the normal expectation for a btree index, which is about 2/3rds fill factor. If the compacted index were 10X smaller then I'd agree that you have a bloat problem. Periodic reindexing on this scale is not doing a lot for you except thrashing your disks --- you're just giving space back to the OS that will shortly be sucked up again by the same index. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] postgresql performance tuning
Ameet Kini schrieb: This didn't get through the first time around, so resending it again. Sorry for any duplicate entries. Hello, I have a question on postgres's performance tuning, in particular, the vacuum and reindex commands. Currently I do a vacuum (without full) on all of my tables. I'm curious ... why no full vacuum? I bet that the full vacuum will compact your (index) tables as much as a reindex would. I guess the best advice is to increase FSM and to use autovacuum. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] postgresql performance tuning
Vivek Khera wrote: On Dec 6, 2005, at 11:14 AM, Ameet Kini wrote: need for vacuums. However, it'd be great if there was a similar automatic reindex utility, like say, a pg_autoreindex daemon. Are there any plans for this feature? If not, then would cron scripts be the next best what evidence do you have that you are suffering index bloat? or are you just looking for solutions to problems that don't exist as an academic exercise? :-) The files for the two indices on a single table used 7.8GB of space before a reindex, and 4.4GB after. The table had been reindexed over the weekend and a vacuum was completed on the table about 2 hours ago. The two indices are now 3.4GB smaller. I don't think this counts as bloat, because of our use case. Even so, we reindex our whole database every weekend. -- Alan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] postgresql performance tuning
On Dec 6, 2005, at 11:14 AM, Ameet Kini wrote: need for vacuums. However, it'd be great if there was a similar automatic reindex utility, like say, a pg_autoreindex daemon. Are there any plans for this feature? If not, then would cron scripts be the next best what evidence do you have that you are suffering index bloat? or are you just looking for solutions to problems that don't exist as an academic exercise? :-) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] postgresql performance tuning
Hello, I have a question on postgres's performance tuning, in particular, the vacuum and reindex commands. Currently I do a vacuum (without full) on all of my tables. However, its noted in the docs (e.g. http://developer.postgresql.org/docs/postgres/routine-reindex.html) and on the lists here that indexes may still bloat after a while and hence reindex is necessary. How often do people reindex their tables out there? I guess I'd have to update my cron scripts to do reindexing too along with vacuuming but most probably at a much lower frequency than vacuum. But these scripts do these maintenance tasks at a fixed time (every few hours, days, weeks, etc.) What I would like is to do these tasks on a need basis. So for vacuuming, by "need" I mean every few updates or some such metric that characterizes my workload. Similarly, "need" for the reindex command might mean every few updates or degree of bloat, etc. I came across the pg_autovacuum daemon, which seems to do exactly what I need for vacuums. However, it'd be great if there was a similar automatic reindex utility, like say, a pg_autoreindex daemon. Are there any plans for this feature? If not, then would cron scripts be the next best choice? Thanks, Ameet ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] postgresql performance tuning
On Dec 6, 2005, at 12:44 PM, Ameet Kini wrote: I have a question on postgres's performance tuning, in particular, the vacuum and reindex commands. Currently I do a vacuum (without full) on all of my tables. However, its noted in the docs (e.g. http://developer.postgresql.org/docs/postgres/routine-reindex.html) and on the lists here that indexes may still bloat after a while and hence reindex is necessary. How often do people reindex their tables out Why would you be running a version older than 7.4? Index bloat is mostly a non-issue in recent releases of pg. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] postgresql performance tuning
Ameet Kini <[EMAIL PROTECTED]> writes: > I have a question on postgres's performance tuning, in particular, the > vacuum and reindex commands. Currently I do a vacuum (without full) on all > of my tables. However, its noted in the docs (e.g. > http://developer.postgresql.org/docs/postgres/routine-reindex.html) > and on the lists here that indexes may still bloat after a while and hence > reindex is necessary. How often do people reindex their tables out > there? Never, unless you have actual evidence that your indexes are bloating. It's only very specific use-patterns that have problems. regards, tom lane ---(end of broadcast)--- TIP 1: 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
[PERFORM] postgresql performance tuning
This didn't get through the first time around, so resending it again. Sorry for any duplicate entries. Hello, I have a question on postgres's performance tuning, in particular, the vacuum and reindex commands. Currently I do a vacuum (without full) on all of my tables. However, its noted in the docs (e.g. http://developer.postgresql.org/docs/postgres/routine-reindex.html) and on the lists here that indexes may still bloat after a while and hence reindex is necessary. How often do people reindex their tables out there? I guess I'd have to update my cron scripts to do reindexing too along with vacuuming but most probably at a much lower frequency than vacuum. But these scripts do these maintenance tasks at a fixed time (every few hours, days, weeks, etc.) What I would like is to do these tasks on a need basis. So for vacuuming, by "need" I mean every few updates or some such metric that characterizes my workload. Similarly, "need" for the reindex command might mean every few updates or degree of bloat, etc. I came across the pg_autovacuum daemon, which seems to do exactly what I need for vacuums. However, it'd be great if there was a similar automatic reindex utility, like say, a pg_autoreindex daemon. Are there any plans for this feature? If not, then would cron scripts be the next best choice? Thanks, Ameet ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
