0

everyone!

I'm struggling for a week to bulk load 3.6 billion rows to an InnoDB table on Aurora MySQL 5.6.10a. This table has one FK to a "Main" table and has 12 columns. The first 1.4 billion were loaded overnight, but right now my insert rate is dropping quiklly.

I disabled unique_check and foreign_keys_check but let autocommit on.

I splited the file into 506 files with around 3.84GB each (7,000,000 rows each) and I'm using LOAD DATA FROM S3 to load them to the table.

Any hints to improve this task?

Thank you very much!

Additional details

All other tables in my SCHEMA use InnoDB as Engine and it works fine since they are much smaller than this one. Is it a good idea to change only this table to MyISAM? What would be the implications of doing so?

My Files are ordered by PK and the PK is an AUTO_INCREMENT BIGINT.

CREATE TABLE `Movement` (
   `idMovement` bigint(20) NOT NULL AUTO_INCREMENT,
   `idLawSuit` bigint(20) NOT NULL,
   `content` mediumtext NOT NULL,
   `movementDate` datetime NOT NULL,
   `captureDate` datetime NOT NULL,
   `isReportContent` tinyint(4) DEFAULT NULL,
   `isDocument` tinyint(4) DEFAULT NULL,
   `contentInS3` tinyint(4) DEFAULT NULL,
   `contentS3Url` text,
   `uniqueConcatId` varchar(255) NOT NULL,
   `captureOrder` bigint(20) DEFAULT NULL,
   `movementExtraInfo` text,
   PRIMARY KEY (`idMovement`),
   KEY `idLawSuit10` (`idLawSuit`),
   CONSTRAINT `idLawSuit10` FOREIGN KEY (`idLawSuit`) REFERENCES `LawSuit` (`idLawSuit`) ON DELETE CASCADE ON UPDATE NO ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=1470000001 DEFAULT CHARSET=utf8

These are my InnoDB parameters:

innodb_adaptive_flushing    ON
innodb_adaptive_flushing_lwm    10
innodb_adaptive_hash_index  OFF
innodb_adaptive_max_sleep_delay 150000
innodb_additional_mem_pool_size 8388608
innodb_api_bk_commit_interval   5
innodb_api_disable_rowlock  OFF
innodb_api_enable_binlog    OFF
innodb_api_enable_mdl   OFF
innodb_api_trx_level    0
innodb_aurora_enable_auto_akp   OFF
innodb_autoextend_increment 64
innodb_autoinc_lock_mode    2
innodb_buffer_pool_dump_at_shutdown OFF
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_dump_pct 100
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances    8
innodb_buffer_pool_load_abort   OFF
innodb_buffer_pool_load_at_startup  OFF
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 96223625216
innodb_change_buffer_max_size   25
innodb_change_buffering none
innodb_checksum_algorithm   none
innodb_checksums    OFF
innodb_cmp_per_index_enabled    OFF
innodb_commit_concurrency   0
innodb_compression_failure_threshold_pct    5
innodb_compression_level    6
innodb_compression_pad_pct_max  50
innodb_concurrency_tickets  5000
innodb_data_file_path   ibdata1:12M:autoextend
innodb_data_home_dir    
innodb_disable_sort_file_cache  OFF
innodb_doublewrite  OFF
innodb_fast_shutdown    1
innodb_file_format  Antelope
innodb_file_format_check    ON
innodb_file_format_max  Antelope
innodb_file_per_table   ON
innodb_flush_log_at_timeout 1
innodb_flush_log_at_trx_commit  1
innodb_flush_method O_DIRECT
innodb_flush_neighbors  1
innodb_flushing_avg_loops   30
innodb_force_load_corrupted OFF
innodb_force_recovery   0
innodb_ft_aux_table 
innodb_ft_cache_size    8000000
innodb_ft_enable_diag_print OFF
innodb_ft_enable_stopword   ON
innodb_ft_max_token_size    84
innodb_ft_min_token_size    3
innodb_ft_num_word_optimize 2000
innodb_ft_result_cache_limit    2000000000
innodb_ft_server_stopword_table 
innodb_ft_sort_pll_degree   2
innodb_ft_total_cache_size  640000000
innodb_ft_user_stopword_table   
innodb_io_capacity  200
innodb_io_capacity_max  2000
innodb_large_prefix OFF
innodb_lock_wait_timeout    50
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  8388608
innodb_log_file_size    50331648
innodb_log_files_in_group   2
innodb_log_group_home_dir   ./
innodb_lru_scan_depth   1024
innodb_max_dirty_pages_pct  75
innodb_max_dirty_pages_pct_lwm  0
innodb_max_purge_lag    0
innodb_max_purge_lag_delay  0
innodb_mirrored_log_groups  1
innodb_monitor_disable  
innodb_monitor_enable   
innodb_monitor_reset    
innodb_monitor_reset_all    
innodb_old_blocks_pct   37
innodb_old_blocks_time  1000
innodb_online_alter_log_max_size    134217728
innodb_open_files   6000
innodb_optimize_fulltext_only   OFF
innodb_page_size    16384
innodb_print_all_deadlocks  OFF
innodb_purge_batch_size 900
innodb_purge_threads    3
innodb_random_read_ahead    OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads  32
innodb_read_only    OFF
innodb_replication_delay    0
innodb_rollback_on_timeout  OFF
innodb_rollback_segments    128
innodb_shared_buffer_pool_uses_huge_pages   ON
innodb_sort_buffer_size 1048576
innodb_spin_wait_delay  6
innodb_stats_auto_recalc    ON
innodb_stats_method nulls_equal
innodb_stats_on_metadata    OFF
innodb_stats_persistent ON
innodb_stats_persistent_sample_pages    20
innodb_stats_sample_pages   8
innodb_stats_transient_sample_pages 8
innodb_strict_mode  OFF
innodb_support_xa   ON
innodb_sync_array_size  1
innodb_sync_spin_loops  30
innodb_table_locks  ON
innodb_thread_concurrency   0
innodb_thread_sleep_delay   10000
innodb_undo_directory   .
innodb_undo_logs    128
innodb_undo_tablespaces 0
innodb_use_native_aio   OFF
innodb_use_sys_malloc   ON
innodb_version  1.2.10
innodb_write_io_threads 4
3
  • According to the FAQ here: aws.amazon.com/rds/aurora/faqs, MyISAM is not supported on Aurora.
    – raphael75
    Aug 13 '19 at 18:38
  • Hi @Yago - May I know your latest on this one? I'm interested in what you ended up doing for this one. I have some plans to ingest large amounts of data into Aurora, and I'm thinking along the lines of ingesting sorted data, split into multiple files and then loading them.
    – The-Big-K
    Apr 9 '20 at 19:10
  • Hi @big-K! I was able to upload them disabling unique_checks, foreign_keys_check and auto_commit. Also, I removed all the keys (PK and FK) and constraints. I still used the 506 files and it took a while (don't remember the exact time but was about 2 days, if I'm not mistaken) but was faisable. Apr 10 '20 at 20:30
0
  • Since your data is in PK order, you may as well have both AUTO_INCREMENT and PRIMARY KEY turned on.

  • It might help to load each 7M-row chunk into a separate table, then INSERT .. SELECT to copy them into the main table.

  • Please provide SHOW CREATE TABLE for further discussion. And some important queries.

  • For tables that size, FKs may be more trouble than worth.

  • Are there any secondary indexes?

  • Are you using LOAD DATA? Or loading from a mysqldump file?

As for MyISAM...

  • If the table will be readonly, then maybe.
  • The disk space will be 1/3 to 1/2 as much as for InnoDB.
  • FKs are ignored.
  • The PK is kept in a separate BTree, not 'clustered' with the data. (This helps with some queries, but hurts with others.)
  • You would need space for both InnoDB's buffer_pool and MyISAM's key_buffer.
  • MyISAM will go away some day.

Change innodb_log_file_size to 1G if Aurora will let you.

13
  • Hi, @Rick James! Thanks for the answer. I'm using LOAD DATA FROM S3 command. I noticed that during the first inserts there was no Disk Reads, only Writes, but now the Reads are increasing along with Insert Latency and my writes are dropping. There are some secondary indexes, but I disabled them in the table creation and will build them after the bulk load. Aug 13 '19 at 18:07
  • Would it be worse if I parallelize the LOAD DATA (Execute multiple queries each with a chunk)? Aug 13 '19 at 18:18
  • @YagoCarvalho - If keys are not disabled, it could be thrashing on KEY idLawSuit10 (idLawSuit).
    – Rick James
    Aug 13 '19 at 18:25
  • I used SET FOREIGN_KEYS_CHECK = 0 Aug 13 '19 at 18:27
  • 1
    @YagoCarvalho - Your files are too big. 1K rows at a time is about optimal -- big enough to get 99% of the benefit, small enough not to hit issues with spilling the undo log. You might even go to 10K. But not 7M.
    – Rick James
    Aug 13 '19 at 19:20

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.