Automatically Optimizing MySQL

TruvisT

Server Management Specialist
Verified Provider
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/
 
Last edited by a moderator:

walesmd

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

Code:
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
 
Last edited by a moderator:

WebSearchingPro

VPS Peddler
Verified Provider
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.
 

Kris

New Member
Code:
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

New Member
@Kris, I never really looked at it but is there a way to actually optimize the config portion automatically?
 
Last edited by a moderator:

Kris

New Member
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 :)
 
Last edited by a moderator:

JDiggity

New Member
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.
 

TruvisT

Server Management Specialist
Verified Provider
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

New Member
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

New Member
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.
 
Last edited by a moderator:

TruvisT

Server Management Specialist
Verified Provider
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

Member
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.
 
Top