MySQL Tuning... With Oracle/Java MySQL Enterprise Monitor

From: Mike Harrison 
------------------------------------------------------

Ed and other heavy MySQL/Maria users..

Dispite that it installs Java, and therefor I probably won't run it all 
the time... (added some server load as well as just being Java) I've been 
playing with the Oracle MySQL Enterprise Monitor (and the Agents you 
install on each SQL server) and learned some things about MySQL tuning 
from it, it actually has some very informative helpful information in it. 
More useful instructions that the MySQL Website (which is pretty good).

Summary: Works best if you install the monitoring agent on each server,
and then install the monitoring server on a seperate system, The Agents 
connect back to the server. They even use SSL connections, you should
be able to run the monitor in your office for systems in colo with some 
firewall rules. It recognized the multi-master slave configs and even a 
second MySQL instance (on strange ports) running on the servers.
It gives advice about user perms/security configs as well as table cache 
and settings for InnoDB writes and such.. Including when doing something 
for a performance increase breaks ACID compliance. While it installs and 
uses MySQL 5.6 and Java, the Agents will work with 5.1 and above,
in my case right now it's looking at two 5.1 servers on Redhat, a 5.5 
server on Ubuntu and three 5.6 servers on Redhat.. across 4 systems on two 
physically separated networks. If you are careful, setting up multiple 
MySQL instances is not that hard..

Worth playing with, and going through the Oracle signup process to get a 
copy to evaluate. Of course.. They'd like you to pay for it for production 
long term use, and I'd have to say it could save you enough headaches it 
would be worth it.

And it has awesome pretty dashboard/charts for the pointy haired types..
as well as useful screen candy for DBA/SysAdmin types.









=============================================================== From: Wil Wade ------------------------------------------------------ How does it compare in capabilities to the services provided by New Relic? (On the db side of course)

=============================================================== From: Mike Harrison ------------------------------------------------------ Hadn't seen the New Relic stuff.. I can't compare. What do you like about the New Relic services?

=============================================================== From: Ed King ------------------------------------------------------ thanks...=A0=A0 I downloaded a copy...=A0 no tellin' what all I agreed to i= n that license that I didn't read ;-) =A0=A0 I'll install it on our "sandbo= x" server and play around with it before I get the courage to install Java = agent on our production server.=A0=A0 We're more of a postgresql shop but w= e do use mysql "on the side" and its always good to tweak for max performan= ce :)=0A=0A=0A=0A=0A

=============================================================== From: Wil Wade ------------------------------------------------------ (Note I do not pay for the service, but have used it a few times) New Relic is focused on optimizing the application not the settings (although I would not be surprised if they did not add some of that, so my info may be out of date). For PHP/MySQL (they support other lang, but this is my experience) applications it is a really nice tool to look inside your application in action for speed restrictions. Slowest query and page lists, averages, etc... Nice error logging and parsing. Lots of other stuff (including caching server monitoring and charts), but I have not used it extensively.