![]() innodb_buffer_pool_size=10G (or less) to free up server memory, you can increase this later when you are using the server for queries, but for inserts it's almost useless.max_allowed_packet=10G (to make memory available for the INSERT).Did the files run slower and slower as you went through through the 300? Is it disabling and re-enabling "keys" 300 times? That means that the indexes are rebuilt 300 times. If there is a COMMIT, then it has to do a lot of work to prepare for a rollback. If there is any swapping, MySQL will slow down, so lower the buffer_pool_size a little to avoid swapping.Īutocommit=0 with InnoDB is not efficient - If there is no COMMIT, then the data is inserted, then rolled back. However, you say it spiked to 100%? I guess I am missing something. 21G for the buffer_pool should keep RAM usage well under the 32G physical size. ![]() However, your settings do not seem to actually use the swap space. Not not use swap space that only slows down MySQL. So, the total table size is somewhere around half a terabyte? The time taken to write that much disk is in the hours, depending on type of disk. No amount of RAM or disk space in the civilized world could hold what max_allowed_packet = 9999999G wants to allocate! Put that back to the default, or at most 256M. The problem consistently persisted even after adding and fiddling with the innodb_* variables. # Disabling symbolic-links is recommended to prevent assorted security risks What can I do? I have considered chunking the INSERT statement into multiple different INSERTs, but this would require some extensive code refactoring due to the multiprocessing flow of my program which generates the SQL files. The SQL files themselves are literally a single INSERT statement with thousands of rows.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |