Share
Embed
Transcript
34 slides
-
presentación • CTO en CHICISIMO • Fundador ONESTIC • NO
SOY NINGÚN GURÚ!!! 2 -
Lecturas recomendadas • High Performance MySQL, 3º edición • MySQL
5.0 Certification Study Guide. AMAZON • http://www.mysqlperformanceblog.com/ • http://www.xaprb.com/blog/ Baron Schwartz • http://miguelangelnieto.net/ • http://hackmysql.com/ 3 -
ABOUT CHICISIMO • Chicisimo ‐> 10M páginas vistas al mes
• Reads / Writes: 98% / 2% • Total 876.27M 661.8/s • QC Hits 557.59M 421.1/s 63.63% • DMS 235.06M 177.5/s 26.83% 4 -
1. Arquitectura mysql • 1º Nivel. Gestor de conexiones, autentificación
y seguridad • 2ª Nivel. Cache, parser de queries, optimizador de consultas, funciones (stored procedures, triggers, views,..) • 3º Nivel. Storage Engines, MyISAM, InnoDB, Achive, custom, … 5 -
storageengines-Myisam • Default storage engine, el más veterano • Lock
a nivel de tabla • Soporta índices FULL TEXT SEARCH • No índices entre tablas (JOINS) • No integridad referencial, no es transaccional • Rápido DUMPS & IMPORTS • Recomendado si tienes una tasa muy alta de lecturas & inserts • Se corrompe con más facilidad, recovery más lento • MyISAM Merge: MySQL 5.1 se puede particionar tablas grandes > rendimiento SELECT 6 -
storageengines-innodb • Storage engine transaccional & entidad referencial • Lock
a nivel de fila + MVCC (row snapshots) • Pueden producirse DEADLOCKS • COUNT(*) no es real debido a multiversiones • COMMIT es lento, OJO! • Muy lento DUMPS & IMPORTS • Más robusto que MyISAM & más rápido reparando • Recomendado si: • Necesitas transacciones • Alto nivel UPDATES & DELETES 7 -
storageengines-MEMORY • Todos los datos se guardan en memoria •
Muy rápida para hacer queries • Podemos usar para mapear datos • Reinicio… adiós a los datos pero se mantiene estructura • MySQL la usa internamente si una query necesita una tabla temporal. Importante 8 -
storageengines-ARCHIVE • Sólo soporta INDEX & SELECT • Cada fila
está comprimida, menos I/O que MyISAM • Perfecta para logging … • Muchos más tipos: Falcon, CSV, NDB Cluster & Maria (remplazo futuro MyISAM) 9 -
2. Configuración Mysql • /etc/my.cnf • Una mala configuración puede
tumbar el server • No hay que obsesionarse! • Percona al rescate! https://tools.percona.com/wizard • Ejemplos ‐> /usr/share/doc/mysql-server-5.X • Herramientas: • mysqlreport: Analiza y resume variables estado • mysqltuner: Asistente optimizar configuración • Google! 10 -
Variables rendimiento… • query_cache_size = 256M reserva la memoria •
Query Cache… Por defecto está desactivada • key_buffer = 256M no reserva la memoria • Key Buffer … tamaño asignado para índices MyISAM • max_connections = 500 ojo, memory leak! • Procesos de MySQL ‐> Clientes conectados • tmp_table_size = 128M • max_heap_table_size = 128M • Memoria o disco? Disco = DEAD • innodb_buffer_pool_size=2G • ¡IMPORTANTE! 50% de RAM mínimo recomendado! 11 -
Variables logging… • log_error = /log/mysql/mysql-error.log • log_queries_not_using_indexes = 1
• slow_query_log = 1 • slow_query_log_file = /log/mysql/mysql- slow.log • Hasta MySQL 5.1.21, queries lentas > 1sg • Parche PERCONA para bajar este tiempo 12 -
3. Mysql& memoryuse • Sumamos todos los buffers: • key_buffer_size,
innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size • Conexiones máximas * 256Kb * • read_buffer_size • sort_buffer_size • read_rnd_buffer_size • tmp_table_size (128M) • Si las consultas son complejas o poco óptimas podemos matar el server 13 -
4. Tipos de campos • Menos es más. TRIVIAL •
Evitar valores NULL: • Definir como NOT NULL ‐> default value • INTEGERS: • Espacio almacenamiento: 8,16,24,32 & 64 BITS • UNSIGNED • INT(11) = INT(1) 14 -
4.1 campos de texto • STRINGS: • varchar(100): hasta 100
bytes + 1/2 byte • Char(10): siempre 10 bytes • BINARY & VARBINARY • Char vs varchar?, cortas y fijas – char • TEXT /BLOB • MySQL los trata como objetos independientes • ORDER BY text = tabla temporal en disco = MUERTE • Parche: ORDER BY SUBSTING(field, length) • ENUM • Muestra STRING, almacena un INT • Mejor controlarlo desde nuestra aplicación 15 -
4.2 campos fecha • DATETIME: • Almacena segundos • Desde
año 1001 – 9999 • TIMESTAMP: • Almacena segundos • Desde 1970 – 2038 • DATETIME necesita el doble de espacio que TIMESTAMP 16 -
4.3 Identificadores • Entero: • UNSIGNED – AUTO_INCREMENT • JOINS
más rápidos • ENUM • solo para tablas estáticas • JOINS no tan rápidos • String • Necesita mucho espacio, inserts & selects lentos • Ojo con los randoms • UUID‐> usamos UHEX() /HEX() y guardamos BINARY(16) • JOINS más lentos 17 -
índices • Estructura ordenada de datos • Importante que los
índices puedan alojarse en memoria • Sirven agilizar búsquedas y ordenaciones • Índice funcionan de izquierda a derecha. • INDEX `user_data` (`apellido`,`nombre`,`fecha_nac`) • ¿Búsquedas de apellidos empiecen por B?: yes! • ¿Búsquedas de BOLUFER y nombre empiecen por D?: yes! • ¿Búsquedas de nombres empiecen por D?: NO! 18 -
índices: ¿tanto se nota? • Tabla comentarios de chicisimo: 1M
filas • CREATE TABLE comments{ `comment_ID` BIGINT(20) …, … `comment_type` VARCHAR(20) …, `comment_approved` VARCHAR(20) …, `comment_date` DATETIME …, `user_id` BIGINT(20) …, PRIMARY KEY (`comment_ID`) } 19 -
índices: ¿tanto se nota? • SELECT SQL_NO_CACHE * FROM `wp_1_comments`
WHERE user_id = 3150 • La consulta tardó sin índice 1,023 seg. No está mal … • La consulta tardó con índice 0,026 seg ¡4000%! 20 id type type keys key_len ref rows Extra 1 SIMPLE ALL NULL NULL NULL 1.012.563 Using where id type type keys key_len ref rows Extra 1 SIMPLE REF user_id 8 CONST 3004 -
índices: ¿tanto se nota? • SELECT SQL_NO_CACHE * FROM `wp_1_comments`
WHERE user_id = 3150 ORDER BY `comment_date` DESC • La consulta tardó sin índice 1,0424 seg • La consulta tardó con índice anterior 0,0417 seg • EXPLAIN ‐> USING WHERE, USING FILESORT • Añadimos índice en comment_date … 0,0403 seg • EXPLAIN ‐> USING WHERE, USING FILESORT • Añadimos índice user_id,comment_date … 0,0253 seg • EXPLAIN ‐> USING WHERE 21 -
optimización: mi servidor muere… 1. Monitorización 2. Analizar SLOW QUERIES
3. Buscar la(s) query(ies) en nuestro código 4. Analizar query usando EXPLAIN 5. Optimizar la query: índices, denormalización, campos calculados,… 6. Si: 1. Sigue fallando:‐> Volver al paso 1. 2. Llevo N iteraciones ‐> Cambia de SERVER/TECNOLOGÍA 3. Funciona OK ‐> Congrats! 23 -
Opt. 1. monitorización • Utilizamos: • Serverstats: Estado del servidor,
memoria, load, cpu, MySQL, … • Pingdom : Avisos ante caídas por aplicación iPhone/Android • mytop: top clone for MySQL 24 -
Opt. 1. monitorización • Estamos probando… • Icinga (fork de
NAGIOS): Monitorización + alertas • PINBA: Monitorización tiempos de ejecución sobre PHP • pt‐collector/pt‐stack: recolectar información en el momento preciso Percona Toolkit 25 -
Opt.2. Slowqueries • slow_query_log_file se guardan las queries lentas. •
Necesitamos procesar el log y agrupar queries. • mk‐query‐digest de Maatkit • mk-query-digest mysqld-slow.log > slow-mayo.log • Qué queries han consumido más tiempo, no las que más veces se han ejecutado • Analiza tiempo ejecución y tiempo de bloqueo • Maatkit ya no se mantiene, Percona Toolkit 26 -
Opt.3. cazando queries • Profiling: • Clase que permite logar
todas las queries • Buscar donde se define una query, WP es un laberinto • Calcula tiempos de ejecución (queries, cURL, PHP, …) • Uso de REDIS (hited, missed, seted, …) • A parte … usamos XHPROF para analizar la pila de llamadas, uso de tiempo y memoria 27 -
Opt.4. EXPLAIN … • Muestra información sobre como procesa MySQL
las queries • Type (de mejor a peor): • const: ID = 1, buscamos por PK o UNIQUE • eq_ref: ID = 1 AND DNI = ‘111X’, buscamos por PK o UNIQUE • ref: user_id=3150, devuelve todas las filas de un índice • range: user_id > 10, devuelve un rango de filas • index: Escaneo completo del índice • all: Escaneo completo de la tabla • Rows: Filas máximas a leer. 28 -
Opt. 5. queries-denormalizar • Joins entre tablas con order +
where = tabla temporal • SELECT SQL_NO_CACHE * FROM wp_1_comments INNER JOIN wp_1_posts ON wp_1_posts.ID = wp_1_comments.comment_post_ID WHERE wp_1_posts.post_author = 3150 AND wp_1_comments.user_id != 3150 ORDER BY wp_1_comments.comment_date DESC • Tiempo ejecución 1,815 seg • Rows: 68.801 • wp_1_posts: using temporary, using filesort • wp_1_comments: using where 29 -
Opt. 5. queries-denormalizar • Denormalizamos añadimos los campos a una
de las tablas • Mediante un TRIGGER mantenemos actualizados los campos que hemos denormalizado • Tiempo ejecución 0,008 seg • wp_1_posts: using where • ¿Este cambio en producción se nota? 30 -
Opt. 5. queries-calculados • Votos: 4,3 Millones • Votos de
usuarios registrados: • Rankings mejores looks del día, mes y año. • COUNT() con un RANGE = MUERTE • ¿Cuantos votos recibe un usuario? COUNT() • ¿Cuantos votos tiene un look? COUNT() ¿Solución? • Proceso cada N que calcula los rankings y actualiza los valores DATO • Si usamos InnoDB, esto es casi una necesidad 32 -
Opt. 5. queries–bricoConsejos • Usar INSERT DELAYED siempre que sea
posible: ejemplo logs/activity • Necesitamos buscar en un campo VARCHAR, ej: URL: • Crear un campo con un hash CRC32(‘http://chicisimo.com’) • Añadimos índice al campo y un TRIGGER para mantener el valor • WHERE url_crc=‘4240669402’ AND url = ‘http://chicisimo.com’ • No utilizar CURRENT_DATE o similares en WHERE, MySQL no las cachea 33