Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuplesinaccurate.
От | David Gould |
---|---|
Тема | Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuplesinaccurate. |
Дата | |
Msg-id | 20180312231417.484d64c0@engels обсуждение исходный текст |
Ответ на | Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate. (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate.
|
Список | pgsql-hackers |
On Mon, 12 Mar 2018 10:43:36 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Re-reading that thread, it seems like we should have applied Jeff's > initial trivial patch[1] (to not hold across > table_recheck_autovac) rather than waiting around for a super duper > improvement to get agreed on. I'm a bit tempted to go do that; > if nothing else, it seems simple enough to back-patch, unlike most > of the rest of what was discussed. This will help. In my testing it reduced the lock contention considerably. I think a lot of users with lots of tables will benefit from it. However it does nothing about the bigger issue which is that autovacuum flaps the stats temp files. I have attached the patch we are currently using. It applies to 9.6.8. I have versions for older releases in 9.4, 9.5, 9.6. I fails to apply to 10, and presumably head but I can update it if there is any interest. The patch has three main features: - Impose an ordering on the autovacuum workers worklist to avoid the need for rechecking statistics to skip already vacuumed tables. - Reduce the frequency of statistics refreshes - Remove the AutovacuumScheduleLock The patch is aware of the shared relations fix. It is subject to the problem Alvero noted in the original discussion: if the last table to be autovacuumed is large new workers will exit instead of choosing an earlier table. Not sure this is really a big problem in practice, but I agree it is a corner case. My patch does not do what I believe really needs doing: Schedule autovacuum more intelligently. Currently autovacuum collects all the tables in the physical order of pg_class and visits them one by one. With many tables it can take too long to respond to urgent vacuum needs, eg heavily updated tables or wraparound. There is a patch in the current commit fest that allows prioritizing tables manually. I don't like that idea much, but it does recognize that the current scheme is not adequate for databases with large numbers of tables. -dg -- David Gould daveg@sonic.net If simplicity worked, the world would be overrun with insects.
Вложения
В списке pgsql-hackers по дате отправления: