Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.