Query Cache: La consulta más eficiente es la consulta que nunca se corre

“The least expensive query is the query you never run.”

MySQL tiene una gran característica llamada “Query Cache“, que es muy útil para las tareas de optimización del rendimiento de MySQL, pero hay una serie de cosas que usted necesita saber.

Query Cache sólo funciona en consultas completas – Lo que significa que no funciona para subselects, inline views, partes de Union. No es compatible con las declaraciones preparadas de antemano y tampoco con cursores.

Sólo las consultas con SELECT se guardan en el caché. Los comandos SHOW y los procedimientos almacenados no.

Hay que evitar comentarios y espacios en el inicio de la consulta – el caché hace una optimización sencilla, comprobando si la la primera letra de la consulta es una “S” se procede con la búsqueda de consulta en la caché, si no – se pasa por alto.

La consulta debe ser determinista – Donde la consulta podría proporcionar los mismos resultados sin importar cuántas veces se ejecuta, si los datos siguen siendo los mismos. Si la consulta funciona con los datos actuales, no hay que utilizar funciones deterministas como UUID (), RAND (), CONNECTION_ID (), etc.

Con el paso del tiempo el caché puede sufrir fragmentación, lo cual reduce el rendimiento. Esto se puede detectar con valores altos en Qcache_free_blocks relacionado con  Qcache_free_memory. El comando FLUSH QUERY CACHE se puede utilizar para la desfragmentación del caché, pero esto puede bloquear el caché por largo tiempo para consultas grandes, lo cual podría ser poco óptimo para aplicaciones en línea.

La fragmentación actual se puede conocer mediante:

SHOW STATUS LIKE 'Qcache_free_blocks';

Cuando el valor se encuentra cerca de 1, es mejor.

Algunas veces con el paso del tiempo se vuelve innecesario. Mientras más se usa, más fragmenta, por lo que hay que estar corriendo FLUSH QUERY CACHE cada cierto tiempo, dependiendo de la carga, pero con un una tarea en cron esto se puede automatizar.

Hay una cantidad limitada de memoria utilizable. Como las consultas están siendo constantemente invalidadas desde el caché debido a actualizaciones en la tabla, esto significa que el número de consultas en el caché y en la memoria utilizada no puede crecer para siempre, incluso si hay una gran cantidad de consultas diferentes que se ejecutan. Por esta razón, cofigurar Query Cache con cierto valor implica vigilar y analizar Qcache_free_memory y Qcache_lowmem_prunes. Si no se está consiguiendo la mayor parte de lowmem_prunes, free_memory mantiene alto, entonces se puede reducir query_cache adecuadamente. De lo contrario se puede aumentar y analizar si aumenta la eficiencia.

La frecuencia de invalidación es controlada por la velocidad de cambio en las tablas. Esto da resultado impredecibles y por lo tanto, algunas veces un rendimiento indeseable. El otro gran problema con el caché es que está protegido por un único mutex. Entonces, en servidores con muchos núcleos, un gran volumen de consultas puede causar una contención mutex extensa.

Para conocer si Query Cache está activado:

SHOW VARIABLES LIKE '%query_cache%';

Para verifica si está funcionando, solamente hay que ejecetar dos consulta y ver la mejora en los distintos resultados de:

SHOW STATUS LIKE '%qcache%';

La consulta se debe correr usando SQL_CACHE depués de SELECT, por ejemplo:

SELECT SQL_CACHE url FROM options WHERE id = '1'

Query Cache funciona en modo por demanda. Si se habilita el caché entonces se operará en modo “Cache Everything“. En ciertas ocaciones es posible que se desee almacenar en caché sólo algunas de las consultas – en este caso se puede establecer query_cache_type en “DEMANDA” para sacar provecho de la ventaja de SQL_CACHE sólo para las consultas necesarias. Si no se utiliza en modo por demanda y se ejecuta en modo por defecto también se puede usar SQL_NO_CACHE para bloquear el almacenamiento en caché de ciertas consultas.

¿Cuándo no debe usarse?

Si la tabla se es afectada por una modificación todas las consultas derivadas de esta tabla se invalidan a la vez. Aunque algunas veces estas modificaciones no afectan el conjunto de resultados, MySQL no tiene manera de identificarlos por lo que se deshace de todas las consultas derivadas de la tabla. Esta es una de las principales características que limita la eficacia del caché.

¿Cuándo no es tan eficiente?

Si hay tablas con un volumen elevado de escritura y modificación, la eficiencia de Query Cache puede ser bastante baja, debido a esto. Además, si hay una gran cantidad de consultas almacenadas en el caché se puede ver afectada un poco la velocidad de actualizar.

Hay que vigilar su eficacia

En primer lugar hay que mirar el número de consultas – Com_select y ver cuantas de ellas se almacenan en caché. La eficiencia del caché sería:

Qcache_hits/(Com_select+Qcache_hits)

Se suma Qcache_hits con Com_select para obtener el número total de todas las consultas.

La eficacia no depende de las consultas que se almacenan en caché y el overhead que produzca. Una parte del overhead del caché es debido a las inserciones. Para ver la cantidad de consultas insertadas se utiliza:

Qcache_hits/Qcache_inserts

otra porción del overhead proviene de las declaraciones de modificación, esto se puede calcular con:

(Com_insert+Com_delete+Com_update+Com_replace)/Qcache_hits

Bueno, estos son algunos de los números con los que se puede jugar, pero es difícil saber lo que es bueno o malo, ya que mucho depende de la complejidad de la declaración, así como la cantidad de trabajo del caché entre otros.

Fuente: http://www.mysqlperformanceblog.com/2011/04/04/mysql-caching-methods-and-tips/

Usuarios y Bases de Datos en MySQL

mysql -u root -p -h localhost
SHOW DATABASES;
select user from mysql.user;

Todas las Bases de Datos de un usuario

SELECT user, host from mysql.db where db = '{DATABASE}';

Todos los permisos de un usuario

SHOW GRANTS FOR '{USER}'@'localhost';

Borrar el usuario (sí y solo sí a esta única tabla gestionaba) y luego la Base de Datos

DROP USER '{USER}'@'localhost';
DROP DATABASE {DATABASE}

Otra manera:

mysql --batch -u root -p -h localhost -e "SELECT user, host from mysql.db where db = '{DATABASE}'"

mysql --batch -u root -p -h localhost -e "DROP USER '{USER}'@'localhost'"

mysql --batch -u root -p -h localhost -e "DROP DATABASE {DATABASE}"

Agregar sitio en mi LAMP local de desarrollo

sudo su

cd /etc/apache2/sites-available/

cp default domain.lh

vi domain.lh
ServerAdmin noreply@domain.lh
ServerName domain.lh
ServerAlias www.domain.lh
DocumentRoot /srv/www/domain.lh/httpdocs/
ErrorLog /srv/www/domain.lh/logs/error.log
CustomLog /srv/www/domain.lh/logs/access.log combined
sudo a2ensite domain.lh

vi /etc/hosts
127.0.1.1 domain.lh
127.0.1.1 www.domain.lh
cd /srv/www;
mkdir domain.lh;
cd domain.lh;
mkdir httpdocs logs;
chown -R aptana:aptana httpdocs

/etc/init.d/apache2 reload
/etc/init.d/apache2 restart
mysql -h localhost -u root -p
CREATE DATABASE {DATABASE_NAME};

GRANT ALL PRIVILEGES ON {DATABASE_NAME}.* TO '{USER}'@'{HOST}' IDENTIFIED BY '{PASSWORD}';

FLUSH PRIVILEGES;

mysql -u {USER} -p {DATABASE_NAME} < backup.sql

Para algunas instalaciones de WordPress

mysql -u {USER} -p {DATABASE_NAME}

USE {DATABASE_NAME};

“Vanilla Installation” desde cero

wget http://wordpress.org/latest.tar.gz

tar -xvzpf latest.tar.gz --strip-components=1

De desarrollo a producción

UPDATE mch_options SET option_value = replace(option_value, 'http://www.domain.com', 'http://www.domain.lh') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE mch_posts SET guid = replace(guid, 'http://www.domain.com','http://www.domain.lh');

UPDATE mch_posts SET post_content = replace(post_content, 'http://www.domain.com', 'http://www.domain.lh');

UPDATE mch_users SET user_url = replace(user_url, 'http://www.domain.com', 'http://www.domain.lh');

Actualizando la base de datos después de mover la instalación

En WordPress cuando se mueve una instalación de un nombre de dominio hacia otro nombre de dominio diferente, en el programa cliente de la base de datos de MySQL es necesario ejecutar las siguientes actualizaciones o sustituciones:

USE {DATABASE_NAME}

UPDATE wp_options SET option_value = replace(option_value, 'http://{OLD_URL}', 'http://{NEW_URL}') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://{OLD_URL}','http://{NEW_URL}');

UPDATE wp_posts SET post_content = replace(post_content, 'http://{OLD_URL}', 'http://{NEW_URL}');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://{OLD_URL}','http://{NEW_URL}');

UPDATE wp_users SET user_url = replace(user_url, 'http://{OLD_URL}', 'http://{NEW_URL}');

Desde el programa cliente de MySQL

Lo primero que hay que hacer es entender la ayuda disponible del servidor de bases de datos MySQL:

mysql --help

También es muy importante conocer su versión:

Desde el Shell:

mysql -V

Desde el programa cliente de MySQL:

SELECT VERSION(), CURRENT_DATE;

Conexión

Sólo requiere de un {USER} y de un {PASSWORD}. Si el servidor se está ejecutando en un ordenador distinto desde donde estamos estableciendo la conexión, también se deberá especificar el nombre de {HOST}.

mysql -u {USER} -p {PASSWORD} -h {HOST}

Por ejemplo, para conectarse como el usuario root del servidor de bases de datos MySQL local se debe utilizar:

mysql -h localhost -u root -p

Bases de datos en el servidor

SHOW DATABASES;

Utilizando una base de datos por defecto

USE {DATABASE_NAME}

Esto le dice a MySQL que use a {DATABASE_NAME} como la base de datos por defecto para la interpretación de los comandos siguientes. Hasta el final de la sesión o hasta que se use nuevamente otro USE.

Ver la base de datos actualmente en uso

SELECT DATABASE();

Si usted no puede listar o usar las bases de datos es porque usted necesita solicitar a la persona encargada de administrar el servidor de bases de datos MySQL los permiso necesarios para hacerlo:

GRANT ALL ON {DATABASE_NAME}.* TO '{USER}'@'{HOST}';

Por favor consulte: http://dev.mysql.com/doc/refman/5.6/en/account-management-sql.html

Tablas contenidas en la base de datos actual

SHOW TABLES;

Ver la estructura de una tabla

DESCRIBE {TABLE_NAME};

Información de los índices de una tabla

SHOW INDEX FROM {TABLE_NAME};

Caso común:

Ingresar como root al programa cliente del servidor de la base de datos MySQL:

mysql -h localhost -u root -p

Crear una nueva base de datos:

CREATE DATABASE {DATABASE_NAME};

GRANT ALL PRIVILEGES ON {DATABASE_NAME}.* TO '{USER}'@'{HOST}' IDENTIFIED BY '{PASSWORD}';

FLUSH PRIVILEGES;

Por favor consulte: http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-data-definition.html

En algunos casos importar o restaurar una base de datos MySQL desde un archivo (Dump):

mysql -h localhost -u {USER} -p {DATABASE_NAME} < {FILENAME}.sql

Otros caso, puede ser, vaciar una base de datos existente:

DROP DATABASE {DATABASE_NAME};
CREATE DATABASE {DATABASE_NAME};

Para más información, por favor consulte: http://dev.mysql.com/doc/refman/5.6/en/tutorial.html