FreeBSD 9 seems to have some troubles with MySQL 5.5.20. A customer has moved from MySQL 5.0 on Linux to MySQL 5.5 on FreeBSD 9. He experienced a lot of periodic slow downs on the new, much stronger, system which he has not seen on the old Linux box.

This slow downs were also shown in high CPU system time but we could not see any I/O going on.

When we looked into MySQL we have seen many threads in Opening tables state in the MySQL processlist.

The first idea was to increase table_open_cache to 2048 and later to 4096. This made the Opening tables disappear but then we got a significant amount of threads hanging in Copying to tmp table state in the processlist.

So we suspected that those table are going to disk. But we did not see any I/O (with iostat) and Created_tmp_disk_tables did not change significantly but just …

FreeBSD 9 seems to have some troubles with MySQL 5.5.20. A customer has moved from MySQL 5.0 on Linux to MySQL 5.5 on FreeBSD 9. He experienced a lot of periodic slow downs on the new, much stronger, system which he has not seen on the old Linux box.

This slow downs were also shown in high CPU system time but we could not see any I/O going on.

When we looked into MySQL we have seen many threads in Opening tables state in the MySQL processlist.

The first idea was to increase table_open_cache to 2048 and later to 4096. This made the Opening tables disappear but then we got a significant amount of threads hanging in Copying to tmp table state in the processlist.

So we suspected that those table are going to disk. But we did not see any I/O (with iostat) and Created_tmp_disk_tables did not change significantly but just …

The MySQL Enterprise Backup (MEB) Team is pleased to announce the release of MEB 3.7.1, a maintenance release version that includes bug fixes and enhancements to some of the existing features.

The most important feature introduced in this release is Automatic Incremental Backup. The new  argument syntax for the --incremental-base option is introduced which makes it simpler to perform automatic incremental backups. When the options --incremental & --incremental-base=history:last_backup are combined, the mysqlbackup command  uses the metadata in the mysql.backup_history table to determine the LSN to use as the lower limit of the incremental backup. You no longer need to keep track of the actual LSN (as in the option --start-lsn=LSN) or even the location of the previous backup (as in the option --incremental-base=dir:directory_path)
…

O’Reilly authors get 10 copies of their own books for free, and my copies of the third edition of High Performance MySQL arrived yesterday. Now it’s official! It feels nice to actually hold it in my hand.

A few people have asked me about messages from Amazon saying that their ship date has changed. I don’t know anything about that; maybe Amazon just made a wild guess the first time and now they actually know something more realistic. Or maybe the book is more popular than expected? It’s currently at position #10 in the SQL category on Amazon, which seems pretty good to me. No “DaVinci Code” to be sure, but not bad for a technical book.

Further Reading:

• High …

On Monday, I took a break from planning for the upcoming Percona Live MySQL Conference (where we have a session, lightning talk, booth, and other misc activities planned) to go attend the UK-Massachusetts Innovation Economies Conference at the MIT Media Lab. The event featured Gov. Deval Patrick, MIT Media Lab Director Joi Ito, industry experts such as Sheila Marcelo (founder of Care.com), and …

At #POSSCON and looking for MySQL SWAG? Find me in Lexington A at 10AM where I am presenting MySQL 5.6 and 20 MySQL Tips in 20 Minutes.

Come and get 'em -- MySQL thumb drives, stickers, and now buttons!

…

I  gave a lightning talk on MySQL Innodb and how realestate.com.au configures their databases to the DevOPS Melbourne meetup last night.

My slides can be found here.

Simply some take aways are

• Use Innodb for everything (unless another engine is appropriate)
• Having the working set in bufferpool makes MySQL Innodb fast by eliminating those foreground randrom reads of tablespace
• Use a RAID controller with BBU and write back cache for transactional log performance

Thanks To Evan Bottcher for owning and organising the meetup! 

Join us for our High Availability Solutions for MySQL seminar in Helsinki on Thursday May 10th 2012 to better understand how to achieve High Availability with MySQL.

During this free Oracle seminar we will review the various options and technologies at your disposal to implement highly available & highly scalable MySQL infrastructures, as well as best practices in terms of architectures.

We will examine the new High Availability features in MySQL 5.5, MySQL Replication, MySQL Cluster and more, including the upcoming Oracle VM Template for MySQL Enterprise Edition as well as what’s new in the development releases MySQL 5.6 and MySQL Cluster 7.2.

Read more and register here

I have an internal project that is generating a materialized view of some pretty important data, using 96 application server cores against a 12 core database with 192g of memory and a buttload of SSD, good for about 250 MB/sec peak write rate in synthetic file creation.

The project was underperforming, even with MySQL 5.5-current. We never had more than 15mb/s sustained write rate, and never more than 24G of dirty pages in innodb. Looking at the appropriate (self-made) merlin graphs showed redo log contention and excessive checkpointing.

Install MySQL 5.6.4-m7, which can do large redo logs. A quick back-of-the-napkin calculation shows that around 32GB of redo log are just fine for our worklog, most likely. So I configure 2 ib_logfile of 16000M each, and we turn on the load.

The result is a write rate of 100mb/sec sustained, until the redo log reaches about 24G in size. Then queries stall, the internal …

One of the most important features of InnoDB is the row level locking. This feature provides better concurrency under heavy write load but needs additional precautions to avoid phantom reads and to get a consistent Statement based replication. To accomplish that, row level locking databases also acquire gap locks.

What is a Phantom Read

A Phantom Read happens when in a running transaction, two identical statements get different values, because some other transaction has modified the table’s rows. For example:


transaction1> START TRANSACTION;
transaction1> SELECT * FROM t WHERE i > 20 FOR UPDATE;
+------+
| i |
+------+
| 21 |
| 25 |
| 30 |
+------+


transaction2> START TRANSACTION;
transaction2> INSERT INTO t VALUES(26);
transaction2> COMMIT; …