How to track effects of my.cnf changes to reach top optimization of MySQL?

postcd

Hello,

my.cnf is configuration file for MySQL server, please is there anyone who can share good way on how to track effects of this file modifications?

i mean so i can know if changes was beneficial (lower load on server..) or not. im using mysqltuner.pl, i can copy/paste its results and compare it somehow, but its very hard for me to orientate in it to see if my last modification was beneficial.

Is there any smart/simple way to monitor my.cnf changes so it allows one to easilly see how to tweak settings further to reach top optimization of mysql server?

Thank you

jar

If it's me I'm running "watch mysqladmin processlist" while also watching memory+CPU usage and doing some basic load testing on my web application.

There may be other ways but you can't knock good old fashioned "sit there and watch it work"

quadhost

That depends on what you mean by "optimization"...

Are you looking to increase the performance of mysql or minimise the application resource usage?

@Jar said:

There may be other ways but you can't knock good old fashioned "sit there and watch it work"

Learn by doing, it's always fun.

If your running it on big data queries or samples and looking to reduce query times just re-run the query/sample. @Jar's described method works well for monitoring load/usage.

postcd

optimize mysql performance without changing php/application

Damian

Are you asking regarding over time, or instantaneously? There doesn't seem to be any good packages for monitoring SQL server performance, and the ones that do exist present data in a way that doesn't make sense to me. I will admit it's probably been about 8 years since I looked, though.

I ended up rolling my own to provide me the data that I want to know. Why don't you do the same? It's a great way to learn the inner workings of MySQL, although if you're just running a script and hoping for the best, may not be your desired level of involvement.

Here's mine:

postcd

@Damian: Damian, im rather asking "over time". target is just the best optimised mysql config. Seems i canot find any easy to understand tool to help in fine-tuning mysql.
Such tool would have to maybe work with mysql slow log, somehow record mysql tmpdir usage and mysql server stats, but im not a programmer.

PS: Your graphs looks nice, the Config Server Firewall allows similar graphs (but not that detailed like yours) and also Munin shows some graphs of MySQL and CPU these ones:

:: MySQL queries

:: MySQL slow queries

:: MySQL threads

:: MySQL throughput

Abdussamad

MySQL collects statistics itself and you can see them with the show status query. Problem is interpreting them requires DBA level knowledge. This is an intro:

http://www.techrepublic.com/article/understanding-the-performance-statistics-from-mysqls-show-status-command/

Also install mytop

Falzo

https://launchpad.net/mysql-tuning-primer

http://mysqltuner.com/

eva2000

postcd said: my.cnf is configuration file for MySQL server, please is there anyone who can share good way on how to track effects of this file modifications?

i use my own script mysqlmymonlite.sh http://mysqlmymon.com

sirmbhe

@eva2000 said:
i use my own script mysqlmymonlite.sh http://mysqlmymon.com

Looks like pretty neat. Will try it

Thanks !!

postcd

mysqlmymonlite.sh is related but does not allow me easy mysql server fine-tuning without deeper DBA knowledge.