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.

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, …

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 …

After a long time, I finally had some time to write again, and this time I intend to keep a periodicity. The reason for my absence? Well now I have Dom Without further ADO, let’s get to the point, what… Continue Reading →

Continue reading What is Iptables, what it’s for, and how to use?

Related posts:

1. O que é Iptables, para que serve, e como usar?
2. Scheduling commands with at linux
3. Using ssh without password

MySQL database usage is popular in OpenStack. Core OpenStack services for Compute (Nova), Storage (Cinder), Neutron (Networking), Image (Glance) and Identity (Keystone) all use MySQL database.

MySQL – as the world’s most popular database, runs inside OpenStack Virtual Machines and serves as database backend to OpenStack cloud based applications. The MySQL instances can be configured to run in virtual machines manually (by simply installing MySQL inside a VM and running it) or can be created in an on-demand fashion by OpenStack Database-as-a-Service (Trove).

In either case, the MySQL data is mission-critical. OpenStack cloud administrators and cloud guests/tenants need the ability to backup and restore their MySQL databases. mysqldump is traditional way of doing MySQL backups and restores. However, based on previous experiences of the MySQL community, it is widely known that mysqldump has …

Benchmarking is a popular topic. People love drawing graphs as much as watching how X is 10% faster than Y; there must be something special in measurements.

For a DBA, however, more tangible improvements come from less popular area of database maintenance. While MariaDB spreads FUD around InnoDB (nonetheless still uses it) I have to admit InnoDB gets more friendly to DBAs.

In MySQL 5.6 new temporary table naming scheme was introduced – one of improvements. Invisible, yet important.

Temporary table names became more random and should not ever be reused.

Some time ago I wrote a post about how to remove …

This post recently caught my attention on Planet MySQL. If you haven’t read it yet, I suggest that you go and do so, and also read the comments. I think Matthew’s request for the queries so that others can run comparative benchmarks is very interesting, and while I don’t have access to the queries used to produce … Continue reading Benchmarking Joomla →

Related posts:

1. Updated mysql-proxy benchmarking script (for proxy 0.7) My previous post contained a lua script for MySQL proxy...
2. Using MySQL Proxy to benchmark query performance By transparently sitting …