High MySQL “InnoDB Writes Per Second” - Can't see the queries?

Question

On our live MySQL 5.5 database server, we use MySQL Workbench for running manual queries, checking running queries to see what's going on, etc.

I noticed today that on the "Server Status" tab there's a very high number of "Queries per Second" and "InnoDB Writes per Second", yet on the tab which shows the list of "Client Connections", which shows all running queries, it is barely showing any queries.. perhaps 2 or 3 every few seconds.

What am I misunderstanding here? The two tabs don't add up at all.

EDIT - here's the output for SHOW ENGINE INNODB STATUS;

 --------------
 ROW OPERATIONS
 --------------
 0 queries inside InnoDB, 0 queries in queue
 2 read views open inside InnoDB
 Main thread process no. 24494, id 139631733794560, state: sleeping
 Number of rows inserted 36474186, updated 163927609, deleted 11892445, read      1747097889216
 1.06 inserts/s, 13.91 updates/s, 0.00 deletes/s, 33615.95 reads/s
 ----------------------------
 END OF INNODB MONITOR OUTPUT
 ============================

Answer 1

What you need is a better visual representation of the InnoDB Storage Engine

Think about the kind of things that trigger writes that can be monitored

Number of Write Operations

• Innodb_dblwr_writes to the Double Write Buffer
• Redo Logs
  • Innodb_os_log_writes and Innodb_os_log_pending_writes : Logical
  • Innodb_os_log_fsyncs and Innodb_os_log_pending_fsyncs : Physical
• InnoDB_data_fsyncs and InnoDB_data_pending_fsyncs
• Innodb_data_writes throughout the InnoDB Architecture

Bytes Written with InnoDB

• Innodb_pages_written : NUmber of pages (data and index) written to InnoDB tables. Multiple this by 16384 (16KB) to get the number of bytes written to InnoDB tables.
• Innodb_dblwr_pages_written Number of Double Write Pages Written. Multiple this by 16384 (16KB) to get the number of bytes written to the Double Write Buffer.
• Innodb_os_log_written Bytes Written to the Redo Logs
• Innodb_data_written Bytes written throughout InnoDB Architecture

These are among the things that are being churned inside and outside of InnoDB. Yet, this is not everything. Check the MySQL Documentation on the Status Variables.

My guess is that MySQL Workbench is just monitoring Innodb_data_writes.

If the data writes are high, given this

1.06 inserts/s, 13.91 updates/s, 0.00 deletes/s

which is about 15 queries per second, you may need to spend some time tuning InnoDB. Since InnoDB does fuzzy checkpointing, InnoDB will reveal a bottleneck in writes if you see constant writes that never abate. That could be due to the OS or VM. It could also be due to an undersized InnoDB Buffer Pool (I see it's 100% full). Lots of reads will bring in data and index pages into the Buffer Pool. New or updated data and index pages from the INSERTs and UPDATEs need to squeeze into the Buffer Pool as well. Perhaps a bigger Buffer Pool is in order.

From another angle, look at the number of rows accessed

     36474186 Rows from INSERTs
    163927609 Rows from UPDATEs
     11892445 Rows from DELETEs
    212294240 Rows Written
1747097889216 from SELECTs

When you divide 1747097889216 by 212294240, that means for every row you update, there are 8229.6 rows that are being read. This goes along with the 2245.46 (33165.95/14.97) SELECTs for every INSERT, UPDATE, or DELETE. I can see writes squeezing into the Buffer Pool. This makes me feel that the Buffer Pool, the Log Buffer, and the Redo Logs should all be increased.

Answer 2

SHOW PROCESSLIST is what I would use, if I were in a hurry. Rolando has written a great post on determining what DML (data modification language) queries are being run and what they are modifying.

As to the SELECTs, SHOW PROCESSLIST is your best bet or Jet Profiler if your looking for something with a GUI.

You can also query the InnoDB Engine directly to get more information on what it is currently working on with SHOW ENGINE INNODB STATUS.

Without knowing exactly how Workbench calculates the queries per second value, it would be safer to trust the SHOW PROCESSLIST and SHOW ENGINE INNODB STATUS output and operations per second calculations.