2

I installed MONyog recently to check for problems on a MySQL server that I admin. Most of the information was useful and helpful, but there's one warning I am not sure is worth fixing.

It reports that Com_change_db commands are excessive (currently 9+ million or 58 per second), or unusually high. The connections to the database are strictly Windows .NET applications and they use Connector/Net as an API.

I typically connect such that I specify which database to use when creating a MySqlConnection object. I discovered in a test that doing this causes the Com_change_db to increment by one when the connection is established.

I can "fix" it by not specifying a database, and instead qualifying the database within the query every time. (e.g. db.table.col instead of table.col)

Is it worth going through the entire code base of the application to make this change? Is MONyog concerned with something that doesn't matter?

1

MONyog

In MySQL 5.6, 341 different status values are being recorded.

If all values are retrieved with:

SHOW GLOBAL STATUS;

Then, you are retrieving all 341 values. Com_change_db is just coming along for the ride.

If each value is retrieved individually, such as with

SHOW GLOBAL STATUS LIKE 'Com_change_db';

this should not be a problem since you can set MONyog's interval for collecting status values.

In either scenario, MONyog is a non-factor in your case.

Source Code

You may want to leave your code alone since you are only generating a Com_change_db 58 times per minute (about 1 per second). Just to be hypothetical, if each Com_change_db took up 2K in transmission over the DB Connection through the network at a rate of 1 per second:

86400 sec X 2K = 168.75 MB per day

That's not a lot to worry about in a day.

Therefore, changing your code just to lower the Com_change_db count would be the only gain you would make. There would be no discernable performance improvement.

Epilogue

I would be more concerned with status variables that count temp table creation or all the handler counts as that would be earmarks for bad queries or large temp tables do to key distribution or setting for temp tables that are too small.

UPDATE 2013-09-26 19:38 EDT

OK my mistake. You said 58 per second, not per minute.

Let me redo my hypothetical example

86400 sec X 2K X 58 =

  • 9.558 GB per day
  • 407.8 MB per hour
  • 6.7968 MB per minute

This shows that could reduce the amount of network traffic by cutting out Com_change_db requests. If you have a good network with 10Gig E, this may not be an issue.

Summary

Think of the tradeoff you were proposing: If you are qualifying all tables with the database name prepended, then you are transmitting more bytes in the SQL text and giving the Query Parser more to work with anyway.

Another way to approach it would be to have a different DB Connection connecting to a separate database. That way, one DB Connection would not have to switch databases and run up the Com_change_db counter and add to the network traffic. The tradeoff would be to have multiple DB Connections per client (requiring more memory).

In other words, something has to bite the bullet in the end.

Again, I suggest leaving everything alone because all the alternatives require major code changes without knowing if the enhancements were worth it in the end.

  • The rate is being reported at 58-60 per second, not per minute. There are a lot of queries coming from multiple clients. Just for clarification, I'm not worried about MONyog's contribution to the command(s); I am certain that the accumulation of Com_db_change commands come strictly from the applications in our company, and that the rate is probably correct. – JYelton Sep 26 '13 at 22:41

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.