MySQL-dump

I have an internal project that is generating a materialized view of some pretty important data, using 96 application server cores against a 12 core database with 192g of memory and a buttload of SSD, good for about 250 MB/sec peak write rate in synthetic file creation.

The project was underperforming, even with MySQL 5.5-current. We never had more than 15mb/s sustained write rate, and never more than 24G of dirty pages in innodb. Looking at the appropriate (self-made) merlin graphs showed redo log contention and excessive checkpointing.

Install MySQL 5.6.4-m7, which can do large redo logs. A quick back-of-the-napkin calculation shows that around 32GB of redo log are just fine for our worklog, most likely. So I configure 2 ib_logfile of 16000M each, and we turn on the load.

The result is a write rate of 100mb/sec sustained, until the redo log reaches about 24G in size. Then queries stall, the internal stall protection kicks in and backs off for a minute or two, and the database checkpoints furiously. The load comes back on after the timeout, and the database stabilizes nicely at 24GB active redo log and with ongoing checkpointing, 150 MB/sec aggregated write rate and a load of 11 (at 12 cores, that is just dandy).

Since nothing exciting happens, I kill -9 the server, hard, with 24G active redo log. I get recovery after 37 minutes, with no data lost.



This is completely made out of win. I am really, really, really looking forward for the 5.6 GA release. Well done, Oracle-MySQL!

(Note: Large Redo logs are also available with Percona XtraDB and MariaDB)

Continue reading "MySQL 5.6.4-m7 is made of pure win"

(This article is based on the work of my colleage Herald van der Breggen. The "I" and "my" below refer to him)

At work, we have a large Perl application running to earn our keep. To read data from MySQL into perl application, there are a few ways to do that, and we wanted to compare performance of these methods.

• HandlerSocket, a NoSQL interfaceus, using Net::HandlerSocket) (columns selectable)
• DBI, the standard Perl database interface (using DBD::mysql underneath) (columns selectable).
• HANDLER DBI, DBI queries, using HANDLER statements instead of SELECT statements. See HANDLER Syntax (full rows only).
• Class::DBI, an ORM wrapper around DBI, which is what we use in the our internal models a lot (ORM magically selects columns for you, often too many).

As a baseline, in a typical page generation we spend around 0-10ms per page on SQL cpu time in the client, out of 500ms total CPU time. Wall clock times are larger, of course, because of processing time in the database plus data transfer time. Width and number of columns transported matter, and the various interfaces are different with respect to their ability to control the columns transferred to the client.
Continue reading "Comparing HandlerSocket, DBI, DBI+Handler Command and Class DBI"

The following is an edited transcription from the #mysql on the Freenode IRC Chat. It is here so that it can be referenced by the IRC bot on that channel.

G> So it's not necessarily a bad idea to use say an autoinc as the primary index of a table?

A> It's a very good idea to have an autoinc primary index. See the Manual.

T> A: I tend to prefer the natural primary key if one exists, unless ORM is an issue.

I> For Innodb autoinc is usually preferrable.


Continue reading "Natural keys vs. auto_increment"

In The Doom Of Multiple Storage Engines, PeterZ lists a number of unique technological challenges that MySQL has to face precisely because it chooses Storage Engines and the way it implements them.

There is another, nontechnical challenge that needs solving by vendors that are producing commercial closed source storage engines for MySQL - my experience is with one specific vendor, but the situation is basically the same for each and every closed source SE:

Where I work, we are a MySQL support customer. Actually, we are a very satisfied MySQL support customer, because in our opinion the MySQL support just plain rocks and has more than once saved our corporate asses, or at least greatly improved our MySQL experience.

If we were to load a closed source storage engine into our MySQL binaries, we would make life a lot harder for MySQL support, and despite the fact that MySQL support is actually extremely cooperative when it comes to non-MySQL binaries, we would most likely end up with a support situation that is a lot less stellar than what we have now.

In the case I am thinking about, the vendor does not just produce a storage engine, but according to their architecture diagram, more or less a fork of MySQL including changes to the optimizer and other non-SE parts of MySQL. The version strings of their binaries I have seen have smaller numbers than current MySQL versions, in some cases much smaller numbers down to what MySQL considers Alpha or Beta versions of the 5.1 server.

If there was a problem with that binary and we were to take it to MySQL support, they would most likely laugh us out of the room, and rightly so. If we took it to the storage engine maker and the problem relates to something that is not related to their product, but to the MySQL proper, we are probably not really talking to the right company for a fix. Or we are looking at a lot of potential for corporate finger pointing.

The solution - for us at least - is really to beat down any effort of our internal customers that is related to researching closed source MySQL storage engines, unless they can show us a support agreement or support endorsement for this storage engine by MySQL/Sun/Oracle. Their product may be technologically sound and the solution to all of our performance problems - we will probably not be interested until there is such an agreement. We'd rather introduce another commercial database server (which is neither MySQL nor Oracle) than run such an unsupported configuration.

So the most important thing for any storage engine maker is to be either open source, or be part of a support alliance with MySQL/Sun/Oracle. Without either you do not exist.

This is the english translation of another article in my german language blog.

How are transactions organized physically

When InnoDB creates a new transaction it is not yet committed. The database has not yet made any promises to the application and so we do not really have to make anything persistent so far.

To be fast InnoDB tries to assemble the transaction in a memory buffer, the innodb_log_buffer. It should be sufficiently large that you actually can assemble such a transaction in memory without needing to write it out in part into the redo log. A size of 1M to 8M is normal.

Once a transaction is to be committed InnoDB has to read the page from disk which contains the image of the row that is being changed. It then has to actually make that change in memory. The changed page is cached in a memory pool called the innodb_buffer_pool. This pool also caches unchanged pages that have been accessed by read operations. All of these pages on disk and in memory are 16K in size and the innodb_buffer_pool_size determines how much RAM we will use as a cache for such pages - usually as much as we can spare.
Continue reading "Configuring InnoDB - An InnoDB tutorial"

This is an english translation of the second part of an article in my german language blog.

Transactions - An InnoDB tutorial

InnoDB does transactions. Meaning: It collects statements working on InnoDB tables and applies them on COMMIT to all tables "at once". Either all of these statements inside one transaction succeed ("commit") or all of them fail ("rollback"), changing nothing.

By default, the database is in AUTOCOMMIT mode. Meaning: The server sees a virtual COMMIT command after each statement. You can disable autocommit completely, or you are starting an explicit transaction inside autocommit using the BEGIN statement.
Continue reading "Transactions - An InnoDB Tutorial"

MySQL offers a variety of storage engines giving you a lot of flexibility in managing your storage and data access needs. Still I encounter customers who are not using this flexibility when they should, because they lack information about the advantages of Non-MyISAM storage engines or which are using storage engines like InnoDB as if they were using MyISAM.

This is the introductory article in a series of texts that will hopefully once become an InnoDB tutorial. A german version of this article is available in my german language blog.

An InnoDB Tutorial

The InnoDB storage engine is an engine that can be operated ACID compliant, does transactions and foreign key constraints. It is useful for all applications that do online transaction processing or have a high rate of concurrent write accesses for other reasons.

Continue reading "An InnoDB tutorial"