6 ms·

PostgreSQL Bloat: origins, monitoring and managing

146 points by craigkerstiens 11y ago

colanderman 11y ago
To the author: those code snippets would be 10x more readable if you got rid of the useless "admin@aws-eu-west-1-portal:compose (session 1)" preceding each input line.
- thwarted 11y ago
  And/or get rid of the insanely narrow fixed-width centered column and let the user resize it along with the window. So many programming blogs put code samples in a fixed-width unresizable column.
  - ak39 11y ago
    Not sure about this suggestion. Long lines of text are difficult for eyes to track. That's why you'll see tools like "Clearly" format text into narrower columns. Apparently there is an optimum number of words per line. Here is one write up on it: http://baymard.com/blog/line-length-readability http://baymard.com/blog/line-length-readability
    - chris_wot 11y ago
      Doesn't help me when I'm reading on an iPad or on the train on an iPhone. Just sayin'.
    - __david__ 11y ago
      Short lines are definitely good for prose (books makers have known this centuries), but I find that code is different. I tend to find code that has longer lines is much easier to read than code that rigidly adheres to (say) an 80 column length limit. I think it's because I tend not to read code in order. I like to see the shape of it, look at just the beginning of the line to see what might be going on in that line and only looking at the whole line if it seems relevant to what I'm doing. Normally just seeing the first 20 or so characters (or pulling some keywords out from syntax highlighting) is enough to understand the broad strokes.
- codepope2014 11y ago
  Good point and I've removed the user/host part, but the session part is relevant. With Compose's Write Stuff programme (compose.io/write-stuff) we try to retain the contributing authors content and styling as far as possible.
arthursilva 11y ago
Upcoming 9.6 has a great feature to speed up vacuum (thus keeping bloat controlled ).
- pritambaral 11y ago
  Was looking for a mention of this in the article.
- pilif 11y ago
  Not really. What's much faster is the wraparound protection vacuum which is a special more thorough vacuum that runs at a configured interval which defaults to every 200M transactions against a database. Only that one is sped up to now run roughly at the same speed as the normal vacuum
  - arthursilva 11y ago
    It looked to me that the freeze map allowed vacuum to skip pages not touched since last run, so avoiding a lot of work between runs.
    - anarazel 11y ago
      That's not new though - the visibility map has done so for years (8.4 IIRC).
comex 11y ago
The second problem the author mentioned, where a normal (non-FULL) vacuum has added dead tuples to a free list but the free regions are still scattered throughout the database files, seems like basically the same phenomenon as fragmentation in a filesystem. The symptoms are the same: extra I/O operations required due to inefficient packing of files/rows in the partition/data file, and inability to shrink the partition/file to the amount of actual data being stored inside (in the filesystem case, if the user explicitly requested a shrink; in the database case, automatically). pgcompact seems analogous to a typical explicit defragmentation tool, and VACUUM FULL probably has filesystem equivalents, though I haven't heard of them. But I've heard that in "modern filesystems", fragmentation is basically a solved problem, with defragmentation handled incrementally behind the scenes. Why do we need some random hacky script to do it with modern PostgreSQL?
- manigandham 11y ago
  PostgreSQL is a great modern database when it comes to operational features and functionality but is not very modern at the infrastructure level. Stuff like backups, replication, scaling, data file maintenance, etc., are still not well developed. The answer to your question is that the work just hasn't been done yet. It seems like the pace has been picking up recently, and I certainly like the new features, but I do wish the team would focus more on the behind-the-scenes infrastructure stuff more.
  - cbsmith 11y ago
    PostgreSQL does backups & replication pretty well these days... as for scaling... not sure what you're getting at there... it does quite well. It took them so long to get there because they were trying very hard to focus on doing the behind-the-scenes infrastructure stuff correctly.
  - ak4g 11y ago
    What's the reference point here? To my mind a lot of the advantages in Postgres are around it's mature tooling for backups and replication; autovacuum is more than sufficient for fairly complex workloads when it comes to data file maintenance, and really the only somewhat-sore point is horizontal scaling - but even there, a lot of the pain comes from there being multiple viable solutions in-the-wild, with none having decisive buy-in across the userbase, and as such no out-of-the-box solution in the standard DB (which is, happily, recognized by the developers and very much a focus of current development work). But, I'm comparing with mostly MySQL(/it's myriad descendants) and the whole NoSQL ecosystem; for all I know the tools available for PG are hopelessly primitive for people coming from of Oracle/IBM/SQL Server/Ingres/Teradata(/others? I pretty regularly learn of new DBMS engines with shockingly large companies and engineering orgs behind that that I've just never heard of; I'm sure there's plenty more.)
bsaul 11y ago
Does anyone knows of a good blog comparing pg against other major rdbms( oracle, mssql, mysql) and especially how they deal with transactions, and replication ( and why, for example pg is the only one in my knowledge having this vacuum issue) ?
- ComodoHacker 11y ago
  >why, for example pg is the only one in my knowledge having this vacuum issue Because other DBMSs have different MVCC implementations. Basically they keep that "snapshot" row versions in other places, not in the table itself. For example, in Oracle they go to undo segment; in MSSQL to tempdb (or you can just disable MVCC); and in MySQL with InniDB to rollback segments similar to Oracle (not surprising :). Other MVCC implementations have their own drawbacks of course.
  - bsaul 11y ago
    ""Other MVCC implementations have their own drawbacks of course."" Thanks for the info, can you elaborate on the drawbacks of other technics ?
    - ComodoHacker 11y ago
      I can for Oracle, which I'm most familiar with. Its approach adds much of complexity both in implementation and configuration/administation. DB engine must ensure integrity and consistency of data in different DB files in scenarios like backup, restore, crash recovery, DDL etc. DBA have to monitor and tune undo space according to database growing rate and workload. If not done properly, long-running SELECT can end up receiving "Snapshot too old" error, which means DB engine could not reconstruct all row data needed as seen at certain point of time.
    - EmielMols 11y ago
      Rollbacks (canceling a transaction) on other systems usually take longer because it physically needs to rewrite the "undo log" back to the data pages.
chris_wot 11y ago
Excellent article - it reminds me of this comment: https://news.ycombinator.com/item?id=11322244 https://news.ycombinator.com/item?id=11322244 In Oracle you can set the amount of MVCC storage, can any experienced PostgreSQL DBAs confirm if bloating is a big issue they must deal with?
- pgaddict 11y ago
  We do provide PostgreSQL support/DBA for a lot of customers, and while I don't have any detailed stats I probably can make some general conclusions ... I'd say that for most deployments bloat it's not a big issue. But of course, once in a while a customer gets bitten by it and we have to intervene. There's a variety of reasons for that: 1. disabled autovacuum Hey, we've disabled autovacuum because we can't risk it affecting production! Hey, we do know our workload patterns much better! Hey! 2. default autovacuum configuration (or bad tuning) The defaults are very conservative, and generally require tuning (cranking up) on large databases. But people tend to do exactly the opposite, driven by the misconception that it will make autovacuum less intrusive. 3. doing things that are known to break things Like, keeping transactions open for a long time. 4. pathological workload patterns There are a few workload patterns that result in bloat (particularly in indexes), and autovacuum can't really fix that. For example large bulk deletes may cause this. This is probably the one thing that can't be fixed by configuration changes, etc.
  - anarazel 11y ago
    Re 4) Bulk deletes causing index fragmentation isn't really related to MVCC tho; and it's certainly something non mvcc engines also have troubles with.
- ivoras 11y ago
  It certainly exists, and in unexpected places, like system catalogues. It's especially visible if a (wrong) mgmt decision has been made to switch off autovacuum - then everything tends to blow up, and fast. In short: explicit bloat management and autovacuum tuning are a necessity for all high transaction rate workloads in PostgreSQL, and the stats need to be monitored closely to detect workload changes.

146 points by craigkerstiens 11y ago

To the author: those code snippets would be 10x more readable if you got rid of the useless "admin@aws-eu-west-1-portal:compose (session 1)" preceding each input line.

thwarted 11y ago
And/or get rid of the insanely narrow fixed-width centered column and let the user resize it along with the window. So many programming blogs put code samples in a fixed-width unresizable column.
- ak39 11y ago
  Not sure about this suggestion. Long lines of text are difficult for eyes to track. That's why you'll see tools like "Clearly" format text into narrower columns. Apparently there is an optimum number of words per line. Here is one write up on it: http://baymard.com/blog/line-length-readability http://baymard.com/blog/line-length-readability
  - chris_wot 11y ago
    Doesn't help me when I'm reading on an iPad or on the train on an iPhone. Just sayin'.
  - __david__ 11y ago
    Short lines are definitely good for prose (books makers have known this centuries), but I find that code is different. I tend to find code that has longer lines is much easier to read than code that rigidly adheres to (say) an 80 column length limit. I think it's because I tend not to read code in order. I like to see the shape of it, look at just the beginning of the line to see what might be going on in that line and only looking at the whole line if it seems relevant to what I'm doing. Normally just seeing the first 20 or so characters (or pulling some keywords out from syntax highlighting) is enough to understand the broad strokes.
codepope2014 11y ago
Good point and I've removed the user/host part, but the session part is relevant. With Compose's Write Stuff programme (compose.io/write-stuff) we try to retain the contributing authors content and styling as far as possible.

arthursilva 11y ago

Upcoming 9.6 has a great feature to speed up vacuum (thus keeping bloat controlled ).

pritambaral 11y ago
Was looking for a mention of this in the article.
pilif 11y ago
Not really. What's much faster is the wraparound protection vacuum which is a special more thorough vacuum that runs at a configured interval which defaults to every 200M transactions against a database. Only that one is sped up to now run roughly at the same speed as the normal vacuum
- arthursilva 11y ago
  It looked to me that the freeze map allowed vacuum to skip pages not touched since last run, so avoiding a lot of work between runs.
  - anarazel 11y ago
    That's not new though - the visibility map has done so for years (8.4 IIRC).

comex 11y ago

The second problem the author mentioned, where a normal (non-FULL) vacuum has added dead tuples to a free list but the free regions are still scattered throughout the database files, seems like basically the same phenomenon as fragmentation in a filesystem. The symptoms are the same: extra I/O operations required due to inefficient packing of files/rows in the partition/data file, and inability to shrink the partition/file to the amount of actual data being stored inside (in the filesystem case, if the user explicitly requested a shrink; in the database case, automatically). pgcompact seems analogous to a typical explicit defragmentation tool, and VACUUM FULL probably has filesystem equivalents, though I haven't heard of them. But I've heard that in "modern filesystems", fragmentation is basically a solved problem, with defragmentation handled incrementally behind the scenes. Why do we need some random hacky script to do it with modern PostgreSQL?

manigandham 11y ago
PostgreSQL is a great modern database when it comes to operational features and functionality but is not very modern at the infrastructure level. Stuff like backups, replication, scaling, data file maintenance, etc., are still not well developed. The answer to your question is that the work just hasn't been done yet. It seems like the pace has been picking up recently, and I certainly like the new features, but I do wish the team would focus more on the behind-the-scenes infrastructure stuff more.
- cbsmith 11y ago
  PostgreSQL does backups & replication pretty well these days... as for scaling... not sure what you're getting at there... it does quite well. It took them so long to get there because they were trying very hard to focus on doing the behind-the-scenes infrastructure stuff correctly.
- ak4g 11y ago
  What's the reference point here? To my mind a lot of the advantages in Postgres are around it's mature tooling for backups and replication; autovacuum is more than sufficient for fairly complex workloads when it comes to data file maintenance, and really the only somewhat-sore point is horizontal scaling - but even there, a lot of the pain comes from there being multiple viable solutions in-the-wild, with none having decisive buy-in across the userbase, and as such no out-of-the-box solution in the standard DB (which is, happily, recognized by the developers and very much a focus of current development work). But, I'm comparing with mostly MySQL(/it's myriad descendants) and the whole NoSQL ecosystem; for all I know the tools available for PG are hopelessly primitive for people coming from of Oracle/IBM/SQL Server/Ingres/Teradata(/others? I pretty regularly learn of new DBMS engines with shockingly large companies and engineering orgs behind that that I've just never heard of; I'm sure there's plenty more.)

bsaul 11y ago

Does anyone knows of a good blog comparing pg against other major rdbms( oracle, mssql, mysql) and especially how they deal with transactions, and replication ( and why, for example pg is the only one in my knowledge having this vacuum issue) ?

ComodoHacker 11y ago
>why, for example pg is the only one in my knowledge having this vacuum issue Because other DBMSs have different MVCC implementations. Basically they keep that "snapshot" row versions in other places, not in the table itself. For example, in Oracle they go to undo segment; in MSSQL to tempdb (or you can just disable MVCC); and in MySQL with InniDB to rollback segments similar to Oracle (not surprising :). Other MVCC implementations have their own drawbacks of course.
- bsaul 11y ago
  ""Other MVCC implementations have their own drawbacks of course."" Thanks for the info, can you elaborate on the drawbacks of other technics ?
  - ComodoHacker 11y ago
    I can for Oracle, which I'm most familiar with. Its approach adds much of complexity both in implementation and configuration/administation. DB engine must ensure integrity and consistency of data in different DB files in scenarios like backup, restore, crash recovery, DDL etc. DBA have to monitor and tune undo space according to database growing rate and workload. If not done properly, long-running SELECT can end up receiving "Snapshot too old" error, which means DB engine could not reconstruct all row data needed as seen at certain point of time.
  - EmielMols 11y ago
    Rollbacks (canceling a transaction) on other systems usually take longer because it physically needs to rewrite the "undo log" back to the data pages.

chris_wot 11y ago

Excellent article - it reminds me of this comment: https://news.ycombinator.com/item?id=11322244 https://news.ycombinator.com/item?id=11322244 In Oracle you can set the amount of MVCC storage, can any experienced PostgreSQL DBAs confirm if bloating is a big issue they must deal with?

pgaddict 11y ago
We do provide PostgreSQL support/DBA for a lot of customers, and while I don't have any detailed stats I probably can make some general conclusions ... I'd say that for most deployments bloat it's not a big issue. But of course, once in a while a customer gets bitten by it and we have to intervene. There's a variety of reasons for that: 1. disabled autovacuum Hey, we've disabled autovacuum because we can't risk it affecting production! Hey, we do know our workload patterns much better! Hey! 2. default autovacuum configuration (or bad tuning) The defaults are very conservative, and generally require tuning (cranking up) on large databases. But people tend to do exactly the opposite, driven by the misconception that it will make autovacuum less intrusive. 3. doing things that are known to break things Like, keeping transactions open for a long time. 4. pathological workload patterns There are a few workload patterns that result in bloat (particularly in indexes), and autovacuum can't really fix that. For example large bulk deletes may cause this. This is probably the one thing that can't be fixed by configuration changes, etc.
- anarazel 11y ago
  Re 4) Bulk deletes causing index fragmentation isn't really related to MVCC tho; and it's certainly something non mvcc engines also have troubles with.
ivoras 11y ago
It certainly exists, and in unexpected places, like system catalogues. It's especially visible if a (wrong) mgmt decision has been made to switch off autovacuum - then everything tends to blow up, and fast. In short: explicit bloat management and autovacuum tuning are a necessity for all high transaction rate workloads in PostgreSQL, and the stats need to be monitored closely to detect workload changes.