Sleeper Agents - Transactions in Hiding
Overview: Imagine that your MySQL server is alerting on too many open transactions and the history list is out of control. SHOW PROCESSLIST displays a long line of connections in a sleep state with NULL info. SHOW ENGINE INNODB STATUS shows active transactions, but again there is nothing to determine what is running behind that “Sleep”. What happened within the connection? Was it just an INSERT, UPDATE, or SELECT, or was it a long string of mixed statements waiting on the commit or rollback? If you kill the connection, what might be lost? This session will walk through how to use out of the box performance schema and a few minor variable changes to get a view into what is really going on in a sleeping connection. Format: Slide presentation Target audience: Beginner to intermediate level MySQL DBAs Agenda: Reviewing the basics - processlist, InnoDB engine status, and why information is lacking Performance schema - what features are available to help Open connection or open transaction - how to tell the difference Adding some depth - changing a few settings can give a lot of context Putting it all together - a sample script to review and kill off unwanted transactions
Database Engineer - MySQL SRE, Dropbox
Brian is a Database Engineer MySQL SRE for Dropbox. He has worked with MySQL for the past 5 years supporting multiple client environments as a consultant or FTE. He spends most of his time as an operational DBA automating routine tasks such as instance cloning and master promotions along with enhancing monitoring and alerts to proactively detect upcoming issues. He has a background as a developer, manager, and systems administrator over the past 20+ years. Brian is based out of Seattle, WA.