MySQL HDD activity with empty process list

Question

What does this mean and how can I understand what is mysql doing? I use InnoDB, my database is huge. This happens when I disconnect my app.

Thanx.

Answer 1

The InnoDB Storage Engine does lots of writing to disk behind-the-scenes. Please remember the subheading ASPECT #4 : Disk Issues from My Answer to Your Previous Question:

ASPECT #4 : Disk Issues

What is also noteworthy is this regard is the system tablespace ibdata1. Every time you access an InnoDB tables, the data dictionary is always consulted. Thus, having spread out tables onto other disk does not negate accessing ibdata1 back in datadir. Accessing two disks would always be in order.

InnoDB has many moving parts in ibdata1:

• Data Dictionary
• Double Write Buffer (Safety Net to Prevent Data Corruption Helps Bypass OS for Caching)
• Insert Buffer (Streamlines Changes to Secondary Indexes)
• Rollback Segments
• Undo Logs
• Click Here to see a Pictorial Representation of ibdata1

Expect the Highest I/O around ibdata1. In light of this, whatever disk tuning you do, give weight to the location of ibdata1.

From what source does InnoDB do this background writing to ibdata1?

• The InnoDB Buffer Pool contains Dirty Pages. Those dirty pages must be systematically flushed to disk. Dirty pages come about when INSERTs, UPDATES, and DELETE are made. Those changes are not posted directly to tables. They are queued up in the InnoDB Buffer Pool's Dirty Pages.
• There are also writes being posted in the Double Write Buffer within ibdatato bypass OS caching.
• Secondary Index changes are queued up in the InnoDB Buffer Pool and migrated to the Insert Buffer within ibdata1
• Since up to 1023 simultaneous Transactions are possible for InnoDB, any data written in the Undo Logs and Rollback Segments are discarded in an orderly fashion.
• When there is heavy writing to InnoDB, the Log Buffer catches the initial changes and are scheduled based on how Transaction Flushing is Done (set by innodb_flush_log_at_trx_commit,)
  • Setting innodb_flush_log_at_trx_commit=0 flushes the log buffer out to the log file once per second and the flush to disk operation is performed on the log file, but no writes are done at a transaction commit. A 1-second loss of data is possible on a server crash or a mysqld crash.
  • Setting innodb_flush_log_at_trx_commit=1 (Default) flushes the log buffer for each commit and flush the logs to disk
  • Setting `innodb_flush_log_at_trx_commit=2 flushes the log buffer out to the file at each commit, but the flush to disk operation is not performed on it. A 1-second loss of data is possible on a server crash.

NOTE : Even if no new InnoDB data have been written in a while, reads from the moving parts of ibdata1 must be done to determine availability of incoming data changes. Running a SELECT query against an InnoDB table requires checking the .frm file of the table and cross checking the data dictionary inside ibdata1. I noted this storage engine behavior in my past post : MySQL tables/databases into different disks