1

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.
3
  • Are some Joins running slow? If so, let's investigate; please provide the Select, the Explain and the other table's Create Table. Also, pulling a million rows out of a table does not sound like a normal thing; what's up?
    – Rick James
    Oct 21 '21 at 1:06
  • Do you need all the columns (SELECT *)? If you don't need Text, don't include it in the Join or the Outfile; it will run a lot faster.
    – Rick James
    Oct 21 '21 at 1:09
  • @RickJames when a simple SELECT is slow, it is meaningless to optimize the JOIN query. What I found so far is that the uncompression process is the bottleneck as cannot keep up with the I/O. Apparently, page compression is slow in writing and column compression in reading.
    – Googlebot
    Oct 21 '21 at 6:35
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.

2
  • you guess was correct, the problem single-process uncompressing was the bottleneck. Still, I need to recreate the whole table without compression to confirm the difference. I just migrated from page_compression, which consumes extra I/O in writing to the underlying sparse file. The write is very slow and floods the innoDB buffer pool. Page_compression is much better than row_compression, but I will totally give up compression.
    – Googlebot
    Oct 21 '21 at 6:31
  • Hi, thanks for letting me know. Yes, page compression is more efficient than row_format compressed. If you decide to give up compression at all, you may want to consider partitioning. Oct 21 '21 at 16:32

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.