Speed of mysql query on tables containing blob depends on filesystem cache
I have a table with approximately 120k rows, which contains a field with a BLOB (not more than 1MB each entry in size, usually much less). My problem is that whenever I run a query asking any columns on this table (not including the BLOB one), if the filesystem cache is empty, it takes approximately 40'' to complete. All subsequent queries on the same table require less than 1'' (testing from the command line client, on the server itself). The number of rows returned in the queries vary from an empty set to 60k+
I have eliminated the query cache so it has nothing to do with it. The table is myisam but I also tried to change it to innodb (and setting ROW_FORMAT=COMPACT), but without any luck.
If I remove the BLOB column, the query is always fast.
So I would assume that the server reads the blobs from the disk (or parts of them) and the filesystem caches them. The problem is that on a server with high traffic and limited memory, the filesystem cache is refreshed every once in a while, so this particular query keeps causing me trouble.
So my question is, is there a way to considerably speed things up, without removing the blob column from the table?
here are 2 example queries, ran one after the other, along with explain, indexes and table definition:
mysql> SELECT ct.score FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 100;
Empty set (48.21 sec)
mysql> SELECT ct.score FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 99;
Empty set (1.16 sec)
mysql> explain SELECT ct.score FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 99;
+----+-------------+-------+-------+---------------+--------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+-------+-------------+
| 1 | SIMPLE | ct | range | status,score | status | 768 | NULL | 82096 | Using where |
+----+-------------+-------+-------+---------------+--------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> show indexes from completed_tests;
+-----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| completed_tests | 0 | PRIMARY | 1 | id | A | 583938 | NULL | NULL | | BTREE | |
| completed_tests | 1 | users_login | 1 | users_LOGIN | A | 11449 | NULL | NULL | YES | BTREE | |
| completed_tests | 1 | tests_ID | 1 | tests_ID | A | 140 | NULL | NULL | | BTREE | |
| completed_tests | 1 | status | 1 | status | A | 3 | NULL | NULL | YES | BTREE | |
| completed_tests | 1 | timestamp | 1 | timestamp | A | 291969 | NULL | NULL | | BTREE | |
| completed_tests | 1 | archive | 1 | archive | A | 1 | NULL | NULL | | BTREE | |
| completed_tests | 1 | score | 1 | score | A | 783 | NULL | NULL | YES | BTREE | |
| completed_tests | 1 | pending | 1 | pending | A | 1 | NULL | NULL | | BTREE | |
+-----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> show create table completed_tests;
+-----------------+--------------------------------------
| Table | Create Table |
+-----------------+--------------------------------------
| completed_tests | CREATE TABLE `completed_tests` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`users_LOGIN` varchar(100) DEFAULT NULL,
`tests_ID` mediumint(8) unsigned NOT NULL DEFAULT '0',
`test` longblob,
`status` varchar(255) DEFAULT NULL,
`timestamp` int(10) unsigned NOT NULL DEFAULT '0',
`archive` tinyint(1) NOT NULL DEFAULT '0',
`time_start` int(10) unsigned DEFAULT NULL,
`time_end` int(10) unsigned DEFAULT NULL,
`time_spent` int(10) unsigned DEFAULT NULL,
`score` float DEFAULT NULL,
`pending` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `users_login` (`users_LOGIN`),
KEY `tests_ID` (`tests_ID`),
KEY `status` (`status`),
KEY `timestamp` (`timestamp`),
KEY `archive` (`archive`),
KEY `score` (`score`),
KEY `pending` (`pending`)
) ENGINE=InnoDB AUTO_INCREMENT=117996 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
1 row in set (0.00 sec)
I originally posted this on mysql query slow at first fast afterwards but I now have more information so I repost as a different question I also posted this on the mysql forum, but I haven't heard back
Thanks in advance as always
Answer
The design of BLOB (=TEXT) storage in MySQL seems to be totally flawed and counter-intuitive. I ran a couple of times into the same problem and was unable to find any authoritative explanation. The most detailed analysis I've finally found is this post from 2010: http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/
General belief and expectation is that BLOBs/TEXTs are stored outside main row storage (e.g., see this answer). This is NOT TRUE, though. There are several issues here (I'm basing on the article given above):
If the size of a BLOB item is several KB, it is included directly in row data. Consequently, even if you SELECT only non-BLOB columns, the engine still has to load all your BLOBs from disk. Say, you have 1M rows with 100 bytes of non-blob data each and 5000 bytes of blob data. You SELECT all non-blob columns and expect that MySQL would read from disk around 100-120 bytes per row, which is 100-120 MB in total (+20 for BLOB address). However, the reality is that MySQL stores all BLOBs in the same disk blocks as rows, so they all must be read together even if not used, and so the size of data read from disk is around 5100 MB = 5 GB - this is 50 times more than you would expect and means 50 times slower query execution.
Of course, this design has an advantage: when you need all the columns, including the blob one, SELECT query is faster when blobs are stored with the row than when stored externally: you avoid (sometimes) 1 additional page access per row. However, this is not a typical use case for BLOBs and DB engine should not be optimized towards this case. If your data is so small that it fits in a row and you're fine with loading it in every query no matter if needed or not - then you would use VARCHAR type instead of BLOB/TEXT.
Even if for some reason (long row or long blob) the BLOB value is stored externally, its 768-byte prefix is still kept in the row itself. Let's take the previous example: you have 100 bytes of non-blob data in each row, but now the blob column holds items of 1 MB each so they must be kept externally. SELECT of non-blob columns will have to read roughly 800 bytes per row (non-blobs + blob prefix), instead of 100-120 - this is again 7 times larger disk transfer than you'd expect, and 7x slower query execution.
External BLOB storage is ineffective in its usage of disk space: it allocates space in blocks of 16 KB and single block cannot hold multiple items, so if your blobs are small and take, for instance, 8 KB each, the actual space allocated is twice that large.
I hope this design will get fixed one day: MySQL will store ALL blobs - big and small - in external storage, without any prefixes kept in DB, with external storage allocation being efficient for items of all sizes. Before this happens, separating out BLOB/TEXT columns seems the only reasonable solution - separating out to another table or to the filesystem (each BLOB value kept as a file).
[UPDATE 2019-10-15]
InnoDB documentation provides now an ultimate answer to the issue discussed above:
https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html
The case of storing 768-byte prefixes of BLOB/TEXT values inline holds indeed for COMPACT row format. According to the docs, "For each non-NULL variable-length field (...) The internal part is 768 bytes".
However, you can use DYNAMIC row format instead. With this format:
"InnoDB can store long variable-length column values (...) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page. (...) TEXT and BLOB columns that are less than or equal to 40 bytes are stored in line."
Here, a BLOB value can occupy up to 40 bytes of inline storage, which is much better than 768 bytes as in the COMPACT mode, and looks like a lot more reasonable approach in the case you want to mix BLOB and non-BLOB types in a table and still be able to scan multiple rows pretty fast. Moreover, the extended (over 20 bytes) inline storage is used ONLY for values sized between 20-40 bytes; for larger values, only the 20-byte pointer is stored (no prefix), unlike in the COMPACT mode. Hence, the extended 40-byte storage is used rarely in practice and one can safely assume the average size of inline storage to be just 20 bytes (or less, if you tend to keep many small values of less than 20B in your BLOB). All in all, it seems DYNAMIC row format, rather than COMPACT, should be the default choice in most cases to achieve good predictable performance of BLOB columns in InnoDB.
An example how to check the actual physical storage in InnoDB can be found here:
https://dba.stackexchange.com/a/210430/177276
As to MyISAM, it apparently does NOT provide off-page storage for BLOBs at all (just inline). Check here for more info: