We've recently seen some great announcements of MySQL Cluster delivering amazing results for both selects and updates. The posts (see related articles below) are full of juicy technical details and proofs, but today I'd like to change the perspective a bit. Let's compare those figures with real-world data and imagine what could be done. Please note that I'm not using any scientific method here, just dreaming about the unlimited opportunities offered by MySQL Cluster today.

Cluster can deliver 1B+ selects per minute with 8 nodes …

New versions of MySQL are always interesting to try out. Often they have features which I may have asked for myself so it’s satisfying to see them eventually appear on a system I use. Often other new features make life easier for the DBA. Finally we hope overall performance will improve and managing the server(s) will be come easier.

So I had a system which needs to make heavy writes, and performance was a problem, even when writing to SSDs. Checkpointing seemed to be the big issue and the ib_logfile size in MySQL 5.5 is limited to 4 GB. That seems a lot, but once MySQL starts to fill these files (and this happens at ~70% of the total I believe),  checkpointing kicks in heavily, and slows things down.  So the main reason for trying out MySQL 5.6 was to see how things performed with larger ib_logfiles. (Yes, MariaDB 5.5 can do this too.)

Things improved a lot for my specific workload which was great news, but one thing …

Any person with half a brain would see from the error messages below that the MySQL server is not operating optimally, or more specifically the MySQL upgrade has not completely successfully and let users can go happily use the website. It amazing me when web hosting providers tell their paying client that an upgrade has been performed yet they did not have the intelligence to actually look at the error log for confirmation. Got a mysql> prompt, it’s all good. One of the first things I check is the error log.

When will people learn the MySQL error log is a valuable resource both for what it contains, and what it should not contain.

120426 17:36:00 [Note] /usr/libexec/mysqld: Shutdown complete

120426 17:36:00 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
120426 17:36:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120426 17:36:00 [Note] Plugin 'FEDERATED' is disabled.
/usr/libexec/mysqld: …

While working with RDS and Google Cloud SQL I have come to realize that excluding the mysql schema from a mysqldump is important. However with many databases, the –all-databases option enables you only to select all or none. There is however an easy solution to exclude one or more databases in mysqldump with this little gem I created.

$ time mysqldump --databases `mysql --skip-column-names -e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','performance_schema','information_schema');" >` >/mysql/backup/rds2.sql

An you can exclude as many schemas as you want.

I checked the mysqldump –help, there was no option in MySQL 5.1, asked a colleague just to be sure I wasn’t wasting my time, and it took all of 2 minutes to create and test a working solution.

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodb             |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> drop schema innodb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './innodb/', errno: 17)

This is an additional schema that is included in an AWS RDS installation. You should not put directories in the MySQL data directory.

PC World has written a post with this title(*) about the upcoming MySQL Connect conference and references the Percona Live conference and an official Percona comment. As this is not syndicated in Planet MySQL I encourage you to read the full article.

This is the MySQL conference to get technical presentations by the many great Oracle/MySQL technical staff who will not be in attendance at Percona Live. There will also be a strong community presence in speaking at Oracle Connect in September. While Oracle was organizing a dedicated MySQL event in April for the community with all vendors including Percona to replace the conference dropped by long term partner O’Reilly (kudos …

Over the last few months, on different systems I have been modifying the ib_log_files and their sizes, increasing them due to the increase in load they have been experiencing. These files are used to contain information which has been committed, but not yet written to the ibdata files or the individual .ibd files if using innodb_file_per_table.  And these files are used on recovery if the server shuts down unexpectedly during recovery processing to get the system back into a consistent state again.

The checkpointing in MySQL is well documented, but I was surprised just how much difference the throughput can change for heavy write applications if the sizes of these files is changed.  It may seem obvious but given how disruptive it can be to shut down a system, change these values and start it up again, this process is not done frequently.  These files help improve performance as changes are written linearly into the file, …

In the yet to be released MySQL 5.6.6 DMR, there has been a change to the restriction of just one TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP syntax. It is now possible for any TIMESTAMP to have either column defintion.

More information at http://dev.mysql.com/doc/refman/5.6/en/news-5-6-5.html

I was again reminded why setting SQL_MODE is so important in any new MySQL environment. While performing benchmark tests on parallel backup features with a common InnoDB tablespace and per file tablespace, I inadvertently missed an important step in the data migration. The result was the subsequent test that performed data population worked without any issues however there was no data in any InnoDB tables.

These are the steps used in the migration of InnoDB tables from a common tablespace model to a per-table tablespace model.

1. Dump all InnoDB tables
2. Drop all InnoDB tables
3. Shutdown MySQL
4. Change the my.cnf to include innodb-file-per-table
5. Remove the InnoDB ibdata1 tablespace file
6. Remove the InnoDB transactional log files
7. Start MySQL
8. Verify the error log
9. Create and load new InnoDB tables

However, step 6 was not …

At the recent leaders summit of world leaders in Oracle, Java and MySQL user communities I gave a presentation on Why Upgrade to MySQL 5.5

This is a more high level overview presentation, specifically designed for Oracle resources with little to no knowledge about MySQL, however it provides a great management approach to the consideration of using the current MySQL GA version.

I have a more detailed technical presentation from last year on Reasons to Use MySQL 5.5.