October 20, 2014

MySQL server memory usage troubleshooting tips

There are many blog posts already written on topics related to “MySQL server memory usage,” but nevertheless there are some who still get confused when troubleshooting issues associated with memory usage for MySQL. As a Percona support engineer, I’m seeing many issues regularly related to heavy server loads – OR OOM killer got invoked and […]

Virident vCache vs. FlashCache: Part 2

This is the second part in a two-part series comparing Virident’s vCache to FlashCache. The first part was focused on usability and feature comparison; in this post, we’ll look at some sysbench test results. Disclosure: The research and testing conducted for this post were sponsored by Virident. First, some background information. All tests were conducted […]

Why you should ignore MySQL’s key cache hit ratio

I have not caused a fist fight in a while, so it’s time to take off the gloves. I claim that somewhere around of 99% of advice about tuning MySQL’s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key buffer hit ratio, and […]

Beware of MyISAM Key Cache mutex contention

Today I was working with the client loading data to MyISAM tables at very high rate. Hundreds of millions rows are loaded daily into single MySQL instance with bursts up to 100K of records/sec which need to be inserted (in the table with few indexes). It was good not all records had to go to […]

The MySQL optimizer, the OS cache, and sequential versus random I/O

In my post on estimating query completion time, I wrote about how I measured the performance on a join between a few tables in a typical star schema data warehousing scenario. In short, a query that could take several days to run with one join order takes an hour with another, and the optimizer chose […]

How much overhead is caused by on disk temporary tables

As you might know while running GROUP BY and some other kinds of queries MySQL needs to create temporary tables, which can be created in memory, using MEMORY storage engine or can be created on disk as MYISAM tables. Which one will be used depends on the allowed tmp_table_size and also by the data which […]

Long PRIMARY KEY for Innodb tables

I’ve written and spoke a lot about using short PRIMARY KEYs with Innodb tables due to the fact all other key will refer to the rows by primary key. I also recommended to use sequential primary keys so you do not end up having random primary key BTREE updates which can be very expensive. Today […]

Cache Performance Comparison

Jay Pipes continues cache experiements and has compared performance of MySQL Query Cache and File Cache. Jay uses Apache Benchmark to compare full full stack, cached or not which is realistic but could draw missleading picture as contribution of different components may be different depending on your unique applications. For example for application containing a […]

What MySQL buffer cache hit rate should you target

“What cache hit rate is good for optimal MySQL Performance” is typical question I’m asked. It could by MyISAM key_buffer or Innodb innodb_buffer_pool it does not really matter. In both cases trying to come up with constant “good” hit rate is looking for trouble. Well of course you can name 100% hit rate and all […]

Using MyISAM in production

There were recently number of posts about MyISAM, for example Arjen wrote pretty nice article about MyISAM features so I thought I would share my own view on using MyISAM in production. For me it is not only about table locks. Table locks is only one of MyISAM limitations you need to consider using it […]