Optimize MySQL - MySQLTuner

[Sp32]

do:

vi /etc/my.cnf/

Then look for the lines that say
max_connections =
wait_timeout =
interactive_timeout =

then change the value of what they equal.

so for max_connections (>150)

find max_connections and change it to 150, if max_connections isn't there, add it like so:

max_connections = 300

[JGM]

Thanks buddy, you said it is better if MySQL has been running for at least 48 hours and we had the server rebooted yesterday so will try it tomorrow and see what happens.

Thanks for your help

JGM.

[Lifetalk]

@JGM
Couple suggestions for you.

MySQL Tuner tells you that your current configuration is set to utilize a maximum of 23% installed RAM. While this might appear to be an 'efficient' setup, it isn't. Technically, it's just redundant resources that you COULD use, but are NOT. Up your max_connections count. You're hitting the limit right now (150). Bring it up to around 500, you should still be within the memory limit. A good memory limit is anywhere between 60-75% of installed RAM.

You're also being suggested an Optimize operation on the tables. Tables that have a lot of 'SELECT' queries being run on them will often end up being fragmented. A scheduled optimize job is the best way to go about it. BUT, running it once in a while is good too. To do it, issue this command:

mysqlcheck -u USERNAME -p --auto-repair --check --optimize --all-databases

It'll automatically check, optimize, and repair all your databases. Depending on how fragmented the tables are, you may or may not notice a performance increase. But your database will

You also have an extremely (and mind you, extremely is an understatement here!) large wait and interactive timeout value. A suitable timeout value, good for MOST applications, is anywhere between 5-8 seconds. 300 and 600 seconds is overkill. Adjust that.

Increase your table_cache size to around 4000-5000 from the default 128. Adjust later as needed, based on continued review. Increase the query_cache_size to 64M; you have a lot of spare RAM.

Don't worry about persistent connections; they're fine as long as you have a reasonable timeout value specified. In fact, persistent connections are good, because with modern systems, you can raise the max_connections limit to a significantly higher amount without impacting performance a lot.

[JGM]

WOW Lifetalk! Thanks for all the information! I need to leave it alone for now though until MySQL has been running for 24 hours and I will make the changes then. Sp32 said to use vi to edit stuff but how do you actually edit stuff? I have never done this before so I could do with knowing how to make a backup too before going further.

Thanks!

JGM.

[Lifetalk]

Copy your my.cnf to a different location, use the command:

cp /etc/my.cnf /some/safe/location/

Then use nano to perform the edits. If you're new to linux and bash, vi is not the best idea to start with. If your system does not already have 'nano', do a:

yum install nano

Else:

nano file.ext

Nano is more notepad like, so you should be able to edit with relative ease.

The edits I mentioned, aren't really dependent on time frame. The optimization, for example, is always accurate. So is the connection count. I've been using mysqltuner for a little over 2 years now on a box that is basically a dedicated mysql server only, so even though mysqltuner says it may not entirely be accurate, it generally is. Especially when it shows you that it's already hitting limits within hours of starting up.

The 24 hour window is for it to analyze DB transactions over a period of time to report on their efficiency. But if you see it hitting limits already, then it definitely needs the tweaks

[JGM]

Cheers for the help buddy very much appreciated!

JGM

[rohan123]

do:

vi /etc/my.cnf/

Then look for the lines that say
max_connections =
wait_timeout =
interactive_timeout =

then change the value of what they equal.

so for max_connections (>150)

find max_connections and change it to 150, if max_connections isn't there, add it like so:

max_connections = 150

you are wrong man,

That script shows current settings and tell us to increase or decrease it.

In JGM's screenshot I see his mysql using 151 connection out of 150

so He should increase the connections as mysqltuner is showing max_connections (>150)

150 is current setting he have... > means he should increase it

max_connections = 200 or more ( the more u increase the more ram it will need )

[Sp32]

I was using it as an example, I didn't mean it literally, however I will change it to avoid confusion if I was unclear. Also I recommend nano if you don't want to learn how to use vi.

[SS-Chris]

Hello there,

The variables it states to adjust are can be found in the /etc/my.cnf file. If there is no variable in that file with the names of the "variables to adjust" recommendations, then the variable is using its default value. It looks like there are a few recommendations, along with some general statements as well. You will need to edit the file through SSH using an editor, like vim, nano or any other one that you may like.

Chris


Edit - sorry, seems others have already posted regarding this, guess I should have refreshed the page.

[GoPantheoN]

Thank you Sp32 and LifeTalk