OK, so i was going through the autovacuum code and i noticed that when we
construct the table list to vacuum in a database per run, we don't process
them in a particular order. I mean since we pick the list up from pg_class
so it may be ordered on oid? but when we select a database we have a
priority algo, which select the db's in XID wraparound danger first, and
that too the in an order that the db nearest to the XID wraparound is picked
first

So wouldn't it make sense to pick up the tables in a similar order as well?
like sorting the list on (deadtuples - calculated threshold) this way we
will be vacuuming the tables in more need first.

Without an order i can imagine a scenario where a table which is near a
wrap-around or has more dead-rows waiting or possible having the XID
warparound while another big table takes a lot of time.

With the launcher - worker architecture in 8.3 the situation will improve as
you can have multiple workers vacuuming multiple tables in parallel, but
having some order there would also help , i would think.

Thanks,
--
Usama Munir Dar http://www.linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

Search Discussions

  • Alvaro Herrera at Jan 3, 2008 at 2:39 pm

    Usama Dar escribió:

    So wouldn't it make sense to pick up the tables in a similar order as well?
    like sorting the list on (deadtuples - calculated threshold) this way we
    will be vacuuming the tables in more need first.
    Sure, feel free to propose a specific ordering. I think you would need
    to take table size into account too.

    Something that's also important to fix while you're doing that is fixing
    the "BUG" that is mentioned in the code that Simon griped about not long
    ago.

    --
    Alvaro Herrera http://www.CommandPrompt.com/
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
  • Usama Dar at Jan 3, 2008 at 3:27 pm

    On Jan 3, 2008 7:39 PM, Alvaro Herrera wrote:

    Sure, feel free to propose a specific ordering. I think you would need
    to take table size into account too.

    Thanks, i thought we were already taking the database size into account
    somewhat when we calculate the vacuum threshold by factoring in reltuples.
    My initial thought is that we already decide to vacuum a table if (vactuples
    vacthresh). if we order the list by vacpriority where
    vacpriority=vactuples - vacthresh

    it would be reasonable start, without being too complicated , thoughts?


    Something that's also important to fix while you're doing that is fixing
    the "BUG" that is mentioned in the code that Simon griped about not long
    ago.

    OK, i will look what that is



    --
    Alvaro Herrera
    http://www.CommandPrompt.com/
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support


    --
    Usama Munir Dar http://www.linkedin.com/in/usamadar
    Consultant Architect
    Cell:+92 321 5020666
    Skype: usamadar

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJan 2, '08 at 3:00p
activeJan 3, '08 at 3:27p
posts3
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Usama Dar: 2 posts Alvaro Herrera: 1 post

People

Translate

site design / logo © 2022 Grokbase