Speeding up SELECT FROM a large table

Question

I have a large table (500M rows with ibd size of 1.5TB)

CREATE TABLE t1
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
ChildID int(11) unsigned NOT NULL,
Number smallint(5) unsigned,
Title varchar(255) COMPRESSED,
Text mediumtext COMPRESSED,
UNIQUE INDEX(ChildID,Number),
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

and I need to JOIN it with other tables. After long experimentations, I found reading from this table is slow. Then, I simply focused on simple SELECT.

SELECT * INTO OUTFILE '/tmp/test1.csv' 
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' 
  FROM t1 LIMIT 1000000

it takes over 700s with an output of 2.9GB file (a read speed of ~4MB/s, which is close to what was reported by iostat). I moved the database to another HDD, but the same results.

I want to use other engines like Aria or partitioning, but each INSERT INTO SELECT takes over 5 days.

1. Is there any trick or tips for improving the SELECT performance from a large table?
2. How can we find the IO bottleneck? I believe my HDD has a higher I/O capability.

Answer 1

If you select 1M rows, your query can't be fast. If you write the result on disk, the time is probably more than doubled.

But you say that writing to a different disk doesn't make the query any faster. That could be because the table reading and the file writing are not parallelised, I don't know. But it could also indicate that the bottleneck is the CPU decompressing the columns. Unfortunately you can't decide which compression algorithm will be used, it's always zlib.

Maybe you can try InnoDB page compression instead. You'll be able to try different algorithms (innodb_compression_algorithm) and compression levels (innodb_compression_level). Also if the pages you're reading are in the buffer pool, they're stored uncompressed, so less uncompressing work is needed.