Fun fact about autovacuum and orphan temp tables - Mailing list pgsql-hackers
From | Grigory Smolkin |
---|---|
Subject | Fun fact about autovacuum and orphan temp tables |
Date | |
Msg-id | 7b7f3e98-9753-3fa9-aaa4-b6f8a766c251@postgrespro.ru Whole thread Raw |
Responses |
Re: Fun fact about autovacuum and orphan temp tables
Re: Fun fact about autovacuum and orphan temp tables Re: Fun fact about autovacuum and orphan temp tables |
List | pgsql-hackers |
<p>Hello, hackers!<p>We were testing how well some application works with PostgreSQL and stumbled upon an autovacuum behaviorwhich I fail to understand.<br /> Application in question have a habit to heavily use temporary tables in funny ways.<br/> For example it creates A LOT of them.<br /> Which is ok.<br /> Funny part is that it never drops them. So whenbackend is finally terminated, it tries to drop them and fails with error:<br /><br /> FATAL: out of shared memory<br/> HINT: You might need to increase max_locks_per_transaction<br /><br /> If I understand that rigth, we are tryingto drop all these temp tables in one transaction and running out of locks to do so.<br /> After that postgresql.logis flooded at the rate 1k/s with messages like that:<br /><br /> LOG: autovacuum: found orphan temp table"pg_temp_15"."tt38147" in database "DB_TEST"<br /><br /> It produces a noticeable load on the system and it`s gettingworst with every terminated backend or restart.<br /> I did some RTFS and it appears that autovacuum has no intentionof cleaning that orphan tables unless<br /> it`s wraparound time:<br /><br /> src/backend/postmaster/autovacuum.c<br/> /* We just ignore it if the owning backend is still active */<br /> 2037 if (backendID == MyBackendId || BackendIdGetProc(backendID) == NULL)<br /> 2038 {<br /> 2039 /*<br /> 2040 * We found an orphan temp table (which was probably left<br /> 2041 * behind by a crashed backend). If it's so old as to need<br /> 2042 * vacuum forwraparound, forcibly drop it. Otherwise just<br /> 2043 * log a complaint.<br /> 2044 */<br /> 2045 if (wraparound)<br /> 2046 {<br /> 2047 ObjectAddress object;<br /> 2048 <br /> 2049 ereport(LOG,<br /> 2050 (errmsg("autovacuum: dropping orphan temp table \"%s\".\"%s\" in database \"%s\"",<br /> 2051 get_namespace_name(classForm->relnamespace),<br /> 2052 NameStr(classForm->relname),<br /> 2053 get_database_name(MyDatabaseId))));<br /> 2054 object.classId= RelationRelationId;<br /> 2055 object.objectId = relid;<br /> 2056 object.objectSubId = 0;<br /> 2057 performDeletion(&object, DROP_CASCADE,PERFORM_DELETION_INTERNAL);<br /> 2058 }<br /> 2059 else<br /> 2060 {<br /> 2061 ereport(LOG,<br /> 2062 (errmsg("autovacuum:found orphan temp table \"%s\".\"%s\" in database \"%s\"",<br /> 2063 get_namespace_name(classForm->relnamespace),<br /> 2064 NameStr(classForm->relname),<br /> 2065 get_database_name(MyDatabaseId))));<br /> 2066 }<br /> 2067 }<br /> 2068 }<br /><br /><br /> What is more troubling is that pg_statistic is starting to bloatbadly.<br /><br /> LOG: automatic vacuum of table "DB_TEST.pg_catalog.pg_statistic": index scans: 0<br /> pages:0 removed, 68225 remain, 0 skipped due to pins<br /> tuples: 0 removed, 2458382 remain, 2408081 are dead butnot yet removable<br /> buffer usage: 146450 hits, 31 misses, 0 dirtied<br /> avg read rate: 0.010 MB/s,avg write rate: 0.000 MB/s<br /> system usage: CPU 3.27s/6.92u sec elapsed 23.87 sec<br /><br /> What is thepurpose of keeping orphan tables around and not dropping them on the spot?<br /><br /><br /><pre class="moz-signature"cols="72">-- Grigory Smolkin Postgres Professional: <a class="moz-txt-link-freetext" href="http://www.postgrespro.com">http://www.postgrespro.com</a> The Russian Postgres Company</pre>
pgsql-hackers by date: