It seems that everyone loves load testing these days. Problem is that everyone is using their own quick scripts, simple or complex, to drive their tests without the ability for other DBAs to duplicate those tests. Let’s say I write a great test and share my results and graphs on the blog – you want to run the same tests to see how your new DB servers compare in performance: this framework allows you to do that without duplicating any work or writing code. This is a basic release that will get the ball rolling. I’ve included some sample tests in the README file, so give them a try.

This codebase offers a user friendly framework for creating and visualizing MySQL database load test jobs. It is based around Sysbench, which is generally considered the industry standard load test application. The framework allows you to do the following:

• standardize your tests without requiring you to write one-off bash scripts to handle …

So I’ve been doing a fair number of automated load tests these past six months. Primarily with Sysbench, which is a fine, fine tool. First I started using some simple bash based loop controls to automate my overnight testing, but as usually happens with shell scripts they grew unwieldy and I rewrote them in python. Now I have some flexible and easily configurable code for sysbench based MySQL benchmarking to offer the community. I’ve always been a fan of giving back to such a helpful group of people – you’ll never hear me complain about “my time isn’t free”. So, let me know what you want in an ideal testing environment (from a load testing framework automation standpoint) and I’ll integrate it into my existing framework and then release it via the BSD license. The main goal here is to have a standardized modular framework, based on sysbench, that allows anyone to compare their server performance via repeatable tests. It’s fun to see …

As you know, Tomas announced the availability of MySQL 5.6.2 Dev Milestone in his MySQL O'Reilly and Collaborate keynote presentations a few weeks back.  Downloads so far have been great, which reflects the feedback and excitement we picked up on from the attendees of both events, especially at the O'Reilly Community reception on that Wednesday (it was great to so many friends, family, peers there!).

You can read John Russell's excellent recap of the new things in 5.6.2 and new innovations currently available in the MySQL Labs here.  And all of the 5.6 ready docs are available to help you learn how to enable and implement those you might find most interesting.

I am especially excited about our continued use of the …

The announcements at the O'Reilly MySQL and Oracle Collaborate conferences last week mark an exciting milestone in the development of MySQL replication.  The purpose of this blog is to summarize those announcements and provide links to further reading, published directly by the engineers who have been working hard to take MySQL replication to the next level.

A number of significant replication enhancements were released as part of the MySQL 5.5 GA just a few months ago. 
We are always listening to our customers and the community.  And, based on their input, the MySQL engineering team has continued to rapidly evolve replication functionality - enabling new classes of applications to rely on MySQL for their …

Great news was announced today at the Collaborate 11 conference today;  the first development milestone release of MySQL 5.6.2 is now available to the MySQL Community!  This is significant because 5.6.2 builds on the momentum generated by the performance, scalability, InnoDB and Replication improvements that rolled out in version 5.5 just last Dec.  It also marks somewhat of a paradigm shift in how the MySQL Engineering team delivers new, defined sets of stable, development complete features (development "milestones") and cutting edge, currently under development, delivered early and often features via http://labs.mysql.com/.  Both options provide a means for Community users to begin using new versions and, more importantly, new features of MySQL very early on ensuring the final product has undergone the highest degree of testing, validation and hardening before we deem it production ready.

 
Some of …

Have you ever looked at your processlist and seen queries in the state “copying to tmp table” and then run an explain on it and noticed that the tmp table is being created on disk? Happens a lot with some servers and some workloads. Of course disk is much slower than RAM so this becomes a slow process and makes queries execute slower than they could if they were allowed to use RAM. So, one way to get this process to speed up (aside from tuning your queries which should be done first) is to create a tmpfs or ram-disk and let MySQL use that for it’s temp-table-on-disk creations. MySQL on Linux defaults to /tmp for the tmpdir location so this will need to be changed.

Here is how you get MySQL to use a 1G size tmpfs. How you size your tmpfs depends how much ram your system has and how much tmpdir space mysql needs for your workload. If you need more tmpdir space than you can make in a tmpfs mount then mysql allows multiple tmpdir locations …

This is a follow up to my previous post titled “MySQL analytics: information_schema polling for table engine percentages”. Here’s an updated query with more output and quicker execution time. What you get: innodb table space utilization percentage, data+index usage total and per innodb/myisam engine, innodb data/index/percentage, myisam data/index/percentages, and overall percentage values. Rather useful for profiling your table engine usage.

Sample output:
innodb_tablespace_utilization_perc: 100
total_size_gb: 26.275011910126
index_size_gb: 2.994891166687
data_size_gb: 23.280120743439
innodb_total_size_gb: 6.751220703125
innodb_data_size_gb: 5.2576751708984
innodb_index_size_gb: 1.4935455322266
myisam_total_size_gb: 19.523791207001
myisam_data_size_gb: 18.02244557254
myisam_index_size_gb: 1.5013456344604
perc_index: 11.3982
perc_data: 88.6018
…

The config files that come with MySQL server are generally not that good. They almost never work well for enterprise server loads and will leave most people wondering why the database needs someone to come fix it. In fact that might be why they roll those configs by default, to keep the consulting services alive and profitable. Personally I have no issues with consulting services, but everyone should start out with good configs to begin with. Well, no worries… here are config files for different system RAM configurations. Just choose the one that fits how much RAM your server has and make sure the cnf file is in place before you install the MySQL server RPM/tar/deb packages. Obviously you’ll want to remove the extension of the filename so it reads “my.cnf” and not “my.cnf_64GB” or the like. These cnfs have default settings in the comments so you know what is changed. There are also several equations listed so you know why I am choosing …

It’s always good to have some equations for reference when you are tuning a MySQL server. How else will you know what to set your buffer sizes to after all? If you have some that I’ve missed… add a comment!

Per-Thread Buffer memory utilization (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections Global Buffer memory utilization innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size Threads and Connections thread_cache miss rate = Threads_created / Connections connection ratio = (max_used_connections*100)/ max_connections threads_per_second = threads_created / uptime Key Buffer key_buffer_free = (key_blocks_unused * key_cache_block_size) / (key_buffer_size * 100) key_cache_miss_rate = key_read_requests / key_reads …

This month is a special month. It’s not because of Valentines day or even the exciting day where we see groundhogs. No, this month is special because I’m have a book contest where you, the reader, get to win something free for doing absolutely nothing more than posting a comment saying that you want one of the several books I have available in the contest.

So without getting into boring details I’ll keep this short. I’ve been reviewing a lot of books lately and I think it’s time to get some books into people’s hands to enjoy themselves. This month the giveaways are all Python oriented.

So, all you have to do is take a look at the following titles and post a comment here saying that you want one of them. At the end of the month two readers will be chosen via a random list sorting python script I’ve whipped up for just this purpose. You will then get an email from the publisher who will send a brand new e-copy of the …