4

As soon as I include a "longtext" type in the select clause, the query times go from 8s to 3min (Amazon RDS t2.small). The longtext isn't used in the where clause, and the result set is empty. See below:

mysql> select id from mbp_process where errorAcknowledged='N' and (exitCode != 0 or exitCode is null);
Empty set (8.03 sec)

mysql> select id, stdoutContents from mbp_process where errorAcknowledged='N' and (exitCode != 0 or exitCode is null);
Empty set (3 min 43.36 sec)

What boggles my mind is that asking for the longtext column by primary key is fast:

select stdoutContents from mbp_process where id = 49213;
...
1 row in set (0.00 sec)

Why is this? The effect is less pronounced on my bare-metal server: the query slows from 0.2s to 1:05m there.

This is the EXPLAIN on the "select id from..." query:

+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+
| id | select_type | table       | type | possible_keys                                       | key                        | key_len | ref   | rows  | Extra                              |
+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+
|  1 | SIMPLE      | mbp_process | ref  | idx_mbp_process_exitCode,idx_mbp_process_errorAcked | idx_mbp_process_errorAcked | 2       | const | 22551 | Using index condition; Using where |
+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+

This is the EXPLAIN from the "select id, stdoutContents from ..." query:

+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+
| id | select_type | table       | type | possible_keys                                       | key                        | key_len | ref   | rows  | Extra                              |
+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+
|  1 | SIMPLE      | mbp_process | ref  | idx_mbp_process_exitCode,idx_mbp_process_errorAcked | idx_mbp_process_errorAcked | 2       | const | 22552 | Using index condition; Using where |
+----+-------------+-------------+------+-----------------------------------------------------+----------------------------+---------+-------+-------+------------------------------------+

They are identical.

This is the create table statement from "SHOW CREATE TABLE mbp_process":

CREATE TABLE `mbp_process` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `command` varchar(1000) DEFAULT NULL,
  `pid` varchar(45) DEFAULT NULL,
  `state` varchar(45) NOT NULL,
  `exitCode` int(11) DEFAULT NULL,
  `stdoutContents` longtext,
  `stdoutTruncated` char(1) DEFAULT NULL,
  `stdoutFilename` varchar(200) DEFAULT NULL,
  `stderrContents` longtext,
  `stderrTruncated` char(1) DEFAULT NULL,
  `stderrFilename` varchar(200) DEFAULT NULL,
  `majorProgress` varchar(45) DEFAULT NULL,
  `minorProgress` varchar(45) DEFAULT NULL,
  `startTime` datetime DEFAULT NULL,
  `endTime` datetime DEFAULT NULL,
  `errorAcknowledged` char(1) DEFAULT 'N',
  `errorComments` text,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mbp_process_command` (`command`(767)),
  KEY `idx_mbp_process_exitCode` (`exitCode`),
  KEY `idx_mbp_process_state` (`state`),
  KEY `idx_mbp_process_errorAcked` (`errorAcknowledged`)
) ENGINE=InnoDB AUTO_INCREMENT=50184 DEFAULT CHARSET=latin1

Choosing another column to include doesn't slow the query:

mysql> select id, created from mbp_process where errorAcknowledged='N' and (exitCode != 0 or exitCode is null);
Empty set (1.69 sec)

This is very weird: if I include "stderrContents", the query is fast. This is also a longtext column, although it generally has much less data in it. However, I'm not asking MySQL to examine the contents of the column, and the result set is empty, so why is it slow for "stdoutContents"?

mysql> select id, stderrContents from mbp_process where errorAcknowledged='N' and (exitCode != 0 or exitCode is null);
Empty set (0.57 sec)
  • Less pronounced? 8s to 3:43min on Amazon is actually a better scaling than from 0.2s to 1:05min on your bare-metal server. – Colin 't Hart Apr 15 '15 at 9:04
  • 1
    Is id your clustered index? Can you post the execution plans for the querys? Is it also slow if you select another column than stdoutcontents and id? – Tom V Apr 15 '15 at 9:33
  • 1
    Show us the SHOW CREATE TABLE mbp_process; output. – ypercubeᵀᴹ Apr 15 '15 at 9:48
  • EXPLAIN on both SELECTS will be key to see the difference, as @TomV hints, it is probably a question of covering index + extra bytes sent. – jynus Apr 15 '15 at 11:44
  • yes that was the line of thinking, selecting id = clustered index, so no bookmark lookup and any kind of bookmark lookup for another field resulting in a table scan because of low selectivity – Tom V Apr 15 '15 at 12:00
3

PRIMARY KEY(id) says that it is clustered with the data. That is not the issue. Nor are the attempts at using a secondary index. Here's what is happening.

In InnoDB, usually all columns are next to the PRIMARY KEY in the BTree that is indexed by the PK. However "big" columns are put elsewhere. At most about 8KB of the row is kept together as you described. The big columns are in their own 16KB block(s). These columns include any TEXT/BLOB, or even long VARCHAR/VARBINARY columns. (The details vary with innodb_file_format and a couple other things. For example, the first 767 bytes of a big column might left with the short columns.)

So, when selecting columns that exclude such big columns, the query will avoid fetching those extra blocks and be relatively fast. It sounds like stdoutContents is really big (multiple 16KB blocks needed)?

Amazon vs bare metal: If I am not mistaken, Amazon stores the data on a SAN-like system, not the same box.

Another thing I see... The EXPLAIN says it is using this secondary key

KEY `idx_mbp_process_errorAcked` (`errorAcknowledged`)

Each secondary key implicitly includes the PK (id). So, the processing is something like:

  • Drill into secondary key for the first entry with errorAcknowledged='N'
  • Scan forward in that BTree. This is the most efficient step. There are probably more than 100 'rows' per 16KB block.
  • For each of those, use the id to reach into the "data" BTree. (1 block per row, hopefully less, if they are cached nicely)
  • Check the rest of the WHERE clause: and (exitCode != 0 or exitCode is null)
  • If the row is still interesting, fetch the desired local columns (id, created, and maybe stderrContents), and
  • Reach over into the 'big' storage for the stdoutContents (if you are asking for it). This is likely not to be cached and possibly involves many disk hits.

I hope that explains it all. Let me know if you need further clarification.

  • Thanks. Is there any way to speed up my query? Perhaps use MyISAM for this table? – Graeme Pyle Apr 17 '15 at 12:03
  • MyISAM might help. Big fields are stored with the record - usually. If there is a lot of 'churn' (deletes/updates, then more inserts) the rows become fragmented. That would lead to lots of I/O. – Rick James Apr 17 '15 at 14:49
0

Since I/O is the main slowdown, and since you have two LONGTEXT fields (that are long), here is another approach to speeding things up.

In the client, compress the *Contents fields and put them into MEDIUMBLOBs. Ordinary text compresses 3:1; those particular types of text might compress more.

Similarly, uncompress in the client. This avoids the network overhead and offloads the server.

This technique is useful for InnoDB or MyISAM. And it is orthogonal to other solutions.

Another thought -- pay for more IOPS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.