Most of the Nagios plugins included with the Percona Monitoring Plugins are purpose-built for specific scenarios that I found in my research to be frequent, severe, or subtle causes of problems in MySQL systems. They are intentionally not generic because I wanted to focus on doing one thing with each plugin, and doing it excellently. This makes the plugins easier to set up and use, and I believe it should help avoid some of the common problems such as spammy alerts.

There is one “generic, flexible” plugin, however, which you can use for fairly arbitrary checks of status counters and variables. This is the …

MySQL version 4.1 was quite revolutionary. The main reason for that was support for sub-queries.1

However since then MySQL users were rather discouraged to use that functionality, basically due to the implementation’s poor performance  and forced to build complicated queries based on joins rather than on subqueries.

Of course you can do some effort to optimize your subquery with sometimes very good results2. Not always it’s easy or even possible if you can’t change the code though.

You’d say it’s not a problem for typical OLTP, web based traffic at all, just don’t use subqueries! That’s true, …

The MySQL Developer Days come to NYC.

I will be attending this event along Ronald Bradford, an Oracle Ace Director, and a great list of Oracle Engineers.

The event will take place Wednesday May 2nd at the Grand Hyatt in NYC.

Please register here 

Background Knowledge

I using PHP v5.3.3-7 PDO running a MySQL v14.14 Distrib 5.1.49 on Debian v6.0.4 64-bit and executing a SQL load data infile statement.

I received “PHP Warning: PDOStatement::execute(): SQLSTATE[HY000]: General error: 126 Incorrect key file for table ‘/tmp/#sql_66f_0.MYI’; try to repair it”. My database table in this instance is using the storage engine of InnoDB and therefore one can not use the “repair table”.

From my experience I’ve found that this error can mean one of two issues however I have not found information from MySQL confirming this.

Solution – Repair Table

The error message may mean the database table is corrupted and requires a repair.

1. Run repair table on the …

It’s taken a little while to sort out the contest logistics, but we’re ready now, so I want to congratulate our three first-prize winners to the Percona Live MySQL Conference and Expo:

• David Juntgen
• Jeff Hansen
• Domenick Petrella

For helping us to promote the conference, they’re receiving full conference passes, including tutorials. I hope you enjoy the conference, guys, and look forward to seeing you there!

Our ten second-prize winners receive a copy of High Performance MySQL Third Edition, and I’m still in progress with that.

By the way, if you’re not registered for the conference yet, a lot of things are getting …

So yesterday I introduced the newly committed HTTP JSON key-value interface in Drizzle. The next step of course is to create some simple application that would use this to store data, this serves both as an example use case as well as for myself to get the feeling for whether this makes sense as a programming paradigm.

Personally, I have been a fan of the schemaless key-value approach ever since I graduated university and started doing projects with dozens of tables and hundreds of columns in total. Especially in small projects I always found the array structures in languages like PHP and Perl and Python to be very flexible to develop with. As I was developing and realized I need a new variable or new data field somewhere, it was straightforward to just toss a new key-value into the array and continue with writing code. No need …

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 …