While looking at partitioning I recently made a mistake which I guess can happen to others. Often this is due to not fully reading the documentation or scanning it too quickly and misunderstanding what’s being said.

So this post is to complain about the MySQL partitioning syntax and to warn others who may easily make the same mistake without realising.

First we probably need to ask why we are partitioning a table in the first place. The main reasons for this are I think:

• to improve query performance
• to reduce individual .ibd file sizes for large tables (if using innodb_file_per_table)

In my case I wanted to do both. I had a several tables which store a large number of rows (batches of data) based on an incremental batch number. One of these tables was around 40 GB and had about 500,000,000 rows in it.  When processing data in this table often all the data from a particular batch …

I recently gave a presentation at the New York Effective MySQL Meetup on the new features of, and some of the compelling reasons to upgrade to MySQL 5.5. There are also a number of new MySQL variables that can have a dramatic effect on performance in a highly transactional environment, innodb_buffer_pool_instances and innodb_purge_threads are just two to consider.

For more information on all the new variables, status, reserved words and benchmarks of new features you can Download Presentation Slides.

Historically it was considered that MySQL will generally use only one index per referenced table in a SQL query. In MySQL 5.0 the introduction of merge indexes enabled for certain conditions the possibility to utilize two indexes however this could result in worst performance then creating a better index. In MySQL 5.1 it became possible to control optimization switches with the optimizer_switch system variable.

However in explaining how to utilize the intersection, union and sort union in queries I discovered that MySQL could use three indexes for one given table.

        Extra: Using union(name,intersect(founded,type)); Using where

I was not aware of this.

Many people consider this information in the MySQL Query Execution Plan (QEP) to indicate that the referenced table is using an index. It actually means that ONLY the index is used. This can for larger and more frequent queries provide a significant boost.

In a recent example, adding an index dropped a query from 190ms to 6ms. However adding a better index dropped that 6ms query to 1.2ms. When executed 100s/1000s of times per second these millisecond improvements can have a huge benefit in greater scalability. While people often tune slow running queries, in a well tuned system shaving milliseconds of queries, in this example making 6ms query 80% better is a far greater improvement.

You can get a detailed explanation of how to identify, create and verify covering indexes from my Percona Live presentation Improving performance …

Today was another example of where a correct SQL_MODE saved customer data from being corrupted. By default, MySQL does not enforce data integrity. It allows what is called silent truncations where the result of what you INSERT or UPDATE does not represent truth. NOTE: I see very few customers ever have this correctly configured, those that do have actually listened to my advice.

If you do not read any further, your production MySQL environments should be running with at the bare minimum of SQL_MODE=STRICT_ALL_TABLES however I would also advocate for additional SQL_MODE settings.

For this example, some modified undesirable code attempted to reduce a counter by 1, however because of an UNSIGNED data type and a correctly set SQL_MODE, the application produced an error and data was not corrupted.

This is what should happen with your SQL.

mysql> update stats set loss_count=loss_count - 1 where user_id=42; …

Today we have a dedicated MySQL conference in New York with Percona Live. It is great to see an overflowing room in the opening keynote. With over 20 speakers and 4 dedicated tracks there is a lot of content for attendees.

With all the confusion over conference ownership since the Oracle acquisition I applaud Percona for taking an initiative, first in San Francisco and now here in New York. Also announced today is the next Percona Live in London which is great for the MySQL ecosystem in Europe.

My list of reasons for never using or recommending Amazon’s MySQL RDS service grows every time I experience problems with customers. This was an interesting and still unresolved issue.

ERROR 126 (HY000): Incorrect key file for table '/rdsdbdata/tmp/#sql_5b7_1.MYI'; try to repair it

You may see this is a MyISAM table. The MySQL database is version 5.5, all InnoDB tables and is very small 100MB in total size.
What is happening is that MySQL is generating a temporary table, and this table is being written to disk. I am unable to change the code to improve the query causing this disk I/O.

What I can not understand and have no ability to diagnose is why this error occurs sometimes and generally when the database is under additional system load. With RDS you have no visibility of the server running the production database. While you have SQL access, an API for managing MySQL configuration options (I also add not all …

Last week at the OUG Harmony conference thanks to Dimitri Kravtchuk I learned that setting query_cache_size=0 does not disable and remove locking from the Query Cache. You actually need to also set query_cache_type=0. This appears to been a bug, seen in the presently still open MySQL bugs database entry #38511.

My recommendation to customers now is to set both variables on all existing MySQL versions if you are not using the MySQL Query Cache.

Thanks to the Performance Schema in MySQL 5.5 for uncovering this. More information in Dimitri’s detailed post at MySQL Performance: Using Performance Schema

Details of all MySQL presentations at …

Many organizations consider MySQL as a database because the initial license cost is free (*). Larger organizations that use Oracle and SQL Server also consider implementing MySQL as a means to lower the total cost of software infrastructure due to the initial cost for new software licenses or expensive upgrades due to new hardware.

However free software does not mean that services to support MySQL should be also free or even cheap. Recently a large multi-national customer wanted professional consulting and training for MySQL resources and they seemed shocked that I wanted to charge a reasonable rate for professional services. My MySQL consulting rates are cheaper then industry MySQL peers and also similar skilled resources providing Oracle consulting.

With over 10 years experience in MySQL, many doing consulting and also over 10 years professional experience before MySQL I am more then qualified to provide the best possible …

A week ago Baron wrote a blog post which can only be described as the final nail in the coffin for MMM. At MySQL AB we never used or recommended MMM as a High Availability solution. I never really asked about details about that, but surely one reason was that it is based on using the MySQL replication. At MySQL/Sun we recommended against asynchronous replication as a HA solution so that was the end of it as far as MMM was concerned. Instead we recommended DRBD, shared disk or MySQL Cluster based solutions. Of course, to replicate across continents (geographical redundancy) you will mostly just use asynchronous replication, also MySQL Cluster used the standard MySQL replication for that purpose.

read more