Re: [PERFORM] When to vacuum a table?
On Sun, Nov 26, 2006 at 5:24 AM, in message [EMAIL PROTECTED], Joost Kraaijeveld [EMAIL PROTECTED] wrote: Are there guidelines (or any empirical data) available how to determine how often a table should be vacuumed for optimum performance or is this an experience / trial- and- error thing? For most of our databases we use a daily VACUUM ANALYZE VERBOSE;. We grep the results to show large numbers of removable or dead rows and to show the fsm numbers at the end. There are a few small tables with high update rates which this doesn't cover. To handle that we set the autovacuum to 0.2/0.1 and 1/1 on a ten second interval and do a daily CLUSTER of these tables. We really don't have many tables which can hit these autovacuum thresholds in one day. We have databases which are 400 GB with the vast majority of that being in tables which are insert-only except for a weekly purge of data over a year old. We do nightly vacuums on the few tables with update/delete activity, and a weekly vacuum of the whole database -- right after the delete of old rows from the big tables. -Kevin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] When to vacuum a table?
Hi, Are there guidelines (or any empirical data) available how to determine how often a table should be vacuumed for optimum performance or is this an experience / trial-and-error thing? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(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
Re: [PERFORM] When to vacuum a table?
Hi, From: http://www.postgresql.org/docs/7.4/interactive/sql-vacuum.html VACUUM reclaims storage occupied by deleted tuples. In normal PostgreSQLoperation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables. The vacuum analyze form additionally collects statistics on the disbursion of columns in the database, which the optimizer uses when it calculates just how to execute queries. The availability of this data can make a tremendous difference in the execution speed of queries. This command can also be run from cron, but it probably makes more sense to run this command as part of your nightly backup procedure - if vacuum is going to screw up the database, you'd prefer it to happen immediately after (not before!) you've made a backup! The vacuum command is very reliable, but conservatism is the key to good system management. So, if you're using the export procedure described above, you don't need to do this extra step. All its tables constantly manipulated (INSERT, UPDATE, DELETE) they need a VACUUM, therefore the necessity to execute at least one time to the day normally of dawn if its database will be very great . [],s Marcelo Costa Secretaria Executiva de Educação do Pará Amazonia - Pará - Brazil 2006/11/26, Joost Kraaijeveld [EMAIL PROTECTED]: Hi, Are there guidelines (or any empirical data) available how to determine how often a table should be vacuumed for optimum performance or is this an experience / trial-and-error thing? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(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 -- Marcelo Costa
Re: [PERFORM] When to vacuum a table?
On Sun, Nov 26, 2006 at 09:43:11AM -0300, Marcelo Costa wrote: All its tables constantly manipulated (INSERT, UPDATE, DELETE) they need a VACUUM Just a minor clarification here: INSERT does not create dead rows, only UPDATE and DELETE do. Thus, if you only insert rows, you do not need to vacuum (although you probably need to analyze). /* Steinar */ -- Homepage: http://www.sesse.net/ ---(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
Re: [PERFORM] When to vacuum a table?
Sorry, realy you are correct. [],s Marcelo Costa. 2006/11/26, Steinar H. Gunderson [EMAIL PROTECTED]: On Sun, Nov 26, 2006 at 09:43:11AM -0300, Marcelo Costa wrote: All its tables constantly manipulated (INSERT, UPDATE, DELETE) they need a VACUUM Just a minor clarification here: INSERT does not create dead rows, only UPDATE and DELETE do. Thus, if you only insert rows, you do not need to vacuum (although you probably need to analyze). /* Steinar */ -- Homepage: http://www.sesse.net/ ---(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 -- Marcelo Costa
Re: [PERFORM] When to vacuum a table?
On 26-Nov-06, at 8:11 AM, Steinar H. Gunderson wrote: On Sun, Nov 26, 2006 at 09:43:11AM -0300, Marcelo Costa wrote: All its tables constantly manipulated (INSERT, UPDATE, DELETE) they need a VACUUM Just a minor clarification here: INSERT does not create dead rows, only UPDATE and DELETE do. Thus, if you only insert rows, you do not need to vacuum (although you probably need to analyze). Not entirely true. An insert rollback will create dead rows. If you attempt and fail a large number of insert transactions then you will still need to vacuum. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] When to vacuum a table?
On Sun, Nov 26, 2006 at 09:24:29AM -0500, Rod Taylor wrote: attempt and fail a large number of insert transactions then you will still need to vacuum. And you still need to vacuum an insert-only table sometimes, because of the system-wide vacuum requirement. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] When to vacuum a table?
Rod Taylor wrote: Just a minor clarification here: INSERT does not create dead rows, only UPDATE and DELETE do. Thus, if you only insert rows, you do not need to vacuum (although you probably need to analyze). Is there no real-time garbage collection at all in Postgres? And if so, is this because nobody has had time to implement garbage collection, or for a more fundamental reason, or because VACUUM is seen as sufficient? I'm just curious ... Vacuum has always seemed to me like an ugly wart on the pretty face of Postgres. (I say this even though I implemented an identical solution on a non-relational chemistry database system a long time ago. I didn't like it then, either.) Craig ---(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
Re: [PERFORM] When to vacuum a table?
Craig A. James [EMAIL PROTECTED] writes: Is there no real-time garbage collection at all in Postgres? No. And if so, is this because nobody has had time to implement garbage collection, or for a more fundamental reason, or because VACUUM is seen as sufficient? If you really want to know, read the mountains of (mostly) junk that have been written about replacing VACUUM in pgsql-hackers. The short answer (with apologies to Winston Churchill) is that VACUUM is the worst solution, except for all the others that have been suggested. 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
Re: [PERFORM] When to vacuum a table?
If you really want to know, read the mountains of (mostly) junk that have been written about replacing VACUUM in pgsql-hackers. The short answer (with apologies to Winston Churchill) is that VACUUM is the worst solution, except for all the others that have been suggested. The lesser of 50 evils? ;) Joshua D. Drake 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] When to vacuum a table?
On Sun, Nov 26, 2006 at 12:24:17PM +0100, Joost Kraaijeveld wrote: Hi, Are there guidelines (or any empirical data) available how to determine how often a table should be vacuumed for optimum performance or is this an experience / trial-and-error thing? Most of the time I just turn autovac on, set the scale factors to 0.2/0.1 and the thresholds to 300/200 and turn on vacuum_cost_delay (usually set to 20). That's a pretty decent setup for most applications. It also doesn't hurt to run a periodic vacuumdb -av and look at the tail end of it's output to make sure you have adequate FSM settings. The exception to that rule is for tables that are very small and have a lot of churn; I'll vacuum those by hand very frequently (every 60 seconds or better). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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
