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 …

I’ve been playing around with some quick system automation scripts that are handy to use when you don’t want / need to setup a chef or puppet action. I like to keep all of my hostnames and login details in a MySQL database (a cmdb actually) but for this example we’ll just use a couple of nested lists. This script executes commands in parallel across the hosts you choose in the menu system via the “pdsh” command, so make sure you have that installed before running. Alternately you can change the command call to use ssh instead of pdsh for a serialized execution, but that’s not as fun or fast. With some customizations here and there you can expand this to operate parallelized jobs for simplifying daily work in database administration, usage reporting, log file parsing, or other system automation as you see fit. Here’s the code. Comments welcome as always!

#!/usr/bin/env python
## NAME: menu_parallel_execution.py
## DATE: …

…with a bit of strategic thinking

They come back, every now and then. Subqueries are far from being perfect at MySQL and they can give you some serious headaches.

Skilled MySQL developers know it better. They avoid subqueries as much as they can. It is not that subqueries do not work, it is just that the optimizer sometimes is, well, “not that optimised”.

So you may stay away from subqueries with some good SQL review. But what happens when the subquery is automatically generated by a script or a tool? If you can change the statement, I’m afraid you need to find some serious workarounds that vary case by case.

Here is an example that I found few weeks ago when I visited one of our customers.

Our customer used Magento for its site. Magento used a couple of queries that I will report here as sales and sales_items, …

The MySQL performance team in Oracle has recently completed a series of benchmarks comparing Read / Write and Read-Only performance of MySQL 5.5 with the InnoDB and MyISAM storage engines.

Compared to MyISAM, InnoDB delivered 35x higher throughput on the Read / Write test and 5x higher throughput on the Read-Only test, with 90% scalability across 36 CPU cores.

A full analysis of results and MySQL configuration parameters are documented in a new whitepaper


In addition to the benchmark, the new whitepaper, also includes:
- A discussion of the use-cases for each storage engine
- Best practices for users considering the migration of existing applications from MyISAM to …

Packt Publishing recently sent me a copy of MySQL for Python to review and after reading through the book I must say that I’m rather impressed at the variety of topics that the book covers.

It starts off with the basics of setting up MySQL for your testing/development needs by going over several of the common installation and configuration methods. After that it’s a quick intro for connection methods and simple error reporting for connections. The author gives a quick intro to CRUD and how it relates to databases and python before heading into the common tasks of simple queries. I was surprised to see some database profiling discussion; which is rather handy for a new coder or a person new to MySQL. Once the basics of …

It is my pleasure to announce that MySQL 5.5 is now GA and ready for production deployment.  You can read Oracle's official press release here.

I am excited about 5.5 because of the performance and scalability gains, new replication enhancements and overall improved technical efficiencies.  Congratulations and a sincere "Thanks!" go out to the entire MySQL Community and product engineering teams for making 5.5 the best release of MySQL to date.

Please join us for today's MySQL Technology …

In my continuing blog series on MySQL 5.5 features (see performance/scale and replication entries) today I covering some of the new InnoDB manageability and efficiency options.  5.5, with the newly re-architected InnoDB, provides better user control over internal InnoDB settings so things like performance, scale and storage can easily be monitored, tuned and optimized for specific use cases and application loads.

Along these lines, some of the key advances and features available in MySQL 5.5 and InnoDB are:
 

• Faster Index Creation - MySQL 5.5 can now add or drop indexes without copying the underlying data of the entire target table.  This improves the …