Search Postgresql Archives

Re: Unusually high IO for autovacuum worker

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Fri, Feb 1, 2013 at 5:19 PM, Vlad Bailescu <vlad@xxxxxxxxxxxxxxxxxx> wrote:


Pavan, it seems there's a small issue with copy-paste and column text-align. Table sizes are:

4136 kB
2448 kB
2336 kB

Ah OK. I see.
 

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  = 2000000000);

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux