Over the last few years, Domas’s technique of using GDB as a profiler has become a key tool in helping us analyze MySQL when customers are having trouble. We have our own implementation of it in Percona Toolkit (pt-pmp) and we gather GDB backtraces from pt-stalk and pt-collect.

Although it’s helped us figure out a lot of problems, sometimes it doesn’t go well. Getting GDB backtraces is pretty intrusive. Here are three things that I’ve witnessed:

1. The server freezes for the duration of the process. This is the most obvious impact on the running server: GDB forklifts the process and gets a stack trace from every thread, then lets it go on working. But this can take a while. It’s usually a couple of seconds, but on big servers with a lot of memory and many threads, it can take much longer (I’ve seen tens of seconds, …

As I stated in my last post, I decided to use the Innodb Table monitor to diagnose an Index count mismatch error a customers found in their mysqld.err log to verify if the problem still existed.

The Innodb Table Monitor has existed for basically forever in Innodb (the MySQL manual discusses it back in the 4.1 manual), and is from a time when what was going on inside of Innodb was a lot murkier than it is now.   To use it, you create a table (in any database you choose), like this:

CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;

This, primitively, tells Innodb that it should start the Table monitor.  The monitor …

Now that InnoDB is the default storage engine in MySQL, is it time to update the default configuration for the InnoDB log file size (innodb_log_file_size) setting?

In general, there are two settings that simply can’t be left at their historical defaults for a production installation. MySQL 5.5 increased the default buffer pool size to something more sane (128MB instead of 8MB), but the log file size remains at 5MB. That’s 10MB total, because there are two logs by default.

Is it time to update this? I think so. You simply can’t run a “real” server with 10MB of transaction logs. What’s a sane value? I’d say that something between 64MB and 256MB would be okay for a lot of entry-level workloads, but that would consume some disk space that might surprise people. Perhaps this is the reason that the default hasn’t been increased from 5MB. Regardless, I think that 64MB times two is okay as a default.

What do you …

We’re told that foreign key constraints are the best way to enforce logical referential integrity (RI) in SQL, preventing rows from becoming orphaned.  But then we learn that the enforcement of foreign keys incurs a significant performance overhead.1,2

MySQL allows us to set FOREIGN_KEY_CHECKS=0 to disable enforcement of RI when the overhead is too high.  But if you later restore enforcement, MySQL does not immediately scan all your data to verify that you haven’t broken any references while the enforcement was disabled.  That’s undoubtedly …

As MLC-based SSD cards are raising popularity, there is also a raising concern how long it can survive. As we know, a MLC NAND module can handle 5,000-10,000 erasing cycles, after which it gets unusable. And obviously the SSD card based on MLC NAND has a limited lifetime. There is a lot of misconceptions and misunderstanding on how long such card can last, so I want to show some calculation to shed a light on this question.

For base I will take Virident FlashMAX M1400 (1.4TB) card. Virident guarantees 15PB (PB as in petabytes) of writes on this card.
15PB sounds impressive, but how many years it corresponds to ? Of course it depends on your workload, and mainly how write intensive it is. But there are some facts that can help you to estimate.

On Linux you can look into the /proc/diskstats file, which shows something like:

 251       0 vgca0 30273954 0 968968610 …

When we’re looking at benchmarks we typically run some stable workload and we run it in isolation – nothing else is happening on the system. This is not however how things happen in real world when we have significant variance in the load and many things can be happening concurrently.

It is very typical to hear complains about MySQL interactive performance – serving simple standard web traffic is drastically impacted when some heavy queries are ran in background or backup is done with mysqldump – a lot more than you would expect from simple resource competition. I finally found some time to look further in this problem and see what can be done to remedy it.

We designed the benchmark the following way – there is a small table (200MB) which completely fits in the Innodb Buffer Pool (512MB). We also have larger table 4GB which does not fit in the buffer pool. We’re running uniform sysbench OLTP on the …

One of the serious limitations in the fast index creation feature introduced in the InnoDB plugin is that it only works when indexes are explicitly created using ALTER TABLE or CREATE INDEX. Peter has already blogged about it before, here I’ll just briefly reiterate other cases that might benefit from that feature:

• when ALTER TABLE does require copying the data into a temporary table, secondary indexes are updated by inserting individual rows rather than sorting;
• OPTIMIZE TABLE could be faster if secondary indexes were temporarily dropped and then recreated using fast index creation;
• dumps produced by mysqldump …

Global transaction IDs are being considered for a future version of MySQL. A global transaction ID lets you determine a server’s replication position reliably, among other benefits. This is great when you need to switch a replica to another master, or any number of other needs.

Sometimes you can’t wait for the real thing, but an imitation is good enough for your needs. That’s why we built a pseudo global transaction ID into pt-heartbeat, the replication heartbeat tool included with Percona Toolkit. It has some of the benefits of a global transaction ID, but it works on any version of MySQL.

Now why would we want this, and who cares anyway? Good question. Let me tell you a tale of two datacenters. Once upon a time, one of our customers had a very “tall” replication topology, with servers replicating back and forth across datacenters …

Percona Live London starts on Monday, so this is the last in the series of free-Percona-Live tickets we’ll give away. But first — have you ever wondered what servers are in your MySQL replication hierarchy?

We have, too. As consultants, a lot of times we need to get a quick overview of the whole replication hierarchy and specific things about it, such as replication filters, versions, whether replication is running, how far behind it is, and so on. These things are very important for making sure that we don’t break something in the system, get blamed for breaking replication when it was already broken, and so on.

So we built pt-slave-find (it was actually born as mk-slave-find, ages ago before I even worked at Percona). It automatically detects and connects to …

When I mention Galera replication as in my previous post on this topic, the most popular question is how does it affect performance.

Of course you may expect performance overhead, as in case with Galera replication we add some network roundtrip and certification process. How big is it ? In this post I am trying to present some data from my benchmarks.

For tests I use tpcc-mysql, datasize 600W (~60GB) with buffer pool 52GB. Workload is run under 48 user connections.
Hardware:

• 1st node: HP ProLiant DL380 G6
• 2nd node: Dell PowerEdge R815
• Both nodes use …