Re: Inserts in MySQL 8 are slower than Inserts in MySQL 5.6

[Peter Brawley]

5-6 hours, sounds like the job required too much swapping.

When we first looked at this system, it could use as much as 5TB of RAM in a box that had 125G RAM. A recipe for disaster. What is max MySQL mem use now? The formula is ...

innodb_ft_total_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size + tmp_table_size + key_buffer_size + max_connections * ( thread_stack + max( max_allowed_packet, net_buffer_length ) + net_buffer_length + read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size )

There's code at https://www.artfulsoftware.com/infotree/mysqltips.php under "Track memory use" that you can run to see how much memory MySQL can use.

For more detailed info on what the bottlenecks are, look at the results of Show Engine Innodb Status whilst slow jobs are running.

And, for each such slowpoke cmd, let's see the query, the result (inside BBCode code tags) of running Explain and Explain Analyze on it, and the Show Create table results for all tables referenced by the offending query.