Re: [GENERAL] delaying autovacuum freeze via storage params?
On Tue, May 26, 2015 at 7:27 PM Alvaro Herrera alvhe...@2ndquadrant.com wrote: See the docs about the freeze max age storage parameter -- the per-table setting can decrease the global setting but not increase it. Thanks Alvaro, that explains it. I found it in the docs: Note that autovacuum will ignore attempts to set a per-tableautovacuum_freeze_max_age larger than the system-wide setting (it can only be set smaller).
Re: [GENERAL] delaying autovacuum freeze via storage params?
Steve Kehlet wrote: Hello, I'd like to postpone an autovacuum: VACUUM public.mytable (to prevent wraparound) and handle it manually at another time. I thought I could set these storage parameters on the large table in question (mytable) like this: ALTER TABLE mytable SET ( autovacuum_freeze_min_age=1000, autovacuum_freeze_table_age=8, autovacuum_freeze_max_age=10 ); See the docs about the freeze max age storage parameter -- the per-table setting can decrease the global setting but not increase it. You can increase the global setting (postgresql.conf) to 2 billion AFAIR which should give you plenty of room. Needs a server restart though. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enabling Autovacuum Postgres 9.1 (was Unexpectedly high disk space usage)
Greg Williamson wrote: running transactions can cause autovacuum processes to stall out or be autocancelled. Long running transactions - is now long? In our system it's rare to have a transaction (even a prepared transaction) last much longer than a few minutes. Is that enough time to cause problems with AutoVacuum? The only situation where I would expect that to be a problem is in a very small table which is updated hundreds of times per second. Could you elaborate on this, or point me to a previous thread ? I had a situation where there was a need for consecutive numbers (i.e., no gaps, to satisfy financial auditors), so we needed assignment of these numbers to be transactional rather than using SEQUENCE or SERIAL objects. There was a very small table for assigning these numbers, which was very frequently updated. In a quiescent state all rows in the table would fit in one page. Before tuning autovacuum to be more aggressive, the table bloated, causing performance to fall off. Then autovacuum would kick in and it would get even worse. So naturally, my first response was to make autovacuum less aggressive, which caused the table to bloat even more under normal load, and caused autovacuum to have an even bigger impact when it did kick in. The table bloated to thousands of pages. Then I tried the opposite approach: I cranked up autovacuum to be very aggressive. Under normal load the table settled in at five to ten pages and performance was great. However, any long-running transaction could cause some bloat, so a big report could still cause this one table to become a performance problem. I found that a CLUSTER ran sub-second, because autovacuum did pare the index down to just the needed entries, so I set up a crontab job to CLUSTER this one table a few times per day. That worked out great for me. I think the trick is to try to make autovacuum keep up as much as possible, identify any issues it is not handling, and narrowly target those particular areas with extraordinary maintenance. -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enabling Autovacuum Postgres 9.1 (was Unexpectedly high disk space usage)
Lists wrote: There's a wealth of how to tune PG instruction that's old and (based on this thread alone) often stale enough to be classified as disinformative. For example, nearest I can tell, the entirety of this page is just wrong and/or irrelevant for 9.x and up: http://wiki.postgresql.org/wiki/VACUUM_FULL. In constrast to the advice to avoid Vacuum Full [...] comes the insight that vacuum full is necessary to clean up stale data that is not at the end of the table. (See Jeff Janes 11/10/2012 email) I thought about posting a clarification to what Jeff said there -- it is only necessary to run VACUUM FULL to free up space in the sense of having the database give disk space back to the OS so that the next time you need space in the table it must be re-allocated from the OS. This can be a bad thing in terms of slower allocation of space for new tuples and allocation of non-contiguous disk space. You are much better off running autovacuum aggressively enough that you don't need to run VACUUM FULL than to run it periodically. I have gone for years at a time on large databases (up to several TB) without ever running VACUUM FULL on a table. It is a valuable tool to recover from a bad state due to failure to properly maintain the database; it is not a substitute for doing things right. Long running transactions can cause autovacuum processes to stall out or be autocancelled. Long running transactions - is now long? In our system it's rare to have a transaction (even a prepared transaction) last much longer than a few minutes. Is that enough time to cause problems with AutoVacuum? The only situation where I would expect that to be a problem is in a very small table which is updated hundreds of times per second. Should I increase the max_workers field from the default of 3 to (perhaps) 10? Noting that my solution to the disk space problem is effectively a max_worker of 1 since it's all done sequentially, I wonder if reducing max_workers would actually be better? I would increase autovacuum_max_workers. Also, what's the thread count ? Is that max_workers? I believe that's what was meant. Why would I want to reduce the cost delay to 0, and how does this relate to cost_limit? I've never gone all the way to 0. I would be reluctant to go below 10; rather I would increase the cost limit. Autovacuum adds costs for what it is doing, and when it hits the limit it sleeps for the cost delay interval. I would think you would want to allow the process to work on other things periodically. given my substantial I/O subsystem, I'd want to drop cost_delay to near zero and set the cost_limit really high, which is a rough restatement of the last quoted paragraph above. (I think) I agree. Assuming that I make these suggestions and notice a subsequent system load problem, what information should I be gathering in order to provide better post-incident forensics? Run a report on bloat periodically, to make sure it is staying under control. You might want to monitor for long-running transactions and prepared transactions which the transaction manager has lost track of. The can be real killers. During any incident of poor performance, it is good to gather iostat or vmstat samples at an interval of 1 to 10 seconds for a few minutes, and to capture the contents of pg_stat_activity and pg_locks. Turning on logging of checkpoint activity, autovacuum activity, and slow queries can provide useful information when you match times from the logging up against the times of slow periods. Lastly, there's the question of reindexing before full vacuum. I've observed that not doing a manual reindex prior to vacuum full did not, in fact, free up the space, even though I've been told that reindex is implicit in the vacuum process. (?!) I'm confident that I can reproduce this behavior given a bit of time to allow one of our database servers to bloat back up. You never did say what version that was on. In 9.0 and later, VACUUM FULL will drop and recreate all indexes from scratch after rebuilding the heap, so it's pretty hard to imagine why dropping and recreating all indexes right *before* rebuilding the heap would have much impact. Prior to 9.0, VACUUM FULL would bloat indexes, so it was always a good idea to REINDEX in addition to running VACUUM FULL, although it was much more effective to run it *after* the VACUUM FULL. -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enabling Autovacuum Postgres 9.1 (was Unexpectedly high disk space usage)
Kevin -- You wrote: ... running transactions can cause autovacuum processes to stall out or be autocancelled. Long running transactions - is now long? In our system it's rare to have a transaction (even a prepared transaction) last much longer than a few minutes. Is that enough time to cause problems with AutoVacuum? The only situation where I would expect that to be a problem is in a very small table which is updated hundreds of times per second. Could you elaborate on this, or point me to a previous thread ? I've got precisely such a database; currently we do an hourly reindex on all user tables in addition to some fairly standard autovac settings. The tables themselves don't seem to bloat much during ordinary operations but if we don't reindex performance tanks; when the site has issues we sometimes see table bloat but it seems to be dealt with by autovac. This is a postgres 9.1 instance w/ SSDs, lots of RAM (24 gigs) and relatively small tables (maybe a few thousands of rows in most cases, total footprint of the database is 240 megs) and being updated in the low thousands of times per second. Thanks! Greg Williamson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enabling Autovacuum Postgres 9.1 (was Unexpectedly high disk space usage)
On 11/13/2012 04:04 AM, Lists wrote: There's a wealth of how to tune PG instruction that's old and (based on this thread alone) often stale enough to be classified as disinformative. For example, nearest I can tell, the entirety of this page is just wrong and/or irrelevant for 9.x and up: http://wiki.postgresql.org/wiki/VACUUM_FULL. Agreed, that needs fixing. I'll sort that out. That information was important before the VACUUM FULL rewrite, but is now severely outdated. It needs to be split into pre-9.1 and 9.1+ sections. In constrast to the advice to avoid Vacuum Full ( http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT) comes the insight that vacuum full is necessary to clean up stale data that is not at the end of the table. (See Jeff Janes 11/10/2012 email) That part isn't quite right AFAIK. If you keep table bloat under control, ordinary VACCUM is perfectly sufficient. You only need/want to VACUUM FULL if you wish to truncate a table, reducing its size by compacting rows. In an actively used database that's usually pretty pointless, since new rows will then be added to the end, growing the table. You might as well just keep on re-using the space, rather than going through those compact-and-expand cycles. You'll suffer from less file fragmentation that way and won't be paying the costs of file system allocations. If you have a table that you've dramatically reduced in size (say, by deleting the vast majority of it) and you won't be adding more rows to replace the old ones, that's when VACUUM FULL makes sense. It's a bit like those utilities that claim to free or clean or de-fragment memory. They seem good, but they're actually grossly counter-productive, because the system then has to re-read cached data and otherwise fight to get back to its old equilibrium. It's typically the same for Pg: you want to aim for equilibrium, not free space that'll just promptly get re-allocated. If you do have a bad bloat problem, I'd set a non-default FILLFACTOR before doing a VACUUM FULL, so you still have some free space within the table after vacuum completes. That way you won't be immediately paying the cost of allocating space for new rows as soon as any UPDATEs or INSERTs come in. non-full vacuum can only free space from the end of the table. This would imply that a full analyze is a good idea, at least periodically (weekly/monthly/quarterly) in a database that combines the use of temp tables and periodic changes to persistent objects. Does autovacuum ever do a full analyze? What about autovacuum and the reindex question at the end of this email? I'm not sure there's any such thing as a full analyze. VACUUM FULL ANALYZE is Do a VACUUM FULL and an ANALYZE, not Do a full analyze. Autovacuum should be taking care of analyze and table statistics. If it isn't, adjust autovacuum parameters so that it does. These two can be killers. Long running transactions can cause autovacuum processes to stall out or be autocancelled. Long running transactions - is now long? In our system it's rare to have a transaction (even a prepared transaction) last much longer than a few minutes. Is that enough time to cause problems with AutoVacuum? Not generally, no. PostgreSQL can't clean up rows that are still visible to a transaction. So if your transactions are three minutes long, that's a three minute delay before VACUUM can clean up DELETEd rows or dead rows left by UPDATEs. Not a biggie even on a pretty high load DB. You should generally be concerned only when transactions are open over user think time or are abandoned by buggy applications - cases where the transaction length is many minutes or hours, potentially unbounded. Uncommitted prepared transactions are also a problem for similar reasons. Lastly, there's the question of reindexing before full vacuum. I've observed that not doing a manual reindex prior to vacuum full did not, in fact, free up the space, even though I've been told that reindex is implicit in the vacuum process. (?!) VACUUM FULL, post-9.1, should take care of index bloat. Pre-9.1 VACUUM FULL could make index bloat worse. Ordinary VACUUM will not truncate indexes AFAIK, only mark free space within them so it can be re-used. Same deal as with the table its self: this is usually what you want. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [GENERAL] Enabling Autovacuum Postgres 9.1 (was Unexpectedly high disk space usage)
On 11/13/2012 10:29 AM, Craig Ringer wrote: On 11/13/2012 04:04 AM, Lists wrote: There's a wealth of how to tune PG instruction that's old and (based on this thread alone) often stale enough to be classified as disinformative. For example, nearest I can tell, the entirety of this page is just wrong and/or irrelevant for 9.x and up: http://wiki.postgresql.org/wiki/VACUUM_FULL. Agreed, that needs fixing. I'll sort that out. That information was important before the VACUUM FULL rewrite, but is now severely outdated. It needs to be split into pre-9.1 and 9.1+ sections. Correction - pre-9.0 and 9.0+ -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [GENERAL] Disable autovacuum on specific tables
On 10/24/2012 02:57 PM, Eliot Gable wrote: In general, autovacuum seems to work well on most of the tables I deal with. However, in a couple of specific cases, it seems to fail miserably. I would like to switch to manual vacuuming on those tables and disable auto-vacuuming for those tables alone. Is this possible? ALTER TABLE foo SET (autovacuum_enabled = false, toast.autovacuum_enabled = false); -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable autovacuum on specific tables
Eliot Gable escribió: In general, autovacuum seems to work well on most of the tables I deal with. However, in a couple of specific cases, it seems to fail miserably. I would like to switch to manual vacuuming on those tables and disable auto-vacuuming for those tables alone. Is this possible? Of course. Exactly how you do it depends on the server version. In versions earlier than 8.4 you had to manually insert a tuple in the pg_autovacuum catalog, with its enabled flag set to false and ensure that all other settings are -1 (not zero). In 8.4 and up, just do ALTER TABLE foo SET (autovacuum_enabled = false) See http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS for docs. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable autovacuum on specific tables
Shaun Thomas escribió: On 10/24/2012 02:57 PM, Eliot Gable wrote: In general, autovacuum seems to work well on most of the tables I deal with. However, in a couple of specific cases, it seems to fail miserably. I would like to switch to manual vacuuming on those tables and disable auto-vacuuming for those tables alone. Is this possible? ALTER TABLE foo SET (autovacuum_enabled = false, toast.autovacuum_enabled = false); Note that if you don't set toast.autovacuum_enabled, the toast table inherits from the main table setting. So you turn it off for both just by setting autovacuum_enabled=false. The toast setting is there so that you can set them differently if necessary. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] canceling autovacuum time
On 28 Únor 2012, 1:53, Jameison Martin wrote: I'm seeing GMTERROR: canceling autovacuum task lines in my logs. It's not GMTERROR, it's GMT ERROR where GMT is a timezone. You should put a space at the end of log_line_prefix I guess. 2012-02-27 23:53:28 GMTLOG: checkpoint starting: time 2012-02-27 23:53:31 GMTERROR: canceling autovacuum task 2012-02-27 23:53:31 GMTCONTEXT: automatic vacuum of table somedb.pg_toast.pg_toast_33254 2012-02-27 23:53:32 GMTERROR: canceling autovacuum task 2012-02-27 23:53:32 GMTCONTEXT: automatic vacuum of table somedb.pg_toast.pg_toast_33485 2012-02-27 23:54:29 GMTLOG: automatic vacuum of table somedb.pg_toast.pg_toast_33254: index scans: 1 It was suggested to me that perhaps I had an exclusive lock on the table that was being vacuumed (e.g. that I was running some DDL that was conflicting with the autovacuum). I'm quite certain that I'm not running any DDL at the time this happens, all i'm running are: * SELECT ... FOR UPDATE * UPDATE * INSERT * DELETE Does anyone have a suggestions as to what the cause of this error might be? Generally yes, autovacuum can cancel itself is something holds a lock. Check pg_locks what locks are there (http://www.postgresql.org/docs/9.1/static/view-pg-locks.html). Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] canceling autovacuum time
Hi, On 28 February 2012 11:53, Jameison Martin jameis...@yahoo.com wrote: I'm seeing GMTERROR: canceling autovacuum task lines in my logs. That's *should* be fine. autovacuum daemon is smart enough to cancel it self when other query needs access to the table. The affected table will be vacuumed/analysed later. You should monitor pg_stat_user_tables view to see how often is the table vacuumed/analysed. -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] canceling autovacuum task error
On Tue, Aug 9, 2011 at 11:07 PM, tamanna madaan tamanna.mad...@globallogic.com wrote: Hi All I am using a cluster setup having postgres-8.4.0 and slon 2.0.4 is being There are known data eating bugs in that version of postgresql, and I personally had issues with earlier 2.0.x releases. There are important updates to BOTH of those packages you need to install. Can anybody shed some light on it if these errors are related or what could be the reason for these errors . You are running a known buggy version of postgresql. Update to the latest 8.4.x immediately. Your database may be corrupted beyond easy recovery, got a backup? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] beginners autovacuum question
On Wed, Jan 5, 2011 at 6:08 PM, u235sentinel u235senti...@gmail.com wrote: I'm tracking a problem with our tables being bloated and was curious if someone regularly kills autovacuum jobs, will autovacuum later reattempt to vacuum the table it was killed under? I've made autovacuum more aggressive and given more worker threads. Yet for some reason we're not keeping up. Can you show us what you've changed to make autovac more aggressive? You might want to make sure you've lowered autovacuum_vacuum_cost_delay (mine's at 0 as we have 28 or so disks in a single RAID-10 and lots of spare IO). Also raise autovacuum_vacuum_cost_limit much higher (mine's at 5000). Assuming these tables get updated a lot, I'm gonna guess that you're not having a problem with too high of threshold settings but with auto-vac keeping up. But it's just a guess. Also, what do you get from things like iostat, vmstat, and sar as regards your IO utilization and such? If your IO system is always at 100% then more aggressive vacuuming isn't gonna do much, because you'll always be behind. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] beginners autovacuum question
Hi, On Wed, 2011-01-05 at 18:08 -0700, u235sentinel wrote: I'm tracking a problem with our tables being bloated and was curious if someone regularly kills autovacuum jobs, will autovacuum later reattempt to vacuum the table it was killed under? In 8.3+, autovacuum kills itself if when it conflicts with other queries. If it is killed by a human or so, it will try to run again in the next cycle (which depends on max_workers and timeout values). I've made autovacuum more aggressive and given more worker threads. Giving more worker threads will probably not make autovacuum aggressive. You will also want to decrease threshold values either in postgresql.conf, or set custom parameters in pg_class per relation. Yet for some reason we're not keeping up. Probably because of the threshold values. Vacuum does complete when I run it manually on a table. But I'm suspecting a coworker working late at night be my killing autovacuum. Reading through the logs right now to see if this is the case. Set log_autovacuum_min_duration to 0, and log all autovac activities. It will let you know when it cancels itself, like ERROR: cancelling autovacuum task or so (this may not be the exact message, I did not check). Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] unexplained autovacuum to prevent wraparound
Gordon Shannon escribió: Ah, now I see what you meant. Forgive me, I thought you were referring to the pg_autovacuum table in 8.3 where you have to specifiy something for each column, and -1 says use the default. It appears in 8.4.0 I have to explicitly set ALL (?) other storage parameters to -1 to get the default, otherwise I am getting zero for each value?? I don't believe the documentation mentions this rather important detail: http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS. Did I miss it somewhere? Err, no, that would be a bug. Please update to 8.4.2, I think we fixed it there. revision 1.28.2.1 date: 2009-08-27 13:19:31 -0400; author: alvherre; state: Exp; lines: +10 -10; Fix handling of autovacuum reloptions. In the original coding, setting a single reloption would cause default values to be used for all the other reloptions. This is a problem particularly for autovacuum reloptions. Itagaki Takahiro -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexplained autovacuum to prevent wraparound
That looks like the fix for this, thanks! I will try to upgrade soon. -- Gordon On Sun, Mar 14, 2010 at 7:43 AM, Alvaro Herrera alvhe...@commandprompt.comwrote: Gordon Shannon escribió: Ah, now I see what you meant. Forgive me, I thought you were referring to the pg_autovacuum table in 8.3 where you have to specifiy something for each column, and -1 says use the default. It appears in 8.4.0 I have to explicitly set ALL (?) other storage parameters to -1 to get the default, otherwise I am getting zero for each value?? I don't believe the documentation mentions this rather important detail: http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS . Did I miss it somewhere? Err, no, that would be a bug. Please update to 8.4.2, I think we fixed it there. revision 1.28.2.1 date: 2009-08-27 13:19:31 -0400; author: alvherre; state: Exp; lines: +10 -10; Fix handling of autovacuum reloptions. In the original coding, setting a single reloption would cause default values to be used for all the other reloptions. This is a problem particularly for autovacuum reloptions. Itagaki Takahiro -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: [GENERAL] unexplained autovacuum to prevent wraparound
Ah, now I see what you meant. Forgive me, I thought you were referring to the pg_autovacuum table in 8.3 where you have to specifiy something for each column, and -1 says use the default. It appears in 8.4.0 I have to explicitly set ALL (?) other storage parameters to -1 to get the default, otherwise I am getting zero for each value?? I don't believe the documentation mentions this rather important detail: http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS. Did I miss it somewhere? Thanks! Gordon On Fri, Mar 12, 2010 at 4:45 PM, Gordon Shannon gordo...@gmail.com wrote: This is 8.4, there is no pg_autovacuum table. I set it like this: alter table foo set (autovacuum_analyze_scale_factor=0.01); On Fri, Mar 12, 2010 at 4:31 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Gordon Shannon escribió: One possibly interesting thing is that this seems to have started just after I set foo's autovacuum_analyze_scale_factor to 0.01, since I wanted more frequent analyze runs. I wonder if that could be related. You probably set the other values to 0, which includes the freeze age. You need to set it (and all other values) to -1 instead. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- If I had more time, I could have written you a shorter letter. (Blaise Pascal)
Re: [GENERAL] unexplained autovacuum to prevent wraparound
Gordon Shannon escribió: One possibly interesting thing is that this seems to have started just after I set foo's autovacuum_analyze_scale_factor to 0.01, since I wanted more frequent analyze runs. I wonder if that could be related. You probably set the other values to 0, which includes the freeze age. You need to set it (and all other values) to -1 instead. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexplained autovacuum to prevent wraparound
This is 8.4, there is no pg_autovacuum table. I set it like this: alter table foo set (autovacuum_analyze_scale_factor=0.01); On Fri, Mar 12, 2010 at 4:31 PM, Alvaro Herrera alvhe...@commandprompt.comwrote: Gordon Shannon escribió: One possibly interesting thing is that this seems to have started just after I set foo's autovacuum_analyze_scale_factor to 0.01, since I wanted more frequent analyze runs. I wonder if that could be related. You probably set the other values to 0, which includes the freeze age. You need to set it (and all other values) to -1 instead. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: [GENERAL] unexplained autovacuum to prevent wraparound
On Fri, 2010-03-12 at 16:45 -0700, Gordon Shannon wrote: This is 8.4, there is no pg_autovacuum table. I set it like this: alter table foo set (autovacuum_analyze_scale_factor=0.01); That is 1% changes. I think you want .10 Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexplained autovacuum to prevent wraparound
Thanks, but I do want 1%. On Fri, Mar 12, 2010 at 5:19 PM, Joshua D. Drake j...@commandprompt.comwrote: On Fri, 2010-03-12 at 16:45 -0700, Gordon Shannon wrote: This is 8.4, there is no pg_autovacuum table. I set it like this: alter table foo set (autovacuum_analyze_scale_factor=0.01); That is 1% changes. I think you want .10 Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- If I had more time, I could have written you a shorter letter. (Blaise Pascal)
Re: [GENERAL] does autovacuum in postgres-8.1.2 use prepared transactions ??
tamanna madaan tamanna.ma...@globallogic.com writes: Can anyone please let me know if autovacuum in postgres-8.1.2 uses prepared transactions. Nope, it does not. Any prepared transactions you see hanging around were created by some external client. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] does autovacuum in postgres-8.1.2 use prepared transactions ??
Thanks Tom ... -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, January 12, 2010 1:35 AM To: tamanna madaan Cc: pgsql-general@postgresql.org; Gaurav Katiyar Subject: Re: [GENERAL] does autovacuum in postgres-8.1.2 use prepared transactions ?? tamanna madaan tamanna.ma...@globallogic.com writes: Can anyone please let me know if autovacuum in postgres-8.1.2 uses prepared transactions. Nope, it does not. Any prepared transactions you see hanging around were created by some external client. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is autovacuum recommended?
Hi, Thanks for your answers! I'm using 8.1 and 8.2 on windows2003 servers, and it's true that i could probably configure them much better. We've recently moved to brand new dedicated database servers with pg8.3 on debian in 2 projects and it has been much easier to configure these correctly. There I don't encounter the probems that i described. The thing is that the whole concept of autovacuum is not feeling right. Per design, the vacuum is likely to kick off when i am doing something big. And when i am doing something big, a vacuum is the last thing i'd wish for. I'd wish for a vacuum when the database is doing nothing at all, but the autovacuum will NEVER kick off in such a moment. That's why i feel better scheduling the vacuum at times at which i know things will be generally quiet. To be honest, i am a bit surprised that all 3 reactions recommend using autovacuum, even if it means i have to buy a new server for this purpouse. I was thinking that autovacuum was just a mechanism to ensure that postgres works well out of the box, but that it would be recommended to schedule your own vacuum tailored to your specific needs. I agree though, that it is a tough tailoring job and that the autovacuum must be doing a better job than i am. It just fires at the wrong time. Just a thought (to think positively..): wouldn't it be possible to let the autovacuum wait until the load goes down, or until the end of the transaction that triggered the autovacuum? Cheers, WBL
Re: [GENERAL] is autovacuum recommended?
On Fri, Jul 10, 2009 at 2:47 PM, Willy-Bas Looswilly...@gmail.com wrote: Hi, Thanks for your answers! I'm using 8.1 and 8.2 on windows2003 servers, and it's true that i could probably configure them much better. Note that support for 8.1 on windows is gone, as it is no longer considered supportable due to design / engineering issues. Upgrading them all to 8.3 or 8.4 is probably a good idea since they are better at autovacuum and such than 8.2 and before. We've recently moved to brand new dedicated database servers with pg8.3 on debian in 2 projects and it has been much easier to configure these correctly. There I don't encounter the probems that i described. Smart move. The amount of effort needed to learn debian or any other linux distro is usually less than the amount of ongoing effort to keep a production pg server happy on windows. Also, 64 bit pgsql on 64 bit unix/linux is capable of better scaling and handling more memory. The thing is that the whole concept of autovacuum is not feeling right. Per design, the vacuum is likely to kick off when i am doing something big. That assumes that autovacuum always runs in some mode that must interfere with db operation. If you set the autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit, autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold parameters properly, as well as the free space map settings large enough to hold all your dead tuples, then autovacuum should not cause a lot of issues, unless your machine is already IO bound. And if it's already IO bound and dragging butt, then the problem isn't autovacuum, but a machine without enough IO bandwidth to do its job well. And when i am doing something big, a vacuum is the last thing i'd wish for. I don't even notice when it kicks in on my servers. I'd wish for a vacuum when the database is doing nothing at all, but the autovacuum will NEVER kick off in such a moment. Again, if the delay and such are set right, then autovac will use so little IO as to be unnoticeable. That's why i feel better scheduling the vacuum at times at which i know things will be generally quiet. For some very large tables on slow IO machines, it makes sense to remove them from the purview of autovac, I agree. Take a look at the pg_autovacuum table. it's pretty easy to see how it works. To be honest, i am a bit surprised that all 3 reactions recommend using autovacuum, even if it means i have to buy a new server for this purpouse. Well, autovacuum is more primitive in the versions you're running, and more likely to get in the way. 8.3, and to a greater extent 8.4, remove a lot of these issues. I was thinking that autovacuum was just a mechanism to ensure that postgres works well out of the box, but that it would be recommended to schedule your own vacuum tailored to your specific needs. I agree though, that it is a tough tailoring job and that the autovacuum must be doing a better job than i am. It just fires at the wrong time. And that's kind of the point, that it's better to have a db that runs a little slow than one that explodes in sheets of flame. Just a thought (to think positively..): wouldn't it be possible to let the autovacuum wait until the load goes down, or until the end of the transaction that triggered the autovacuum? You could set up a cron job that updated the pg_autovacuum table at certain times to accomplish this. I'm not sure how easy it would be to program autovac to do the same thing. You could certainly set the cost delay higher than normal (like 20 or 40 ms) for some tables so that autovac didn't get in the way, but then you run the risk of it never keeping up, and on 8.1 with only one thread to autovac, that could be bad. Definitely consider upgrading pg versions on your windows machines. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is autovacuum recommended?
Willy-Bas Loos escribió: Hi, Whenever i start a big action, like inserting millions of recs or doing a large update, the autovacuum fires on top of that. It has some adverse effects on performance when i need it most. More than once a postgres service crashed on me because of it. Sure, it had too little memory, but it wouldn't have happened if it wasn't for the autovacuum. 1. the server should never crash. If it does you have another problem that perhaps is triggered by the high load. Investigate that. 2. what PG version are you running? What operating system? 3. You can turn autovacuum off for particular tables. This would allow you to have it process most tables, and manually vacuum only the specific tables that are causing you grief. Should ik keep autovacuum on, or is it better to run a script like: vacuumdb -az (daily except one day) vacuumdb -azf (once a week) 4. VACUUM FULL is (almost) never needed unless your server is not properly tuned. If you find a need for it, it's a symptom that you need to tweak something somewhere. Need more details about the problem to give you more advice. 2a. Upgrading to 8.4 may give you several benefits in this area. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is autovacuum recommended?
Hi, On Thursday 09 July 2009 19:25:15 Willy-Bas Loos wrote: Whenever i start a big action, like inserting millions of recs or doing a large update, the autovacuum fires on top of that. You can configure autovacuum to use less resources. http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html It has some adverse effects on performance when i need it most. More than once a postgres service crashed on me because of it. Sure, it had too little memory, but it wouldn't have happened if it wasn't for the autovacuum. That sounds like misconfiguration. PG got OOM killed? It is also possible that your max_fsm_pages were to small, so autovacuum hat to start all over. My database is growing, so really freeing up space isn't such an issue. What I want is optimal performance. Well, not doing vacuum regulary can lead to significant bloat which makes lookups (and thus updates/deletes) way much slower. Of course autovacuum is more generic, but for me i think the daily scheme will be sufficient. That may be. Hard to say without knowing more though. How fast are your tables changing? Inserts only or also updates/deletes? A connection told me it would be better to enable the autovacuum, because it does more than the above script. Can anyone verify that? I dont think it really does more. vacuumdb -azf (once a week) Using -f is normally not a good idea. It takes an exclusive lock on the tables - so you cannot do anything in those tables. Which version of PG are you using? Another possibility would be to upgrade to 8.4 - keeping track of free space works quite different there. Andres -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is autovacuum recommended?
In response to Willy-Bas Loos willy...@gmail.com: Whenever i start a big action, like inserting millions of recs or doing a large update, the autovacuum fires on top of that. It has some adverse effects on performance when i need it most. More than once a postgres service crashed on me because of it. Sure, it had too little memory, but it wouldn't have happened if it wasn't for the autovacuum. If autovacuum is interfering with performance, you have two basic choices: disable autovac or add hardware (assuming your system is already optimally tuned). Should I keep autovacuum on, or is it better to run a script like: vacuumdb -az (daily except one day) vacuumdb -azf (once a week) VACUUM FULL is seldom a good choice for a scheduled activity. That being said, there are some corner use cases where a regular VACUUM FULL is a good idea. Also, it's likely that a daily vacuum won't be enough. If that were the case, then autovac wouldn't be kicking off as often as it does. Once you start vacuuming multiple times per day, you're back to interfering with performance again. However, if you're doing it on a controlled schedule, it's possible that you can schedule it in such a way that it's less intrusive. Overall, however, you're probably going to have to solve the problem by adding hardware, or better tuning your system. My database is growing, so really freeing up space isn't such an issue. What I want is optimal performance. If you _only_ do inserts, then vacuum isn't required at all. If you do _ANY_ UPDATE or DELETE operations, then you still need vacuum or your filesystem will fill up with data that's no longer used. If autovacuum is kicking off, then you need to do vacuums, as autovac isn't random, it uses statistics on the usage of tables to determine if it's needed. You can also manually vacuum as part of your application. For example, vacuum a table manually after a bunch of UPDATEs. If you do this intelligently, you can leave autovacuum enabled, and it will only pick vacuum tables that aren't done manually. Of course autovacuum is more generic, but for me i think the daily scheme will be sufficient. Unlikely. Unless your database sees very few updates, daily vacuum probably isn't often enough. However, your usage pattern will dictate that. A connection told me it would be better to enable the autovacuum, because it does more than the above script. Can anyone verify that? Autovacuum is smarter -- it won't vacuum tables that don't need it, whereas the above script vacuums everything, even if it doesn't need it. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is autovacuum on?
On Tue, Mar 18, 2008 at 3:20 PM, Filip Rembiałkowski [EMAIL PROTECTED] wrote: yes. select setting from pg_settings where name = 'autovacuum'; Ah ha, thankyou! I assumed there must have been a view for the settings, I guess I missed it when I looked at the various pg_* views. Cheers, -Blair -- In science one tries to tell people, in such a way as to be understood by everyone, something that no one ever knew before. But in poetry, it's the exact opposite. - Paul Dirac -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is autovacuum on?
On Mar 17, 2008, at 11:25 PM, Blair Bethwaite wrote: On Tue, Mar 18, 2008 at 3:20 PM, Filip Rembiałkowski [EMAIL PROTECTED] wrote: yes. select setting from pg_settings where name = 'autovacuum'; Ah ha, thankyou! I assumed there must have been a view for the settings, I guess I missed it when I looked at the various pg_* views. Or, a simple: SHOW autovacuum; will to the trick. http://www.postgresql.org/docs/8.2/interactive/sql-show.html Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is autovacuum on?
On Wed, Mar 19, 2008 at 1:29 AM, Erik Jones [EMAIL PROTECTED] wrote: SHOW autovacuum; That's even better, thanks Erik. Cheers, -Blair -- In science one tries to tell people, in such a way as to be understood by everyone, something that no one ever knew before. But in poetry, it's the exact opposite. - Paul Dirac -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is autovacuum on?
2008/3/18, Blair Bethwaite [EMAIL PROTECTED]: Hi all, I've just upgraded to 8.3 and am looking at using autovacuum. We have a long running application with high update frequency that periodically issues vacuum commands itself. I'd like to be able to add code to the app like: if pg.autovacuum == on: self.routine_vacuuming = False else: self.routine_vacuuming = True so that we can avoid manually issuing vacuum commands at sites where Postgresql is running autovacuum. But so far I haven't been able to find a way for a non-privileged user to query the autovacuum status, is this possible? yes. select setting from pg_settings where name = 'autovacuum'; Also, the routine-vacuuming section of the manual states that the purpose of the autovacuum daemon is to periodically issue VACUUM and ANALYZE commands - am I correct in thinking this implies that it will not issue VACUUM FULL commands? Yes, you're correct. -- Filip Rembiałkowski -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Some Autovacuum Questions
On Feb 12, 2008, at 9:13 AM, Thomas Chille wrote: vacuum_cost_delay = 200 vacuum_cost_page_hit = 6 vacuum_cost_limit = 100 Vacuum is going to take forever with those settings. I strongly suggest you set them back to default. If you need to throttle vacuum, try setting cost_delay to between 10 and 20. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Some Autovacuum Questions
On Tue, Feb 12, 2008 at 04:13:33PM +0100, Thomas Chille wrote: We are still using 8.1.4 because a database upgrade for us and our product is a hefty step wich involves a lot of customer databases. But if it could help we consider to upgrade to 8.1.11 or 8.3. What would u suggest? Obviously 8.1.11, given the upgrade should only take moments... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Some Autovacuum Questions
Thomas Chille wrote: My 1. question is, if the known bugfixes for autovacuum after release 8.1.4 addressing my depicted issues? Not directly, but keep reading. We are still using 8.1.4 because a database upgrade for us and our product is a hefty step wich involves a lot of customer databases. But if it could help we consider to upgrade to 8.1.11 or 8.3. What would u suggest? You will be really happy when your database stops working because of a bug in autovacuum which was fixed in 8.1.6. I should add that perhaps autovacuum is not processing your databases at all because it is stuck on processing template0 due to that bug -- this could cause dead rows to accumulate. I'm not sure why you say it's a hefty step to upgrade to 8.1.11. Perhaps you're not up to speed on our upgrade procedures. On the other hand, perhaps this bug hasn't bit you yet. But I can promise, if you don't upgrade it will. My 2. questions is, if i possible configured something improper? If you didn't tune autovacuum at all, yeah, perhaps it would be good to adjust the settings somewhat. My 3. question is, if it possible to read the vaccuming or analyzing state of a given table manually? Are there any indicatores in statistic tables, wich the autovacuum demaon is using too wich can show me the progress of a running autovacuum? Yes, they are kept in pg_stat_all_tables. (Not all autovacuum numbers are displayed however). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Some Autovacuum Questions
Thomas Chille wrote: Hi! Some of our clients databases are performing less good after a while. We are using autovacuum to vacuuming and analyzing the tables. After some analyzes by my own it looks like that the tables or table indexes are not analyzed or vacuumed fully or correctly. You might have some index bloat A count(*) query takes multiple times longer on this databases as on a fresh dumped/restored version of the same database on the same machine. Are you using a where clause with your count? If not, the PostgreSQL must do a table scan to count the rows. If you have a lot of churn prior to running vaccum, that might increase your table size significantly and thus account for the additional time. You should find that it drops back to the dump/restore time if you do a vacuum full (or cluster), but that has other performance implications. You could also tune AVD to be a bit more aggressive, but that might have other performance-related implications for you. During the query it looks like that postgres scans all the time the harddisk and is reading a lot more data in then from the fresh restored database. This showed me the output of the vmstat-commands 'bi'-attribute (blocks reading in) and the up to 99,9% wait-state of the cpu. My 1. question is, if the known bugfixes for autovacuum after release 8.1.4 addressing my depicted issues? We are still using 8.1.4 because a database upgrade for us and our product is a hefty step wich involves a lot of customer databases. But if it could help we consider to upgrade to 8.1.11 or 8.3. What would u suggest? I don't think so...though HOT in 8.3 might help a bit (its a feature, not a bug fix though), but based on what you say your application does below, I'm not sure that you would see a benefit in HOT anyways. My 2. questions is, if i possible configured something improper? Well, you might be better off using the row estimates in pg_class, rather than doing a count() to get them. pg_class values are estimates, and will be somewhat accurate provided you did a recent analyze... For this i will give a brief overview of our database. The database stores mainly historical data for a reporting application. This data will be consolidated per day at frequent intervals. For this the data of one day will be removed from the historical tables and will be newly calculated out of some tables with raw data. Depending on the daytime and the amount of data it takes normaly up to 2 minutes to summarize an compress the data of one day. After one minute break it starts again. We talk about up to 3000 records per day out of up to 3 million records (and growing) in the whole historical table. Can autovacuum handle that much changing data with this configuration? Sure. though you might be able to improve performance by using PostgreSQL inheritance and putting the static data (that doesn't change much) in a larger parition that you don't need to vacuum as frequently. From default configuration differing settings: vacuum_cost_delay = 200 vacuum_cost_page_hit = 6 vacuum_cost_limit = 100 autovacuum_naptime = 60 Strange enough a manual analyze and vacuum makes the measured count(*) query less performant? See attached vacuum log. I'm sure if you did the manual vacuum at the same interval as the auto-vacuum you would see it be similarly performant. My 3. question is, if it possible to read the vaccuming or analyzing state of a given table manually? Are there any indicatores in statistic tables, wich the autovacuum demaon is using too wich can show me the progress of a running autovacuum? Sure, you can look at the table level row i/o stats. If you add the number of rows deleted with the number of rows updated, you'll get a count of the number of dead tuples (assuming you reset stats and didn't do a vacuum ). AVD uses these values to determine when it needs to vacuum and analyze... We are using a customized debian Linux on Pentium 4 2,8 GHz Thanks for any help! regards, thomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/877-258-8987 http://www.otg-nc.com ---(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: [GENERAL] enabling autovacuum
Chander Ganesan wrote: Jeremy Harris wrote: Version: PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070418 (Red Hat 4.1.2-10) We have one problematic table, which has a steady stream of entries and a weekly mass-delete of ancient history. The bloat query from Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns: Inserts don't generate dead tuples, and AVD looks at obsolete tuples.. As such, I wouldn't expect AVD to kick off until after you did a mass delete...assuming that delete was sizable enough to trigger a vacuum. Ah, that would explain it - thankyou. So I need to retreat to the question of why the weekly vacuum permits the observed bloat. Any ideas? More information that I could gather? Thanks, Jeremy ---(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: [GENERAL] enabling autovacuum
Jeremy Harris wrote: Chander Ganesan wrote: Inserts don't generate dead tuples, and AVD looks at obsolete tuples.. As such, I wouldn't expect AVD to kick off until after you did a mass delete...assuming that delete was sizable enough to trigger a vacuum. Ah, that would explain it - thankyou. So I need to retreat to the question of why the weekly vacuum permits the observed bloat. Any ideas? More information that I could gather? Autovacuum will kick off an analyze if you do enough inserts however. ---(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: [GENERAL] enabling autovacuum
Jeremy Harris wrote: Hi, We're starting to run autovacuum for the first time on a system that's been running with nightly cron-driven vacuum for some time. Version: PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070418 (Red Hat 4.1.2-10) We have one problematic table, which has a steady stream of entries and a weekly mass-delete of ancient history. The bloat query from Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns: Inserts don't generate dead tuples, and AVD looks at obsolete tuples.. As such, I wouldn't expect AVD to kick off until after you did a mass delete...assuming that delete was sizable enough to trigger a vacuum. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com Ask me about our Expert PostgreSQL PostGIS Training ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] enabling autovacuum
On Jan 28, 2008 10:17 PM, Jeremy Harris [EMAIL PROTECTED] wrote: Hi, We're starting to run autovacuum for the first time on a system that's been running with nightly cron-driven vacuum for some time. Version: PostgreSQL 8.2.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070418 (Red Hat 4.1.2-10) We have one problematic table, which has a steady stream of entries and a weekly mass-delete of ancient history. The bloat query from Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns: schemaname | tablename | reltuples | relpages | otta | tbloat | wastedpages | wastedbytes | wastedsize |iname| ituples | ipages | iotta | ibloat | wastedipages | wastedibytes | wastedisize ++---+--+--++-+-++-+-++---++--+--+- public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 | 1194262528 | 1139 MB| rcpt_audit_msg_audit_id_idx | 1300300 | 6798 | 3819 |1.8 | 2979 | 24403968 | 23 MB public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 | 1194262528 | 1139 MB| rcpt_audit_id_idx | 1300300 | 4727 | 3819 |1.2 | 908 | 7438336 | 7264 kB We have uncommented autovacuum = on in postgresql.conf and run service postgresql reload. pg_stat_all_tables shows 4 tables as autoanalyzed at about that time; 3 of which were also autovacuumed. The problem table is not included; no other autos are logged there in the succeeding 24 hours. Is other action needed to enable autovacuum? The autovacuum tuning parameters are all at default settings. We have max_fsm_pages = 200 max_fsm_relations = 10 ... Are there any other changes we should make to stop this table getting so bloated? Is it possible that this table didn't see many updates, today? You could add an entry to pg_catalog.pg_autovacuum to customize the handling of your Favorite Table. http://www.postgresql.org/docs/8.2/static/catalog-pg-autovacuum.html You might lower the thresholds for that table... -- http://linuxfinances.info/info/linuxdistributions.html The definition of insanity is doing the same thing over and over and expecting different results. -- assortedly attributed to Albert Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] enabling autovacuum
Christopher Browne wrote: Is it possible that this table didn't see many updates, today? Nope; about 24000 (according to the id sequence). - Jeremy ---(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
[OT] Re: [GENERAL] enabling autovacuum
On Mon, 2008-01-28 at 22:17 +, Jeremy Harris wrote: We have one problematic table, which has a steady stream of entries and a weekly mass-delete of ancient history. The bloat query from Greg Sabino Mullane (thanks to Greg Smith for pointing it out) returns: schemaname | tablename | reltuples | relpages | otta | tbloat | wastedpages | wastedbytes | wastedsize |iname| ituples | ipages | iotta | ibloat | wastedipages | wastedibytes | wastedisize ++---+--+--++-+-++-+-++---++--+--+- public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 | 1194262528 | 1139 MB| rcpt_audit_msg_audit_id_idx | 1300300 | 6798 | 3819 |1.8 | 2979 | 24403968 | 23 MB public | rcpt_audit | 1300300 | 152149 | 6365 | 23.9 | 145784 | 1194262528 | 1139 MB| rcpt_audit_id_idx | 1300300 | 4727 | 3819 |1.2 | 908 | 7438336 | 7264 kB Can you let me know what is the sql used to generate such a nice summary of the tables? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [OT] Re: [GENERAL] enabling autovacuum
On Tue, 29 Jan 2008, Ow Mun Heng wrote: Can you let me know what is the sql used to generate such a nice summary of the tables? Might as well dupe the old text; this went out to the performance list: Greg Sabino Mullane released a Nagios plug-in for PostgreSQL that you can grab at http://bucardo.org/nagios_postgres/ , and while that is itself nice the thing I found most remarkable is the bloat check. The majority of that code is an impressive bit of SQL that anyone could use even if you have no interest in Nagios, which is why I point it out for broader attention. Look in check_postgres.pl for the check_bloat routine and the big statement starting at the aptly labled This was fun to write section. If you pull that out of there and replace $MINPAGES and $MINIPAGES near the end with real values, you can pop that into a standalone query and execute it directly. That's what gives the summary Jeremy included in his message. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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: [OT] Re: [GENERAL] enabling autovacuum
On Mon, 2008-01-28 at 20:57 -0500, Greg Smith wrote: On Tue, 29 Jan 2008, Ow Mun Heng wrote: Can you let me know what is the sql used to generate such a nice summary of the tables? Might as well dupe the old text; this went out to the performance list: Greg Sabino Mullane released a Nagios plug-in for PostgreSQL that you can grab at http://bucardo.org/nagios_postgres/ , and while that is itself nice the thing I found most remarkable is the bloat check. The majority of that code is an impressive bit of SQL that anyone could use even if you have no interest in Nagios, which is why I point it out for broader attention. Look in check_postgres.pl for the check_bloat routine and the big statement starting at the aptly labled This was fun to write section. If you pull that out of there and replace $MINPAGES and $MINIPAGES near the end with real values, you can pop that into a standalone query and execute it directly. I'm subscribed to perf list and I _did_ take a look at the tool previously. However, something happened and I didn't managed to look at it throughly or something. I'll take another look at it and thanks for the pointers.. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.2.3 AutoVacuum not running
Schwenker, Stephen wrote: Hello, I've just compiled an instance of Postgresql 8.2.3 on a new linux box and have added some databases to it. I've noticed however that the autovacuum is not running. I have turned on the autovacuum, stats_start_collector, and stats_row_level and still the autovacuum is not running. Was this solved? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.2.3 AutoVacuum not running
Schwenker, Stephen [EMAIL PROTECTED] writes: It says it's on and I have also turned on all stats collecting. My guess is that it's actually running but is not choosing to do any vacuums for some reason. Try setting log_min_messages to DEBUG5 for awhile and trawling the postmaster log for evidence. You should at minimum see messages indicating that the postmaster is launching an autovac worker process once a minute; the next question is what that process is doing with itself. (You might want to adjust log_line_prefix to include PID so that you can track which messages come from which process. I find including a timestamp is often helpful also.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.3 AutoVacuum not running
It says it's on and I have also turned on all stats collecting. -Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: Monday, April 09, 2007 3:06 PM To: Schwenker, Stephen Cc: Tom Lane; pgsql-general@postgresql.org Subject: Re: [GENERAL] 8.2.3 AutoVacuum not running Schwenker, Stephen wrote: Hey, I've also notice one difference between my 8.1 instance and my 8.2 instance. I run a ps and on the 8.1 instance there is a 'stats buffer process' and in the 8.2 instance there is no 'stats buffer instance' Does that give you anymore reasons as to why the autovacuum is not working? No -- the stats buffer process was removed in 8.2 on purpose. If you do a show autovacuum, does it show as on? Maybe it was disabled due to misconfiguration. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 8.2.3 AutoVacuum not running
Hey, I've also notice one difference between my 8.1 instance and my 8.2 instance. I run a ps and on the 8.1 instance there is a 'stats buffer process' and in the 8.2 instance there is no 'stats buffer instance' Does that give you anymore reasons as to why the autovacuum is not working? :) From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Fri 06/04/2007 1:21 PM To: Schwenker, Stephen Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] 8.2.3 AutoVacuum not running Schwenker, Stephen [EMAIL PROTECTED] writes: I've just compiled an instance of Postgresql 8.2.3 on a new linux box and have added some databases to it. I've noticed however that the autovacuum is not running. How sure are you of that? Check pg_stat_all_tables to see if the last_autovacuum column is changing. regards, tom lane
Re: [GENERAL] 8.2.3 AutoVacuum not running
I'm sure. That column is null for all tables in my databases accept for the few tables that I've vacuumed manually. Any other suggestions? :) From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Fri 06/04/2007 1:21 PM To: Schwenker, Stephen Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] 8.2.3 AutoVacuum not running Schwenker, Stephen [EMAIL PROTECTED] writes: I've just compiled an instance of Postgresql 8.2.3 on a new linux box and have added some databases to it. I've noticed however that the autovacuum is not running. How sure are you of that? Check pg_stat_all_tables to see if the last_autovacuum column is changing. regards, tom lane
Re: [GENERAL] 8.2.3 AutoVacuum not running
Hey, I've just found something else. When I compiled postgresql, I assumed that because it compiled then it was a supported system. This was on the first line of the configure output. checking build system type... x86_64-unknown-linux-gnu I've looked at the supported systems list and the x86_64 doesn't appear to be supported. Can someone help me figure out how to make that system type to get supported? Maybe I can turn on some debugging to figure out why it's not running. I'm willing to put in some time to figure it out. Thank you, Steve. From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Fri 06/04/2007 1:21 PM To: Schwenker, Stephen Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] 8.2.3 AutoVacuum not running Schwenker, Stephen [EMAIL PROTECTED] writes: I've just compiled an instance of Postgresql 8.2.3 on a new linux box and have added some databases to it. I've noticed however that the autovacuum is not running. How sure are you of that? Check pg_stat_all_tables to see if the last_autovacuum column is changing. regards, tom lane
Re: [GENERAL] 8.2.3 AutoVacuum not running
Schwenker, Stephen wrote: Hey, I've also notice one difference between my 8.1 instance and my 8.2 instance. I run a ps and on the 8.1 instance there is a 'stats buffer process' and in the 8.2 instance there is no 'stats buffer instance' Does that give you anymore reasons as to why the autovacuum is not working? No -- the stats buffer process was removed in 8.2 on purpose. If you do a show autovacuum, does it show as on? Maybe it was disabled due to misconfiguration. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.2.3 AutoVacuum not running
OK, do you have the stats system enabled also? You require at least row_level stats for autovacuum to work. Schwenker, Stephen wrote: I'm sure. That column is null for all tables in my databases accept for the few tables that I've vacuumed manually. Any other suggestions? :) *From:* Tom Lane [mailto:[EMAIL PROTECTED] *Sent:* Fri 06/04/2007 1:21 PM *To:* Schwenker, Stephen *Cc:* pgsql-general@postgresql.org *Subject:* Re: [GENERAL] 8.2.3 AutoVacuum not running Schwenker, Stephen [EMAIL PROTECTED] writes: I've just compiled an instance of Postgresql 8.2.3 on a new linux box and have added some databases to it. I've noticed however that the autovacuum is not running. How sure are you of that? Check pg_stat_all_tables to see if the last_autovacuum column is changing. regards, tom lane ---(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: [GENERAL] 8.2.3 AutoVacuum not running
Schwenker, Stephen [EMAIL PROTECTED] writes: I've just compiled an instance of Postgresql 8.2.3 on a new linux box and have added some databases to it. I've noticed however that the autovacuum is not running. How sure are you of that? Check pg_stat_all_tables to see if the last_autovacuum column is changing. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
