One of item I always look at SHOW ENGINE INNODB STATUS to see if there are any transactions spending very long time in ACTIVE state. In the perfect world if you’re running online system you should not see transactions spending more than couple of seconds in ACTIVE state. Especially ACTIVE transactions which do not currently run any query are suspicious. There are however cases when screaming fire about ACTIVE transactions alone would be misleading. There is a whole set of applications which run quite fine while having ACTIVE measured in hours. It is JAVA applications which often run in AUTOCOMMIT=0 mode and do not explicitly commit transactions unless there were any writes. If database is configured in READ-COMMITTED transaction mode it is actually fine from performance point of view as Innodb does not have to preserve row versions going back to start of transactions.

There is however a better metric, which in most cases will …

At Percona Live last week, someone showed me a graph from their Cacti monitoring system, using the templates that I wrote. It was the buffer pool pages read, written, and created. He asked me if the graph was okay. Shouldn’t there be a lot more pages read than written, he asked? It’s a great question.

I’ve blogged before about the danger of trying to interpret ratios. Ratios are not good ways to discover whether systems are healthy. So, why graph them, then?

First, let me say that the graph actually doesn’t show a ratio — it just shows the absolute values of the reads, writes, and creates per second, stacked on top of each other. The person was mentally comparing them and creating a ratio from them. But there’s no …

MySQL 5.5 and Percona Server 5.5 do not solve all scalability problems even for read only workloads. Workloads which got a lot of attention such as Sysbench and DBT2/TPC-C scale pretty well a they got a lot of attention, there can be other quite typical workloads however which do not scale that well. This is why it is important to test performance and scalability for your application on your hardware if you really want results most relevant for your situation.

In this example I spotted the query pattern responsible for large portion of the load in the application by aggregating query log with mk-query-digest. When I filtered out only queries of this pattern and got a simplified part of production workload which focuses only on one query but yet mimics real world values distribution.

The query looks something like this:

SELECT *  FROM table WHERE deleted = 0 AND group_id IN (62715996, 62716592, 62717660, …

InnoDB compression is getting some traction, and I see quite contradictory opinions. Someone has successful deployments in productions, and someone says that compression in current implementation is useless.
To get some initial impression about performance I decided to run some sysbench with multi-tables benchmarks.
I actually was preparing to do complex research, but even first initial results are quite discouraging.

My setup: Dell PowerEdge R900, running Percona-Server-5.1.57-rel12.8 (will be in public release soon), storage is FusionIO 320GB MLC card, which does not matter a lot in this case of CPU-bound benchmark.

First stage – load data. Scripts for multi-table sysbench allow to load data in …

Few weeks ago I wrote about Innodb Caching with main idea you might need more cache when you think you are because Innodb caches data in pages, not rows, and so the whole page needs to be in memory even if you need only one row from it. I have created the simple benchmark which shows a worse case scenario by picking the random set of primary key values from sysbench table and reading them over and over again.

This time I decided to “zoom in” on the time when result drop happens – 2x increase in number of rows per step hides a lot of details, so I’m starting with some number of rows when everything was still in cache for all runs and increasing number of rows being tested 20% per step. I’m trying standard Innodb page size, 4KB page size as 16K page size compressed to 4. The data in this case compresses perfectly (all pages …

I started going over the slides from talks from MySQL Conference and Expo 2011 to pick set of must see presentations and publish the list, but this is not happening due to lack of time. Instead I’m only going to recommend 1 talk, from list of tutorials I had a chance to review. If you have not check it out yet, take a look at Linux and Hardware Optimizations by Yoshinori Matsunobu. Check out the slides which are very well done with many graphs and verbose enough explanations so you can easily follow slides alone.

Were you excited by some other talk on MySQL Conference and Expo ? Feel free to leave it as a comment to this post.

In a recent blog post, I wrote about four fundamental metrics for system performance analysis. These are throughput, residence time, “weighted time” (the sum of all residence times in the observation period — the terminology is mine for lack of a better name), and concurrency.

I derived all of these metrics from two “even more fundamental” performance metrics, which I’ll call the “basic” metrics. That’s what this post is about. But it doesn’t stop there, because my list of four fundamental metrics isn’t the only thing you can derive from the two basic metrics. In fact, these two basic metrics encode a tremendous amount of information, from which you can compute lots of other things. And by the way, it’s a good thing the two basic metrics really are basic, because beyond “fundamental” and …

We’ve just rented more rooms, and published an additional two tracks of speakers for Percona Live in New York on May 26th. The schedule is here. There is a long queue of speaker submissions we’re finalizing and will be adding to the schedule, to fill the few empty slots in those new rooms.

My favorite not-yet-confirmed session is from a company who has built their business in the Amazon cloud, and has seen just about every angle of running a large database in the cloud. This isn’t an extraordinary database, all things considered — as they told me, “it’s not a science fiction use case. It’s just science fiction to run it in the cloud.” That is precisely why this is such an interesting story to hear. There is a lot of wisdom to be gleaned from people who’ve done such things.

Tickets are selling fast, and we still expect to …

My previous benchmark on Performance Schema was mainly in memory workload and against single tables.
Now after adding multi-tables support to sysbench, it is interesting to see what statistic we can get from workload that produces some disk IO.

So let’s run sysbench against 100 tables, each 5000000 rows (~1.2G ) and buffer pool 30G.

The scripts and results are on Benchmark Wiki.

If we look on performance overhead it appears rather big in read-only benchmark, and it is well explained in …

There are many ways to slice and aggregate metrics of activity on a system such as MySQL. In the best case, we want to know everything about the system’s activity: we want to know how many things happened, how big they were, and how long they took. We want to know precisely when they happened. We want to know resource consumption, instantaneous status snapshots, and wait analysis and graphs. These things can be expensive to measure, and even more importantly, they can require a lot of work to analyze. And sometimes the analysis is only valid when very stringent conditions are satisfied. What metrics can we gather that provide the most value for the effort?

There’s a relationship known as Little’s Law (sometimes erroneously referred to as the fundamentals of queueing theory), that lays out some simple properties between various metrics. One of the …