We are now ready to accept your submissions to present at PyPgDay. Please submit as soon as possible; the deadline is January 20.
We are particularly looking for speakers who can present on using PostgreSQL and Python together, including Django, GeoDjango, Pylons, psycopg2, SQLAlchemy, PL/Python, NumPy, and the new PostgreSQL JSON features. General talks about PostgreSQL administration and performance, pitched for a developer audience, are also welcome. How-to talks, case studies, personal experiences, and new software introductions are welcome. We will also have lightning talks.
Submit now!
Tuesday, December 18, 2012
Saturday, December 15, 2012
Reminder: pgCon Call For Papers
The pgCon Call For Papers has been open since December 1st. This is part of an effort by the conference committee to get all the speakers notified on time this year, so please submit your proposals before January 15th. See you at pgCon!
Friday, December 7, 2012
Save The Date: PyPgDay
PostgreSQL has become the "default database" of Python users everywhere. And Python has become the most popular scripting language for Postgres. So, to celebrate that, we're throwing a miniconference at PyCon 2013.
PyPgDay will be a full-day PostgreSQL event at PyCon this year, on March 13th (all day), at the Santa Clara Convention Center. Watch the PyPgDay wiki page and this blog for updates on the event, as we get it organized.
In other event news, the Call For Papers for pgCon is now open. Please submit your PostgreSQL hacking, development, performance, and advanced technique proposals.
PyPgDay will be a full-day PostgreSQL event at PyCon this year, on March 13th (all day), at the Santa Clara Convention Center. Watch the PyPgDay wiki page and this blog for updates on the event, as we get it organized.
In other event news, the Call For Papers for pgCon is now open. Please submit your PostgreSQL hacking, development, performance, and advanced technique proposals.
Freezing Your Tuples Off, Part 3
First, read Part 1 and Part 2 of this series.
To understand the purpose of this parameter, you have to understand how vacuum has worked since version 8.4. Vacuum no longer usually scans the entire table; instead, it has a bitmap (called the visibility_map) which tells it which data pages in the table have reclaimable space, so that it scans only the portion of the table which is "dirty". This is a considerable optimization for large tables, where only 20% of the rows might have been updated or deleted since the last vacuum cycle.
This does, however, introduce a problem. Since old, untouched data pages aren't being vacuumed (since they don't have any reclaimable rows anymore), they are not getting frozen regardless of the setting of vacuum_freeze_min_age. This makes it much more likely you'll hit an eventual wraparound vac when you least expect it.
The idea of vacuum_freeze_table_age is to compliment autovacuum_max_freeze_age by telling the database: "If it's almost vacuum_freeze time for this table, and you were vacuuming the table anyway, then scan the whole table and to freeze out tuples." In theory, this should allow you to hold off wraparound vacuum by running a regular, cancellable vacuum instead. In practice, though, that's a dubious benefit for large tables, since either way you get a full-table scan and a vacuum which runs for a long time. Small, heavily updated tables will tend to get most of their pages vacuumed most of the time anyway, and thus be unaffected by this setting.
As such, there's no point in setting it to a particularly low or creative level. Simply set it to 80% of whatever autovacuum_max_freeze_age is set to (recommended: 800 million).
By now it should have occurred to you that Postgres has a serious problem with vacuum freeze behavior on large tables. It's the unfortunate bad side-effect of optimizations in several other areas. You can tweak the settings as I recommended, but you'll still be in danger of having a wraparound vacuum kick in and saturate your IO at some unexpected time. So what can you do to behave better and proactively freeze tables before they get to that point?
Well, you have one piece of information PostgreSQL doesn't. You know when the slow periods in your application usage are. Try following this "flexible freeze" program in a cron script:
During expected "slow periods", run the query from Part I to get a list of tables, and then:
Or, you can use our Flexible Freeze python script.
Obviously the suggestions in this article are workarounds. Where PostgreSQL really needs to go is to find some way to avoid needing to vacuum old, cold data ever. The obstacle to doing this is that nobody has figured out how.
One of the first methods suggested was to have an 8-byte XID, which would postpone wraparound by a billion cycles. However, since there are two XIDs on every row header in the database, this would dramatically increase database sizes for many users, especially the users who need more XIDs in the first place. It would also increase memory requirements for a lot of Postgres operations. A second method I suggested during the 9.2 cycle was to have an "XID cycle" counter in the page header of each data page. This would have the advantage of not increasing the size of rows. However, it would have the drawback of failing in cases where there were rows from more than one XID cycle in the page.
Further, both of these methods hit a major performance problem: the CLOG. PostgreSQL's CLOG tracks which XIDs committed and which rolledback, and thus which rows are valid and which are orphaned. Currently, checking the CLOG is quite rapid because it's kept small. Any patch which involves increasing the size of XIDs or keeping more of them will cause the CLOG to bloat by multiples, substantially affecting response times on transaction-processing workloads. That's unacceptable. We've discussed radically restructuring the CLOG, but that's a major enough change that it would need to come with other benefits than just avoiding freeze.
An alternate approach to never freezing old data at all would be to develop some kind of daemon which did background freezes of chunks of large tables. This daemon would need to be able to tell when the system was "busy" and stop work. It would also need to be able to track what old data pages it had visited and which it hadn't, and which ones need to be revisited because they've been dirtied.
Anyone wanting to take up the above -- either by coding or by funding -- let me know! Until then, happy freezing!
vacuum_freeze_table_age
To understand the purpose of this parameter, you have to understand how vacuum has worked since version 8.4. Vacuum no longer usually scans the entire table; instead, it has a bitmap (called the visibility_map) which tells it which data pages in the table have reclaimable space, so that it scans only the portion of the table which is "dirty". This is a considerable optimization for large tables, where only 20% of the rows might have been updated or deleted since the last vacuum cycle.
This does, however, introduce a problem. Since old, untouched data pages aren't being vacuumed (since they don't have any reclaimable rows anymore), they are not getting frozen regardless of the setting of vacuum_freeze_min_age. This makes it much more likely you'll hit an eventual wraparound vac when you least expect it.
The idea of vacuum_freeze_table_age is to compliment autovacuum_max_freeze_age by telling the database: "If it's almost vacuum_freeze time for this table, and you were vacuuming the table anyway, then scan the whole table and to freeze out tuples." In theory, this should allow you to hold off wraparound vacuum by running a regular, cancellable vacuum instead. In practice, though, that's a dubious benefit for large tables, since either way you get a full-table scan and a vacuum which runs for a long time. Small, heavily updated tables will tend to get most of their pages vacuumed most of the time anyway, and thus be unaffected by this setting.
As such, there's no point in setting it to a particularly low or creative level. Simply set it to 80% of whatever autovacuum_max_freeze_age is set to (recommended: 800 million).
Flexible Freeze
By now it should have occurred to you that Postgres has a serious problem with vacuum freeze behavior on large tables. It's the unfortunate bad side-effect of optimizations in several other areas. You can tweak the settings as I recommended, but you'll still be in danger of having a wraparound vacuum kick in and saturate your IO at some unexpected time. So what can you do to behave better and proactively freeze tables before they get to that point?
Well, you have one piece of information PostgreSQL doesn't. You know when the slow periods in your application usage are. Try following this "flexible freeze" program in a cron script:
During expected "slow periods", run the query from Part I to get a list of tables, and then:
- Set PostgreSQL to "soft vacuum" with a high vacuum_cost_delay (such as 50ms).
- Set PostgreSQL to aggressively freeze tuples, with vacuum_freeze_table_age at 50% of autovacuum_freeze_max_age, and vacuum_freeze_min_age set to 10% of its usual value.
- Until the slow period is almost over, loop through the list, vacuuming each table.
- This will help you avoid wraparound vacuum when you least expect it.
Or, you can use our Flexible Freeze python script.
Further Work
Obviously the suggestions in this article are workarounds. Where PostgreSQL really needs to go is to find some way to avoid needing to vacuum old, cold data ever. The obstacle to doing this is that nobody has figured out how.
One of the first methods suggested was to have an 8-byte XID, which would postpone wraparound by a billion cycles. However, since there are two XIDs on every row header in the database, this would dramatically increase database sizes for many users, especially the users who need more XIDs in the first place. It would also increase memory requirements for a lot of Postgres operations. A second method I suggested during the 9.2 cycle was to have an "XID cycle" counter in the page header of each data page. This would have the advantage of not increasing the size of rows. However, it would have the drawback of failing in cases where there were rows from more than one XID cycle in the page.
Further, both of these methods hit a major performance problem: the CLOG. PostgreSQL's CLOG tracks which XIDs committed and which rolledback, and thus which rows are valid and which are orphaned. Currently, checking the CLOG is quite rapid because it's kept small. Any patch which involves increasing the size of XIDs or keeping more of them will cause the CLOG to bloat by multiples, substantially affecting response times on transaction-processing workloads. That's unacceptable. We've discussed radically restructuring the CLOG, but that's a major enough change that it would need to come with other benefits than just avoiding freeze.
An alternate approach to never freezing old data at all would be to develop some kind of daemon which did background freezes of chunks of large tables. This daemon would need to be able to tell when the system was "busy" and stop work. It would also need to be able to track what old data pages it had visited and which it hadn't, and which ones need to be revisited because they've been dirtied.
Anyone wanting to take up the above -- either by coding or by funding -- let me know! Until then, happy freezing!
Monday, November 12, 2012
PostGIS 2.0 in Berkeley Tommorrow
Brian Hamlin will be presenting PostGIS 2.0 at Soda Hall in Berkeley tommorrow. If you can't make it, it will probably be live on video (not sure about wireless quality).
Tuesday, October 30, 2012
pgConf.EU 2012 report
I just got back from my first European PostgreSQL conference since 2009. If you missed it, you should be sorry you did. Me, I mostly went because it was in Prague this year ... my sweetie really wanted to go, so off we went.
Nearly 300 people attended from what I heard, and the rooms were certainly full enough to support that estimate. I got to see a lot of people I don't see at pgCon. Highlights of the conference for me were:
Thanks to the whole pgConf organizing crew for a great time, and for helping with getting the students to the conference.
Not sure I'll make it next year; October is often a busy time for other conferences, and I think it's more important for me to speak at non-Postgres conferences than to our existing community. However, if they hold it in Barcelona, I might have to find a way to go.
Nearly 300 people attended from what I heard, and the rooms were certainly full enough to support that estimate. I got to see a lot of people I don't see at pgCon. Highlights of the conference for me were:
- Hallway Track: getting to hash stuff out with Joe Celko, Peter Geoghegan, Rob Napier, Hannu Krosing, and Laurenz Albe, whom I never see otherwise. Not all at the same time, of course!
- Playing Chess in the DB: Gianni Colli's pgChess extension and supporting presentation were excellent. If you want evidence that you can do anything with Postgres, load up pgChess!
- GSOC Students: Atri Sharma, Alexander Korotkov, and Qi Huang all came to the conference, got to meet the community, and presented on their projects from Google Summer of Code.
- Lightning Talks: I love LTs, of course. Harald did a particularly good job running them.
- Boat Party: Heroku rented a party boat to cruise up the Vltava River while we drank lots of Budvar and talked databases.
Thanks to the whole pgConf organizing crew for a great time, and for helping with getting the students to the conference.
Not sure I'll make it next year; October is often a busy time for other conferences, and I think it's more important for me to speak at non-Postgres conferences than to our existing community. However, if they hold it in Barcelona, I might have to find a way to go.
Saturday, October 13, 2012
Freezing Your Tuples Off, Part 2
Continued from Part 1.
The vacuum_freeze_min_age setting determines the youngest XID which will be changed to FrozenXID on data pages which are being vacuumed anyway. The advantage of setting it low is that far more XIDs will already be frozen when the data page is finally evicted from memory. This is ideal from a maintenance perspective, as the data page may never need to be read from disk for freezing. The disadvantage of setting it too low is additional time and CPU used during vacuum, especially if the page ends up being vacuumed several times before it's written out.
The other disadvantage of setting it low, according to the pgsql-hackers list, is that you will have less data for reconstructive database forensics if your database gets corrupted. However, given PostgreSQL's very low incidence of corruption bugs, this is not a serious consideration when balanced against the very high cost of a vacuum freeze on a really large table.
Therefore, we want the setting to be low, but not so low that XIDs used a few minutes ago are expiring. This is where things get difficult and not auto-tunable in current Postgres. You really want XIDs to freeze after a few hours, or maybe a day if your application makes use of long-running transactions frequently. But Postgres has no single-step way to determine how many XIDs you use per hour on average.
The best way is to monitor this yourself. In recent versions of PostgreSQL, you can get this from pg_stat_database, which has the counters xact_commit and xact_rollback. If these are part of a monitoring scheme you already have in place (such as Nagios/Cacti, Ganglia or Munin), then you can look at the transaction rates using those tools. If not, you need to follow these three steps:
1. Run this query, and write down the result:
SELECT txid_current();
2. Wait three or four hours (or a day, if you use long-running transactions)
3. Run the query again.
4. Subtract the number from the first query run from the second query run. If the 2nd number is lower, then you've wrapped around zero, and should try again.
5. Round up to the nearest multiple of 10.
So, as an example:
josh=# select txid_current();
txid_current
--------------
1000811
... wait four hours ...
postgres=# select txid_current();
sum
---------
2062747
So my transaction burn rate is 1,061,936 for four hours, which I round to 1,000,000. I then set vacuum_freeze_min_age to 1000000. The approximate burn rate, 250,000 per hour, is also a handy figure to keep to figure out when XID wraparound will happen next (in about 1 year, if I was starting from XID 3).
Without doing the above, it's fairly hard to estimate a reasonable level, given that XID burn rate depends not only on the amount of write activity you're doing, but how you're grouping the writes into transactions. For example, in a data collection database, if you're doing each imported fact as a separate standalone INSERT, you could be burning a million XIDs per hour, but if you're batching them in batches of a thousand rows, that cuts you down to 1000 XIDs per hour. That being said, if you really don't have time to check, here's my rules-of-thumb settings for vacuum_freeze_min_age:
You'll notice that all of these are lower than the default which ships in postgresql.conf. That default, 100 million, is overly conservative, and means that preemtive freezing almost never happens on databases which run with the defaults. Also, the default of 100m is half of 200m, the default for autovacuum_freeze_max_age, meaning that even after you've completely vacuumed an entire table, you're left with many XIDs which are 50% of freeze_max_age old. This causes more wraparound vacuums than are necessary.
Also, to some degree, this isn't worth worrying about below 500 writes/minute, given that it takes 8 years to reach XID wraparound at that rate. Few PostgreSQL installations go 8 years without a dump/reload.
To be continued in Part 3 ...
NOTE: The above blog entry was updated in April 2015 to reflect and improved method suggested by Noah below.
vacuum_freeze_min_age
The vacuum_freeze_min_age setting determines the youngest XID which will be changed to FrozenXID on data pages which are being vacuumed anyway. The advantage of setting it low is that far more XIDs will already be frozen when the data page is finally evicted from memory. This is ideal from a maintenance perspective, as the data page may never need to be read from disk for freezing. The disadvantage of setting it too low is additional time and CPU used during vacuum, especially if the page ends up being vacuumed several times before it's written out.
The other disadvantage of setting it low, according to the pgsql-hackers list, is that you will have less data for reconstructive database forensics if your database gets corrupted. However, given PostgreSQL's very low incidence of corruption bugs, this is not a serious consideration when balanced against the very high cost of a vacuum freeze on a really large table.
Therefore, we want the setting to be low, but not so low that XIDs used a few minutes ago are expiring. This is where things get difficult and not auto-tunable in current Postgres. You really want XIDs to freeze after a few hours, or maybe a day if your application makes use of long-running transactions frequently. But Postgres has no single-step way to determine how many XIDs you use per hour on average.
The best way is to monitor this yourself. In recent versions of PostgreSQL, you can get this from pg_stat_database, which has the counters xact_commit and xact_rollback. If these are part of a monitoring scheme you already have in place (such as Nagios/Cacti, Ganglia or Munin), then you can look at the transaction rates using those tools. If not, you need to follow these three steps:
1. Run this query, and write down the result:
SELECT txid_current();
2. Wait three or four hours (or a day, if you use long-running transactions)
3. Run the query again.
4. Subtract the number from the first query run from the second query run. If the 2nd number is lower, then you've wrapped around zero, and should try again.
5. Round up to the nearest multiple of 10.
So, as an example:
josh=# select txid_current();
txid_current
--------------
1000811
... wait four hours ...
postgres=# select txid_current();
sum
---------
2062747
So my transaction burn rate is 1,061,936 for four hours, which I round to 1,000,000. I then set vacuum_freeze_min_age to 1000000. The approximate burn rate, 250,000 per hour, is also a handy figure to keep to figure out when XID wraparound will happen next (in about 1 year, if I was starting from XID 3).
Without doing the above, it's fairly hard to estimate a reasonable level, given that XID burn rate depends not only on the amount of write activity you're doing, but how you're grouping the writes into transactions. For example, in a data collection database, if you're doing each imported fact as a separate standalone INSERT, you could be burning a million XIDs per hour, but if you're batching them in batches of a thousand rows, that cuts you down to 1000 XIDs per hour. That being said, if you really don't have time to check, here's my rules-of-thumb settings for vacuum_freeze_min_age:
- Low write activity (100 per minute or less): 50000
- Moderate write activity (100-500 per minute): 200000
- High write activity (500 to 4000 per minute): 1000000
- Very high write activity (higher than 4000 per minute): 10000000
You'll notice that all of these are lower than the default which ships in postgresql.conf. That default, 100 million, is overly conservative, and means that preemtive freezing almost never happens on databases which run with the defaults. Also, the default of 100m is half of 200m, the default for autovacuum_freeze_max_age, meaning that even after you've completely vacuumed an entire table, you're left with many XIDs which are 50% of freeze_max_age old. This causes more wraparound vacuums than are necessary.
Also, to some degree, this isn't worth worrying about below 500 writes/minute, given that it takes 8 years to reach XID wraparound at that rate. Few PostgreSQL installations go 8 years without a dump/reload.
To be continued in Part 3 ...
NOTE: The above blog entry was updated in April 2015 to reflect and improved method suggested by Noah below.
Subscribe to:
Posts (Atom)