3 ms·

> long-running transactions don't do that if you value your uptime at all. Long-running transactions are the bane of any Postgres setup, no matter the XID over

by pilif 8y ago

> long-running transactions don't do that if you value your uptime at all. Long-running transactions are the bane of any Postgres setup, no matter the XID overflow. A few weeks ago, one slipped through my monitoring and a cache table we often sequence scan which normally had 10k rows in it had 28M dead rows that could never be removed due to that one transaction. That sequence scan suddenly became a lot more expensive.

mjcl 8y ago
Just out of curiosity, what do you find to be long running? Five minutes, five days? Depends on the volume of other transactions happening simultaneously?
- pilif 8y ago
  As it's the case very often in our business, the answer is: "it depends". It depends on the write load, the read load, and the write patterns. Think about it this way: for the duration of any transaction, Postgres is basically in append-only mode. If you have cache tables that normally get rewritten entirely, no row will be deleted during the duration of any transaction in the whole cluster (so this even affects databases other than the one the transaction is connected to). In our case, we run some heavy import jobs daily which causes transaction lifetimes in hours, depending on the job. This isn't an issue, or rather, it's an issue, you can throw hardware at in order to circumvent it. The transaction I was referring to in my earlier comment had a lifetime in days and nearly brought down the whole system.
- Arnt 8y ago
  Practically speaking: A long-running transaction is one that issues BEGIN, locks some rows, then blocks on other network input, and finally COMMIT. Once a day/week/month that input arrives very slowly and the COMMIT happens minutes or hours after the BEGIN.
evanelias 8y ago
To be fair, MySQL/InnoDB can also hit performance problems in a similar set of conditions. A long-running transaction will prevent old row versions from being purged, which can hurt performance due to accumulation of them. The impact just isn't quite as drastic as in Postgres, since InnoDB effectively stores old row versions out-of-band (i.e. in a separate file).

by pilif 8y ago

mjcl 8y ago

Just out of curiosity, what do you find to be long running? Five minutes, five days? Depends on the volume of other transactions happening simultaneously?

pilif 8y ago
As it's the case very often in our business, the answer is: "it depends". It depends on the write load, the read load, and the write patterns. Think about it this way: for the duration of any transaction, Postgres is basically in append-only mode. If you have cache tables that normally get rewritten entirely, no row will be deleted during the duration of any transaction in the whole cluster (so this even affects databases other than the one the transaction is connected to). In our case, we run some heavy import jobs daily which causes transaction lifetimes in hours, depending on the job. This isn't an issue, or rather, it's an issue, you can throw hardware at in order to circumvent it. The transaction I was referring to in my earlier comment had a lifetime in days and nearly brought down the whole system.
Arnt 8y ago
Practically speaking: A long-running transaction is one that issues BEGIN, locks some rows, then blocks on other network input, and finally COMMIT. Once a day/week/month that input arrives very slowly and the COMMIT happens minutes or hours after the BEGIN.

evanelias 8y ago

To be fair, MySQL/InnoDB can also hit performance problems in a similar set of conditions. A long-running transaction will prevent old row versions from being purged, which can hurt performance due to accumulation of them. The impact just isn't quite as drastic as in Postgres, since InnoDB effectively stores old row versions out-of-band (i.e. in a separate file).