Percona Live: Data Performance Conference 2016 Logo

April 18-21, 2016

Santa Clara, California

Sleeper Agents - Transactions in Hiding

Sleeper Agents - Transactions in Hiding

 19 April 04:50 PM - 5:15 PM @ Room 210
Experience level: 
25 minutes conference
Operations and Management
Focus Areas


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


Brian Cain's picture

Brian Cain

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.

Share this talk