Recently while writing a data loading application for a customer, we’ve come into a problematic situation with the Python MySQLdb module that can be installed with base RHEL repository or DVD. As a little background, this application uses an HA architecture where a Virtual IP can be assigned to different servers during a failover scenario. However, during failover, as long as the VIPs are not re-assigned, the application could hang waiting for query response. A quick strace reveals something like:

sendto(5, "W\0\0\0\3SELECT * FROM dataloader.bo"..., 91, 0, NULL, 0) = 91
recvfrom(5,

Further test reveals that this problem will not timeout until after about 15 minutes which matches the default tcp_retries2 value on the system (see man 7 tcp):

[root@node1 ~]# cat /proc/sys/net/ipv4/tcp_retries2
15

It turns out that this version of MySQLdb module has client net read and write timeouts set to 0 allowing the TCP setting …

After some grueling IO testing on 7200rpm disks, I got my hands on some shiny new Samsung 840 SSDs and wanted to share the performance results in similar fashion.

Dell r630, E5-2630 v3 @ 2.40GHz (16 cores), 256GB RAM, Samsung 840 EV SSD 960GB.

Testing was sysbench, fileio-rndrw, async+direct io, 16 threads, 5.5TB RAID-6 using XFS mounted with noatime,inode64 and using the deadline scheduler.

On 5 November, I’ll be speaking at #SFMySQL Meetup about Data Recovery Software for MySQL

Add Slipped & DROP’d your TABLE? Recover w/TwinDB’s Undrop for InnoDB toolkit to your calendar.

There will be a demo and if you want to try to undrop a table yourself bring in a laptop with Linux.

Download the latest revision of the TwinDB Data Recovery Toolkit from LaunchPad.

Internet connection isn’t necessary, but make sure you install dependencies: gcc, make, flex, bison.

The post Presenting TwinDB Data Recovery Toolkit …

Today I encountered a situation where MySQL Enterprise Backup caused to much load on the I/O subsystem of the server to cause the application to be so slow that it wasn't usable any longer. So I wanted to limit the mysqlbackup process so it wouldn't cause any more issues.

The mysqlbackup command has settings to for the number of read, write and process threads. The defaults are 1 read, 1 write and 6 process threads. So that isn't really useful for throttling as I was using the defaults.

Using the ionice utility wouldn't work as that requires the CFG I/O scheduler.

I found a solution in this blog post. It is to use cgroups on Linux. I had used cgroups before to test how a galera setup works when one of the three servers had a much slower CPU.

# mkdir /cgroup/blkio
# mount -t …

Percona Toolkit’s pt-table-checksum is a great tool to find data inconsistencies between a MySQL master and its replicas. However it is sometimes not enough to know that there are inconsistencies and let pt-table-sync fix the issue: you may want to know which exact rows are different to identify the statements that created the inconsistency. This post shows one way to achieve that goal.

The issue

Let’s assume you have 2 servers running MySQL 5.5: db1 the master and db2 the replica. You want to upgrade to MySQL 5.6 using an in-place upgrade and to play safe, you will upgrade db2 (the slave) first. If all goes well you will promote it and upgrade db1.

A good thing to do after upgrading db2 is to check for potential data …

Stop increasing max_connections every time there’s a 1040: Too Many Connections error. Every additional connection is another share to further divide the available memory.

Instead, while it would be best to manage the workload, it is also reasonable to properly utilize the available hardware with good server configuration.

There are three relevant server configuration options for managing connection counts as they relate to satisfying web requests.

1. max_connections – the queue depth
2. innodb_thread_concurrency – the count of queue consumers
3. innodb_concurrency_tickets – the amount of work a consumer can do on a query before switching to the next query request

Correctly configuring these three variables, and controlling your workload of course, can prevent 1040 Too many connections errors, assuming, …

Mon, 2014-11-03 13:09anatoliydimitrov

Yii is a fast and secure framework for developing PHP applications. It has excellent support for various databases, including MariaDB, as we'll illustrate here.

Yii installation is simple and straightforward. Just download the framework archive and extract it to a directory outside your website's document root. For example, if your site resides in /var/www/html/, extract the archive to /var/www/ so that you can access the framework at /var/www/framework. Then run the shell command /var/www/framework/yiic webapp /var/www/html/ to create the skeleton of a new web application in /var/www/html.

To make sure the PHP applications you create with Yii can work with MariaDB, make sure your PHP installation has MySQL PDO support, which means it will also have MariaDB support. In CentOS, the package that provides this is …

Mon, 2014-11-03 13:09anatoliydimitrov

Yii is a fast and secure framework for developing PHP applications. It has excellent support for various databases, including MariaDB, as we'll illustrate here.

Yii installation is simple and straightforward. Just download the framework archive and extract it to a directory outside your website's document root. For example, if your site resides in /var/www/html/, extract the archive to /var/www/ so that you can access the framework at /var/www/framework. Then run the shell command /var/www/framework/yiic webapp /var/www/html/ to create the skeleton of a new web application in /var/www/html.

To make sure the PHP applications you create with Yii can work with MariaDB, make sure your PHP installation has MySQL PDO support, which means it will also have MariaDB support. In CentOS, the package that provides this is …

We recently announced our 5.7.5 Milestone Release, yet another milestone on our road to 5.7 GA. The purpose of our milestone releases is to get community feedback and to ensure high quality on 5.7 from day one. This blog post gives the reader a high level view on 5.7 so far, while also attempting to demonstrate where we are heading as many of the individual pieces make much more sense when you can see the bigger picture. You might further drill into the series of milestone blog posts (5.7.1, 5.7.2, …

Facebook recently made opensource, osquery. It gives you operating system data via SQL queries! Its very neat, and you can test this even on MacOSX (it works on that platform & Linux). It is by far the project with the most advanced functionality, linked here in this post.

I noticed that rather quickly, there was a PostgreSQL project, called pgosquery, based on Foreign Data Wrappers with a similar idea. (apparently it was written in less than 15 minutes; so a much lower learning curve than the regular MySQL storage engine interface)

I immediately thought about an older MySQL project, by Chip Turner (then at Google, now at Facebook), called …