Just a reminder that a new version of the 5.1 beta has been released, so please download the latest and greatest and keep submitting any bugs that you find. Also please shoot me a mail (rschumacher@mysql.com) and let me know how you’re using 5.1 and what features you find most useful. Thanks!
During our last major survey, we had a number of you indicate that you’re running MySQL databases in the 500GB to multi-TB range. We’re now mapping out the plans for upcoming server releases, and we’re especially interested in adding enhancements for very large MySQL DB’s. If you own one of these beasts, please contact me at rschumacher@mysql.com, let me know how big your database(s) are, and what features we should add to make your life easier.
We’re rounding the corner on releasing the first MySQL 6.0 beta, which will have a number of updates to our new Falcon transaction storage engine. One enhancement I wanted to give everyone a sneak-peek on is the addition of user-defined tablespaces for Falcon.
Many of you have requested the ability to define tablespace storage containers that allow you to place data exactly where you want it. Well, now in MySQL 6.0/Falcon you get your wish (below example is on Windows):
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.1-alpha-pb605 MySQL Pushbuild Edition, build 605
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use gimf;
Database changed
mysql> create tablespace gimf_ts
-> add datafile 'c:/dev/gim_ts1.fts'
-> engine=falcon;
Query OK, 0 rows affected (1.17 sec)
mysql> create table t1 (c1 int) engine=falcon tablespace gimf_ts;
Query OK, 0 rows affected (0.16 sec)
mysql> insert into t1 values (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> drop table t1;
Query OK, 0 rows affected (0.05 sec)
mysql> drop tablespace gimf_ts engine=falcon;
Query OK, 0 rows affected (0.00 sec)
No more having to add datafiles to a single tablespace defined in the my.cnf file and stop/restart the server for the change to take affect - with Falcon, you now have dynamic storage management abilities, which is nice.
I’ll write a more in-depth article on Falcon tablespace management once the 6.0 beta is available so be looking for that as well as the 6.0 beta release announcement that will be coming soon!
Just wanted to say thanks to the many of you that responded to my recent MySQL on Windows article that was posted two weeks ago. Without a doubt, this article has caused more feedback than any other piece I’ve written for our Dev/Community zone.
If haven’t read the article yet and you’re using MySQL on Windows now, or you’ve been thinking about it, please check it out and shoot me your thought on MySQL for WIN (what you like, hate, how you’d make it better, etc.)
Thanks!
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!