This is the second blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take a look at [...]
Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
One of the most painful troubleshooting tasks with MySQL is troubleshooting memory usage. The problem usually starts like this – you have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL takes much more memory than you would expect, causing swapping or other [...]
Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
Overview Profiling, analyzing and then fixing queries is likely the most oft-repeated part of a job of a DBA and one that keeps evolving, as new features are added to the application new queries pop up that need to be analyzed and fixed. And there are not too many tools out there that can make [...]
Dynamic row format for MEMORY tables
September 6, 2011 By 5 Comments
The latest Percona Server release has one new feature: now MEMORY tables can have BLOB and TEXT columns, and VARCHAR columns will not waste space due to implicit extension to CHAR.
MySQL Partitioning – can save you or kill you
December 11, 2010 By 44 Comments
I wanted for a while to write about using MySQL Partitioning for Performance Optimization and I just got a relevant customer case to illustrate it. First you need to understand how partitions work internally. Partitions are on the low level are separate table. This means when you’re doing lookup by partitioned key you will look [...]
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 [...]
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 [...]
MySQL-Memcached or NOSQL Tokyo Tyrant – part 3
October 19, 2009 By 25 Comments
This is part 3 of our series. In part 1 we talked about boosting performance with memcached on top of MySQL, in Part 2 we talked about running 100% outside the data with memcached, and now in Part 3 we are going to look at a possible solution to free you from the database. The [...]
Quick comparison of MyISAM, Infobright, and MonetDB
September 29, 2009 By 30 Comments
Recently I was doing a little work for a client who has MyISAM tables with many columns (the same one Peter wrote about recently). The client’s performance is suffering in part because of the number of columns, which is over 200. The queries are generally pretty simple (sums of columns), but they’re ad-hoc (can access [...]
How number of columns affects performance ?
September 28, 2009 By 11 Comments
It is pretty understood the tables which have long rows tend to be slower than tables with short rows. I was interested to check if the row length is the only thing what matters or if number of columns we have to work with also have an important role. I was interested in peak row [...]
