Re: random-access I/O

[Rick James]

300GB will take a long time -- maybe days. "Count the disk hits".

Do you have blobs? Can you compress them? That will save a significant amount of I/O (and space).

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes -- all relevant tables
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

> What would you recommend me to do in order to improve the performance?
Let's take a careful look at the indexes in both the old and the new tables. And let's see the INSERT...SELECT.

At 300GB, you are a candidate for PARTITIONing. And you may have a case for partitioning just to cut down on thrashing the cache.

> used to reorder the DB based on a new primary key
It is unclear whether you should sort before inserting or insert randomly. Need to see the details.