I/O performance (mostly write?) intermittently goes severely low

[Shinji Suzuki]

Hello. I'm looking at an instance (MySQL 2.7 2nd gen + 4vcpu + 15GB RAM + 100GB SDD + no failover) that has been running happily for about a year but have started to show intermittent performance issue  since late Apr.

Insertions that get executed at several tens of times per second suddenly start  to take tens or hundreds of seconds to get just a row inserted. When this happens I see lots of messages in the log like 

  textPayload: "2021-05-08T11:16:29.861999Z 369333 [Note] Aborted connection 369333 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error writing communication packets)"

  textPayload: "2021-05-08T11:14:10.448301Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 9784ms. The settings might not be optimal. (flushed=521 and evicted=0, during the time.)" 

A restart seems to resolve the issue momentary but the problem resurfaces after some hours or days

Is there a solution or a workaround for the issue?

TIA for your help.

[Oded Valin]

As it is mostly write, it can be from several reasons like: Having unnecessary indexes, not using a bulk insert, uploading the data from remote servers,  etc. 
you can use EverSQL to get a performance analysis and find missing MySQL indexes.

[Shinji Suzuki]

Thanks for your input. But the insertions are performed in bulk (like 1000 rows) and light in index. They read data from local files. Above all, they execute fast enough until the described problem gets triggered.

2021年5月10日月曜日 4:47:54 UTC+9 od...@eversql.com:

[David (Cloud Platform Support)]

Hello,

Here you can find some information on how to resolve this error with the aborted connections. Using connection pools should definitely help as well. I understand that this issue seems to only show up after the aborted connections error gets triggered. However, generally speaking, if you want to achieve higher performance on inserts, you can try disabling the autocommit flag. Please note that autocommit is enabled by default to prevent users from accidentally forgetting to commit a transaction. You could also try enabling the performance schema flag although I’m not sure it’ll make much difference for 1k batches of insert.

[Shinji Suzuki]

Thank you, David. I'm not sure if the aborted connection is the cause or the result of the slow down or they are unrelated. though I do see isolated connection aborts. As for transaction processing, after starting to see this problem I modified the programs to do inserts in a transaction and the number of data fsync was reduced by half . I expected more reduction but your note reminds me that the reduction is less significant when dealing with bulk inserts.

I got the problem this morning again and I terminated running connections one by one. I think I've tried that before and it did not work but this time the i/o performance has recovered. Now I'm suspecting that having two connections writing massively into a single table triggers this problem, which strangely results in slow down of inserts into other tables as well. I'll serialize the jobs to ensure no simultaneous inserts and see if it makes any difference.  

[Shinji Suzuki]

One month after implementing serialization of the ingesting processes no performance degradation has been observed.

That suggests inserting into a table concurrently by multiple processes degrades insertion performance by order of magnitude beyond my expectation.

Strangely the degradation occurs not only on the table but also on others.

Anyway the problem seems to be successfully worked around. 

2021年5月11日火曜日 16:33:52 UTC+9 Shinji Suzuki: