How to get best configurations for MySQL my.cnf

[harrygreen90]

What is the best way to to get best configurations for MySQL my.cnf? I want to configure my.cnf best for my server but not sure which tools or ways to do this? any suggestions?

 

[Rob Whisonant]

Fire up phpmyadmin and click on the "Status" tab. Now look at the 2 pages under this tab.

1. All Status Variables. Look at the red values. This will show you where things are slowing down or having issues.

2. Advisor. This will show you recommendations on what to change.

It's best to get some traffic flowing to put some stress on the mysql server first, before reviewing these pages.

Check these pages over time to see if things change because of new scripts installed, more traffic etc.

 

[harrygreen90]

harrygreen90

Fire up phpmyadmin and click on the "Status" tab. Now look at the 2 pages under this tab.

Never seen this way before. I must check it out.

 

[ProResellerHost_Jack]

Best answer

Harry,
One alternative way of testing your MySQL configuration is to test it with the MySQLTuner script. This script offers you the ability to see if there are specific pieces of the configuration that may give you issues with performance and where you could adjust those to increase that performance.
I've ran it on some of my websites and have come away with some decent information.

You can find that on GitHub: github.com/major/MySQLTuner-perl (note: I have no affiliation with the script, I just think it's a good script)

 

[harrygreen90]

harrygreen90

You can find that on GitHub: github.com/major/MySQLTuner-perl (note: I have no affiliation with the script, I just think it's a good script)

I will check it out. Thanks for sharing!

 

[digitalbs]

digitalbs

You took the words right out of my mouth.

MySQLtuner is a powerful tool. Check it out and it's very easy to use.

 

[harrygreen90]

harrygreen90

Sure I applied but I don't see the difference.
Is there a way to test before and after applied it to my.cnf file?

 

[digitalbs]

digitalbs

Did you save the my.cnf file?

Did you restart mysql after you made the changes?

 

[harrygreen90]

harrygreen90

I did.
But i want to know if new configuration file will give better performance or not. How to test that?

 

[digitalbs]

digitalbs

Blast some junk traffic to your website to simulate a server / mysql load