Automatically Optimizing MySQL

[TruvisT]

Found this on one of our old servers during spring cleaning while moving sites over to our new highend servers. Enjoy.
 


#!/bin/sh

mysql -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do
mysql -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
if [ "$datafree" -gt 0 ] ; then
fragmentation=$(($datafree * 100 / $datalength))
echo "$database.$name is $fragmentation% fragmented."
mysql -NBe "OPTIMIZE TABLE $name;" "$database"
fi
done
done
SRC: http://blog.solidshellsecurity.com/2013/05/31/automatically-optimizing-mysql/

 

[Reece-DM]

Few changes?

http://meinit.nl/optimize-only-fragmented-tables-mysql

Nice share though

 

[walesmd]

I knew your script appeared to be a bit excessive, assuming CentOS 6.4 64bit:

wget http://goo.gl/BLhej -O /etc/yum.repo.d/mariadb.repo
sudo yum install MariaDB-server MariaDB-client
sudo yum install MariaDB-Galera-server MariaDB-client galera
sudo /etc/init.d/mysql start

 

[WebSearchingPro]

Found this on one of our old servers during spring cleaning

 

Don't you just love it when you find little scripts that keep things running smoothly or funny comments in the code far after your forgot it was there?

Its like Christmas! (sort of) in a sort of nostalgic way.

 

[JDiggity]

Rename this to optimize table. As it doesn't optimze mysql.

 

[Kris]

wget --no-check-certificate https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

perl mysqltuner.pl

• Repair all DBs : mysqlcheck -Ar
• Optimize all DBs: mysqlcheck -Ao

 

[JDiggity]

@Kris, I never really looked at it but is there a way to actually optimize the config portion automatically?

 

[Kris]

Not really. You take the output it gives you and make the changes accordingly in /etc/my.cnf

Quick repair, optimize and optimizing my.cnf never let me down

 

[JDiggity]

Damn, I wish there was a a program that did it for me. There it is people write that program and you guys will be a hero of the low end community.

 

[jcaleb]

I think optimizing my.cnf really needs human judgement

 

[vanarp]

I am aware of this script but never really had to use it for my small database needs.

https://github.com/major/MySQLTuner-perl

 

[TruvisT]

I think optimizing my.cnf really needs human judgement

Truth.

It can be done automatically, assuming the script was moniting the activitiy and edited based on that along with usage and system resources. But would it be reliable?

 

[Kris]

It can be done automatically, assuming the script was moniting the activitiy and edited based on that along with usage and system resources. But would it be reliable?

Not really. You can end up with an outlier of traffic one day which would try to increase the query_cache_size and tmp_table_size well over 128M (which is the recommended limit to avoid any degradation of performance.)

In the end, I always use mySQL tuner, and use those numbers as a guideline when editing my.cnf.

If it's telling you to increase certain values (while also warning you some will cause a slow-down) that's just judgement.

 

[acd]

From a theoretical perspective, yes. You could make an application that monitored system CPU, RAM, and IO needs,disk cache hit/miss, and read styles, compared to mysql QPS, avg query time per query type (baysian match), http pages service rate, cpu time per page, page popularity, varnish/nginx/etc cache size, churn rate, hit/miss rate, total service pool and expire-before-reuse rate and turn that into a reasonable set of auto-tuning parameters based on your usage case and application stack.

The problem would be you'd use more resources than you could expect to save unless you processed the log files off-system. Also, you'd need a reasonable understanding of machine learning algorithms that most low-end providers simply don't have the expertise for. Any site in the multiple-dedicated range can afford to hire a fellow to tune their application stack, if they don't already have that knowledge in-house. Smaller than that and it's far cheaper to either DIY or upgrade to a more capable box. An automated system would be like killing some cockroaches by beating them with a 1U server.

 

[TruvisT]

From a theoretical perspective, yes. You could make an application that monitored system CPU, RAM, and IO needs,disk cache hit/miss, and read styles, compared to mysql QPS, avg query time per query type (baysian match), http pages service rate, cpu time per page, page popularity, varnish/nginx/etc cache size, churn rate, hit/miss rate, total service pool and expire-before-reuse rate and turn that into a reasonable set of auto-tuning parameters based on your usage case and application stack.

The problem would be you'd use more resources than you could expect to save unless you processed the log files off-system. Also, you'd need a reasonable understanding of machine learning algorithms that most low-end providers simply don't have the expertise for. Any site in the multiple-dedicated range can afford to hire a fellow to tune their application stack, if they don't already have that knowledge in-house. Smaller than that and it's far cheaper to either DIY or upgrade to a more capable box. An automated system would be like killing some cockroaches by beating them with a 1U server.

VEry well said. Great analogy too!

 

[ICPH]

When i am running the OP (first post of this topic) script via cronjob monthl, it always send me e-mail with output:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

But when i run that script manually, it always processing database good, but i think somewhere in the process it still shows the mentioned error. Seems that the script may need tweaking to work 100%. I am not a coder so i suppressed any errors by adding: &>/dev/null
at the end of the line where i am launching the script.