Slamming the door shut on mysql server access

Has there been a situation when you needed to perform some serious maintenance work on your db server, and you just don't want anyone at all to connect to the db server?

Here's a one liner:

set global init_connect="kill connection_id();"

(I should have posted this 6 days ago)

Planet MySQL RSS feeds not working?

Not sure if its just me, but has anyone else been having trouble with the RSS feeds? From Safari, it looks like there are no new posts for the last 3 days. I thought it was Safari (after upgrading to 5.0.4) but I see the same issue with Google Reader?? Or have I suddenly developed a case of stupid-itus?

Why the Facebook/Percona versions of MySQL are so much better

One of my roles at Yahoo is to provide a rock solid infrastructure for MySQL-based projects. In order to provide that infrastructure, I have to satisfy many groups at Yahoo: Developers need an easy way of deploying the db server in their environment. Software Engineering (Operations) is looking for features to help scale MySQL, and DBAs are looking for more knobs and gauges to help tune the server. After about one year of attempting to manage the infrastructure, and following the MySQL sagas, my job is getting easier thanks to companies like Facebook and Percona as well as one of my predecessors (jcole) for releasing quality code that extends MySQL in so many ways than just raw performance. I've been able to take the best code from each company and mold together an internal version that satisfies all crowds. MySQL/Oracle, you should do the same.

Are you using the deadline scheduler? (Part 2)

The deadline scheduler has many advantages over the cfq scheduler, which is the default in operating systems like RedHat Enterprise Linux. In my previous post, I quickly showed how much of a performance gain can be had by switching to the deadline scheduler. Now I will show some real performance numbers for different RAID configurations.

	2 disk RAID-0	4 disk RAID-0	6 disk RAID-5	6 disk RAID-10
CFQ	461.467	947.067	845.943	862.763
Deadline	851.067	2876.933	2145.866	2580.430

All of the tests were performed on a Dell PowerEdge 2950 with 2xQuad Core Xeon,16GB of memory and 6x146Gb SAS drives on a Perc/5 RAID controller, and all filesystems were standard EXT3. The TPCC benchmarks were conducted with a smallish buffer pool (2GB) and a 1GB log file size. The database is approximately 7GB in size (100 warehouses). I wanted to show what performance an I/O bound test would yield. The numbers here show that its possible just to get that added boost without resorting to re-creating the entire database with a different filesystem (like XFS). I will come back to XFS later as it provides the best performance.

Are you using the deadline scheduler? (Part 1)

There have been many posts about performance, benchmarking and the results. Many DBAs have talked in the past about the deadline scheduler, available in all modern Linux distributions.  The deadline scheduler is very effective for database systems, as it tries to prevent starvation of I/O requests.  To see what the net effect of using the deadline scheduler looks like, I am using a simple TPCC benchmark program, created by the Percona team, tpcc-mysql to measure the number of New-Order transactions per minute (TpmC).  All of the testing was performed using RedHat Enterprise Linux 5, using the standard ext3 file system and a combination of the default scheduler (cfq) and the deadline scheduler (dl):

CFQ:  845 TpmC

DL:  2145 TpmC

This is a huge difference in performance between the two schedulers. How do you know if you are using the deadline scheduler?  There are a few ways. The deadline scheduler can be enabled for all of your disks at boot time via the LILO or GRUB boot loaders. The scheduler can be changed by appending the string elevator=deadline to the boot line (file /boot/grub/grub.conf). If you issue the command cat /proc/cmdline this will display the kernel parameters that the system was booted with.

The second way of switching schedulers is to change it "on the fly." You can list all of the available schedulers, as well as determine which scheduler is current by using this command: cat /sys/block/drive/queue/scheduler where drive is the actual drive represented to your system (i.e. sda, hda).  The list of schedulers is displayed, and the current scheduler is noted within square brackets.  To change the scheduler, echo the name of the new scheduler to the meta-file from the previous command (as user root). Be careful when switching schedulers on the fly, as there is a possibility to hand the system.

In part 2, I'll go over my testing methodology and show how using the deadline scheduler with different types of RAID drive configurations and filesystem types affects the overall throughput of the system.

MySQL 5.5 may be GA but not the docs.....

I did not fall off the face of the earth....

It's been a looong time since I've posted anything. For the past year, I've been focused on operations and streamlining DBA tasks, as the group's responsibilities continues to grow. Its one thing to manage 10-20 production MySQL database servers, but when the number starts climbing to 160-200, things start getting interesting.  For 2010, I expect that number to double. Performance is key, but more important is reliability, uptime, monitoring and notification. Dashboards are a good start, but the most important subsystem will be monitoring. How scalable does the system need to be? For 10-20 off-the-shelf products work fine.  But when thousands of systems need to be monitored, then it starts getting interesting. I'll share my thoughts along the way as far as how we are handling this type of growth. 

Should you be worried about STATEMENT based replication?

Earlier this month, an announcement about STATEMENT based binary logging would be the default starting with MySQL version 5.1.29. I've always preached that backwards compatibility was key to new releases. In this case, lessons were not learned until close to final GA date.

I would like to point out that for 90% of customer cases, STATEMENT based replication will work fine as advertised. But I'd like to point out some use cases where STATEMENT based replication will be at best spotty (at least it is in 5.1.28).

If you primarily use InnoDB as your storage engine you will want to pay close attention to your transaction isolation level. There is a minimum requirement that READ COMMITED level be used, otherwise statement based replication can not be used.

Partitioning + InnoDB + STATEMENT-based binlog also has its problems. We faced constant issues, getting the error 'Binary logging not possible. Message: Statement-based format required for this
statement, but not allowed by this combination of engines'. What the heck does this mean?

It's a misleading error message. Partitioning is not a true storage engine, but is a virtual one. The first time an underlying table is opened, the partition engine caches the table flags of the real storage engine, in this case InnoDB. Lets say for example, an app performs a SELECT on a partitioned InnoDB table. Let's also assume that the transaction isolation level is READ UNCOMMITTED. The SELECT will execute without any issues. But, try to insert a record from a different session, and it will fail every time. I filed this BUG#39084 over a month and a half ago. Repeatable test cases were also given.

Since we are committed to releasing our reporting database and can not wait for MySQL to come around and realize mistakes they made, we came up with our own patch that addresses this immediate concern, and for this reporting db.

So, should you be worried? I think I would be.

Version 3 of mysqlbackup - small bug fix

I just posted version 3 of mysqlbackup to MySQL Forge.

Small bugfix: Added option --add-drop-table to the default options for mysqldump. This was causing a failure in restoring views.

Not all MySQL errors are visible to replication

This probably warrants a bug report to MySQL, but I want to let other people know about this first. There are situations where MySQL receives incomplete statements from replication relay logs, but does not trigger a replication error. Case in point is exceeding max_allowed_packet.

I recently had a situation where once of my machines was incorrectly configured with a different value for max_allowed_packet. What happened is not what I had expected. Instead of receiving a replication error (which we monitor for using Nagios), the MySQL error log was spewing with messages about exceeding max_allowed_packet. Instead, the only visible problem through our monitoring framework was that replication had fallen behind, and was continuing to fall behind.

Fixing the problem was rather easy: stop the slave, change the max_allowed_packet variable globally in the db server and in the configuration file, and then start the slave.

This is one of those things that falls under the category "MySQL annoyances and one-offs". Shouldn't this really trigger a true replication error, rather than spewage in log files? I will have to reproduce this and then file a bug report to MySQL, but I really shouldn't have to if there was some consistency in error reporting.

Script to backup binary logs on a master

I have recently posted a script on MySQL Forge to back up MySQL binary logs. One of the ideas that I had when I originally wrote the script was to take into account all of the slaves and what master log file & position that each one has executed. This way, only the relevant binary logs would get archived and then subsequently purged. You can find the script here.

Why is QA so important?

Someone should really check the label before mass producing. Doesn't this look like SQL from SQL Server????

MySQL and iSCSI - a winning combo!

So it's been a very long time again between posts. So much has happened. Let me first begin by saying that I am very impressed with iSCSI performance and I believe that it is mature enough to actually run production workloads (but it really depends on the type of workload).

After all of the benchmarking and analysis, we finally decided on moving forward with a purchase of an iSCSI storage solution. For the types of queries we run (large amount of records to scan, small resultset returned) we had to tweak the schema just a bit in order to realize the performance that we desired (that plus good quality fibre-channel drives to get that extra oomph that's needed).

Bottom line is we had to make a significant investment in hardware in order to realize the benefits of having a proper storage solution in place. The benefits though outweigh the overwhelming maintenance required to keep all of the machines running. Backups using the storage provider's snapshot mechanism will be extremely beneficial as well. All in all, a good decision to ease our minds.