Álvaro de la Mata González Software Developer & Sysadmin Intexdev SL Grupo Intexmedia @_alvaromg Mimando a MySQL 

Mimando a MySQL @_alvaromg Veamos una posible situación 

Mimando a MySQL @_alvaromg Comenzamos a desarrollar un nuevo proyecto... 

Mimando a MySQL @_alvaromg Terminamos y todo funciona, lanzamos a producción... 

Mimando a MySQL @_alvaromg El proyecto muere por éxito, no aguanta 

Mimando a MySQL @_alvaromg Responsabilidades del desarrollador ● Revisar y mejorar las queries de la aplicación a la base de datos. ● Poner el proyecto bajo estrés para conocer los puntos de mejora. ● Valorar posibles alternativas a una base de datos relacional en cada caso. ● Colaborar con el administrador (si lo hay) para pulir las configuraciones de producción. ● Seguimiento de la evolución del proyecto en producción. ● Conocer qué está pasando bajo las abstracciones: ORM, Active Records, Hype Magic, ... 

Mimando a MySQL @_alvaromg La consulta más barata es la que no se hace PHP Browser MySQL 

Mimando a MySQL @_alvaromg La consulta más barata es la que no se hace Caché respuestas HTTP completas Almacenamiento en memoria y disco Bloques ESI Control con VCL Caché de objetos Almacenamiento en memoria Mayor control 

Mimando a MySQL @_alvaromg Características MySQL Versión actual: 5.7.x Motores de almacenamiento: InnoDB, MyISAM, NDB, Memory, … MySQL replication no beta en >= 5.5 Se puede extender con plugins Es fácil de usar Tiene una gran comunidad 

Mimando a MySQL @_alvaromg Flujo ejecución consultas en MySQL 

Mimando a MySQL @_alvaromg Motores almacenamiento MySQL InnoDB MyIsam Memory Permite transacciones Integridad referencial Bloque a nivel de registros Garantiza integridad de datos Búsquedas Fulltext en >= 5.6 Por defecto en >= 5.5 Rápido con muchas lecturas Bloqueo a nivel de tabla Por defecto en <= 5.4 Almacenadas en memoria Datos volátiles. Bloqueo a nivel de tabla Accesos muy rápido 

Mimando a MySQL @_alvaromg Caché de consultas Almacena resultados en memoria de consultas exactamente iguales, byte a byte. # Consultas consideradas distintas SELECT a, b FROM table; SELECT b, a FROM table; Select a, b FROM table; En los siguientes casos NO se usa la caché: ● En subconsultas de una consulta padre. ● Consultas ejecutadas en un procedimiento almacenado. ● Consultas que usan funciones definidas por el usuario. ● Consultas que devuelven algún campo TEXT o BLOB. ● ... 

Mimando a MySQL @_alvaromg Caché de consultas Activación en my.cnf: # desactivada query_cache_type = 0 # activada (default) query_cache_type = 1 # bajo demanda (SELECT SQL_CACHE a, b FROM …) query_cache_type = 2 

Mimando a MySQL @_alvaromg Caché de consultas Monitorizar y hacer un seguimiento de las estadísticas de caché mysql> show status like 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 2319 | | Qcache_free_memory | 7119040 | | Qcache_hits | 143073915 | | Qcache_inserts | 67474731 | | Qcache_lowmem_prunes | 9384509 | | Qcache_not_cached | 71545921 | | Qcache_queries_in_cache | 7528 | | Qcache_total_blocks | 17974 | +-------------------------+-----------+ 8 rows in set (0.00 sec) mysql> RESET QUERY CACHE; 

● query_cache_limit (1M) Tamaño máximo del resultado de una query para que se pueda cachear. ● query_cache_size (1M) Espacio máximo que se puede utilizar para resultados de caché. ● max_heap_table_size (16M) Tamaño máximo hasta el que puede crecer una tabla en el motor MEMORY. ● tmp_table_size (16M) Tamaño máximo que puede ocupar una tabla temporal. Si es mayor, se guardará en disco. Está ligada a max_head_table_size Mimando a MySQL @_alvaromg Configuraciones mínimas https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html 

● sort_buffer_size Buffer para ordenación de resultados. Subir a más de 2MB puede ser peligroso. ● tmpdir Directorio donde se crearán las tablas temporales. Puede ser útil usar una partición en memoria (tmpfs, por ejemplo). Mimando a MySQL @_alvaromg Configuraciones mínimas https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html 

Mimando a MySQL @_alvaromg Índices en MySQL ● Se utilizan para encontrar información rápidamente. ● Afectan a las partes WHERE, ORDER y JOIN de una query. ● Utilizan estructura B-TREE en InnoDB y MyISAM. Estructura HASH en Memory. ● Índices multicolumna: (a, b, c) se puede usar para (a), (a + b) y (a + b + c) ● Bien usados mejoran mucho el rendimiento y reducen el consumo de recursos. ● Los índices numéricos son muy rápidos. 

Mimando a MySQL @_alvaromg Explain mysql> EXPLAIN SELECT p.id, p.name, c.name FROM Programs p INNER JOIN Categories c ON p.category_id = c.id WHERE c.id = 401 AND p.banned = 0; ● Detalla como MySQL conecta las partes de una query para producir el resultado. ● Permite analizar cómo MySQL aprovecha (o no) los índices. 

Mimando a MySQL @_alvaromg Explain EXPLAIN SELECT p.id, p.name, c.name FROM Programs p INNER JOIN Categories c ON p.category_id = c.id WHERE c.id = 401 AND p.banned = 0\G ****** 1. row ************* id: 1 select_type: SIMPLE table: c type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: ****** 2. row ************* id: 1 select_type: SIMPLE table: p type: index_merge possible_keys: IDX_83F,idx_banned key: IDX_83F,idx_banned key_len: 5,1 ref: NULL rows: 46 Extra: Using intersect(IDX_83F, idx_banned); Using where 2 rows in set (0.00 sec) id: Identificador secuencial, para cuando hay varias partes select_type: El tipo de SELECT. Posibles valores: ● SIMPLE: Un simple SELECT sin subconsultas ni UNION ● PRIMARY: Es el SELECT principal en una consulta con JOIN ● DERIVED: Parte de una subconsulta en el FROM ● SUBQUERY: El primer SELECT en una subconsulta ● DEPENDENT SUBQUERY: Subconsulta que depende de una consulta exterior ● UNCACHEABLE SUBQUERY: una subconsulta que no es cacheable ● UNION: Segundo SELECT o posterior en un UNION ● DEPENDENT UNION: Segundo SELECT o posterior en un UNION dependendiente de una subconsulta ● UNION RESULT: El resultado de un UNION table: La tabla referenciada por esa parte 

Mimando a MySQL @_alvaromg Explain type: Cómo MySQL hace join con esta tabla. Posibles valores: ● system: La tabla tiene 0 o 1 registros. ● const: La tabla solo tiene 1 registro y cubierto por algún índice. ● eq_ref: Se usa completamente algún índice y éste es PRIMARY o UNIQUE NOT NULL. ● ref: Se leen todos los registros de un índice. ● fulltext: se usa algún índice FULTEXT. ● ref_or_null: Igual que "ref", pero contiene valores NULL. ● index_merge: Se usan varios índices. unique_subquery: Subconsulta IN con 1 solo resultado. ● index_subquery: similar a unique_subquery pero más de 1 resultado. ● range: Usa un rango del índice. Normalmente al usar BETWEEN, IN, >, >=, etc ● index: Se recorre el índice completo. ● all: Se recorre toda la tabla. Es el caso más lento y hay que evitarlo. EXPLAIN SELECT p.id, p.name, c.name FROM Programs p INNER JOIN Categories c ON p.category_id = c.id WHERE c.id = 401 AND p.banned = 0\G ****** 1. row ************* id: 1 select_type: SIMPLE table: c type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: ****** 2. row ************* id: 1 select_type: SIMPLE table: p type: index_merge possible_keys: IDX_83F,idx_banned key: IDX_83F,idx_banned key_len: 5,1 ref: NULL rows: 46 Extra: Using intersect(IDX_83F, idx_banned); Using where 2 rows in set (0.00 sec) 

Mimando a MySQL @_alvaromg Explain possible_keys: Muestra los índices que MySQL puede utilizar para encontrar registros en la tabla, pero no tiene por qué utilizarlos. Si contiene NULL, huele a optimización. key: Indica la clave o claves utilizadas por MySQL. Es posible que MySQL utilice alguna clave que no esté en "possible_keys". Cuando se utilizan muchas tablas, puede ser que encuentre un índice más óptimo que los posibles. key_length: Lo que ocupa el índice en X caracteres. ref: Muestra la columna o constante que se compara con el índice en cuestión. rows: Número de registros leídos para obtener el resultado. Valor muy importante a valorar cuando se están optimizando queries. Extra: Contiene información adicional sobre la ejecución. Valores como "Using temporary” o “Using filesort” indican que hay algún problema con la query. EXPLAIN SELECT p.id, p.name, c.name FROM Programs p INNER JOIN Categories c ON p.category_id = c.id WHERE c.id = 401 AND p.banned = 0\G ****** 1. row ************* id: 1 select_type: SIMPLE table: c type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: ****** 2. row ************* id: 1 select_type: SIMPLE table: p type: index_merge possible_keys: IDX_83F,idx_banned key: IDX_83F,idx_banned key_len: 5,1 ref: NULL rows: 46 Extra: Using intersect(IDX_83F, idx_banned); Using where 2 rows in set (0.00 sec) 

Mimando a MySQL @_alvaromg Uso de índices (WHERE) mysql> SELECT id, name FROM Programs WHERE slug LIKE 'ares'; 1 row in set (0.0140 sec) +----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | Programs | ALL | NULL | NULL | NULL | NULL | 39849 | Using where | +----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+ 

Mimando a MySQL @_alvaromg mysql> CREATE INDEX idx_slug ON Programs (slug); mysql> SELECT id, name FROM Programs WHERE slug LIKE 'ares'; 1 row in set (0.0003 sec) Uso de índices (WHERE) +----+-------------+-----------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | Programs | range | idx_slug | idx_slug | 212 | NULL | 1 | Using where | +----+-------------+-----------+-------+---------------+----------+---------+------+------+-------------+ 

Mimando a MySQL @_alvaromg Uso de índices (ORDER BY) mysql> SELECT id, name FROM Programs ORDER BY creationDate DESC LIMIT 0, 10; 1 row in set (0.0208 sec) +----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+ | 1 | SIMPLE | Programs | ALL | NULL | NULL | NULL | NULL | 40425 | Using filesort | +----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+ 

Mimando a MySQL @_alvaromg Uso de índices (ORDER BY) mysql> CREATE INDEX idx_creationDate ON Programs (creationDate); mysql> SELECT id, name FROM Programs ORDER BY creationDate DESC LIMIT 0, 10; 1 row in set (0.0004 sec) +----+-------------+-----------+-------+---------------+------------------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+------------------+---------+------+------+-------+ | 1 | SIMPLE | Programs | index | NULL | idx_creationDate | 8 | NULL | 10 | | +----+-------------+-----------+-------+---------------+------------------+---------+------+------+-------+ 

Mimando a MySQL @_alvaromg Uso de índices (ORDER BY) mysql> EXPLAIN SELECT SQL_NO_CACHE id, name FROM Programs WHERE category_id =400 ORDER BY creationDate DESC *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Programs type: ref possible_keys: IDX_83F071312469DE2,category_id key: IDX_83F071312469DE2 key_len: 5 ref: const rows: 35 Extra: Using where; Using filesort 1 row in set (0.00 sec) 

Mimando a MySQL @_alvaromg Uso de índices (ORDER BY) mysql> CREATE INDEX category_id_n_date ON Programs (category_id, creationDate); mysql> EXPLAIN SELECT SQL_NO_CACHE id, name FROM Programs WHERE category_id =400 ORDER BY creationDate DESC *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Programs type: ref possible_keys: IDX_83F071312469DE2,category_id,category_id_n_date key: category_id_n_date key_len: 5 ref: const rows: 35 Extra: Using where 1 row in set (0.00 sec) 

Mimando a MySQL @_alvaromg "Using temporary" ● No siempre se pueden evitar. ● El problema es cuando no caben en memoria (tmp_table_size) y se generan en disco. Y esto MySQL no lo indica. ● Según la documentacion, se puden usar tablas temporales bajo estas condiciones: ○ Evaluación de sentencias UNION, con algunas excepciones. ○ Evaluación de algunas vistas, como las que usan el algoritmoTEMPTABLE, UNION o aggregation. ○ Evaluación de tablas derivadas (subsonsultas en la claúsula FROM). ○ Evaluación de sentencias que contienen un ORDER BY y un GROUP BY distintos o para los que ORDER BY o GROUP BY contienen columnas de tablas que no sean las de la primera en la cola del JOIN. ○ Evaluación de DISTINCT combinado con ORDER BY. ○ Sentencias con UPDATE de varias tablas. ○ Evaluación de expressiones GROUP_CONCAT() o COUNT(DISTINT). ○ ... 

Mimando a MySQL @_alvaromg "Using filesort" ● Nombre poco adecuado. ● Provocado cuando no se puede ordenar por un índice. ● Si el espacio para ordenar es mayor que sort_buffer_size, entonces se harán ordenaciones parciales y se unirán al final ● Puede corregirse con índices de 1 o varias columnas. 

Mimando a MySQL @_alvaromg Slow Queries Registro con queries que superan un umbral de tiempo o no utilizan índices. log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 0.2 log-queries-not-using-indexes ● Puede generar mucha información en producción, dependiendo del tráfico. ● En desarrollo / testing: activar, estresar X minutos y desactivar. ● En producción: activar, esperar X minutos y desactivar. ● Genera información en bruto que hay que procesar. 

Mimando a MySQL @_alvaromg Slow Queries bajo estrés ● Con alta concurrencia aparecen nuevos problemas que antes ni imaginábamos. ● Hay que analizar el comportamiento de la aplicación bajo estrés $ apt-get install siege $ siege -c 100 -t 60S -i -f urls.txt ** Siege 2.60 ** Preparing 100 concurrent users for battle. The server is now under siege...done Transactions: 339 hits Availability: 93.39 % Elapsed time: 67.47 secs Data transferred: 4273708 bytes Response time: 8.25 secs Transaction rate: 5.02 trans/sec Throughput: 63342.34 bytes/sec Concurrency: 41.47 Successful transactions: 337 Failed transactions: 26 Longest transaction: 17.77 secs Shortest transaction: 0.37 secs 

Mimando a MySQL @_alvaromg pt-query-digest Paquete de Percona Toolkit para "masticar" la información de slow query $ apt-get install percona-toolkit $ pt-query-digest mysql-slow.log > digest-slow.log # Overall: 5.20k total, 104 unique, 11.70 QPS, 0.15x concurrency _________ # Time range: 2016-02-25 14:58:06 to 15:05:30 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 67s 175us 825ms 13ms 59ms 58ms 690us # Lock time 849ms 69us 3ms 163us 247us 86us 144us # Rows sent 241.92k 0 22.32k 47.69 24.84 810.53 3.89 # Rows examine 12.35M 1 103.11k 2.44k 14.47k 9.50k 329.68 # Rows affected 6 0 1 0.00 0 0.03 0 # Bytes sent 43.63M 52 4.47M 8.60k 964.41 159.20k 271.23 # Query size 890.98k 46 1.80k 175.62 400.73 113.84 124.25 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============= ===== ====== ===== =============== # 1 0x966D089BD0518BE0 13.0509 19.3% 60 0.2175 0.09 SELECT buscador # 2 0x09370C6290E4364E 8.3179 12.3% 21 0.3961 0.08 SELECT programas programas_descargas_cache # 3 0x347D193E55A11E98 7.2263 10.7% 174 0.0415 0.12 SELECT buscador # 4 0x42B4EC768E5CA585 5.8534 8.7% 11 0.5321 0.11 SELECT programas programas_descargas_cache # 5 0xC3B406D1CF181D1B 2.8834 4.3% 9 0.3204 0.08 SELECT programas programas_descargas_cache 

Mimando a MySQL @_alvaromg pt-query-digest # Query 1: 0.14 QPS, 0.03x concurrency, ID 0x966D089BD0518BE0 at byte 2315980 # This item is included in the report because it matches --limit. # Scores: V/M = 0.09 # Time range: 2016-02-25 14:58:06 to 15:05:30 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 60 # Exec time 19 13s 536us 369ms 218ms 356ms 143ms 279ms # Lock time 1 10ms 133us 501us 172us 214us 64us 159us # Rows sent 0 308 0 20 5.13 19.46 8.32 0 # Rows examine 21 2.68M 20 74.13k 45.69k 72.41k 30.25k 59.57k # Rows affecte 0 0 0 0 0 0 0 0 # Bytes sent 0 17.41k 179 940 297.10 621.67 185.28 183.58 # Query size 1 9.02k 139 224 153.90 166.51 13.39 151.03 # String: # Databases softgame_s... (26/43%), pgport_pro... (15/25%)... 4 more # Hosts 172.17.0.3 # Last errno 0 # Users userdb1 # Query_time distribution # 1us # 10us # 100us ########## # 1ms # 10ms ############################# # 100ms ################################################################ # 1s # 10s+ select busqueda, contador from buscador where vacia=0 and (busqueda like '%dfx%') and (busqueda not like 'dfx') order by contador desc limit 0,20\G 

Mimando a MySQL @_alvaromg Mysqltunner Script en Perl que revisa la configuración y estado de MySQL para sugerir cambios que mejoren el rendimiento y la estabilidad. $ perl mysqltuner.pl >> MySQLTuner 1.6.12 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Logged in using credentials from debian maintenance account. [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.47-0ubuntu0.14.04.1 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in InnoDB tables: 336M (Tables: 68) [--] Data in MyISAM tables: 661M (Tables: 236) [OK] Total fragmented tables: 0 -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [--] There are 605 basic passwords in the list. -------- CVE Security Recommendations -------------------------------------------------------------- [!!] CVE-2016-0546 : Candidate [!!] CVE-2016-2047 : Candidate 

Resto del esquema de la BDD Mimando a MySQL @_alvaromg Tablas con elevado número de escrituras Stats 

Mimando a MySQL @_alvaromg Tablas con elevado número de escrituras Stats Bloqueos (registros, TABLAS) Actualizaciones del índice. Invalidaciones de caché 

Resto del esquema de la BDD Mimando a MySQL @_alvaromg Tablas con elevado número de escrituras Stats_Buffer Stats Back Process MySQL Redis .... 

Resumen consejos ● El "tiempo real" es caro. Cachear información con sentido común. ● El diseño del esquema de la BDD puede evitar problemas y mejorar el rendimiento. ● Revisar slow queries durante el desarrollo del proyecto. ● En las consultas SELECT obtener los campos estrictamente necesarios para cada caso. ● No dejar ninguna query sin índices. Exprimir EXPLAIN al máximo. ● Cuidado con los índices en tablas con elevado número de escrituras. Extraer del esquema. ● Mantener el equilibrio entre el caos y la sobreoptimización. ● Revisar qué está pasando debajo de las abstracciones (ORM). ● Hacer seguimiento del proyecto en producción con tráfico real. 

Gracias ;) Mimando a MySQL @_alvaromg