Introducción:

Existe mucha incognita y dudas mediante como optimizar o siquiera igualar un el motor MySQL en nuestro sistema para obtener mayor desempeno del mismo.
Hay varios análisis a realizar y considerar a la hora de optimización y todo hasta el minimo punto cuenta como ventaja, hay que considerar optimizar las consultas hechas al motor, la memoria fisica otorgada a cada proceso del motor, si existe o no mas servicios ejecutandose en ese servidor entre otras. Por la presente se otorga algunos tips a considerar para intentar optimizar el motor y sacarle la mayor ventaja posible a evitarse los cuellos de botellas.

OPTIMIZACIÓN DEL ARCHIVO DE CONFIGURACIÓN MY.CNF O MY.INI

Para desconocimiento sobre la efectividad de configuración de su archivo principal del motor, este script en bash (aplicable solo a sistemas tipo Unix) puede echarles una mano en el proceso de modificar my.cnf a una mejor ventaja del equipo:

http://day32.com/MySQL/tuning-primer.sh

Herramientas para Optimizar MySQL


La fórmula mágica del consumo de memoria


Memoria MySQL = key_buffer_size + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)

Dicho script anterior les comentara que valores podrian ser optimos para configurar o modificar en su motor MySQL, basta con subirlo al localhost donde este alojado y funcionando MySQL y ejecutar dicho script.

Valores optimizados:

MyISAM key cache


Variable
key_buffer_size =

Consulta SQL:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.TABLES
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

Comando shell script:

find /var/lib/mysql -name '*.MYI'|xargs du -shc

InnoDB Buffer Pol

Variable
innodb_buffer_pool_size =

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.TABLES
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

En KB:

SELECT SUM(data_length+index_length)/POWER(1024,1) IBPSize_KB
FROM information_schema.TABLES WHERE engine='InnoDB';

En MB:

SELECT SUM(data_length+index_length)/POWER(1024,2) IBPSize_MB
FROM information_schema.TABLES WHERE engine='InnoDB';

En GB_

SELECT SUM(data_length+index_length)/POWER(1024,3) IBPSize_GB
FROM information_schema.TABLES WHERE engine='InnoDB';


Usando MyISAM se recomienda usar ROW_FORMAT=FIXED
Esto converite internament todos tus campos VARCHAR a CHAR.
La tabla MyISAM será más grande, pero ejecutar consultas select será mucho más rápido.

ALTER TABLE tblname ROW_FORMAT=FIXED;

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ROW_FORMAT=Fixed;')
FROM information_schema.tables
WHERE table_schema
NOT IN ('information_schema','mysql')
AND engine = 'MyISAM' ORDER BY data_length



Ejemplo fichero my.cnf optimizado

# Security
# No community software I am aware of actually needs LOAD DATA LOCAL,
# so I just disable it. See:
# http://dev.mysql.com/doc/refman/5.1/en/load-data-local.html
local-infile             = 0
# This prevents granting access to stored procedures automagically just
# for creating them.
automatic_sp_privileges  = 0
# safe-user-create prevents autocreating users with the GRANT statement
# unless the user has the insert privilege on mysql.user
safe-user-create         = 1
# secure-auth is probably not relevant if your server was built in the
# past several years. I just like it on.
secure-auth              = 1
# Remove skip-show-database if you use phpMyAdmin or a similar tool to
# manage your databases, it will just frustrate you or your users.
skip-show-database
# This limits where the LOAD_FILE, LOAD_DATA and SELECT INTO OUTFILE
# statements can read from or write to. This is a good option to set.
secure-file-priv         = /tmp


# Networking
# I have replication setup, on a separate interface, so I bind MySQL to
# a socket and the address for that interface. If you're just using a
# single machine, use
# skip-networking
# instead. Sockets are faster than TCP connections.
bind-address            = 192.168.0.1
# There's no reason to waste time resolving domain names. If the ip
# changes, we'll know.
skip-name-resolve


# Tuning
# See my first guide at
# http://vekseid.com/blogs/vekseid/optimizing_a_server_for_mysql
# For how to enable HugePages so you can use the large-pages option
large-pages
# The client default is 16M, while the server default is 1M. Setting
# the server to 16M can make some large operations easier.
max_allowed_packet       = 16M
# There's no serious reason to have a long interactive timeout. If you
# are low on connections, you shouldn't set this higher than
# wait_timeout
interactive_timeout      = 3600
# I am of the opinion that the default value is far too high. IF you
# use persistent connections, even a timeout of 300 may be too high.
wait_timeout             = 300
# The following two are best set to the same size, because the size
# of temporary tables is limited by the lower o the two.
# I have not found any benefit in increasing the value past my
# tmp_table_size default.
tmp_table_size           = 32M
max_heap_table_size      = 32M
# The next two lines replace the basic table_cache value as of MySQL
# 5.1. table_definition_cache should be big enough for every table
# in your database, plus temporary tables, and table_open_cache
# should be a reflection of how many of these will be open in a live
# connection at once - it will likely exceed your definition cache.
# It doesn't hurt to set these to large values. They don't take a lot
# of RAM and it's better than hitting the limit.
table_definition_cache   = 4096
table_open_cache         = 16384
# The default optimizer_search_depth is 62. This causes MySQL to take
# an obscene amount of time planning a query, though when it finally
# executes, it is pretty close to optimal. Since the vast majority of
# queries my software runs involve four or fewer relations, I set it
# to four.
optimizer_search_depth   = 4
# I'm not personally a fan of UTF8, multi-byte strings are less
# efficient and annoying, while websites perform just fine by using
# ampersand codes to represent nonstandard characters.
character-set-server     = latin1

# Buffers
# Past allocations of 256K, Linux switches from malloc () to the less
# time-efficient mmap (). Making buffers larger than 256k, then, is
# not necessarily a good idea. You will have fewer 'bad' queries,
# individually, but you lose out on the vast majority of other queries.
# read_rnd_buffer_size is an exception - especially on forums with
# verbose posters, a lot of times, this really is reading in a megabyte
# or so at a time, so a higher value is beneficial.
join_buffer_size         = 256K
sort_buffer_size         = 256K
read_buffer_size         = 256K
read_rnd_buffer_size     = 2M

# Threads
# 256K is now the default for 64-bit systems, this line is just a
# legacy from when I had it configured on 32-bits and was following
# the above advice.
thread_stack             = 256K
# max_connections is how many connections your server will tolerate at
# once, while thread_cache_size is how many of these your server will
# cache. There is no reason not to set these to be an equal number - I
# have seen no evidence that the trivial amount of RAM a low
# thread_cache_size is worth the performance hit of opening up a new
# thread under load.
# In realistic terms, you should 'tune to failure' - you don't want
# to support more active connections than your system can feasibly handle.
# 128 is a good number for most low-end servers produced these days.
# Increase if you have multiple drive arrays or faster disks.
thread_cache_size        = 256
max_connections          = 256

# Query cache
# I laid out reasons and limitations of the query cache in the previous
# article:
# http://vekseid.com/blogs/vekseid/mysql_query_cache_and_innodb_considerations
# The only additional point I would make here is that changing
# query_cache_min_res_unit from the default is effectively useless,
# especially if you flush regularly like the above article describes.
query_cache_type         = 1
query_cache_limit        = 256K
query_cache_size         = 256M
query_cache_min_res_unit = 4K

# MyISAM
# 16M is the most MySQL will store entirely in large-pages. Past that,
# it will start shunting some of it off to normal memory. Since I only
# use about 7 megs normally, this isn't a problem. Otherwise, however,
# it can make calculating how much space you need difficult.
key_buffer               = 16M
# Set this to the size of a filesystem block - e.g. 4k
key_cache_block_size     = 4K
# These two should both be the default values. If you are bulk-loading
# data from a script, you may want to increase bulk_insert_buffer_size
# to speed up operation.
bulk_insert_buffer_size  = 8M
myisam_sort_buffer_size  = 64M
# Auto-creates a backup when running the recover operation.
myisam-recover           = BACKUP
# By default, MyISAM tables will INSERT into deleted row space before
# appending to the end of the disk. In exchange for saving this trivial
# amount of space, once a row gets deleted, only one insert operation
# may occur at a time until holes are filled. Setting concurrent_insert
# to 2 stops this silly behavior, at the cost of wasting a bit of disk
# space, for a significant performance improvement in MyISAM tables.
concurrent_insert        = 2

# InnoDB
# The following three line are only necessary in MySQL 5.1, for loading
# the plugin which supports the new InnoDB file format.
# If you have 5.5 or later, skip these lines.
ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
default-storage-engine          = InnoDB
# In theory, you want this to encompass your entire database. In
# practice, you only have so much RAM available. Right now I 'need'
# 20 gigabytes in the buffer pool I only have 12.
# Fortunately, as long as you can store a big enough chunk that it
# gets a 99% hit rate, you will be fine.
innodb_buffer_pool_size         = 8192M
# As with everything, only move this if you know you need to.
# innodb_log_group_home_dir       = /data/mysql
# innodb_data_home_dir            = /var/lib/mysql
# I set 128M as the size of ibdata1 because that's how big individual
# extent entries are in the ext4 filesystem.
innodb_autoextend_increment     = 128
innodb_data_file_path           = ibdata1:128M:autoextend
# Supposedly, smaller is better because it makes recovery faster, even
# if larger means slightly better performance. I have no idea what the
# logic of this is - if I have a crash, downtime is expected. But I
# have not had InnoDB crash on me in three years now.
innodb_log_files_in_group       = 2
innodb_log_file_size            = 512M
# No reason to make the buffers too large, however.
innodb_log_buffer_size          = 2M
innodb_additional_mem_pool_size = 2M
# Setting innodb_flush_log_at_trx_commit to 0 causes InnoDB to only
# flush to disk once per second, improving performance considerably.
# In a community environment, you are more likely to lost a topic
# read entry or something equally trivial than a post, so the data
# is very rarely going to be critical, and users often have backups
# of their own posts.
innodb_flush_log_at_trx_commit  = 0
# file_per_table makes checking out which tables are doing the heavy
# lifting a lot easier, for sure. It is also required for the
# barracuda file format.
innodb_file_per_table
innodb_file_format              = barracuda
# Enabling strict mode helps prevent messing up creating or altering
# a table to support the new row formats in barracuda.
innodb_strict_mode              = 1
# I haven't played too much with innodb_thread_concurrency. In my
# experience, for web purposes at least, Intel architectures work best
# up to four times the number of cores. Your mileage may vary.
innodb_thread_concurrency       = 8
# I set this to sixty because I have obsessive compulsive disorder.
# Don't fret over it.
innodb_lock_wait_timeout        = 60
# O_DIRECT bypasses the operating system's handling of data. This
# can drastically improve how well your system handles large amounts
# of RAM by removing double buffers (once in InnoDB's cache, again
# in the filesystem's cache), at a slight cost to reliability.
# This appears to be more dramatic the more RAM you have.
innodb_flush_method             = O_DIRECT

# thread_concurrency is for Solaris only. It does not apply to your
# Linux box.
#thread_concurrency     = 10

# * Logging and Replication
#
# As the default configuration file says, as of 5.1 you can
# enable the general log at runtime.
#general_log_file         = /var/log/mysql/mysql.log
#general_log              = 1
#
# Error logging is defined in the mysqld_safe entry.
#
# Here you can see queries with especially long duration
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
# You can set this to non-integer numbers now. One second is rare
# enough for me that I consider it 'long'.
long_query_time     = 1
# In order to keep your sanity, you should only use this when
# developing software. It would be nice if developers of community
# software did track this more often.
#log-queries-not-using-indexes
#
# The binlog is for replication, so I've commented it out here.
# Setting sync_binlog to an extraordinarily high value (256 in my case)
# significantly reduces the load the binlog puts on the server.
#server-id                 = 1
#log_bin                   = /var/log/mysql/mysql-bin.log
#binlog_cache_size         = 256K
#sync_binlog               = 256
#expire_logs_days          = 14
#max_binlog_size           = 1G
#binlog_do_db              = include_database_name
#binlog_ignore_db          = include_database_name

# The following items are largely defaults as setup by Debian Squeeze
# If you are largely using InnoDB, only the mysqldump section is of
# any concern.
[mysqldump]
quick
quote-names
max_allowed_packet        = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
# Tab completion is a freaking sanity saver.

[isamchk]
key_buffer                = 256M
sort_buffer_size          = 256M
read_buffer               = 16M
write_buffer              = 16M

[myisamchk]
key_buffer                = 256M
sort_buffer_size          = 256M
read_buffer               = 16M
write_buffer              = 16M

A continuación pego y detallo archivo my.cnf en una Pentium 2Ghz con 1GB de ram:

[mysqld]
socket=/path/to/mysql.sock
datadir=/var/lib/mysql
skip-locking
skip-innodb

# MySQL 4.x has query caching available.
# Enable it for vast improvement and it may be all you need to tweak.
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M

# max_connections=500
# Reduced to 200 as memory will not be enough for 500 connections.
# memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections
# which is now: 64 + (1 + 1) * 200 = 464 MB
# max_connections = approx. MaxClients setting in httpd.conf file
# Default set to 100.
#max_connections=200

#interactive_timeout=180
interactive_timeout=100
#wait_timeout=180
#wait_timeout=100
# Reduced wait_timeout to prevent idle clients holding connections.
#wait_timeout=30
wait_timeout=15
connect_timeout=10
# max_connect_errors is set to 10 by default
#max_connect_errors=10

#table_cache=256
#table_cache=1024
# Checked opened tables and adjusted accordingly after running for a while.
table_cache=512
#tmp_table_size=32M by default
#thread_cache=128
# Reduced it to 32 to prevent memory hogging. Also, see notes below.
thread_cache=32
# key_buffer=258M
# Reduced it by checking current size of *.MYI files, see notes below.
key_buffer=128M
# Commented out the buffer sizes and keeping the default.
# sort_buffer_size=2M by default.
#sort_buffer_size=1M
# read_buffer_size=128K by default.
#read_buffer_size=1M
# 1Mb of read_rnd_buffer_size for 1GB RAM -- see notes below.
# read_rnd_buffer_size=256K by default.
#read_rnd_buffer_size=1M
# myisam_sort_buffer_size used for ALTER, OPTIMIZE, REPAIR TABLE commands.
# myisam_sort_buffer_size=8M by default.
#myisam_sort_buffer_size=64M
# thread_concurrency = 2 * (no. of CPU)
thread_concurrency=2

# log slow queries is a must. Many queries that take more than 2 seconds. 
# If so, then your tables need enhancement.
log_slow_queries=/var/log/mysqld.slow.log
long_query_time = 2
slow_query_log = 1
slow_query_log_file= /var/log/mysqld-slow-querys.log

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash


[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout 

[client]
socket=/path/a/mysql.sock


Listado completo de variables:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
http://dev.mysql.com/doc/refman/5.5/en/table-cache.html


A continuación, una descripción basica de cada funcionalidad presentada en mi archivo de configuración.

Las variables más importantes son query_cache_size y key_buffer_size que pueden contener valores altos
de hasta 512M para equipos con suficiente memoria RAM > 4GB.

query_cache_size:

key_buffer_size:

EL tamano total de esta variable es el tamano otorgado al motor para guardar los indices de las tablas MyiSAM, mientras mayor es el tamano de dicho buffer, mas rapido puede la consulta SQL finalizarse en una busquedad, lo ideal como tamano para esta variable es asignarle un 1/4 pero no mas de la mitad del total de memoria ram fisica de la cual se dispone en el equipo. Dicho tamano sera suficiente para guardar todos los indices de tablas MyiSAM (El tamano total de todos los archivos .MYI del servidor)

Una manera sencilla para verificar el comportamiento del buffer es examinar cuatro variables adicionales: key_read_requests, key_reads, key_write_requests y key_writes. (Una forma de verificar estas variables es buscarlas en SHOW STATUS o SHOW VARIABLES LIKE 'key%'). Al obtener el resultado de estas cuatro, si divides el valor total de la variable key_read con key_reads_requests el resultado no debe ser mayor a 0.01 por igual manera si divides key_write con key_write_requests no debe superar el valor 1. Si supera dichos valores, deberas optimizar de mejor manera esta variable 'key_buffer_size' ya sea aumentando o disminuyendo su tamano.

table_cache:

Por defecto es de 64MB. Cada vez que MySQL accesa una tabla, la guarda en la cache, si el sistema accesa a muchas tablas, es mas rapido para este tenerlas en cache, MySQL al ser multi-hilo puede ejecutar muchas consultas queries en una sola tabla al mismo tiempo, y cada peticion de estas realizadas abrira una tabla (aun siendo la consulta en la misma tabla). Es conveniente examinar la variable 'table_cache' y verificar luego la variable 'open_tables' si verificas que permanece el tamano de esta variable igual que la variable 'table_cache' y luego la variable 'opened_tables' comienza a crecer masivamente, deberias incrementar el valor de 'table_cache' si posees memoria suficiente para ello.

Las variabels "buffer size":

myisam_sort_buffer_size
sort_buffer_size
read_buffer_size
read_rnd_buffer_size

Estas variable son importantes para que myisamchk verifique las condiciones de las tablas MyISAM al iniciar el motor MySQL o reiniciarlo.
  
read_rnd_buffer_size:

El tamano definido en esta variable es importante ya que mediante esta se utiliza espacio para ordenar consultas queries que requieran ORDER BY, dependiendo el tamano total de resultados a retornar y si el espacio de esta variable lo permite estara o no bien optimizada la consulta.

thread_cache:

Si posees un servidor con conecciones masivas de distintos clientes, ofrecele tamano suficiente a esta variable para que en SHOW STATUS deje de aumentar masivamente esta variable.

---

Monitorización


myTop es un monitor para MySQL de consultas en tiempo real.

innotop es quizás el mejor clon de top para MySQL.

---

Ejemplo Monitorix MySQL Foro de elhacker.net

300 consultas por segundo aproximadamente

---

Linux performance tuning & stabilization tips (mysqlconf2010)