Eric Bergen

Last week I was working with a client that rediscovered a bug where setting expire_logs_days and issuing a flush logs causes the server to crash. It’s MySQL Bug #17733 if you want to have a look. Seeing MySQL crash was enough inspiration to fix something that I and others have wanted to fix in MySQL for years.

Currently a flush logs command tries to flush all of the following logs in order:

• General Log
• Slow Query Log
• Binary Log
• Relay Log
• Store Engine Logs (If available)
• Error Log

The reason I wanted to fix this is because my client was issuing a flush logs to rotate the error log on a server with no replication. The crash was caused by replication. With individual flush logs it’s less likely for this to happen again in the future. People can simply issue a query for the log they want to flush. The new commands flush logs named in the command. They are:

• flush general log;
• flush slow log;
• flush binary log;
• flush relay log;
• flush engine logs;
• flush error log;

The words log and logs are interchangeable. The query “flush general log” is just as valid as “flush general logs” even though there is only one log. I submitted the patch as a fix for MySQL Bug #14104.

The patch, flush_logs.patch was diffed against 6.0.4 but also applies on 5.1.24.

Rotation for different log files isn’t uniform. Rotating the slow log simply closes and opens it. I’m planning to write a second patch that rotates log files using the same numbered scheme as binary logs. This fixes the rotation for slow and general log as well as eliminating the annoying issue of error logs being destroyed after they are rotated to foo.log-old.

This patch hasn’t been accepted or committed yet so if you have any suggestions on how to make it better please let me know.

Have you ever executed a query from the MySQL command line client only to find that the output wrapped and the result is unreadable? In the past you have to run the query again with \G instead of ; or \g to get it to display the output in a vertical mode. My feature in MySQL 6.0.4 fixes that. The auto-vertical-output option tells the command line client to display the results in vertical format if the results are going to be too wide to display horizontally. It does this without re-executing the query because MySQL passes the length of each column in the result set. If the client isn’t able to determine the width of the screen it will default to 80 chars.

This is a continuation of the MySQL User Conference replication notes part one.

The session is opening up talking about failover. The shared disk in this case is drbd. DRBD is a fine product for replicating block devices of single disk systems. It’s made redundant by raid and doesn’t provide as much protection as binary log failover. You can find my notes on why I don’t recomment DRBD for MySQL in drbd in the real world.

Lars went a bit quick through the other two configurations. I’ll try to review the slides and post comments.

The next configuration is using federated. The federated storage engine has many problems that make it almost useless for any production deployment. Mats says, “Federated isn’t the fastest engine in the world”. That’s an understatement. Join on two tables as they describe it is almost impossible. Aside from the performance issues this is my favorite limitation of the federated engine, “There is no way for the FEDERATED engine to know if the remote table has changed. The reason for this is that this table must work like a data file that would never be written to by anything other than the database system. The integrity of the data in the local table could be breached if there was any change to the remote database.”

Lars mentions Jeremy’s failover design that is recommended by Proven Scaling. Thanks Lars!

There is a lot of confusion about the difference between row based and statement based replication. Hopefully my row based replication post can clear up some of the confusion.

For keeping things simple and easy to debug I recommend sticking with either row or statement based replication. Teaching the implications of each to application developers is going to be more difficult than sticking with one model. The exception is things that can’t use row based replication like DML statements.

When using a SAN make sure you have redundant SANs, your backups aren’t on the same SAN as mysql and that if you have to use a shared san you have good control over the resources. MySQL is very sensitive to i/o latency increases. If someone else does a large operation on the SAN it can increase the i/o latency in mysql causing operations to take longer which can bring down your application.

I don’t recommend using a hardware load balancer to manage write load between masters. There is a risk of sending writes to both masters at the same time. In a properly configured dual master setup half the writes will be rejected on a read only error. The worst case is that writes go to both masters causing replication error and inconsistent data.

The tutorial is wrapping up now. I look forward to using row based replication 5.1. Thanks Lars and Mats.

I’m attempting to live blog corrections and notes while sitting in the replication tutorial. Lars is covering available options in MySQL replication. I’m going to attempt to cover some recommended best practices and things that are possible to do in MySQL but should be avoided. Please keep in mind that I’m writing this during the presentation. If anything is confusing post a comment and I will clean it up.

When designing a MySQL architecture that are several possible configurations. Two that should be avoided are dual master where you write to both masters. Configuring replication in a dual master dual writer setup means there is no single authority on the data. There is also no need to write to both masters as this doesn’t give you any performance improvement. Each master has to process the same sql statements. One step further is circular replication that wasn’t mentioned in the talk but has been in other publications. When using three or more masters if one dies there is no way to restore it without brining down the other masters.

There are several configuration options for filtering data on the slaves. You can filter but the current database or by tables. My recommendation is to keep the data on the slave the same as the master as much as possible. When the slave is different you’re much more likely to have queries succeed on the master but fail on the slave. The worst of these options is replicate-do-db. This option filters queries based on “USE db”. If the use db is set differently on the master the query will succeed but can be passed through the slave without executing. This is a silent failure and won’t cause replication to stop.

A quick note about show binlog events. This is similar to mysqlbinlog in that it dumps events in the binary log. The difference is that show binlog events doesn’t include set variables.

When using relay slaves it’s very important to always configure them in pairs. If a relay slave dies there is no good way to to connect it’s slaves to the relay slave’s master. Using blackhole can cause silent failures to be passed through as well as issues with auto increment and storage engine differences. Blackhole should always be avoided.

Reset slave should function the way it does on the slide in that it should delete everything and leave the slave in a blank state. It actually deletes the logs and resets the host, user, and password. The functionality has been changed a few times in different version so be sure to check the manual. The differences are in that mysql may keep the old host information in memory or it may forget.

Purge master logs was covered briefly. It should be noted that purge master logs won’t purge logs that a connected slave hasn’t downloaded. If a slave is disconnected the master will happily delete logs that a slave hasn’t downloaded yet. I recommend keeping at least 7 days of logs on the master for situations where slave gets disconnected.

Now we’re into the section of specialized slaves. These slaves have a subset of data on the master. Avoid this as much as possible see my row based replication post about half way down for the reasons.

The slide for HA + Scale out has dual master with all of the slaves hanging off of one master. It’s better to have slaves hanging off each master because if the active master dies hard enough that you can’t get binlog offsets from it then there isn’t a good way to change slaves over to the existing master. Balancing your slaves will make it easier to failover. Ah excellent someone up front just pointed this out.

I woke up this morning to a nice surprise. Peter Zaitsev from MySQL Performance Blog mentioned my fix for a crash in MySQL. Thanks Peter! That reminded me that I need to write this post about another fix.

A storage engine is called a “handler” in MySQL internals. Handler events are per row at the storage engine layer. For example an insert query that inserts 3 rows will cause one Com_insert increment and 3 handler_write increments. The way these statistics are implemented the storage engine is responsible for incrementing statistics itself. Innodb is missing function calls to increment the handler_delete and handler_update status variables. I’ve filed bug #35537 with MySQL that contains a patch adding in the missing statistic_increment calls.

It seems strange that storage engines are responsible for incrementing global statistics. Brian Aker tells me that this is because the handler interface is a direct pass through to the storage engine. I’m guessing that it was easier to do the statistics increment in the storage engine rather than chase down all the handler calls in the server and change them to functions that call the handler and increment statistics.

While working with a client we found a query that can crash MySQL. It has to do with ref_or_null outer joins where the table contains a null value. MySQL fails to tell the storage engine to initialize the index before reading from it. It crashes when used with MyISAM tables but it could result in an incorrect key file for table error with other storage engines. You can find the test case and patch in bug #34945. I’ve waited to blog about it until MySQL approved my patch. As of this writing my patch has been approved and committed for release in 5.0.60.

[Updated: 2008-03-05 Jeremy writes to tell that I left out the provenscaling.com contact form submission on the 21st and the blank email with a pdf on the 27th]
[Updated: 2008-03-26 Rob Young from MySQL AB sent me an email on the 17th saying, "We are currently working to implement PayPal to cut down on the email interactions we have in place around our manual fulfillment process. Testing is going well and we should have this implemented in the next few weeks."]

Recently Proven Scaling decided to purchase a MySQL Enterprise subscription. We will be evaluating Enterprise to see if it’s knowledge base is valuable to our customers as a supplement to Proven Scaling’s consulting services.

The subscription process seems remarkably manual on MySQL’s part. It involves them sending us five emails (not counting the mysql.com registration), all of which are from different senders. Some are from people others are automated. Here is a breakdown in chronological order:

Feb 20 12:25PM
shop@mysql.com - Online Order Confirmation

Feb 20 8:20PM
support-feedback@mysql.com - Welcome to MySQL Enterprise

Feb 20 8:49PM
enterprise-feedback@mysql.com - MySQL Enterprise: User account password changed

Feb 21 8:51AM
provenscaling.com contact form submission with creepy upsell - “Please provide my contact information to the person(s) responsible so I can assist with future orders. There are packaging options that are not available with on line purchases….”

Feb 22 10:39AM
ar@mysql.com - Your - MySQL - Invoice

Feb 22 8:41PM
rwolfejones@mysql.com - Credit Card receipt from MySql

Feb 27th 4:25pm
julie@mysql.com - MySQL receipt for Invoice

This seems a bit excessive. It also raises several questions about the registration process. Why does it take 8 hours for the order to be filled? Does someone have to manually approve the order? The second email is “Welcome to MySQL Enterprise” from support-feedback while the third email is from enterprise-feedback with notification of an account change. This email is because Enterprise generates and emails out a password on the first login.

What’s the difference between support-feedback and enterprise-feedback? A few days later after the account is setup accounts receivable sends us an invoice. Then ten hours later a credit card receipt. Why isn’t MySQL using automated credit card processing? The credit card receipt also looks like it was copied and pasted into the email.

I hope that we’re some kind of special case and that not all of the MySQL Enterprise customers have to go through the same process. I look forward to hearing comments from MySQL on this process.

In Issue 3 of MySQL Magazine Peter Zaitsev writes, ” I would set [innodb_log_file_size] to 256 megabytes for small sized boxes up to 5% of the total memory on the big boxes.” I tend to set my log file size to around 128M. Depending on your system setting the log file size around a few hundred megs should be the right balance between preventing checkpointing and still having fast recovery times.

The manual states, “Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group.” The sample my.cnf files that come with MySQL state to set the innodb_log_file_size to 25% of the innodb buffer pool size. I think this mistakenly advises people to set their log files much larger than they need to be causing unnecessarily long recovery times.

Crash recovery time is an important feature of InnoDB. In the event of a server crash large log files could mean waiting hours for InnoDB to recover data while your site is down. A log file size of a few hundred megs will enable InnoDB to perform recovery in a few minutes on reasonable hardware.

When I received and email almost two years ago about a announcement that MySQL was going to release and enterprise product I was very excited. I was looking forward to a redhat style model of vetting releases in the community then offering a proven stable version to paying customers. I saw it as a great way to for MySQL to generate revenue as well as eliminate the need for people to stay a few releases back from the head and guess when to upgrade.

The release shocked me. What was originally emailed to me and the final plan were two very different things. It was a plan to hand paying customers bleeding edge code that had been tested only by MySQL’s QA team. It seems MySQL has forgotten the years of testing by millions of community members that has given MySQL the stability we have grown to trust. I predicted the instability of MySQL enterprise back in October ‘06 by saying that releasing patches in enterprise that hadn’t been tested by the community would result in instable releases being delivered to paying customers. These should always be tested by the community first.

MySQL 5.0.48 enterprise is rock solid proof that the release cycle MySQL chose to implement is completely broken. It was released then pulled due to some very basic sorting functionality being broken. I hope they’re working to change the release cycle to more of a redhat model both to put trust back in the community for testing and to give enterprise customers the stable version they’re paying for.

MySQL Bug Tracker

I realize that MySQL is in a transitional phase making it’s development more transparent to the community so this entry isn’t really a rant but something that will hopefully ease the transition. The public MySQL bug tracker doesn’t seem to have any way to view the history of status changes to a bug. Things like changing a bug from open to inactive. I assume there will always be things that should be hidden from bugs like links to sites internal to MySQL AB but things like simple status changes should be visible to the public.

For example on this Bug #20358 Heikki posted a comment that he was moving the bug from open to unable to repeat. Did he actually make the change? I have no idea because I can’t access the status log. Was it ever changed from unable to repeat back to open or verified? Again I have no idea…

Another “feature” of the bug system that I find questionable is the bug bot. The script trolls the bug database looking for bugs that need an automatic state change. In my opinion it should never find anything. I’ve noticed that it changes state on bugs that haven’t been commented on for a month to an inactive status. I have one example of a Bug #26489 that I think is fairly critical being marked in an inactive state not because the bug wasn’t still occurring, but because we had no new useful information to post at the time. Just because no new information has been found doesn’t necessarily mean that a bug should be taken off of people’s radar. If humans want to mark them as inactive it’s fine but a script making that decision worries me.

I do appreciate the things MySQL has done recently to make the development process more accessible for those of us without MySQL AB on our name tag. #mysql-dev on freenode is well populated with very intelligent minds. It’s nice to have them readily accessible to bounce ideas off of.

Keep the momentum on this transition going and please don’t mark my blog post as inactive