Optimize data load of LARGE read-only table

Question

Some context/background on my issue. I'm a sys admin asked to perform some DBA functions etc. I have a very large 2TB data file to load into a mysql table that will end up being used in read-only mode. I'm transitioning from a mysql 5.5 myisam table on Ubuntu 14.04 to a mysql 5.7 innodb table on Ubuntu 18.04 (different systems). The table is partitioned to keep memory usage reasonable within system RAM.

Using myisam, this large load would take a couple of days but that was expected and no issues with the undo or redo logs etc. Since myisam is being depreciated, I tried loading this data into an InnoDB table. Here are the system specs:

2 x 12-core CPUs
512GB system RAM.
mysql data is currently on 6TB hard drive

Over the weekend, I let it load for 3-days into a brand new mysql install with these config params:

datadir         = /export/home/data/mysql
tmpdir          = /export/home/SSD1/tmp
skip-external-locking
key_buffer_size         = 16M
max_allowed_packet      = 256M
thread_stack            = 192K
thread_cache_size       = 8
max_heap_table_size = 16G;
tmp_table_size = 4G
query_cache_limit       = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size         = 100M
innodb_file_per_table=1
innodb_file_format=barracuda
innodb_strict_mode=1
innodb_buffer_pool_size = 300G
innodb_log_file_size = 200M
innodb_log_buffer_size = 100M
innodb_flush_log_at_trx_commit = 2

I was monitoring the load and noticed that it had written about 2TB of mysql data files in the database directory which was expected. However, the ibdata1 file had grown from 32M to 330GB. I'm assuming that most of this space is for the undo log transactions? During the load, I ran "show engine innodb status" and noticed this for the undo entries:

TRANSACTIONS

Trx id counter 46678
Purge done for trx's n:o < 46678 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421569004301048, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 18023, ACTIVE 418094 sec inserting
mysql tables in use 1, locked 1
100 lock struct(s), heap size 8400, 0 row lock(s), undo log entries 16758402383

1. Since this table will be mostly used in read-only like it was when it was a MyISAM table, are there any optimizations that would help with the data load? Are there any InnoDB optimizations for a read-only table?

2. Besides explicitly moving the undo logs to a separate disk/directory, is there anything to be done to improve the performance of the undo logs?

Here's the create table: CREATE TABLE Orthologs_New ( GeneID1 varchar(50) NOT NULL, GeneID2 varchar(50) NOT NULL, OrgID1 varchar(50) NOT NULL, OrgID2 varchar(50) NOT NULL, PValue double DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (OrgID1,OrgID2) PARTITIONS 100 */

I'm not a DBA, so I'm a bit lost here. Thanks in advance!

Answer 1

URGENT: If your MyISAM table is 2TB, it may not fit as InnoDB on a 6TB drive. Even if it does fit, there won't be room to do any non-trivial ALTER.

URGENT: innodb_file_per_table = 1 needed to be set before creating the table. Please elaborate on how you converted from MyISAM to InnoDB; I don't have an obvious answer to the "330GB". There is probably a .frm file for the table; is there a .ibd file? If not, then somehow the file_per_table failed.

Readonly InnoDB loves innodb_buffer_pool_size = 300G. With 512G of RAM, this is viable and would help some:

innodb_buffer_pool_size = 400G

innodb_buffer_pool_instances = 16 will help a little.

You mentioned partitioning. It usually does not help performance; in fact, it sometimes hurts performance. Please provide SHOW CREATE TABLE, the number of partitions, and typical queries. In particular, PARTITION BY KEY (OrgID1,OrgID2) is no better than having INDEX(OrgID1,OrgID2).

(Minor warning: The need for this is going away: innodb_file_format=barracuda.)

More discussion of the engine conversion: http://mysql.rjweb.org/doc.php/myisam2innodb

Based on the provided schema, I recommend these changes for the loading:

• InnoDB really likes to have a PK, so add this to the table definition:

    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id)
  

• Get rid of the partitioning.

• How much precision do you need in PValue? DOUBLE provides about 16 significant digits at a cost of 8 bytes. FLOAT gives 7 for 4 bytes. If 7 is 'good enough', consider whether it is worth saving the ~100GB of space that might save.

• After loading, add the following index. What is the typical WHERE clause? (I sense that it is always `WHERE OrgID1='...' AND OrgID2 = '...'.)

    ALTER TABLE Orthologs_New
        ADD INDEX(OrgID1,OrgID2);
  

If you will periodically be reloading the info, then I can give you a recommendation on how to replace the table using RENAME TABLE.

Is it the case that this pair (OrgID1,OrgID2) is Unique? If so, the above PK + INDEX would be better done by simply

PRIMARY KEY(OrgID1,OrgID2)

That would slightly speed up SELECTs. But then the LOAD DATA may slow down unless the data comes in in that order. If the source data is in that order the LOAD should be fast. If not, then it must jump around, which will be very heavy on I/O -- depending on the relative size of innodb_buffer_pool_size (400G or whatever) versus the ultimate size of the table (4TB or whatever). If possible, sort the data by that pair of values before doing the LOAD.

This may be a faster way to load:

CREATE TABLE ... ENGINE=MyISAM;  -- with no keys
LOAD DATA INFILE ...
ALTER TABLE Orthologs_New
    ADD  (key(s) as discussed above)
    ENGINE=InnoDB;

The rationale is that the LOAD will be quite fast, then the ALTER will rebuild the table, add the indexes, etc.

If, instead, the 4 columns are unique, then make them the PRIMARY KEY and arrange them in the PK based on what the common queries will be. Possibly this will eliminate your need for an SP building memory tables. (I am more interested in seeing the queries that it supports than in seeing the SP itself.)