2

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?

  • Can you convert the text column to VARCHAR? – ypercubeᵀᴹ Jan 17 '15 at 23:00
  • No, I can't change the structure at all because it is a "mutt" wordpress table (wp_options) that gets used for all kinds of different data by different wordpress processes. – pjv Jan 18 '15 at 13:21
  • What does the update query look like, and what are the indexes on that table? – Lennart Feb 28 '19 at 9:16
1

Any UPDATE to a row requires the following (sooner or later):

  • Read the 16KB block containing the row (unless cached in the buffer pool)
  • Write the block back to disk (or cache it a long time)
  • The "doublebuffer" is written once per transaction
  • Do one write to the InnoDB log for transactional integrity. (The details here depend on various settings and whether you wrap things in BEGIN..COMMIT.) Since you say the buffer_pool is big enough (apparently for the entire table and any secondary indexes?), the first two items will not tally many IOPs.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.