MySQL slow primary key query table

Question

So, I have a table that is used mainly as a NoSQL installation. Structure:

id bigint primary key data mediumblob modified timestamp

It has about 350 thousand lines. The queries performed on it are structured as follows:

select data from the table where id = XXX;

The table engine is InnoDB. I notice that sometimes queries against this table are pretty slow. Sometimes they need 3 seconds. The table is 3 GB on disk, and I gave innodb_buffer_pool_size 4G.

Is there something I'm missing here? Are there any settings that I can tune to improve performance?

Edit: on request, explain the output:

+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | cache | const | PRIMARY | PRIMARY | 8 | const | 1 | | +----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+ 

create table:

 CREATE TABLE `cache` ( `id` bigint(20) unsigned NOT NULL DEFAULT '0', `data` mediumblob, `modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 

Answer 1

There are two questions that I see here initially. First, you have a query with a blob data type. This will lead to performance issues when it comes to getting data. Secondly, you are using InnoDB, which is optimized for writing. This means that while this is probably the best choice overall, in extreme reading situations it may be less productive than MyISAM. None of these problems are transaction killers, but each of them improves productivity. However, beyond that, I'm not sure I can give you a good answer about what you can do to better optimize without first having to do profiling. This is what I would recommend you do first. Profile your query to find out what the execution plan is, and then determine why the implementation plan is so slow.

Here is a good "Top 10" list of MySQL optimizations. At least the couple is directly applicable in your situation:

http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/

Here is another good optimization article that also goes into server settings (specifically for InnoDB):

http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

Based on the CREATE TABLE statement you provided, I thought of another thing that you should specify (again, this is not a query killer, but a different result). If there is no business case for using bigint for your ID field, select int instead. Int will contain 2.1 billion lines, so you should not end numbers. Creating this switch will save you disk space and improve query performance. Here is an article about it:

http://ronaldbradford.com/blog/bigint-v-int-is-there-a-big-deal-2008-07-18/

Answer 2

Could you place your CREATE TABLE statement as well as the EXPLAIN select data from table where id=XXX output EXPLAIN select data from table where id=XXX ? How do io wait on the system?

My best guess is that you are tied to IO and because the strings are not the same size, but the need to search for data. You have enough memory to store cached data. This link describes some low-level profiling in MySQL that may be useful.

http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html

Answer 3

Try using the minimum identifier size. If this is a numeric key, which, as you know, will never be more than a few million, you can use MEDIUMINT UNSIGNED and save a byte for each INT record, which can speed up the search a bit. However, 3 GB is a lot for just 350,000 lines.

It looks like you can also get some sigh for your dollar, using the split function to split the table into logical units. You might want to google "mysql vertical partitioning" in particular; if there are large columns that you don’t often access, it would be much more efficient to move them to a separate table and query it only when you need it.

Answer 4

Things I would like to find:

• when do slow queries appear?

  • Is this after a new start to the database? then this can only be a temporary problem - requests that fall into the cold cache
  • Is this at boot / boot DB? - then change your backup policies - for example, use replication or add more I / O to the disk (add more disks to RAID, change disks to SSD, redistribute your system on several disks, etc.).
  • Is this during peak read / write? replication can also help here - write to the master and balancing read balance between the master and slaves
• Besides that, what is really needed there?