I'm looking for ways to auto optimize MySQL for a shared hosting server to keep the cache from getting fragmented and built up. this is a 74GB server so I have a lot of ram.
So far I have found the following options:
#!/bin/sh
#solidshellsecurity.com
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
and
1 * * * * mysqlcheck --all-databases --optimize > /dev/null 2>&1
Are any of these ways good or are there better ways to reduce the build up fragmented cache that slows performance?
my.cnf just in case it maters:
[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
max_connections = 500
key_buffer = 2048M
myisam_sort_buffer_size = 256M
join_buffer_size = 256M
read_buffer_size = 256M
sort_buffer_size = 256M
read_rnd_buffer_size = 64M
table_cache = 2048
thread_cache_size = 16K
wait_timeout = 60
connect_timeout = 60
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 256M
max_connect_errors = 15
query_cache_limit = 32M
query_cache_size = 256M
query_cache_type = 1
low_priority_updates=1
concurrent_insert=ALWAYS
log-error=/var/log/mysql/error.log
tmpdir=/home/mysqltmp
myisam_repair_threads=16
innodb_force_recovery=6
[mysqld_safe]
open_files_limit = 8192
log-error=/var/log/mysql/error.log
[mysqldump]
quick
max_allowed_packet = 512M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
