Consejos para MySQL

ByLuis

Una serie de consejos bastante útiles para usar en MySQL, algunos ya conocidos y otros no tanto:

  • Optimiza tus queries para la caché: por ejemplo es preferible crear una condición en el WHERE que sea fecha>’$fecha’ que fecha>now(), ya que indicando que fecha exáctamente, si la query se vuelve a ejecutar puede ser rescatada de la cache.
  • Usa mismo tipo de campos para las JOINs: si los campos son de distinto tipo en las tablas, al hacer el join MySQL no es capaz de usar uno de los índices (suponiendo que estén indexados, claro).
  • No uses ORDER BY RAND(): es preferible obtener una lista de ids aleatorios mediante código y luego obtener los registros por esos IDs.
  • Evita SELECT *: cuantos más campos leas, más información recuperas y más lentas serán las queries.
  • Usa ENUM en vez de VARCHAR: cuando uses un campo para un número limitado de strings es preferible usar ENUM ya que internamente se usa un tinyint.
  • Utiliza PROCEDURE ANALYSE(): comprobará tu tabla y te recomendará cambios para mejorar su optimización.
  • Usa campos de longitud fija: hará que la tabla sea más rápida.

Más consejos interesantes en el post original.

Top 20+ MySQL Best Practices

Vía / @jlantunez

Similar Posts

Engine MySQL para AWS S3

ByLuis

AWS S3 es un servicio de almacenamiento en red de Amazon que muchos lugares utilizan para ahorrar costes de disco duro. Para trabajar con este servicio existe una API que nos facilita la tarea, por lo que su uso no es complicado, aunque lógicamente lo es más que simplemente acceder de disco duro propio.
Para facilitar más las cosas, existe un motor para MySQL que permite transformar el protocolo de S3 en un modelo de datos para MySQL, pudiendo crear tablas y trabajar con ellas. La cuenta S3 viene a ser un comando CREATE SERVER, los buckets tablas, los items filas, las claves de los items serán claves primarias y el contenido de los items BLOB o VARCHAR.
A MySQL Storage Engine for AWS S3 (PDF)

|

Script PHP para sincronizar estructuras de BD MySQL

ByLuis

Interesante script que permite dados dos estructuras del MySQL, obtenidas por ejemplo usando un mysqldump, ver que diferencias hay entre ambos y devuelve las queries necesarias para igualar las dos estrucuras.

Por ejemplo, si tenemos la estructura siguiente:

CREATE TABLE IF NOT EXISTS `archive` (
`id` int(11) NOT NULL auto_increment,
`topFile` varchar(255) NOT NULL default '',
`msgId` int(11) NOT NULL default '0',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP,
`topTpl` varchar(255) NOT NULL default '',
`file` varchar(255) NOT NULL default '',
`template` varchar(255) NOT NULL default '',
`instanceKey` varchar(255) NOT NULL default '',
`orderby` varchar(25) NOT NULL default '',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10256 ;

Y este otro:

CREATE TABLE IF NOT EXISTS `archive` (
`id` int(11) NOT NULL auto_increment,
`topFile` varchar(255) NOT NULL default '',
`msgId` int(11) NOT NULL default '0',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`topTpl` varchar(255) NOT NULL default '',
`file` varchar(255) NOT NULL default '',
`template` varchar(255) NOT NULL default '',
`instanceKey` varchar(255) NOT NULL default '',
`encoding` varchar(50) NOT NULL default '',
`orderby` varchar(255) NOT NULL default '',
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10256 ;

Nos devolvería las siguientes sentencias:

ALTER TABLE `archive` ADD `encoding` varchar(50) NOT NULL;
ALTER TABLE `archive` MODIFY `orderby` varchar(255) NOT NULL;
ALTER TABLE `archive` MODIFY `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
ALTER TABLE `archive` DROP PRIMARY KEY;

Un script bastante útil cuando, debido al despiste, tenemos diferencias entre la base de datos de desarrollo y la de producción y no sabemos qué cambios hemos realizado.

Database structure synchronizer

MySQL Migration Toolkit

ByLuis

mysql.pngEl otro día nos preguntaban una forma de migrar una base de datos Access a otra MySQL. En ese momento no conocía ninguna aplicación que pudiera hacerlo, pero hoy me he enterado que los propios de MySQL tienen MySQL Migration Toolkit, que permite mediante una serie de pasos realizar una migración de forma correcta.
mysql_migration.png
Entre las base de datos que podemos migrar nos encontramos: Oracle, Microsoft SQL Server, Microsoft Access y otras. A parte reduce riesgos usando una metodología, ahorra costes incrementando la productividad, elimina días de trabajo de querys manuales, test y debug.
He visto también en en artículo desde el que hacemos referencia, que MySQL Workbench, del que hicimos referencia en su día, no está disponible para su descarga como lo estaba anteriormente, esperemos que sea porque anden mejorando este producto.
MySQL Migration Toolkit
Vía / Conexiones Razonables

Replicación en MySQL

ByLuis

Replicar una base de datos MySQL puede sonar a algo muy complicado, resulta más sencillo siguiendo el siguiente artículo que pasamos a comentar. Se trata de una de las base de datos (master) hacer que genere un log y otra (slave) lo lea.

Lo primero es añadir las siguientes líneas al fichero my.conf:

log-bin=<nombre-fichero-log>
binlog-do-db=<nombre-base-datos>
server-id=<numero-servidor>

El nombre de la base de datos si que debe ser el mismo en ambos servidores, los otros dos pueden ser cualesquiera, aunque el nombre del log suele ser el de host seguido de “-bin” y el id del servidor suele ser “1” para el maestro y “2” para el esclavo.

Después debes crear un usuario el cual se usará para replicarse con el esclavo:

GRANT REPLICATION SLAVE ON *.* TO '<usuario>'@'%' IDENTIFIED BY '<contraseña>';

También se puede usar el nombre del servidor esclavo en vez de %. Una vez creado el usuario, actualizamos los datos mediante FLUSH PRIVILEGES y FLUSH TABLES.

Si ejecutamos SHOW MASTER STATUS nos devuelve unos datos que son importantes para la replicación, sobre todo la columna File y la columna Position.

Una vez que el maestro ya está configurado, podemos empezar con la configuración del esclavo, eso sí, antes del inicio de la replicación ambas bases de datos deben tener la misma información. Para configurar el esclavo deberemos modificar su my.conf de la siguiente forma:

server-id=<numero-servidor>

siendo el número distinto al que usamos en el maestro, en nuestro ejemplo el maestro era “1” y el esclavo “2”. Después deberemos ejecutar la siguiente sentencia:

CHANGE MASTER TO MASTER_HOST='<servidor-maestro>', MASTER_USER='<usuario-replicacion>', MASTER_PASSWORD='<contraseña-replicacion>', MASTER_LOG_FILE='<fichero-log>', MASTER_LOG_POS=<posición-log>;

Y ya por último reiniciamos MySQl, paramos el esclavo(STOP SLAVE), cargamos los datos del maestro (LOAD DATA FROM MASTER) y arrancamos el esclavo (START SLAVE).

MySQL replication

Vía / PixelGroovy

Obtener logs de todas las queries con MySQL 5.0

ByLuis

En algunas ocasiones necesitamos tener un log de todas las queries que se realizan, sobre todo en preproducción para poder tener una visión general de qué consultas se realizan y del coste.
En MySQL 5.1 es sencillo porque se puede poner el tiempo máximo para slow queries a milisegundos, por lo que todas las queries serían lentas y obtendríamos datos de sobre las consultas bastante interesantes.
En MySQL 5.0 tan solo se puede reducir el tiempo de slow queries a un segundo, por lo que es necesario usar tcpdump para capturar el tráfico de MySQL a un log y luego mediante otros comandos obtener las queries.
Logging all the queries with MySQL 5.0

Optimizaciones para MySQL

ByLuis

Ya el otro día hablábamos sobre las optimizaciones que podríamos realizar en nuestro entorno LAMP referente a Apache y PHP. Ahora, la gente de IBM nos vuelve a ofrecer otro artículo en el que nos indican cosas que hacer para mejorar el rendimiento de nuestra base de datos MySQL.
Las mejoras que se pueden realizar se dividen en tres conceptos: optimizar la máquina, configurar el proceso de MySQL y optimizar las querys. El servidor de base de datos nos puede dar problemas ya que una base de datos consume muchos recursos, lo cual se puede solucionar en muchos casos mejorando la CPU y la velocidad de disco duro, y si no, aumentar la RAM, cosas lógicas y normales.
Para la optimización de MySQL y el tratamiento de las querys lentas, se pueden realizar varias acciones. La primera de ella sería tener un log de qué querys emplean mucho tiempo en ejecutarse, para lo cual deberemos cambiar en la configuración de MySQL el parámetro log-slow-queries para que se guarden qué querys tardan más de cierto tiempo.
Otro tema importante es cachear las querys que se realizan, para ello hay dos formas, la que nos cuentan en el artículo, que es mediante configuración, y la otra que es mediante código, porque si lo realizamos mediante código podemos controlar mejor que querys cacheamos.
Al igual que ocurre en Apache, en MySQL es importante controlar el número máximo de conexiones, el timeout y el número máximo de errores, ya que si configuramos mal estos valores nos podemos encontrar con rendimientos poco deseados.
En total, una serie de recomendaciones muy buenas que debemos tener en cuenta cuando nuestra aplicación web requiere ajustar los recursos al máximo.
Tuning LAMP systems, Part 3: Tuning your MySQL server