MySQL Performance Blog

This is part 3 of my Tyrant extra’s, part 1 focused on durability, part 2 focused on the perceived performance wall.

#3.  Tokyo Cabinet Can have only a single writer thread, bottlenecking performance

When writing an application using Tokyo Cabinet only one connection can be opened as a “writer”  while the rest are readers.  Tyrant allows for multiple “writes”  to be sent in from multiple applications but it still single threads them when writing out to disk.   If you run several threads all just inserting into Tyrant your will see tyrant hit 100% Cpu on 1 core, and your writes will start to peter out quickly.

[read more...]

Continuing my look at Tokyo Tyrant/Cabinet and addressing some of the concerns I have seen people have brought up this is post #2.

#2.  As your data grows does  Tokyo Cabinet slow down?

Yes your performance can degrade. One obvious performance decrease with a larger dataset  is you start to increase the likelihood that your data no longer fits into memory.  This decreases the number of memory operations and trades them for more expensive disk based operations.    As fast as any application is, as you read off disk opposed to memory performance is going to drop off substantially.  One of the more difficult things to test with Tyrant is disk bound performance.  The FS Cache can make Tyrant seem like small amounts of memory will still make it scream.  Once your data set is larger then that, people start to claim they hit the performance “wall”.

In order to help test this I went ahead an mounted the FS with my data files with the sync option which effectively disables the FS cache.  This should help show the real performance of the hash engine.  Here performance dips substantially, as expected :

[read more...]

You know how in addition to the main movie you have extras on the DVD.  Extra commentary, bloopers, extra scenes, etc? Well welcome the Tyrant extras.  With my previous blog posts I was trying to set-up a case for looking at NOSQL tools, and not meant to be a decision making tool.  Each solution has pros and cons that will impact how well the technology works for you.  Based on some of the comments and questions to the other blogs, I thought I would put together a little more detail into some of the deficiencies and strengths of Tokyo Tyrant.

#1.  How durable is Tokyo Tyrant?
[read more...]

We often encounter customers who have partitioned their applications among a number of databases within the same instance of MySQL (think application service providers who have a separate database per customer organization … or wordpress-mu type of apps). For example, take the following single MySQL instance with multiple (identical) databases:
[read more...]

Since DTrace was released for Solaris I am missing it on Linux systems… It can’t be included in Linux by the same reason why ZFS can’t be – it’s licensing issue. Both ZFS and DTrace are under CDDL, which is incompatible with GPL. So you can see DTrace and ZFS on Solaris, FreeBSD, MacOS, but not on Linux.

However I follow the project SystemTap for couple of years (it was started in 2005), which is supposed to provide similar to DTrace functionality.
[read more...]

If you ever wondered how big is that or another index in InnoDB … you had to calculate it yourself by multiplying size of row (which I should add is harder in the case of a VARCHAR – since you need to estimate average length) on count of records. And it still would be quite inaccurate as secondary indexes tend to take more space. So we added more detailed index statistics into our xtrabackup utility. The thanks for this feature goes to a well known Social Network who sponsored the development.

[read more...]

It seems pretty common to find customers install DRBD for the wrong reasons. There are many pros/cons to compare DRBD to replication, but I’ve managed to cut down my spiel I give to customers to these two points:

• DRBD’s aim (assuming replication mode C) is to provide 100% consistency, and then as much uptime as possible.
• MySQL Replication (with a manager such as MMM) aims to have 100% availability, at the potential loss of some data surrounding a failure.

So if you are installing DRBD with the aim of purely “availability”, and are not worried about losing that last write on the crash to your master database that (hopefully) happens only once every few years, you may be using the wrong technology.

While the prized “1 minute failover” is possible in DRBD, it doesn’t really explain the full picture. The typical crash recovery process in DRBD is a lot longer:

• After resource transfer, a filesystem check runs (0-5 seconds).
• mysqld is started (1-5 seconds)
• InnoDB runs through crash recovery (1 minute – several hours). Peter wrote about this here.
• The server is then ready to accept connections.

Now, having said that: If you have an application that requires 100% consistency, then DRBD is one of your best choices on the mysql-market today.

For the last couple of months, we’ve been quietly developing a MySQL protocol parser for Maatkit. It isn’t an implementation of the protocol: it’s an observer of the protocol. This lets us gather queries from servers that don’t have a slow query log enabled, at very high time resolution.

With this new functionality, it becomes possible for mk-query-digest to stand on the sidelines and watch queries fly by over TCP. It is only an observer on the sidelines: it is NOT a man in the middle like mysql-proxy, so it has basically zero impact on the running server (tcpdump is very efficient) and zero impact on the query latency. There are some unique challenges to watching an entire server’s traffic, but we’ve found ways to solve those. Some of them are harder than others, such as making sense of a conversation when you start listening in the middle. In general, it’s working very well. We can gather just about every bit of information about queries that mysql-proxy can, making this a viable way to monitor servers without the disadvantages of a proxy. In theory, we can gather ALL the same information, but in practice we are going for the 95% case.

[read more...]

With the growing adoption of Google’s User Statistics Patch**, the need for supporting scripts has become clear. To that end, we’ve created check-unused-keys, a Perl script to provide a nicer interface than directly querying the INFORMATION_SCHEMA database.

[read more...]

The web is going the way of utf8.  Drizzle has chosen it as the default character set, most back-ends to websites use it to store text data, and those who are still using latin1 have begun to migrate their databases to utf8.  Googling for “mysql convert charset to utf8″ results in a plethora of sites, each with a slightly different approach, and each broken in some respect.  I’ll outline those approaches here and show why they don’t work, and then present a script that can generically be used to convert a database (or set of tables) to a target character set and collation.

[read more...]