August 28, 2014

ext4 vs xfs on SSD

As ext4 is a standard de facto filesystem for many modern Linux system, I am getting a lot of question if this is good for SSD, or something else (i.e. xfs) should be used. Traditionally our recommendation is xfs, and it comes to known problem in ext3, where IO gets serialized per i_node in O_DIRECT […]

Beware: ext3 and sync-binlog do not play well together

One of our customers reported strange problem with MySQL having extremely poor performance when sync-binlog=1 is enabled, even though the system with RAID and BBU were expected to have much better performance. The problem could be repeated with SysBench as follows:

Rotating MySQL slow logs safely

This blog post is part two of two. Like part one, published Wednesday, this is a cross-post from Groupon’s engineering blog. Thanks again to Kyle Oppenheim at Groupon. And one more reminder that I’ll be at the Percona Live MySQL Conference and Expo next week in Santa Clara, California so look for me there. You […]

Binary log file size matters (sometimes)

I used to think one should never look at max_binlog_size, however last year I had a couple of interesting cases which showed that sometimes it may be very important variable to tune properly. I meant to write about it earlier but never really had a chance to do it. I have it now!

Make your file system error resilient

One of the typical problems I see setting up ext2/3/4 file system is sticking to defaults when it comes to behavior on errors. By default these filesystems are configured to Continue when error (such as IO error or meta data inconsistency) is discovered which can continue spreading corruption. This manifests itself in a worst way […]

Recovery after DROP [ TABLE | DATABASE ]

In your recovery practice we often face the problem when data lost by execution DROP TABLE or DROP DATABASE statement. In this case even our InnoDB Data Recovery tool can’t help, as table / directory with files was deleted (if you have innodb-file-per-table). And the same for MyISAM, all .MYD / .MYI / .frm – […]

Slow DROP TABLE

It is a known fact that ext3 is not the most efficient file system out there and for example file removals can be painfully slow and cause a lot of random I/O. However, as it turns out, it can sometimes have a much more severe impact on the MySQL performance that it would seem. When […]

read_ahead (disabled) as steroid

Last week we were busy to align XtraDB performance with 5.4, now we have some results. Currently it is available as “hacks” to XtraDB (available on Lauchpad lp:~percona-dev/percona-xtradb/hacks-porting-tune if you are interested). Basically we took improvements from 5.4 and backported ones performance related to XtraDB. Here are results for tpcc-like workload, 100W (~10GB) ( raw […]

Working with many files and file system fragmentation

Working on performance optimization project (not directly MySQL related) we did a test – creating 100 files writing 4K in the random file for some time and when checking the read speed on the files we end up with, compared to just writing the file sequentially and reading it back. The performance difference was huge […]

Innodb Performance Optimization Basics

Note: There is an updated post on this topic here. Interviewing people for our Job Openings I like to ask them a basic question – if you have a server with 16GB of RAM which will be dedicated for MySQL with large Innodb database using typical Web workload what settings you would adjust and interestingly […]