3

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.

High MySQL Usage

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
 ============================
  • The number of writes per second is pretty close to the number of queries per second. This graph doesn't give enough detail to determine how many queries each client is executing on the server. So perhaps, those 30 clients are executing dozens of queries. – Craig Efrein Sep 22 '14 at 14:47
  • The other tab shows which queries are running on which clients, but there's only a few queries showing up every few seconds. It's a bit pointless me getting a screenshot as I'll have to blank out the majority of the data for privacy reasons. When I run SHOW FULL PROCESSLIST; from the command line, I get around 30 rows returned on average, and 28 of those are usually "sleeping". My question is more, how can I see what these 272 queries are that are allegedly running right now? Or am I misunderstanding completely? – BT643 Sep 22 '14 at 14:55
  • Updated my answer – Craig Efrein Sep 22 '14 at 15:37
4

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

InnoDB Architecture

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

Number of Write Operations

Bytes Written with InnoDB

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.

  • Wow! Thanks for your incredibly detailed response! innodb_log_buffer_size = 8388608 innodb_buffer_pool_size = 57982058496 Looks like we may need to buy some more memory for the server then.. we're already using 54GB of our total 64GB for the buffer pool. I can't find anything about the redo logs in our config file so I guess they're using whatever the default value is on Ubuntu 12.04 Server with MySQL 5.5.34. Certainly something to think about! Thank you so much. – BT643 Sep 23 '14 at 9:11
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.

enter image description here

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.

  • When I run SHOW FULL PROCESSLIST; from the command line, I get around 30 rows returned on average, and 28 of those are usually "sleeping"... not sure where the rest are! I ran that as root also. – BT643 Sep 22 '14 at 15:16
  • SHOW PROCESSLIST is going to give you the queries being executed now, how does MySQL workbench calculate the actual number of queries being processed? – Craig Efrein Sep 22 '14 at 15:20
  • How bizarre! SHOW ENGINE INNODB STATUS just confuses things even further for me! 1.06 inserts/s, 13.91 updates/s, 0.00 deletes/s, 33615.95 reads/s, yet MySQL Workbench still shows tons of writes per second, and barely any reads per second. – BT643 Sep 22 '14 at 16:02

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.