MySQL Import too slow workaround

Half-tone data scientists are going to toss data, get a look at the data, 3.6G zip file, unzip to see, 12 g of SQL file in a slot. Okay, I'm going to toss the SQL data again. First thing, it must be a database, import data.

Toss over the SQL import of the parents know, MySQL default parameters, import speed is still very slow, especially the data is very much the case. The data, after the toss, there are 1000W so much, do not guess also know, slow to die, so need to do some settings for the database.

There are two places that can be set, and the first one is innodb_flush_log_at_trx_commit. The official manual explains the values as follows:

Controls the balance between strict ACID compliance for commit operations and higher performance that's possible when COM mit-related I/O operations is rearranged and done in batches.  You can achieve better performance by changing the default value and then you can lose up to a second of transactions in a Crash. The default value of 1 is required to full ACID compliance. With this value, the contents of the InnoDB log buffer is written out to the log file at each transaction commit and the Log file is flushed to disk.  With a value of 0, the contents of the InnoDB log buffer is written to the log file approximately once per second and the Log file is flushed to disk. No writes from the log buffer to the log file is performed at transaction commit. Once-per-second Flushing is not guaranteed to happen every second due to process scheduling issues.  Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions With any mysqld pRocess crash.  With a value of 2, the contents of the InnoDB log buffer is written to the log file after each transaction commit and the Log file is flushed to disk approximately once per second. Once-per-second Flushing is not 100% guaranteed to happen every second, due to process scheduling issues.  Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions In an operating system crash or a power outage.  InnoDB log flushing frequency is controlled by innodb_flush_log_at_timeout, which allows your to set log flushing frequency To n seconds (where n is 1 ... 2700, with a default value of 1). However, any mysqld process crash can erase up to N seconds of transactions. DDL changes and other internal InnoDB activities flush the InnoDB log independent of the Innodb_flush_log_at_trx_commit SE Tting. InnoDB crash Recovery works regardless of the Innodb_flush_log_at_trx_commit setting. Transactions is either applied entirely or erasedEntirely.  For durability and consistency in a replication setup the uses InnoDB with transactions:if binary logging is enabled, set Sync_binlog=1.always set Innodb_flush_log_at_trx_commit=1.cautionmany operating systems and some disk hardware fool the F Lush-to-disk operation. They may tell mysqld that the flush had taken place, even though it had not. In this case, the durability of transactions isn't guaranteed even with the setting 1, and in the worst case, a power out Age can corrupt InnoDB data. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the Operation Safer. You can also try to disable the caching of disk writes in hardware caches.

That is

• 1 default value, slowest, every transaction commit is written to log and flushed to disk, which is the safest way
• 0 fastest, flush log to disk every 1S, but not guaranteed. A transaction commit does not trigger a log write. is not safe, MySQL hangs, then the last second of data are lost.
• 2 A compromise, transaction commits are written to log, but log refreshes or once per second is not guaranteed. At such times, even if MySQL collapses, the data will be written to disk as long as the operating system is still running.

As mentioned here, some disk systems, even if the refresh can not guarantee that the data is actually written, the author came across the file copy to the hard disk (mechanical hard disk), the machine died, restart, only less than half of the data is still there. Only to know that the data was written to the hard disk cache, not yet written to the hard disk.

This parameter can be set in My.ini, but we only use it temporarily, and I use the local Docker MySQL, the configuration file is more troublesome, so directly in the MySQL command line is set.

mysql> set GLOBAL innodb_flush_log_at_trx_commit = 0;

The second place that can be set, the parameters used when importing SQL:

Net_buffer_length

Each client thread is associated with a connection buffer and result buffer. Both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement.This variable should not normally be changed, but if you have very little memory, you can set it to the expected length of statements sent by clients. If statements exceed this length, the connection buffer is automatically enlarged. The maximum value to which net_buffer_length can be set is 1MB.

Max_allowed_packet

The maximum size of one packet or any generated/intermediate string, or any parameter sent by the Mysql_stmt_send_long_dat A () C API function. The default is 4MB. The packet message buffer is initialized to net_buffer_length bytes and can grow up to max_allowed_packet bytes when need Ed. This value by default is small, to catch large (possibly incorrect) packets. You must increase this value if you are using large BLOB columns or long strings. It should is as big as the largest BLOB you want to use. The protocol limit for Max_allowed_packet is 1GB. The value should be a multiple of 1024; Nonmultiples is rounded down to the nearest multiple. When you change the message in buffer size by changing the value of the Max_allowed_packet variable, you should also change t He buffer size on the client side if your client program permits it. The default Max_allowed_packet value built in to the client library are 1GB, but individual client programs might override This. For example, MySQL and mysqldump have defaults of 16MB and 24MB, respectively. They also enable the change of the Client-side value by setting max_allowed_packet on the command line or in an option fil E.the session value of this variable are read only. The client can receive up to as many bytes as the session value. However, the server won't send to the client more bytes than the current global max_allowed_packet value. (The global value could was less than the session value if the global value was changed after the client connects.)

Note that you need to first determine the server settings, the client's settings can not be greater than the server settings.

mysql>‘max_allowed_packet‘;mysql>‘net_buffer_length‘;

In fact, I used the mariadb docker, these two values have been set very large. And the official also mentioned that the MySQL command line inside the default settings are large enough, but I test the results, or write up, speed will be a bit faster, do not know why.

mysql -h127.0.0.1 -uroot -proot123 data_base_name --max_allowed_packet=16777216 --net_buffer_length=16384<your_sql_script.sql

However, although the speed is a lot faster, but also a few hours of kung fu to finish, this time the data text is mostly, do not know whether it is because of this, or what other settings I do not know.

By the way, the back for convenience or to the data toss into the MONGO inside, the data accounted for a lot of space, but the same single-threaded operation, the middle also added a lot of data processing, but within an hour to get it done.

Half-tone data scientist, also continue to toss data ...

(*￣^￣)

MySQL import too slow workaround