MySQL Forums
Forum List  »  Performance

Speed of query on tables containing blob depends on filesystem cache
Posted by: Periklis Venakis
Date: February 29, 2012 03:08AM

Hello,
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)

Thanks in advance

Options: ReplyQuote


Subject
Views
Written By
Posted
Speed of query on tables containing blob depends on filesystem cache
3943
February 29, 2012 03:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.