About the Author

Robin Schumacher is MySQL's Director of Product Management and has over 13 years of database experience in DB2, MySQL, Oracle, SQL Server and other database engines. Before joining MySQL, Robin was Vice President of Product Management at Embarcadero Technologies.

We are always looking for interesting articles about MySQL! Have you written something and would like to it published here? Please contact us via feedback form »

A look at Falcon Diagnostic Tables

Performance tuning is one of the top disciplines (if not THE top discipline) that database professionals want to excel at. Being able to take a system that's running sluggish and turn it into one that's running as fast as a scalded dog is a talent that's part art and part science, but whatever the combination necessary to make it happen, there will always be strong demand for folks who are good at it.

When a database performance analyst begins to look at a poorly performing database, there are usually several forms of analysis that they will use:

Bottleneck analysis – this concerns itself with answering the question "what is my database waiting on?" Perhaps it's a case of lock contention or other I/O bottleneck that's causing a database to slow down, but whatever the issue, the end goal is to get things "unclogged" so the database can get back up to speed.
Workload analysis – this type of analysis deals with two things: who is logged on and what are they doing? Here you are looking at user load (active, idle, etc.), physical and logical I/O activity, and the SQL code that is being executed along with how many times various statements are executed. Anyone familiar with database tuning knows it only take one well-timed bad query from hell to send everything down the drain in short order.
Ratio analysis – this is the least useful form of analysis, but it can be somewhat helpful to get some basic rule of thumb measurements. Using various SQL scripts, you can get measurements such as memory vs. disk reads (cache hit ratios) and other like statistics.

If you've been using MySQL for a while, you know that – sadly – MySQL doesn't have as rich a performance management interface as some of the other proprietary databases like Oracle, so doing a thorough diagnostic analysis of a slow-running system can be challenging. Fortunately, new versions of MySQL are introducing more and improved diagnostic objects to help you better troubleshoot MySQL servers.

One of the new performance management enhancements is found in the new Falcon transactional storage engine that was introduced in MySQL 6.0. The Falcon team has designed a set of new Information Schema tables to help you understand how well Falcon is performing and where issues may be developing. Let's take a quick look at some of these new tables and see how you can make use of them when you're working with Falcon.

Falcon Diagnostic Tables Overview

In MySQL 5.0, the Information Schema (IS) was introduced to help MySQL users get metadata on objects, security privileges, and more info that exists on a particular MySQL server. In MySQL 6.0, the Falcon storage engine adds the following new tables to the IS:

mysql> use information_schema
Database changed
mysql> show tables like 'FAL%';
+-------------------------------------+
| Tables_in_information_schema (FAL%) |
+-------------------------------------+
| FALCON_TABLES                       |
| FALCON_RECORD_CACHE_SUMMARY         |
| FALCON_SYSTEM_MEMORY_DETAIL         |
| FALCON_SERIAL_LOG_INFO              |
| FALCON_VERSION                      |
| FALCON_TRANSACTION_SUMMARY          |
| FALCON_DATABASE_IO                  |
| FALCON_SYNCOBJECTS                  |
| FALCON_TRANSACTIONS                 |
| FALCON_RECORD_CACHE_DETAIL          |
| FALCON_SYSTEM_MEMORY_SUMMARY        |
+-------------------------------------+
11 rows in set (0.00 sec)

Some of the new Falcon tables only provide information when the server is in debug mode – these include the FALCON_RECORD_CACHE_SUMMARY, FALCON_RECORD_CACHE_DETAIL, FALCON_SYSTEM_MEMORY_SUMMARY, and FALCON_SYSTEM_MEMORY_DETAIL tables. But the others contain valuable diagnostics that you can access to determine the health of Falcon-related database performance.

The most basic Falcon table is the FALCON_TABLES object, which simply shows the various Falcon objects in a MySQL instance and their database and tablespace assignments:

mysql> select * from information_schema.falcon_tables;
+-------------+--------------------+-----------+-------------+--------------------+
| SCHEMA_NAME | TABLE_NAME         | PARTITION | TABLESPACE  | INTERNAL_NAME      |
+-------------+--------------------+-----------+-------------+--------------------+
| GIMF        | BROKER             |           | FALCON_USER | BROKER             |
| GIMF        | CLIENT             |           | FALCON_USER | CLIENT             |
| GIMF        | CLIENT_TRANSACTION |           | FALCON_USER | CLIENT_TRANSACTION |
| GIMF        | INVESTMENT         |           | FALCON_USER | INVESTMENT         |
| GIMF        | INVESTMENT_TYPE    |           | FALCON_USER | INVESTMENT_TYPE    |
| GIMF        | OFFICE_LOCATION    |           | FALCON_USER | OFFICE_LOCATION    |
+-------------+--------------------+-----------+-------------+--------------------+

The other tables are more performance-related and less concerned with general database metadata.

Looking at Falcon I/O Performance

Suppose we have a Falcon database that resembles the following model (which, by the way, was created in MySQL Workbench, MySQL's new data modeling tool that you can download at: http://dev.mysql.com/downloads/workbench/5.0.html):

As Falcon doesn't support foreign keys yet, ignore the FK relationships you see in the above model. The model/database is a simple design centered around an investment firm. If the MySQL server was just started, that would mean no data has been accessed on the system yet; as a result, the Falcon diagnostic tables would be empty:

mysql> select * from information_schema.falcon_database_io;
Empty set (0.00 sec)

But once we access a Falcon object, then the system contains data we can analyze:

mysql> select count(*) from gimf.client_transaction;
+----------+
| count(*) |
+----------+
|    18675 |
+----------+
1 row in set (0.56 sec)

mysql> select * from information_schema.falcon_database_io;
+------------------+-----------+---------+----------------+--------+---------------+-------+
| TABLESPACE       | PAGE_SIZE | BUFFERS | PHYSICAL_READS | WRITES | LOGICAL_READS | FAKES |
+------------------+-----------+---------+----------------+--------+---------------+-------+
| FALCON_MASTER    |      4096 |    2560 |             51 |      1 |          1041 |     0 |
| FALCON_TEMPORARY |      4096 |    2560 |              1 |      0 |             0 |     1 |
| FALCON_USER      |      4096 |    2560 |            299 |      0 |         56028 |     3 |
+------------------+-----------+---------+----------------+--------+---------------+-------+

Tables such as the FALCON_DATABASE_IO table can be used for both workload and ratio-based analysis. For example, to get an overall cache hit rate for falcon, you could use this SQL:

mysql> select 100 * 1-(sum(physical_reads) / sum(logical_reads)) falcon_hit_rate
    -> from information_schema.falcon_database_io;
+-----------------+
| falcon_hit_rate |
+-----------------+
|         99.9938 |
+-----------------+

So 99+% of the Falcon I/O has been memory-based (logical) vs. physical, which is good by a simple rule of thumb but in no way indicates things are performing well overall. You can also get per tablespace statistics by using this query:

mysql> select tablespace,
    -> 100 * 1-(sum(physical_reads) /
    -> sum(if(logical_reads > 1, logical_reads,1))) falcon_hit_rate
    -> from information_schema.falcon_database_io
    -> group by tablespace;
+------------------+-----------------+
| tablespace       | falcon_hit_rate |
+------------------+-----------------+
| FALCON_MASTER    |         99.9510 |
| FALCON_TEMPORARY |         99.0000 |
| FALCON_USER      |         99.9947 |
+------------------+-----------------+

Again, don't confuse high hit rates with being able to nod off on your DBA job. Plenty of systems have been brought to a crawl with excessive logical I/O that wreaks havoc. Instead, you should focus your attention on reducing all forms of I/O – both logical and physical – as well as the number of SQL statement executions as best you can. And don't forget about the MySQL query cache; if used in the right scenarios, it can greatly help reduce the I/O strain on a MySQL server.

If, however, you see great volumes of physical reads, you do have fairly low hit rates, and you've done your research on minimizing overall executions and feel confident about your indexing strategy, then it's a fair bet you want to look into increasing the Falcon record and page cache sizes (falcon_record_memory_max, falcon_page_cache_size).

Another I/O area to review is Falcon log performance. The serial log is used during normal operation as a performance boost for write operations, and after a crash for database recovery. During normal operation, the serial log allows Falcon to write out many data changes at once when a transaction commits, rather than writing out pages scattered in different parts of the database file. When a transaction commits, it creates a log entry saying it is committing, then writes log entries with the final state of every record it modified to the log file in memory. It then flushes the log file to disk. That write makes the transaction durable (the D in ACID).

Falcon has a diagnostic table to view how busy the log has been:

mysql> select * from information_schema.falcon_serial_log_info;
+---------------+--------------+--------+---------+---------+
| DATABASE      | TRANSACTIONS | BLOCKS | WINDOWS | BUFFERS |
+---------------+--------------+--------+---------+---------+
| FALCON_MASTER |            0 |      0 |      34 |      20 |
+---------------+--------------+--------+---------+---------+

You can also check the parameter setting for the Falcon log with this command:

mysql> show global variables like '%falc%ser%buf%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| falcon_serial_log_buffers | 20    |
+---------------------------+-------+

So the diagnostic table tells us that all buffers have been accessed and that no current transactions are open at this time. When you first view the table, there may be a point of confusion since there is both a WINDOWS and BUFFERS column. WINDOWS is the number of windows in to the serial log being tracked by internal Falcon code objects and this number may be higher than the falcon_serial_log_buffers setting. The BUFFERS column shows how many of those code objects actually have 1 MB buffers and the statistic never gets larger than the falcon_serial_log_buffers setting.

When the serial log is opened, all the buffers requested are allocated initially and they are handed out to the internal code objects on an as-needed basis. When they are all in use, they are switched between windows when they are not in use by another. Finally, note that this diagnostic table reports how many internal code objects are active in regards to windows and how many of them have buffers; it does not currently indicate whether those buffers are in use.

If you see that the BUFFERS column never reaches that amount you've set for the falcon_serial_log_buffers parm, then you can likely reduce that value as you could be wasting memory.

Looking at Falcon Transaction Bottlenecks

One key to understating database performance is getting a handle on transactional activity and bottlenecks in a busy system. To help with this, Falcon supplies two tables that help with the analysis of global transaction traffic and detailed transactional work. Let's create a blocking lock situation and see what these tables tell us:

*** first session ***

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update investment_type set investment_type_name = 'hi' 
  -> where investment_type_id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

*** second session ***

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> use gimf
Database changed
mysql> update investment_type set investment_type_name = 'hi2' 
  -> where investment_type_id = 1;

*** back to first session ***

mysql> select a.id thread, a.user,b.id txn_id,a.db,b.waiting_for, substr(statement,1,30)
    -> from   information_schema.processlist a,
    -> information_schema.falcon_transactions b
    -> where  a.id = b.thread_id;
+--------+------+--------+------+-------------+--------------------------------+
| thread | user | txn_id | db   | waiting_for | substr(statement,1,30)         |
+--------+------+--------+------+-------------+--------------------------------+
|      2 | root |      6 | gimf |           0 |                                |
|      3 | root |      7 | gimf |           6 | update investment_type set inv |
+--------+------+--------+------+-------------+--------------------------------+

Falcon helps us easily see what transactions are blocking other work on the system via the FALCON_TRANSACTIONS table, which can be joined to another IS table – the PROCESSLIST table. We can see both connections involved in the block along with the SQL statement being blocked. If we rollback both transactions, we can then look at the global Falcon transaction table and see what's happened on the system:

mysql> select * from information_schema.falcon_transaction_summary;
+---------------+-----------+-------------+--------+----------------+--------------------+
| DATABASE      | COMMITTED | ROLLED_BACK | ACTIVE | PENDING_COMMIT | PENDING_COMPLETION |
+---------------+-----------+-------------+--------+----------------+--------------------+
| FALCON_MASTER |         0 |           2 |      0 |              0 |                  0 |
+---------------+-----------+-------------+--------+----------------+--------------------+

Being able to quickly see transaction bottlenecks, such as the above, that are occurring inside Falcon can be a big help when things suddenly seem to freeze up on a system.

Conclusions

Performance tuning will always be a top priority for database pro's, and being able to separate the wheat from the chaff in terms of knowing what to focus on vs. knowing what to disregard will be a coveted skill in this discipline. With Falcon, you have a number of good, new diagnostic aids to help you when you start your analysis in the area of bottlenecks, workloads, and ratios.

Keep in mind that the new Falcon diagnostic tables are still in beta form right now, so you may encounter bugs and such when using them. The Falcon team would certainly appreciate you logging any bugs you find in our bug system at http://bugs.mysql.com/.

You can download a copy of the current Falcon beta at: http://dev.mysql.com/downloads/mysql/6.0.html (Note: the 6.0 binary may be labeled ‘alpha' but it does contain the Falcon transaction engine beta). Please give Falcon a try and let us know what you think.

As always, thanks for your support of MySQL!