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

Full article...