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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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?
ibdatato bypass OS caching.
innodb_flush_log_at_trx_commit=0flushes 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.
innodb_flush_log_at_trx_commit=1 (Default) flushes the log buffer for each commit and flush the logs to disk
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