Have you wanted to compress only certain types of columns in a table while leaving other columns uncompressed? While working on a customer case this week I saw an interesting problem where a table had many heavily utilized TEXT fields with some read queries exceeding 500MB (!!), and stored in a 100GB table. In this [...]
Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 17 Comments
I have been working for a customer benchmarking insert performance on Amazon EC2, and I have some interesting results that I wanted to share. I used a nice and effective tool iiBench which has been developed by Tokutek. Though the “1 billion row insert challenge” for which this tool was originally built is long over, [...]
Connecting orphaned .ibd files
May 13, 2011 By 19 Comments
There are two ways InnoDB can organize tablespaces. First is when all data, indexes and system buffers are stored in a single tablespace. This is typicaly one or several ibdata files. A well known innodb_file_per_table option brings the second one. Tables and system areas are split into different files. Usually system tablespace is located in [...]
MySQL 5.5.8 – in search of stability
January 3, 2011 By 11 Comments
A couple of days ago, Dimitri published a blog post, Analyzing Percona’s TPCC-like Workload on MySQL 5.5, which was a response to my post, MySQL 5.5.8 and Percona Server: being adaptive. I will refer to Dimitri’s article as article [1]. As always, Dimitri has provided a very detailed and thoughtful article, and I strongly recommend reading if [...]
Lost innodb tables, xfs and binary grep
November 9, 2010 By 12 Comments
Before I start a story about the data recovery case I worked on yesterday, here’s a quick tip – having a database backup does not mean you can restore from it. Always verify your backup can be used to restore the database! If not automatically, do this manually, at least once a month. No, seriously [...]
Reasons for run-away main Innodb Tablespace
June 10, 2010 By 2 Comments
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 [...]
Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
InnoDB has an oft-unused parameter innodb_concurrency_tickets that seems widely misunderstood. From the docs: “The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread [...]
How innodb_open_files affects performance
November 18, 2009 By 4 Comments
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 [...]
Tuning for heavy writing workloads
October 14, 2009 By 9 Comments
For the my previous post, there was comment to suggest to test db_STRESS benchmark on XtraDB by Dimitri. And I tested and tuned for the benchmark. I will show you the tunings. It should be also tuning procedure for general heavy writing workloads. At first, <tuning peak performance>. The next, <tuning purge operation> to stabilize [...]
Impossible – possible, moving InnoDB tables between servers
June 8, 2009 By 9 Comments
This is probably the feature I missed most from early days when I started to use InnoDB instead of MyISAM. Since that I figured out how to survive without it, but this is first question I hear from customers who migrated from MyISAM to InnoDB – can I just copy .ibd files from one server [...]
