Обсуждение: max_fsm_pages question
I got this log on 8.3.9: Jan 24 02:13:28 db23.zmi.at postgres[29696]: [3-1] DB= U= H= WARNING: relation "pg_toast.pg_toast_1910021" contains more than "max_fsm_pages" pages with useful free space Jan 24 02:13:28 db23.zmi.at postgres[29696]: [3-2] DB= U= H= HINT: Consider using VACUUM FULL on this relation or increasing the configuration parameter "max_fsm_pages". Jan 24 02:13:28 db23.zmi.at postgres[29696]: [4-1] DB= U= H= LOG: automatic vacuum of table "dbmail.pg_toast.pg_toast_1910021": index scans: 1 Jan 24 02:13:28 db23.zmi.at postgres[29696]: [4-2] pages: 0 removed, 740218 remain Jan 24 02:13:28 db23.zmi.at postgres[29696]: [4-3] tuples: 601310 removed, 2397087 remain Jan 24 02:13:28 db23.zmi.at postgres[29696]: [4-4] system usage: CPU 5.73s/1.92u sec elapsed 835.67 sec and I'd like to know 1) which db uses pg_toast.pg_toast_1910021? (Later I found it:) That should be dbmail, as it writes "dbmail.pg_toast.pg_toast_1910021" later on. But wouldn't it be good to log that directly? Making it easier for admins... 2) what table is using that toast? 3) why did postgres suddenly decide to remove the old cruft suddenly? Autovacuum is on, the nightly backups do an extra "vacuum analyze", and once a week a CLUSTER is done for the big tables. Maybe I missed one? -- mit freundlichen Grüssen, Michael Monnerie, Ing. BSc it-management Internet Services http://it-management.at Tel: 0660 / 415 65 31 // Wir haben zwei Häuser zu verkaufen: // http://zmi.at/langegg/ // http://willhaben.at/iad/realestate/object?adId=15923011
Вложения
Michael Monnerie wrote: > why did postgres suddenly decide to remove the old cruft suddenly? Have you read through this yet?: http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM > Autovacuum is on, the nightly backups do an extra "vacuum analyze", > and once a week a CLUSTER is done for the big tables. You should probably make that a "vacuum analyze verbose" to get a good idea of where you should set max_fsm_pages, and to look for where you are accumumlating free space to track. -Kevin
On Montag, 25. Januar 2010 Kevin Grittner wrote: > Michael Monnerie wrote: > > why did postgres suddenly decide to remove the old cruft suddenly? > > Have you read through this yet?: > > http://www.postgresql.org/docs/8.3/interactive/runtime-config-resourc > e.html#RUNTIME-CONFIG-RESOURCE-FSM Yes I did. > > Autovacuum is on, the nightly backups do an extra "vacuum analyze", > > and once a week a CLUSTER is done for the big tables. > > You should probably make that a "vacuum analyze verbose" to get a > good idea of where you should set max_fsm_pages, and to look for > where you are accumumlating free space to track. That's why I find it strange. I always log the VACUUM VERBOSE ANALYZE command, it is run nightly: INFO: free space map contains 21517 pages in 107 relations DETAIL: A total of 22912 page slots are in use (including overhead). 22912 page slots are required to track all free space. Current limits are: 150000 page slots, 1000 relations, using 984 kB. So, as there was that one relation that was bloatet - how could it be? Autovaccuum, nightly vacuum analyze, weekly cluster - and still a heavy bloated toast* something. I must do something wrong. -- mit freundlichen Grüssen, Michael Monnerie, Ing. BSc it-management Internet Services http://it-management.at Tel: 0660 / 415 65 31 // Wir haben zwei Häuser zu verkaufen: // http://zmi.at/langegg/ // http://willhaben.at/iad/realestate/object?adId=15923011
Вложения
Michael Monnerie <michael.monnerie@is.it-management.at> wrote: > So, as there was that one relation that was bloatet - how could it > be? Autovaccuum, nightly vacuum analyze, weekly cluster - and > still a heavy bloated toast* something. I must do something wrong. Any chance you had or have long-running transactions. We once had very low free space in a big database which suddenly ballooned. It turned out an application programmer had left a connection in "idle in transaction" state for a few days. Another possibility is that you had an update or delete which affected a lot of rows. Even if it rolls back, it can cause bloat. -Kevin
On Montag, 25. Januar 2010 Kevin Grittner wrote: > Any chance you had or have long-running transactions. We once had > very low free space in a big database which suddenly ballooned. It > turned out an application programmer had left a connection in "idle > in transaction" state for a few days. > > Another possibility is that you had an update or delete which > affected a lot of rows. Even if it rolls back, it can cause bloat. I don't believe that. This machine holds the "dbmail" Mail Database, locks and transactions are very small, just to insert a new mail. The system is very low loaded, so such a bloat can't happen in a day. Finding out which table the toast belongs to would be great, maybe that helps further investigation. -- mit freundlichen Grüssen, Michael Monnerie, Ing. BSc it-management Internet Services http://it-management.at Tel: 0660 / 415 65 31 // Wir haben zwei Häuser zu verkaufen: // http://zmi.at/langegg/ // http://willhaben.at/iad/realestate/object?adId=15923011
Вложения
Michael Monnerie <michael.monnerie@is.it-management.at> wrote: > such a bloat can't happen in a day. That is evidence that you may have a problem with some long-running transaction which stays open for days, possibly "idle in transaction". Bloat will accumulate, without any vacuum being able to prevent it or recover from it, until the transaction terminates. It will not show up as free space in your vacuum verbose output while the transaction remains open; although I believe it will show up in the "dead row versions cannot be removed yet" count. > Finding out which table the toast belongs to would be great, maybe > that helps further investigation. Have a look at reltoastrelid: http://www.postgresql.org/docs/8.3/interactive/catalog-pg-class.html -Kevin
On Montag, 25. Januar 2010 Kevin Grittner wrote: > Have a look at reltoastrelid: Hmm.. select * from pg_class; doesnt give anything containing 1910021 that I had in the log. So what? A temporary table? But that shouldn't be bloated nor auto-vacuumed, right? -- mit freundlichen Grüssen, Michael Monnerie, Ing. BSc it-management Internet Services http://it-management.at Tel: 0660 / 415 65 31 // Wir haben zwei Häuser zu verkaufen: // http://zmi.at/langegg/ // http://willhaben.at/iad/realestate/object?adId=15923011