MySQL query slow querying table on primary key

Question

So I have a table that's being used basically like a NoSQL setup. The structure is:

id bigint primary key data mediumblob modified timestamp

It has around 350k rows. The queries that run on it are all structured as follows:

select data from table where id=XXX;

The table engine is InnoDB. I'm noticing that sometimes queries run against this table are rather slow. Sometimes they take 3 seconds to run. The table is 3 GB on disk and I gave the innodb_buffer_pool_size 4G.

Is there anything I'm missing here? Are there any settings I can tweak to improve performance?

Edit: As requested explain 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 issues that I see here initially. First is that you have a query with a blob data type. This will cause speed issues when it comes to data retrieval. Second, you are using InnoDB, which is optimized for writing. This means that while it is probably the best choice overall, in extreme read situations it might be less performant than MyISAM. Neither of these issues are necessarily deal-killers but they do each add a performance hit. Beyond this, however, I'm not sure I can give you a good answer as to what you can do to better optimize without first having you do profiling. That is what I would recommend you do first. Profile your query to figure out what the execution plan is and then identify why the execution plan is so slow.

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

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

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

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

Based on the CREATE TABLE statement you provided, I did think of another thing that you should address (again, not a query-killer but it is another performance hit). Unless there is a business case for using a bigint for your ID field, choose an int instead. An int will allow 2.1 billion rows so you shouldn't run out of numbers. Making this switch will save you disk space and it will 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 post your CREATE TABLE statement as well as the output of EXPLAIN select data from table where id=XXX? How is the io wait on the system?

My best guess is that you're IO bound and because the rows aren't all the same size, it's having to search through the data. You have enough memory that it should be able to keep the data cached. This link describes some low level profiling in MySQL that might be helpful.

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

Answer 3

Try using the minimum size of id as possible. If it's a numeric key that you know will never be larger than a few million, you could use a MEDIUMINT UNSIGNED and save yourself a byte for each record over an INT, which might speed up searches a little. Still, 3 GB is an awful lot for just 350,000 rows.

It sounds like you might also get some bang for your buck by using the partitioning feature to split your table up into logical units. You might want to Google "mysql vertical partitioning" in particular; if there are large columns that you don't access frequently, it would be much more efficient to move them out into a separate table and only query it when you need it.

Answer 4

Things I would look for:

• when are the slow queries appearing?

  • is it after a fresh start of the DB? then this might be just a temporary problem - queries hitting in a cold cache
  • is it during DB dump/load? - then change your backup policies - use replication for example, or add more disk IO (adding more disks in RAID, change disks to SSD, repartition your system on multiple disks, etc)
  • is it during peak read/write times? replication might also help here - write into master and load balance the reads between master and slaves
• Also - is that mediumblob really necessary there?