The latest from the MySQL community

So, I recently ran into one of those situations where a customer complains that his MySQL database is slow, and “it worked great until about two weeks ago”. The first thing I look at in these situations is not the queries or any code, but the indexes. Once I determined that the indexes were almost nonsensical, I had a look at the queries themselves, which proved that the indexes were, in fact, nonsensical. Running the queries as written in the code, from a mysql shell, with EXPLAIN, I was able to further confirm that the indexes (most of them, anyway) were never used.

Easy right? Just reindex the table!

NOTE: I’m going to skip the talk about all of the database design issues on this gig and just go straight to solving the immediate problem at hand. Just know that I had nothing to do with the design.

But, supposing this table has 15 million rows and is running on a machine with 2GB of RAM and only …

I’ve created a set of Cacti templates for graphing stats about MySQL. While these were based on several other people’s work, there are many improvements. One of them in particular I want to bring up, and I’ll go so far as to say it ought to be a “best practice” for graphing. [...]

I presented these at an internal MySQL professional services meeting about a month ago… its mostly a hodge podge of various benchmarks… but enjoy! The big difference in these benchmarks vs the other benchmarks is I am testing on the memoright GT drive, which is supposed to be one of the fastest SLC drives out their currently. Lets get right too it:

Looking at sysbench Random read/write iops:

A lot of my clients are in a position where their database performance is deteriorating but they are not “big enough” (or not willing/able to) explore sharding all of their data structures. They’re too big for the solution to be adding another read slave, but too small to justify the resources for re-designing their [...]

GreenSQLOpenSource is becoming a popular product. We have received a number of inquiries from our users asking about the database firewall performance. In addition, in the new version, we have incorporated numerous optimization patches. In order to get a feel of the gap, we decided to conduct several performance tests and publish their results.

As you probably know, GreenSQLOpenSource works as an SQL proxy. As a result, the system adds additional layer to your existing application infrastructure. In addition, from the network point of view, GreenSQLOpenSource db firewall creates another network socket when connecting to the genuine MySQL server. All this adds some overhead.

We have tested the system using the speedy configuration. Application logs were disabled. In addition, we have build a list of allowed SQL patterns so the system was running in the firewall mode. As a a result, no risk calculation were performed during our …

I just wrote about how Maatkit has gotten some new features for helping people make sure their slaves have an exact copy of the master’s data. This work was largely sponsored by one of Percona’s customers, whose name I have permission to mention: Vantage Media.

Thanks Vantage! Your sponsorship helps many people and businesses.

What’s the new libdrizzle? It’s a complete rewrite of the client library for the Drizzle project, but it also has full support for the current MySQL protocol (4.1+). Right now Drizzle uses the same protocol as MySQL, but work is being done to design a new, more robust protocol. Even when the Drizzle protocol changes, I plan to keep full support for the MySQL protocol since there is a need for a good low-level non-blocking client library. Also, once libdrizzle turns into a full protocol library (server and client packets) it could make for some interesting Drizzle/MySQL hybrid proxies.

Over the weekend I made a lot of progress on the new library, and last night I just finished up the first pass at the core functionality. It can now do full query and result processing. Currently only non-cached results are being used (no store_result() function), but the cached interfaces will be …

This is a post about SYSDATE() and NOW() and CURRENT_TIMESTAMP() functions in MySQL.

Firstly, note is that of these three, only CURRENT_TIMESTAMP() is part of the SQL Standard. NOW() happens to be an alias for CURRENT_TIMESTAMP() in MySQL.

Secondly, note that replication does not work well with non-deterministic functions. And “hey, what time is it?” is non-deterministic. Ask it twice, with a second apart between asking, and both times you get different results (with at least second precision).

You can start to see the problem here….but there’s more…. (more…)

It's a balancing act between high-volume community adoption and enterprise differentiation. But if you do it right, the impact is huge READ MORE

Quite often we need to perform a so-called “MySQL instance audit”. This common DBA procedure should give you a general view of the MySQL environment. You may be interested in a basic understanding of what kind of operation MySQL performs, how much memory does it use, or how well does it look from the performance point of view. There is no easy out-of-the-box way to do such an audit on a MySQL server. You can use SHOW STATUS and check the list of system variables, but this way can hardly be called DBA-friendly.

Fortunately there are several tools to make this process easier. Among most popular are mysqlreport and MySQLTuner. In this post I’d like to give a brief overview of MySQLTuner.

So, what can MySQLTuner do? Quoting the documentation: “MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for …