Robin’s Blog

You would think I’d get very use to seeing the power of the Web and open source in action working at MySQL. However, recently I got another dose of “wow” on the power and speed of both.

As a project for a Master’s program I’m in, I created a new web site and organization. I was amazed at how quickly I got everything out on the Web. I’m not a “host it yourself” kind of guy, so I picked a highly rated web hosting outfit and after filling out a single form and giving them an obscenely small amount of money, my domain was registered and I upload my entire site in under 10 minutes.

As part of this same project, I needed to create Podcasts, so I downloaded Audacity, which is a FOSS audio mixing product. Before long, I had professionally sounding Podcasts which surprised even my friends in terms of how good they sound (all I used was Audacity and a $40 mic headset). One friend commented that a family member had recently gotten into broadcasting and spent over $5,000 on audio mixing software and mics, and his stuff sounded the same as mine. Reminds me of how MySQL packs a punch in the face of much higher-priced DBMS’s…

Just a quick note to let everyone know that the new MySQL 6.0 alpha is now available for download. At last week’s MySQL User’s Conference, all the Falcon sessions were packed so there is definitely a lot of interest about our new transactional storage engine. Do note the version number change - previous alpha’s were labeled 5.2, but we’ve now made the decision to move to 6.0 for the Falcon release of the MySQL Server.

The latest alpha contains bug fixes, performance improvements, and the first cut of the new diagnostic/performance views (contained inside the INFORMATION_SCHEMA database) that helps DBA’s get insight into how well Falcon is performing and the resources it’s using:

mysql> show tables like 'FAL%';
+-------------------------------------+
| Tables_in_information_schema (FAL%) |
+-------------------------------------+
| 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                   |
+-------------------------------------+
9 rows in set (0.00 sec)

Download the new 6.0 alpha today and let me know what you think about the Falcon engine and what other performance metrics you’d like to see exposed.

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 [email protected] 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.

I used to wear glasses (was nearsighted), but through some natural processes that I won’t go into here, I actually reversed my nearsightedness and now see clearly without the need for glasses. Having clear vision is a great thing, both for individuals and for companies.

One of the terrific aspects about MySQL is that the vision for the database is established with the help of the very active and innovative community of MySQL users who are daily pushing and extending the MySQL database server to do new and exciting things. We are in the process now of crafting the future vision of the server, and that process would be incomplete without your input.

Where would you like to see MySQL go in the coming years? What new areas would you like to see MySQL address? What does your business need MySQL to do that it currently doesn’t? We’re not talking about detailed feature requests here, but more of a “big picture” type of thing.

Please think about these things and shoot me a mail at [email protected] so we can include your feedback and input into what we come up with. I’ll make sure and let you know what the end result is.

Thanks for your support and see you at the upcoming Users Conference!