October 25, 2014

Reasons for run-away main Innodb Tablespace

So you’re running MySQL With innodb_file_per_table option but your ibdata1 file which holds main (or system) tablespace have grown dramatically from its starting 10MB size. What could be the reason of this growth and what you can do about it ? There are few things which are always stored in main tablespace – these are […]

How innodb_open_files affects performance

Recently I looked at table_cache sizing which showed larger table cache does not always provides the best performance. So I decided to look at yet another similar variable – innodb_open_files which defines how many files Innodb will keep open while working in innodb_file_per_table mode. Unlike MyISAM Innodb does not have to keep open file descriptor […]

Statistics of InnoDB tables and indexes available in xtrabackup

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 […]

A few administrative updates

I wanted to write a few administrative updates in one so I didn’t spam everyone’s feed readers too much. Here we go: We’ve had reports of some lost comments.  We reported this via Twitter a while ago, but thought it was fixed.  We’ll try and pay more attention to spam filtering, but we wanted to […]

Compression for InnoDB backup

Playing with last version of xtrabackup and compress it I noticed that gzip is unacceptable slow for both compression and decompression operations. Actually Peter wrote about it some time ago, but I wanted to review that data having some new information. In current multi-core word the compression utility should utilize several CPU to speedup operation, […]

The perils of InnoDB with Debian and startup scripts

Are you running MySQL on Debian or Ubuntu with InnoDB? You might want to disable /etc/mysql/debian-start. When you run /etc/init.d/mysql start it runs this script, which runs mysqlcheck, which can destroy performance. It can happen on a server with MyISAM tables, if there are enough tables, but it is far worse on InnoDB. There are […]

Some little known facts about Innodb Insert Buffer

Despite being standard Innodb feature forever Insert Buffers remains some kind of mysterious thing for a lot of people, so let me try to explain thing a little bit. Innodb uses insert buffer to “cheat” and not to update index leaf pages when at once but “buffer” such updates so several updates to the same […]

MySQL 5.0, 5.1 and Innodb Plugin CPU Efficiency

We’ve recently done benchmarks comparing different MySQL versions in terms of their CPU efficiently in TPC-C like Workload. We did it couple of weeks ago so MySQL 5.0.67, MySQL 5.1.29 and Innodb Plugin 1.0.1 were used which are not very recent, though we do not think results will differ a lot with today versions.

Testing InnoDB “Barracuda” format with compression

New features of InnoDB – compression format and fast index creation sound so promising so I spent some time to research time and sizes on data we have on our production. The schema of one of shards is

Updated msl (microslow) patch, installation walk-through!

For a couple of months there have been no updates to our msl patch, however recently I managed some time to change this. The functionality was extended a little bit and what’s even more important the patch is available for all the recent MySQL releases. To remind anyone who has not yet come across this […]