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
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?
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:
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!