Robin Schumacher

In case you didn’t know it, the demand is extremely high right now for MySQL DBA’s. As MySQL becomes more popular and begins to manage more critical applications for modern businesses, the demand for solid MySQL DBA expertise will only continue to grow.

To make sure we’re doing all the right things at MySQL to make your job as a MySQL DBA (or as someone who administers MySQL databases) as easy as possible, we’ve created a *very* brief survey that asks you how much time you spend across various activities (e.g. managing replication, performance tuning, etc.) Please take a few minutes and fill out the new survey - it is much appreciated.

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!

If you’re coming to the MySQL User’s Conference next week, be sure to attend the sessions on backup and recovery, especially the session on the MySQL backup roadmap and the Online Backup BOF that will be hosted by the members of the MySQL Backup team.

I got a chance this week to kick the tires of our new online backup that the team has been working on, and I’m excited about the things I see. For a quick test, I did a backup of a 9 million row archive table in one session while I fired off several inserts into that same table in another session:

*** first session - running backup ***

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  8964018 |
+----------+
mysql> backup database bkp to 'bkp.txt';
+---------------------------------------+
| Backup Summary                        |
+---------------------------------------+
| Backed up   1  table in database bkp. |
|  header     =      754 bytes          |
|  data       = 123968768 bytes         |
|               --------------          |
|  total        123969522 bytes         |
+---------------------------------------+
6 rows in set (4.78 sec)
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  8964021 |
+----------+

*** second session running inserts into same table ***

mysql> insert into t1 values (1,1,1,'h',1,1,'a',now(),now(),'a',1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (1,1,1,'h',1,1,'a',now(),now(),'a',1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (1,1,1,'h',1,1,'a',now(),now(),'a',1,1);
Query OK, 1 row affected (0.00 sec)

Not bad! Again, make sure you stop by the backup sessions, check out the demos, and provide the team with lots of feedback. See you there!

I posted a note last week about the new beta of MySQL 5.1 being released (5.1.17). One thing that we believe needs a special call out is the fact that prepared statements can now work with the query cache. Observe:

Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.17-beta-community-nt-debug MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show global variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 8388608 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.02 sec)

mysql> show global status like '%qc%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 8379648 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 0       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

mysql> prepare tst from "select broker_last_name from broker where office_location_id > 1";
Query OK, 0 rows affected (0.03 sec)
Statement prepared

mysql> execute tst;
+------------------+
| broker_last_name |
+------------------+
| MCMILLIAN        |
| MONSOUR          |
| FRAZIER          |
| HIGHT            |
| KAELIN           |
| MCMANUS          |
| PERKINS          |
| SANDERS          |
| SCHWARTZ         |
| SCHULTZ          |
| JACKSON          |
| FORD             |
| CARRIER          |
| BONNER           |
| BOYCE            |
| LEE              |
| BRADY            |
| HARBSMEIR        |
+------------------+
18 rows in set (0.00 sec)

mysql> execute tst;
+------------------+
| broker_last_name |
+------------------+
| MCMILLIAN        |
| MONSOUR          |
| FRAZIER          |
| HIGHT            |
| KAELIN           |
| MCMANUS          |
| PERKINS          |
| SANDERS          |
| SCHWARTZ         |
| SCHULTZ          |
| JACKSON          |
| FORD             |
| CARRIER          |
| BONNER           |
| BOYCE            |
| LEE              |
| BRADY            |
| HARBSMEIR        |
+------------------+
18 rows in set (0.00 sec)

mysql> show global status like '%qc%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 8378112 |
| Qcache_hits             | 1       |
| Qcache_inserts          | 1       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 0       |
| Qcache_queries_in_cache | 1       |
| Qcache_total_blocks     | 4       |
+-------------------------+---------+
8 rows in set (0.00 sec)

Very nice! Of course, be sure to check the 5.1 manual for the restrictions that do apply. Enjoy!

The new 5.1.17 Beta of the MySQL Server has just hit the streets so make sure you visit the 5.1 download page and get your updated copy. Version 5.1.17 has some interesting additions, including a new DEFINER clause that many of you asked for in the 5.1 event/job scheduler, and prepared statements are now supported in the MySQL query cache, although make sure you read the 5.1 reference manual to understand the various conditions under which they work.

If you haven’t done so already, download the 5.1 technical whitepaper, which shows how to use all the great new functionality in MySQL 5.1. Also shoot me your feedback on how you’re using 5.1 when you get a chance.

Thanks for supporting MySQL!

Many of you who use MySQL replication have requested that we do more to assist you in monitoring and troubleshooting issues with various replicaton topologies. Right now, we’re in the process of designing replication monitoring enhancements for our current Monitoring and Advisory Service so I thought I’d request your input on what you’d like to see.

The most common questions I hear that come up with respect to replication monitoring are these:

- Are there any global replication issues in my monitored replication topologies?
- Do I have any down master servers?
- Do I have any down slave servers?
- Are my SQL and I/O slave threads running on my slaves?
- Is the replication latency between any master and slave too high?
- What are the top ‘N’ worst performing (highest latency) slaves?
- Have any slaves experienced a replication-specific error?
- What are the last “N” errors in a master or slave error log?
- Are my binary logs using too much space on my master?
- Are there too many binary log files on my master?
- What is the current replication configuration of a selected master or slave server?

What other answers would help you better monitor and maintain your MySQL replication setups? Shoot me a mail at rschumacher@mysql.com and let me know.

I just finished working with our great dev team (thanks Chad and Giuseppe!) on working out a few minor kinks with the new SQL Profiler that was introduced in the 5.0.37 version of the MySQL Community Server. Overall, the SQL Profiler is a great new diagnostic aid that helps you understand exactly where your queries are spending their time during processing and execution. It’s also a great example of the MySQL community in action as the Profiler was originally developed outside of MySQL by Jeremy Cole of Proven Scaling.

I just finished a new article on the Profiler that demonstrates how to use it for troubleshooting problem SQL queries. Check it out when you can and let me know what enhancements you’d like to see to the Profiler that would make it even better.