This is a followup question to my earlier How can I determine which Innodb table is being written?
Using the accepted answer from the above-referenced question I was able to determine which (two) of a hundred Innodb tables were being constantly updated on my server. Using some trial and error, I was able to determine which specific rows in those tables were being updated.
The rows are essentially counters that are updating and updates are happening about 1/s (I ran a query that counted 54 updates in 60 seconds). The rows are tiny with 4 columns:
BIGINT, VARCHAR, LONGTEXT, VARCHAR. The value in the
LONGTEXT column of the row in question is just a 10-character string and that is the column that is being updated.
I ran a query that calculated the change in
Innodb_data_written over 60 seconds and ran it several times. It ranged from 4.5 to 5.5 MB over 60 seconds. When I turned off the process that is responsible for the 1-character row updates, the change in
Innodb_data_written over 60 seconds went down to .03 MB, so it definitely seems to be these tiny row updates that are responsible for the ~ 5MB per minute of Innodb disk IO.
innodb_buffer_pool_size is ample and the Innodb buffer use percentage is around 30%.
So, the question is, how are a couple of 1/s row updates that are only changing 1 character in a tiny row generating ~ 5MB per minute of Innodb disk IO?