Re: [HACKERS] Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)
On Mon, Feb 4, 2013 at 9:05 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Feb 1, 2013 at 12:33 PM, Pavan Deolasee pavan.deola...@gmail.com wrote: I can write a patch in next couple of days if we are willing to accept for this release. I think it should be fairly easy and non-intrusive. I think it's too late to consider this for 9.3, but I think we should entertain it for 9.4. Its going to be fairly non The biggest problem I see is that the naming of the new reloptions might end up being something kind of unintuitive, like autovacuum_vacuum_enabled and autovacuum_analyze_enabled. You need a degree in PostgreSQLology to understand what that means, but I haven't got a better idea. Yeah, I also thought of those two names. May be autovacuum should have been called autovacanalyze or something like that. But that's too late. May be someday we overhaul the maintenance activities and call it bgmaintainer ? Or have two different threads to do autovacuum and autoanalyze ? For now, I can't think of anything better than autovacuum_vacuum_enabled and autovacuum_analyze_enabled. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)
On Fri, Feb 1, 2013 at 10:53 PM, Bruce Momjian br...@momjian.us wrote: Are these TODO items? BTW, there are couple of TODOs. 1. Analyze should be done based on the total row churn across the parent + children. Looking at the parent only, as we do now, can result in analyzing too often or too seldom. 2. Auto-analyze should analyze parent and each child table in a separate transaction. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)
On Tue, Feb 5, 2013 at 01:41:05PM +0530, Pavan Deolasee wrote: On Fri, Feb 1, 2013 at 10:53 PM, Bruce Momjian br...@momjian.us wrote: Are these TODO items? BTW, there are couple of TODOs. 1. Analyze should be done based on the total row churn across the parent + children. Looking at the parent only, as we do now, can result in analyzing too often or too seldom. 2. Auto-analyze should analyze parent and each child table in a separate transaction. I added a link to this thread on the TODO list under: Improve autovacuum tuning -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)
On Fri, Feb 1, 2013 at 12:33 PM, Pavan Deolasee pavan.deola...@gmail.com wrote: I can write a patch in next couple of days if we are willing to accept for this release. I think it should be fairly easy and non-intrusive. I think it's too late to consider this for 9.3, but I think we should entertain it for 9.4. The biggest problem I see is that the naming of the new reloptions might end up being something kind of unintuitive, like autovacuum_vacuum_enabled and autovacuum_analyze_enabled. You need a degree in PostgreSQLology to understand what that means, but I haven't got a better idea. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)
On Fri, Feb 1, 2013 at 5:54 PM, Pavan Deolasee pavan.deola...@gmail.comwrote: There is another problem that I noticed while looking at this case. The analyze took close to 500sec on a fairly good hardware (40GB RAM, 10K rpm disks on RAID10) because many large child tables were scanned at once. Just a small correction to get a good benchmark The ~500 sec analyze time was on a test VM running on a i5 2.4 Ghz with 2 dedicate cores, 4 GB of RAM and stored on a notebook HDD. The partitioned data was about 80GB. On our production environment (described by Pavan) it took ~90 second for ~55GB of data in 8 child/partition tables (we stopped the import of partitioned data when we discovered this issue - we COPYed and TRUNCATEd partitions to speed up upgrade procedure from 8.4 to 9.1 by pg_dump/pg_restore). Vlad
[HACKERS] Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)
On Fri, Feb 1, 2013 at 6:10 PM, Pavan Deolasee pavan.deola...@gmail.comwrote: 2012-12-05 00:44:23 EET LOG: automatic analyze of table fleet.fleet.vehicle_position system usage: CPU 4.46s/0.61u sec elapsed 465.09 sec This is the interesting piece of information. So its the auto analyze thats causing all the IO activity. That explains why it was a read only IO that we noticed earlier. Whats happening here, and something that changed from 8.4 to 9.1, is that whenever the parent table is analyzed, the child tables are also automatically analyzed. I don't remember the rational for doing this change, but in your case the analyze on the parent table itself is quite useless because even though you inserting a large number of new tuples, you are also immediately deleting them. I don't want to comment on the design aspect of that, but you should be able to fix this problem by disabling auto-analyze on the parent table. Having said that, I don't see an easy way to just disable auto-analyze on a table. You can run ALTER TABLE foo SET (autovacuum_enabled = false), but that would also disable auto-vacuum, which you certainly don't want to do because the parent table would just keep growing. You can set autovacuum_analyze_threshold to an artificially high value to mitigate the problem and reduce the frequency of auto-analyze on the table or see if you can completely avoid insert/delete on the parent table. ALTER TABLE vehicle_position SET (autovacuum_analyze_threshold = 20); While looking at this particular case on -general, I realized that there is no way to *only* disable auto-analyze on a table. While one can cheat like what I suggested to the OP by setting threshold very high, I think it will be useful to be able to just off analyze. In this particular case, the OP is inserting and then deleting the same rows from the parent table, thus keeping it almost empty. Of course, he would want to run auto-vacuum on the table to remove the dead rows. Usually auto-analyze would have returned quite fast, especially because we vacuum a table first and then analyze it. But in this case, since the table is a parent of a number of large child tables, we end up analyzing the child tables too, which takes significantly longer time and is quite unnecessary because in this case the activity on the parent table must not have changed any stats for the child tables. A new reloption such as autovacuum_analyze_enabled is what we need. Comments ? Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee
Re: [HACKERS] Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)
Pavan Deolasee pavan.deola...@gmail.com writes: While looking at this particular case on -general, I realized that there is no way to *only* disable auto-analyze on a table. While one can cheat like what I suggested to the OP by setting threshold very high, I think it will be useful to be able to just off analyze. In this particular case, the OP is inserting and then deleting the same rows from the parent table, thus keeping it almost empty. Of course, he would want to run auto-vacuum on the table to remove the dead rows. Usually auto-analyze would have returned quite fast, especially because we vacuum a table first and then analyze it. But in this case, since the table is a parent of a number of large child tables, we end up analyzing the child tables too, which takes significantly longer time and is quite unnecessary because in this case the activity on the parent table must not have changed any stats for the child tables. A new reloption such as autovacuum_analyze_enabled is what we need. This seems to me to be a wart that doesn't fix the actual problem --- the actual problem is to make the autovac daemon smarter about when an inheritance-tree ANALYZE pass is needed. That should be done somehow based on the total row churn across the parent + children. Looking at the parent only, as we do now, can result in analyzing too often (the OP's case) or too seldom (the much more common case). A manual off switch fixes only the less common case, and requires user intervention that we'd be better off without. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)
Pavan Deolasee escribió: While looking at this particular case on -general, I realized that there is no way to *only* disable auto-analyze on a table. While one can cheat like what I suggested to the OP by setting threshold very high, I think it will be useful to be able to just off analyze. In this particular case, the OP is inserting and then deleting the same rows from the parent table, thus keeping it almost empty. Of course, he would want to run auto-vacuum on the table to remove the dead rows. Usually auto-analyze would have returned quite fast, especially because we vacuum a table first and then analyze it. But in this case, since the table is a parent of a number of large child tables, we end up analyzing the child tables too, which takes significantly longer time and is quite unnecessary because in this case the activity on the parent table must not have changed any stats for the child tables. A new reloption such as autovacuum_analyze_enabled is what we need. I was thinking in this option just three days ago, so yeah. I think we also want an option to turn off just vacuum. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)
On Fri, Feb 1, 2013 at 9:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Pavan Deolasee pavan.deola...@gmail.com writes: A new reloption such as autovacuum_analyze_enabled is what we need. This seems to me to be a wart that doesn't fix the actual problem --- IMHO this case is just an example, but I'm sure there would be similar such examples which may not involve inheritance. For example, say user has a very large table which is updated very frequently but not in a way that his query plans will be affected. The user may want to turn auto analyze in such cases. And given that we allow the user to control all other parameters, I don't understand why we would not let him turn it off completely. There is another problem that I noticed while looking at this case. The analyze took close to 500sec on a fairly good hardware (40GB RAM, 10K rpm disks on RAID10) because many large child tables were scanned at once. We analyze all of them in a single transaction. This long running transaction will cause a lot of bloat for heavily updated tables since HOT will fail to keep up. I wonder if we should set up the child tables in the tableoid_list just like we do for toast tables so that each table is analyzed in its own transaction. This is also important because partitioning will typically involve very large tables. Of course, if we could ever run analyze on a single table in multiple smaller transactions, that will be even better. But I'm not sure if thats feasible. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)
On Fri, Feb 1, 2013 at 9:07 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Pavan Deolasee escribió: A new reloption such as autovacuum_analyze_enabled is what we need. I was thinking in this option just three days ago, so yeah. I think we also want an option to turn off just vacuum. +1. I think that will be useful too in some situations, especially when user wants to let autovacuum take care of some tables and manually vacuum the rest. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)
On Fri, Feb 1, 2013 at 12:37:21PM -0300, Alvaro Herrera wrote: Pavan Deolasee escribió: While looking at this particular case on -general, I realized that there is no way to *only* disable auto-analyze on a table. While one can cheat like what I suggested to the OP by setting threshold very high, I think it will be useful to be able to just off analyze. In this particular case, the OP is inserting and then deleting the same rows from the parent table, thus keeping it almost empty. Of course, he would want to run auto-vacuum on the table to remove the dead rows. Usually auto-analyze would have returned quite fast, especially because we vacuum a table first and then analyze it. But in this case, since the table is a parent of a number of large child tables, we end up analyzing the child tables too, which takes significantly longer time and is quite unnecessary because in this case the activity on the parent table must not have changed any stats for the child tables. A new reloption such as autovacuum_analyze_enabled is what we need. I was thinking in this option just three days ago, so yeah. I think we also want an option to turn off just vacuum. Are these TODO items? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning auto-analyze off (was Re: [GENERAL] Unusually high IO for autovacuum worker)
On Fri, Feb 1, 2013 at 10:53 PM, Bruce Momjian br...@momjian.us wrote: On Fri, Feb 1, 2013 at 12:37:21PM -0300, Alvaro Herrera wrote: A new reloption such as autovacuum_analyze_enabled is what we need. I was thinking in this option just three days ago, so yeah. I think we also want an option to turn off just vacuum. Are these TODO items? I can write a patch in next couple of days if we are willing to accept for this release. I think it should be fairly easy and non-intrusive. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
