Pruebas de rendimiento en MySQL

ByLuis

Algo muy importante en cualquier desarrollo web son las pruebas de rendimiento, con ellas podremos prever cuales son los límites de nuestro sistema, ya sean a nivel de software o hardware. Las bases de datos deben ser algo fundamental en estas pruebas.

En el caso de MySQL, existe una herramienta disponible desde la versión 5.1.4 que nos permite realizar estas pruebas. mysqlslap es un programa de diagnóstico diseñado para emular la carga de un cliente para un servidor MySQL y ofrecer informes para cada estado. Funciona como si múltiples clientes accedieran a nuestro servidor.

mysqlslap [opciones] nombre_base_datos

Da la posibilidad de que las sentencias SQL se generen automáticamente, pero para unas pruebas correctas de rendimiento se deberían usar las sentencias que ejecuta nuestro sistema. Para lo cual recomiendo sacar por trazas las sentencias en un fichero y usar luego este fichero para simular a un usuario.

Hay que tener en cuenta que cuando lee un fichero usará para delimitar las queries el retorno de carro, por lo que cada query deberá ir en una única línea, aunque se puede usar la opción –delimiter para indicar la cadena delimitadora.

Son también importantes los campos –concurrency e –iterations, el primero indica el número de clientes simultáneos, y el segundo las veces que se ejecuta la prueba.

También es recomendable usar la aplicación jMeter para realizar nuestras pruebas.

Similar Posts

|

Google ofrece sus modificaciones a MySQL

ByLuis

google_mysql.pngGoogle, usuario de MySQL, ha publicado los cambios que ha realizado a la base de datos MySQL. Los cambios están destinados a mejorar la disponibilidad y la manejabilidad.
La disponibilidad se refiere a permitir que los servicios sigan ejecuntándose aunque el servidor donde está funcionando falle, cambiando a una máquina backup cuando falle.
Aunque los cambios no han sido aceptados en el proyecto MySQL, Google quiere que si se acepten, y hasta que esto ocurra, han publicado los cambios con la misma licencia de MySQL para que la gente pueda disfrutar de ellos.
Google offers its own changes to MySQL

|

Outer joins en SQL

ByLuis

Hace días que llevo pensando en explicar cómo hacer un outer join en SQL (MySQL y Oracle), y hoy he encontrado unos diagramas que explican los diferentes tipos de joins que podemos hacer (MySQL).

El outer join se usa cuando queremos que obtener todos los campos de una tabla que cumplen una condición y los campos de otra tabla que coinciden con los de la primera tabla.

outerjoin.png

Imaginamos que tenemos una tabla de palabras en inglés y una tabla de traduciones:

Tabla : palabras
id / palabra
1 / bird
2 / flower
3 / sky
4 / tree
Tabla: traducciones
id / traduccion / idioma
1 / pájaro     / es
3 / cielo      / es

Se trata de tener la tabla de palabras y sus correspondientes traducciones (en este caso solo lo he hecho para español, pero se podrían incluir registros para otros idiomas). Si nos fijamos veremos que un join normal no lo podremos hacer porque si igualamos el id de palabras y traducciones habrá registros que no se mostrarán en la tabla de palabras porque no existen sus correspondientes en la tabla de traducciones. Por ello deberemos ejecutar las siguientes instrucciones en MySQL y Oracle:

/* MySQL */
SELECT *
FROM palabras
LEFT OUTER JOIN traducciones
ON palabras.id = traducciones.id
/* Oracle */
SELECT palabra, traduccion
FROM palabras, traducciones
WHERE palabras.id = traducciones.id (+)

Echadle un vistazo al resto de representaciones gráficas de los joins porque son muy aclaratorias.

A Visual Explanation of SQL Joins

|

Búsquedas geolocalizadas en WordPress

ByLuis

Este es un ejemplo raro de uso de WordPress, pero el otro día una persona lo preguntaba en el foro de soporte de WordPress. El usuario tenía los posts geolocalizados (latitud/longitud), supongo con postmetas correspondientes.

Para poder realizar este tipo de búsquedas es necesario usar MySQL procedures, claro que no todos los hosting lo permiten:

CREATE PROCEDURE geodist (IN mylat decimal(18,12), IN mylon decimal(18,12), IN dist float)
BEGIN
declare lon1 float;
declare lon2 float;
declare lat1 float;
declare lat2 float;
set lon1 = mylon-dist/abs(cos(radians(mylat))*69);
set lon2 = mylon+dist/abs(cos(radians(mylat))*69);
set lat1 = mylat-(dist/69);
set lat2 = mylat+(dist/69);
SELECT p.*, 3956 * 2 * ASIN(SQRT( POWER(SIN((mylat -lat.meta_value) * pi()/180 / 2), 2) +COS(mylat * pi()/180) * COS(lat.meta_value * pi()/180) *POWER(SIN((mylon - lon.meta_value) * pi()/180 / 2), 2) )) as distance FROM wp_posts as p, wp_postmeta lat, wp_postmeta lon WHERE p.ID = lat.post_id and lat.meta_key = 'latitude' and p.ID = lon.post_id and lon.meta_key = 'longitude' and lon.meta_value between lon1 and lon2 and lat.meta_value between lat1 and lat2
and post_status = 'publish'
having distance < dist ORDER BY Distance;
END

Una vez creado el procedure deberemos llamarlo para obtener los resultados:

global $wpdb;
$posts = $wpdb->query($wpdb->prepare('call geodist(?, ?, ?)', $lat, $lon, $dis));

Acceder al código en Gist

Consejos para optimizar MySQL

ByLuis

Buenos consejos para optimizar MySQL, ordenados por distintas categorías.

Queries

  • Usa EXPLAIN para comprender la ejecución de la query
  • Usa Slow Query Log
  • No uses DISNTINCT cuando usas o puedes usar GROUP BY
  • Crea INSERT o REPLACE en procesos batch y usa LOAD DATA antes que INSERT
  • LIMIT m,n no es tan rápido como parece
  • No uses ORDER BY RAND() si tienes más de dos mil registros
  • Usa SQL_NO_CACHE cuando selecciones datos que se actualizan con frecuencia o son abundantes
  • Evita usar caracteres especiales en los LIKE
  • Evita usar subconsultas correlacionadas en el SELECT y el WHERE
  • No hagas comparaciones calculadas
  • ORDER BY y LIMIT funcionan mejor con
  • No pongas text/blobs en los resultados si no vas a necesitarlos
  • Subqueries en el FROM pueden ser útiles para recuperar BLOBs sin necesidad de ordenarlos. Si haces un join de una tabla consigo misma, puede ser bueno si en la primera obtienes el indice y en segunda obtienes el resto de los datos.
  • ALTER TABLE … ORDER BY puede tomar los datos ordenados cronologicamente y reordenarlos mediante un campo diferente, esto podría hacer que las consultas vayan más rapidas por ese campo, aunque quizás fuera necesario que estuviera en el índice.
  • Es bueno saber cuando dividir una query compleja en la unión de otras más pequeñas
  • Elimina pequeñas cuentas si puedes
  • Haz queries similares para que el cache las use
  • Usa estándares
  • No uses características deprecated
  • Cambia a OR en índices de múltiples campos para que la UNION mejore la velocidad (> mysql 5.0)
  • No uses COUNT (*) en tablas Innodb para cada búsqueda. Para ello deberías usar SQL_CALC_FOUND_ROWS y SELECT FOUND_ROWS()
  • Usa INSERT … ON DUPLICATE KEY update (INSERT IGNORE) para evitar el SELECT

Escalado

  • Usa benchmarking
  • Aisla las cargas de trabajo, no dejes que las tareas administrativas interfieran en el rendimiento (por ejemplo, backups)
  • Testea en vez de hacer debug
  • A la vez que los datos crecen los índices cambian. Haz tu esquema tan modular como tu código.

Rendimiento de red

  • Minimiza el tráfico recuperando solo lo que necesitas.
  • Accede directamente a la paginación con LIMIT no recuperando los anteriores.
  • No uses SELECT *
  • No uses muchas queries rápidas y una algo más compleja puede ser más eficiente.

Mejoras en el sistema operativo

  • Usa particiones en los datos
  • Manten el servidor tan limpio como sea posible
  • Crea un entorno de test

Servidor MySQL

  • innodb_flush_commit=0 puede ayidar en el retraso del slave
  • Optimiza los tipos de datos, se consistente. Usa PROCEDURE ANALYSE() para ayudarte a determinar el tamaño de los datos que necesitas.
  • Si puedes comprime text y blobs
  • Comprime datos estáticos
  • No hagas backup de datos estáticos con frecuencia
  • Activa e incrementa el cache de las queries y el budder si es apropiado
  • Comprueba SHOW STATUS y SHOW VARIABLES
  • Defragmenta tablas, reconstruye índices, haz mantenimiento
  • Aumenta myisam_sort_buffer_size para optimizar inserta largos

Estos son los que me han parecido más interesantes, el resto los podéis encontrar en:

Top 1000 SQL Performance Tips

Gracias David por el aviso

Consejos para mejorar la paginación

ByLuis

La paginación es algo de lo más costoso a la hora de realizar una búsqueda. Si nos ponemos en el caso de miles de registros con ordenación por cierto campo, la búsqueda puede ser una query muy costosa. Si además unimos el cálculo total de registros encontrados, la página puede tardar mucho en devolverse.

Debido a ello, para agilizar este tipo de páginas existen unos consejos a tener en cuenta:

  • Cachea todo lo que se pueda: el caché evita que se realicen consultas a la BD.
  • No muestres todos los resultados: Google no lo hace y nadie se queja por ello. Limita el total de registros a 100 o 500, nadie visita más de 10 páginas normalmente.
  • No muestres el total de páginas o páginas intermedias: ofrece tan solo en enlace a “página siguiente”, para lo cual puedes obtener 21 registros y mostrar solo 20, así si la consulta te devuelve menos de 21 es que no hay página siguiente.
  • Estima el número de resultados: esto también lo hace Google, en vez de calcular el número exacto, se trata de estimar el número aproximado de resultados. Usa EXPLAIN para obtener ese número, puedes usar el campo rows como aproximación.

Four ways to optimize paginated displays

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