New Falcon Performance Diagnostics
At next week’s MySQL User’s Conference, we’ll be having a number of in-depth sessions on the new MySQL transactional storage engine named Falcon. Jim Starkey, the creator of Falcon, will be leading a session on Falcon internals, another session on Falcon Concurrency Control, and a Falcon BOF.
One new delivery from the Falcon team are new performance diagnostic objects that help DBAs and developers better troubleshoot and tune a Falcon database. One of the requests I get constantly in the field is to provide more performance stats that help MySQL less of a “black box”. The Falcon team has done a nice job in answering this call and has recently provided a number of new diagnostic views into what the Falcon engine is doing. Although still in alpha and likely to change somewhat, the new performance objects (located in the INFORMATION_SCHEMA database) include the following:
+---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | FALCON_RECORD_CACHE_SUMMARY | | FALCON_SYSTEM_MEMORY_DETAIL | | FALCON_SYSTEM_MEMORY_SUMMARY | | FALCON_SYNCOBJECTS | | FALCON_RECORD_CACHE_DETAIL | | FALCON_TRANSACTION_SUMMARY | | FALCON_DATABASE_IO | | FALCON_TRANSACTIONS | | FALCON_SERIAL_LOG | +---------------------------------------+
What can you do with these new objects? You can get an idea of how well/poorly Falcon is using memory, get insight into the efficiency of its serial log, and much more. For example, you can see I/O per database:
mysql> select * from information_schema.falcon_database_io; +----------+-----------+---------+-------+--------+---------+-------+ | DATABASE | PAGE_SIZE | BUFFERS | READS | WRITES | FETCHES | FAKES | +----------+-----------+---------+-------+--------+---------+-------+ | RMS | 4096 | 2560 | 0 | 109 | 98687 | 615 | | GIMF | 4096 | 2560 | 565 | 28 | 56870 | 3 | +----------+-----------+---------+-------+--------+---------+-------+
And do something that many of you have asked for, which is understand who is blocking who in a transactional situation, how long the blocked users have been waiting, and the statement causing the blocking lock situation:
mysql> select a.id thread, a.user,b.id txn_id,b.database,a.time, b.waiting_for, statement
-> from information_schema.processlist a, information_schema.falcon_transactions b
-> where a.id = b.thread_id;
+--------+------+--------+----------+------+-------------+--------------------------------+
| thread | user | txn_id | database | time | waiting_for | statement |
+--------+------+--------+----------+------+-------------+--------------------------------+
| 2 | root | 8 | GIMF | 0 | 0 | |
| 3 | root | 9 | GIMF | 76 | 8 | update rms set c1=5 where c1=1 |
+--------+------+--------+----------+------+-------------+--------------------------------+
All good stuff! Another great thing is that we’ve already added these new diagnostics into the MySQL Enterprise Monitoring and Advisory Service, so Enterprise subscribers will have new built-in intelligence for Falcon when it goes GA.
Look for these new objects to appear in the next alpha drop of the Falcon release. And again, be sure to come to the Falcon sessions at the upcoming User Conference!