Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Mimando a MySQL

Mimando a MySQL

MySQL es un servidor de base de datos fácil de usar y con una gran comunidad. Es habitual comenzar a desarrollar un proyecto sin prestar demasiada atención a lo que está ocurriendo detrás de esa gran cantidad de consultas que lanzamos contra la base de datos.

alvaromg

June 07, 2016
Tweet

More Decks by alvaromg

Other Decks in Technology

Transcript

  1. Álvaro de la Mata González Software Developer & Sysadmin Intexdev

    SL Grupo Intexmedia @_alvaromg Mimando a MySQL
  2. 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, ...
  3. 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
  4. 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
  5. 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
  6. 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. • ...
  7. 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
  8. 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;
  9. • 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
  10. • 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
  11. 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.
  12. 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.
  13. 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
  14. 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)
  15. 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)
  16. 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 | +----+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
  17. 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 | +----+-------------+-----------+-------+---------------+----------+---------+------+------+-------------+
  18. 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 | +----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+
  19. 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 | | +----+-------------+-----------+-------+---------------+------------------+---------+------+------+-------+
  20. 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)
  21. 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)
  22. 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). ◦ ...
  23. 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.
  24. 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.
  25. 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
  26. 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
  27. 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
  28. 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 <[email protected]> >> 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
  29. Resto del esquema de la BDD Mimando a MySQL @_alvaromg

    Tablas con elevado número de escrituras Stats
  30. Mimando a MySQL @_alvaromg Tablas con elevado número de escrituras

    Stats Bloqueos (registros, TABLAS) Actualizaciones del índice. Invalidaciones de caché
  31. Resto del esquema de la BDD Mimando a MySQL @_alvaromg

    Tablas con elevado número de escrituras Stats_Buffer Stats Back Process MySQL Redis ....
  32. 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.