MySQL database not catching up with INSERT statements

Question

I'm having troubles with my MySql server. It's used mainly for storing production logs, but unfortunately it seems like it it too slow and i dont know if there is something i could do to improve it.Sometimes there is even few hours of delay. The log database is working on MyISAM engine.

Here is mytop output:

Answer 1

Observation from data provided,

There appears to be a process running unattended-upgr (upgrade) that could be contributing to some of your frustration.

Select_scan events at RPHr of 1,299 could be minimized with index to reduce runtime to create results. Drop long_query_time by 1 second a day until you have results in your Slow Query Log to be corrected by creating an appropriate index.

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section

key_cache_age_threshold=7200  # from 300 (seconds) to age out MyISAM data to reduce key_reads RPS of 2.01.
key_cache_division_limit=50  # from 100 to have HOT / WARM storage and further reduce key_reads RPS of 2.01.
net_buffer_length=98304  # from 16384 to reduce packet in / out counts
read_rnd_buffer_size=16384  # from 262144 to reduce handler_read_rnd_next RPS of 10,825. 
slow_query_log=ON  # from OFF for investigation and index creation, when missing.

There are many more opportunities to improve performance. View profile please.

Answer 2

• InnoDB is faster at concurrent INSERTs. Switch to InnoDB.

• In particular, that Processlist probably implies something else is happening with the table. Again, InnoDB is better at handling concurrency.

• If practical, batch the inserts -- A single INSERT with 100 rows runs about 10 times as fast as 100 single-row INSERTs.

• Having lots of indexes on the log table slows things down some.

• The use of Summary Tables may avoid the need for lots of indexes.

• See High speed ingestion for even better performance (within MySQL/MariaDB).

• MyISAM has been removed from MySQL 8.0 and all "clustering" configurations. That is, your next upgrade may force you to change to InnoDB.