Un buen script que analizará el rendimiento de MySQL es Tuning-Primer. Nos dará consejos para establecer algunas variables de la configuración, basándose en la información que proporciona el servidor.
Para usarlo:
# wget http://www.day32.com/MySQL/tuning-primer.sh
# chmod 755 tuning-primer.sh
# ./tuning-primer.sh
My.cnf
A continuación copio la configuración genérica para un servidor mysql más optimizado que lo que viene por defecto habitualmente en el archivo my.cnf.
Asume que hay al menos 1 GB de RAM y me ha resultado compatible con las versiones de MySQL 3.23.x, 4.0.x y 4.1.x
Si jamás va a usarse el servidor para conexiones remotas, y sólo conectaremos desde localhost, podemos activar “skip-networking” teniendo en cuenta que no podremos conectar por TCP/IP desde phpmyadmin, por ejemplo, teniendo que adecuar la configuración.
# The following options will be passed to all MySQL clients [client] port = 3306 socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
[mysql.server] user=mysql basedir=/var/lib # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock
# skip-innodb skip-locking
# CONNECTION LIMITS max_connection = 500 max_user_connections = 500
# QUERY-CACHE query_cache_limit=16M query_cache_size=304M query_cache_type=1
# TIMEOUTS interactive_timeout=20 wait_timeout=20 connect_timeout=6 max_connect_errors=10
# TUNING max_allowed_packet=16M
thread_cache_size=192
table_cache=1024 key_buffer=92M join_buffer=16M record_buffer=16M sort_buffer_size=16M read_buffer_size=16M myisam_sort_buffer_size=64M
# Try number of CPU's*2 for thread_concurrency thread_concurrency = 4
# Don't listen on a TCP/IP port at all. This can be a security enhancement. #skip-networking
# Replication Master Server (default) # binary logging is required for replication #log-bin
# required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1
# Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname
[mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
[safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/lib/mysql/mysql.pid open_files_limit=8192
[mysqldump] quick max_allowed_packet = 16M
[mysql] no-auto-rehash
[isamchk] key_buffer=32M sort_buffer=32M read_buffer=16M write_buffer=16M
[myisamchk] key_buffer=32M sort_buffer=32M read_buffer=16M write_buffer=16M
[mysqlhotcopy] interactive-timeout