Search Postgresql Archives

Re: CPU-intensive autovacuuming

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

 



Phil,

If you complete this patch, I'm very interested to see it.

I think I'm the person Matthew is talking about who inserted a sleep value. Because of the sheer number of tables involved, even small values of sleep caused pg_autovacuum to iterate too slowly over its table lists to be of use in a production environment (where I still find its behavior to be preferable to a complicated list of manual vacuums performed in cron).

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Jun 7, 2005, at 6:16 AM, Phil Endecott wrote:

Matthew T. O'Connor wrote:

Phil Endecott wrote:

> Could it be that there is some code in autovacuum that is O (n^2) in
> the number of tables?

Browsing the code using webcvs, I have found this:

for (j = 0; j < PQntuples(res); j++)
{
    tbl_elem = DLGetHead(dbs->table_list);
    while (tbl_elem != NULL)
    {  Have I correctly understood what is going on here?



Indeed you have. I have head a few similar reports but perhaps none as bad as yours. One person put a small sleep value so that it doesn't spin so tight. You could also just up the sleep delay so that it doesn't do this work quite so often. No other quick suggestions.


I do wonder why autovacuum is keeping its table list in memory rather than in the database.

But given that it is keeping it in memory, I think the real fix is to sort that list (or keep it ordered when building or updating it). It is trivial to also get the query results ordered, and they can then be compared in O(n) time.

I notice various other places where there seem to be nested loops, e.g. in the update_table_list function. I'm not sure if they can be fixed by similar means.

--Phil.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx


[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