5 Answers
5

It asks for the mysql administrator's username and password and then lists some General statistics, then storage statistics and then performance metrics before finally giving you a list of recommendations.

It saves hours on the initial review of your server instance.

Once you have fixed things, I would suggest running the script every hour or so for a week ( maybe less often / maybe for longer, depending how busy your database is ) to see if any settings need changing after being run for real.

Once you know your MySQL instance is sane, you will need to optimise the specific queries in your application code, you can identify the worst offenders by checking out the slow query log (if anything is in there) and using a profiler like XDebug.

+1 rackerhacker's scripts are great; I've used them in the past on a small and large MySQL server installs, and i've always seen better performance from them.
–
GruffTechJun 7 '10 at 22:47

+1 Agreed. Major even posted a comment on my one week old blog, so obviously has an interest in what people think of his scripts and is still looking at ways they can be improved.
–
Richard HollowayJun 7 '10 at 22:59

The first problem that you have is that you are running on a 32bit OS. You have 12G of Ram (if I read your post right) and you will never see Mysql use that much because your running 32bit. Upgrade Centos to a 64bit OS, and then start using the my.large.cnf file that they provide. That should get you going in a much better direction. If you are still having problems, then things to be evaluated such as is this mostly reads, writes, both. That can tell you that you should be using Innodb or MYISAM. Innodb is going to be good for lots of updates/writes and reads, where if everything is mostly reads then MyISAM is a good choice. We'll need to know this as well to help figure out Key Cache, etc. but the first thing is getting the OS to use all that RAM.

if the hugemem kernel is installed, he can access the 'excess' memory
–
warrenJun 8 '10 at 1:38

Not all of it, and it will be application dependent. That means then you can access that memory but are having extra io as the OS is trying to swap the memory locations back and forth since it's mathematically above what it can support. This is exactly like enabling PAE on windows. You only get so far and in heavy usage it can become a hindrance instead of something that helps.
–
RaginBajinJun 10 '10 at 2:14

Wrong command. mysqld starts a new server daemon. mysql starts a client to connect to the server daemon. mysql is the one you need here. You can run the client and the daemon on the same machine. The most likely error you will get when you type mysql is Access denied. If that happens, try the -u and -p options to add an appropriate username and password.
–
LadadadadaJun 8 '10 at 12:39

You've not got a lot of users on via mysql yet its consuming 98.7% of your CPU in the screenshot of 'top' is this representative of the way the system is behaving most of the time? If so then something is very wrong (and the high CPU usage is a key symtom of the actucal problem). Even under heavy load with lots of memory, mysqld should be spending a lot of its time in I/O wait.

This bug describes problems with high CPU using 64bit MySQL - although an older version.