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.
The 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?
Best Answer
Any UPDATE to a row requires the following (sooner or later):
But that was not your question.
The actual I/O (pages) related to the buffer_pool is (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed)
You will probably find this very low because of good caching: Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests