1

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
11
  • Are you trying to important from workbench or terminal ? Can you share error logs ? – JYOTI RAJAI Mar 26 at 10:29
  • I'm doing it from terminal in a remote server. I can share the error logs, can you please tell me how to do so? Where to look for it? – scientific_explorer Mar 26 at 10:30
  • show global variables like 'log_error'; check the output of this query it will return file path of error log – JYOTI RAJAI Mar 26 at 11:34
  • @JYOTIRAJAI I have added the logs to the question. – scientific_explorer Mar 26 at 11:38
  • Can you give me path of ``` show global variables like 'tmpdir';``` ? check the size is available at this path . – JYOTI RAJAI Mar 26 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.