I have a 40GB xml file which needs to be loaded into the db. Currently we are not sure about PK/FK relationships or schema details. So I am just loading them up without any indexes or keys. Every time I load it, I get the following error.
mysql> LOAD XML INFILE '/var/lib/mysql-files/myfile.xml' INTO TABLE my_tbl ROWS IDENTIFIED BY '<entry>';
ERROR 2013 (HY000): Lost connection to MySQL server during query
MySQL version is 8.0.23 running on Ubuntu 20.04
mysql> select version();
+-------------------------+
| version() |
+-------------------------+
| 8.0.23-0ubuntu0.20.04.1 |
+-------------------------+
1 row in set (0.00 sec)
$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=20.04
DISTRIB_CODENAME=focal
DISTRIB_DESCRIPTION="Ubuntu 20.04.2 LTS"
Details of the file I want to bulk load
$ sudo du -sh /var/lib/mysql-files/myfile.xml
42G /var/lib/mysql-files/myfile.xml
$ sudo grep '<entry>' /var/lib/mysql-files/myfile.xml | wc -l
5659877
$ sudo wc -l /var/lib/mysql-files/myfile.xml
1906171631 /var/lib/mysql-files/myfile.xml
The xml has 67 tags. The create table statement is as follows. The reason for TEXT is because of unknown number of characters per column, and the data type. These are yet to be clarified by the client, so not exactly in my hands right now.
CREATE TABLE IF NOT EXISTS my_tbl (
`col1` TEXT,
`col2` TEXT,
...
`col67` TEXT
);
I referred to this answer here - MySQL any way to import a huge (32 GB) sql dump faster? and made some changes to my variables and still I am not able to import my file. I keep running out of RAM (16 GB).
Custom variables in /etc/mysql/my.cnf (Even innodb_buffer_pool_size = 4G didn't work)
innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
I have also tried both with and without the following option, which still eats up my RAM.
service mysql restart --innodb-doublewrite=0
These are my settings for innodb variables.
mysql> show variables like '%innodb%';
+------------------------------------------+------------------------+
| Variable_name | Value |
+------------------------------------------+------------------------+
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_parts | 8 |
| innodb_adaptive_max_sleep_delay | 150000 |
| 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_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 2 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_in_core_file | ON |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 2147483648 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | crc32 |
| 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_deadlock_detect | ON |
| innodb_dedicated_server | OFF |
| innodb_default_row_format | dynamic |
| innodb_directories | |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | OFF |
| innodb_doublewrite_batch_size | 0 |
| innodb_doublewrite_dir | |
| innodb_doublewrite_files | 0 |
| innodb_doublewrite_pages | 0 |
| innodb_extend_and_initialize | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_per_table | ON |
| innodb_fill_factor | 100 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 0 |
| innodb_flush_method | fsync |
| innodb_flush_neighbors | 0 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_fsync_threshold | 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_idle_flush_pct | 100 |
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
| innodb_lock_wait_timeout | 50 |
| innodb_log_buffer_size | 268435456 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 1073741824 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_spin_cpu_abs_lwm | 80 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_log_write_ahead_size | 8192 |
| innodb_log_writer_threads | ON |
| innodb_lru_scan_depth | 1024 |
| innodb_max_dirty_pages_pct | 90.000000 |
| innodb_max_dirty_pages_pct_lwm | 10.000000 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_numa_interleave | OFF |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 4000 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_cleaners | 4 |
| innodb_page_size | 16384 |
| innodb_parallel_read_threads | 4 |
| innodb_print_all_deadlocks | OFF |
| innodb_print_ddl_logs | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_purge_threads | 4 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_redo_log_archive_dirs | |
| innodb_redo_log_encrypt | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_spin_wait_pause_multiplier | 50 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | ON |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| innodb_undo_tablespaces | 2 |
| innodb_use_native_aio | ON |
| innodb_validate_tablespace_paths | ON |
| innodb_version | 8.0.23 |
| innodb_write_io_threads | 16 |
+------------------------------------------+------------------------+
146 rows in set (0.00 sec)
I am open to suggestions. I am not an experienced db admin so please bear with me.
EDIT: Forgot to mention this, the table already has data from other XML files
mysql> select count(*) from my_tbl;
| count(*) |
+----------+
| 59108495 |
+----------+
1 row in set (7 min 56.21 sec)
These are the error logs from /var/log/mysql/error.log
2021-03-26T07:09:15.979529Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23-0ubuntu0.20.04.1) starting as process 259210
2021-03-26T07:09:15.996260Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-03-26T07:09:20.080643Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
InnoDB: Progress in percents: 12021-03-26T07:09:20.292822Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-03-26T07:09:20.329258Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2021-03-26T07:09:20.338715Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2021-03-26T07:09:20.385715Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-03-26T07:09:20.386031Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-03-26T07:09:20.434241Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23-0ubuntu0.20.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu).
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 1002021-03-26T07:35:21.894979Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.23-0ubuntu0.20.04.1).
2021-03-26T07:35:22.969672Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23-0ubuntu0.20.04.1) (Ubuntu).
2021-03-26T07:35:23.372829Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23-0ubuntu0.20.04.1) starting as process 259662
2021-03-26T07:35:23.382151Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-03-26T07:35:23.788896Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-03-26T07:35:23.985189Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-03-26T07:35:24.058018Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-03-26T07:35:24.058228Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-03-26T07:35:24.094547Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23-0ubuntu0.20.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu).
2021-03-26T07:53:42.007890Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23-0ubuntu0.20.04.1) starting as process 259770
2021-03-26T07:53:42.025140Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-03-26T07:53:45.447340Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
InnoDB: Progress in percents: 12021-03-26T07:53:45.655048Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-03-26T07:53:45.705071Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2021-03-26T07:53:45.714392Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2021-03-26T07:53:45.809253Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-03-26T07:53:45.809541Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-03-26T07:53:45.854160Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23-0ubuntu0.20.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu).
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 1002021-03-26T08:50:33.398181Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.23-0ubuntu0.20.04.1).
2021-03-26T08:50:34.805891Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23-0ubuntu0.20.04.1) (Ubuntu).
2021-03-26T08:50:35.189593Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23-0ubuntu0.20.04.1) starting as process 260012
2021-03-26T08:50:35.198967Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-03-26T08:50:35.861104Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-03-26T08:50:35.997855Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-03-26T08:50:36.057783Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-03-26T08:50:36.058016Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-03-26T08:50:36.081717Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23-0ubuntu0.20.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu).
2021-03-26T08:59:32.725975Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23-0ubuntu0.20.04.1) starting as process 261895
2021-03-26T08:59:32.742508Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-03-26T08:59:50.946015Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
InnoDB: Progress in percents: 12021-03-26T08:59:51.163131Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-03-26T08:59:51.215994Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2021-03-26T08:59:51.229481Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2021-03-26T08:59:51.274875Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-03-26T08:59:51.275612Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-03-26T08:59:51.317661Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23-0ubuntu0.20.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu).
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 1002021-03-26T09:01:38.991851Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.23-0ubuntu0.20.04.1).
2021-03-26T09:02:05.263915Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23-0ubuntu0.20.04.1) (Ubuntu).
2021-03-26T09:02:05.640912Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23-0ubuntu0.20.04.1) starting as process 262009
2021-03-26T09:02:05.650539Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-03-26T09:02:05.964542Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-03-26T09:02:06.082196Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-03-26T09:02:06.147600Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-03-26T09:02:06.147786Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-03-26T09:02:06.168913Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23-0ubuntu0.20.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu).
2021-03-26T09:11:36.238684Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23-0ubuntu0.20.04.1) starting as process 262903
2021-03-26T09:11:36.255959Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-03-26T09:11:48.086742Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
InnoDB: Progress in percents: 12021-03-26T09:11:48.298196Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '127.0.0.1' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-03-26T09:11:48.351430Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2021-03-26T09:11:48.361190Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2021-03-26T09:11:48.413180Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-03-26T09:11:48.414045Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-03-26T09:11:48.458539Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23-0ubuntu0.20.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu).
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
show global variables like 'log_error';check the output of this query it will return file path of error log