I was wondering if there is a way to tell if I am running out of rollback_segments or if there is contention in this area?
We sometimes have a high number of sessions with some very long running transactions.
InnoDB defaults to 128 rollback segments. Each segment is capable of supporting 1023 active transactions. Unless you have over 100,000 active transactions, this is not an issue
I recommend installing and configuring Percona Monitoring and Management so that you can get a sense of where your contention issue, if any, is truly located.
Hi @matthewb ,
Thanks very much for getting back to me. This information is really good to know. We have some really long running transactions and I was trying to figure out if we might be running out of segments.
We sometimes have transactions running for 00’s or even 000’s of seconds with millions of undo log entries.
If this were causing a problem what other symptoms would we see other than blocking?
With that many rows, you’re probably having issues with the undo logs, not the rollback segments. Look into Percona MySQL’s parallel undo log feature and undo log recycling.
Switching to READ-COMMITTED can also reduce the amount of versions of a page the undo process needs to manage.
Thanks very much @matthewb I will take a look at Percona MySQL’s parallel undo log feature and undo log recycling. I will also read up on undo logs.