mysqlcheck - optimizar la base de datos

Mejores prácticas de optimización de bases de datos MySQL

Question

¿Cuáles son las mejores prácticas para optimizar una instalación MySQL para obtener el mejor rendimiento cuando se manejan tablas algo más grandes (> 50k registros con un total de alrededor de 100MB por tabla)? Actualmente estamos buscando reescribir DelphiFeeds.com (un sitio de noticias para la comunidad de programación Delphi) y notamos que las declaraciones simples de actualización pueden tomar hasta 50 ms. Esto parece mucho. ¿Hay alguna configuración de configuración recomendada que debemos habilitar / configurar que normalmente están deshabilitadas en una instalación estándar de MySQL (por ejemplo, para aprovechar más RAM para almacenar en caché las consultas y los datos, etc.)?

Además, ¿qué implicaciones de rendimiento tiene la elección de los motores de almacenamiento? Estamos planeando ir con InnoDB, pero si se recomienda MyISAM por razones de rendimiento, podríamos usar MyISAM.

Answer 1

Ve a comprar "High Performance MySQL" de O''Reilly. Son casi 700 páginas sobre el tema, así que dudo que encuentres una respuesta concisa en SO.

Answer 2

La "mejor práctica" es:

1. Mida el rendimiento, aislando el subsistema relevante lo mejor que pueda.
2. Identifique la causa raíz del cuello de botella. ¿Está usted vinculado a E / S? CPU atada? Memoria atada Esperando en bloqueos?
3. Haga cambios para aliviar la causa raíz que descubrió.
4. Mida nuevamente, para demostrar que ha solucionado el cuello de botella y por cuánto .
5. Vaya al paso 2 y repita según sea necesario hasta que el sistema funcione lo suficientemente rápido.

Suscríbase a la fuente RSS en http://www.mysqlperformanceblog.com y lea sus artículos históricos también. Es un recurso enormemente útil para la sabiduría relacionada con el desempeño. Por ejemplo, preguntaste sobre InnoDB vs. MyISAM. Su conclusión: InnoDB tiene ~ 30% más de rendimiento que MyISAM en promedio. Aunque también hay algunos escenarios de uso donde MyISAM supera a InnoDB.

• Puntos de referencia InnoDB vs. MyISAM vs. Falcon - parte 1

Los autores de ese blog también son coautores de "High Performance MySQL", el libro mencionado por @Andrew Barnett.

Comentario de @ ʞɔıu: Cómo saber si estás vinculado a E / S frente a límite de CPU frente a límite de memoria depende de la plataforma. El sistema operativo puede ofrecer herramientas como ps, iostat, vmstat o top. O puede que tenga que obtener una herramienta de terceros si su sistema operativo no proporciona uno.

Básicamente, cualquiera que sea el recurso vinculado al 100% de utilización / saturación es probable que sea su cuello de botella. Si la carga de su CPU es baja pero su carga de E / S está al máximo para su hardware, entonces está obligado a E / S.

Sin embargo, ese es solo un punto de datos. El remedio también puede depender de otros factores. Por ejemplo, una consulta SQL compleja puede estar haciendo una clasificación de archivo, y esto mantiene la E / S ocupada. ¿Debería tirar hardware más / más rápido en él, o debería rediseñar la consulta para evitar el fileser?

Hay demasiados factores para resumir en una publicación de , y el hecho de que existan muchos libros sobre el tema lo respalda. Mantener las bases de datos funcionando de manera eficiente y hacer el mejor uso de los recursos es un trabajo de tiempo completo que requiere habilidades especializadas y estudio constante.

Jeff Atwood acaba de escribir un buen artículo de blog sobre cómo encontrar cuellos de botella en un sistema:

• El juego Shell de rendimiento informático

Answer 3

Es difícil hacer una limpieza general, pero es posible una vista de nivel moderadamente alto.

• Necesita evaluar las relaciones de lectura: escritura. Para tablas con proporciones inferiores a aproximadamente 5: 1, probablemente se beneficie de InnoDB porque las inserciones no bloquearán las selecciones. Pero si no está utilizando transacciones, debe cambiar innodb_flush_log_at_trx_commit a 1 para recuperar el rendimiento de MyISAM.
• Mira los parámetros de memoria. Los valores predeterminados de MySQL son muy conservadores y algunos de los límites de memoria se pueden aumentar en un factor de 10 o más incluso en hardware común. Esto beneficiará a sus SELECT en lugar de INSERT.
• MySQL puede registrar cosas como consultas que no usan índices, así como consultas que tardan demasiado tiempo (definibles por el usuario).
• El caché de consultas puede ser útil, pero debe instrumentarlo (es decir, ver cuánto se usa). Cacti puede hacer eso; como puede Munin.
• El diseño de la aplicación también es importante:
  • Los conjuntos de datos ligeramente cacheados que se obtienen con frecuencia pero de poca contextura tendrán una gran diferencia (es decir, la vida útil de la memoria caché de unos pocos segundos).
  • No recuperes datos que ya tienes a mano.
  • El almacenamiento en varios pasos puede ayudar con un gran volumen de inserciones en tablas que también están ocupadas. La idea básica es que puede tener una tabla para inserciones ad-hoc ( INSERT DELAYED también puede ser útil), pero un proceso por lotes para mover las actualizaciones dentro de MySQL desde allí hasta donde están ocurriendo todas las lecturas. Hay variaciones de esto.
• No olvides que la perspectiva y el contexto también son importantes: lo que podrías pensar que es mucho tiempo para que ocurra una UPDATE podría ser realmente trivial si esa actualización "larga" solo ocurre una vez al día.

Answer 4

Hay toneladas de mejores prácticas que se han discutido anteriormente, por lo que no hay motivos para repetirlas. Para consejos concretos sobre qué hacer, intentaría ejecutar MySQL Tuner . Es un script de Perl que puede descargar y ejecutar en su servidor de base de datos, le dará un montón de estadísticas sobre cómo está funcionando su base de datos (por ejemplo, visitas de caché) junto con algunas recomendaciones concretas para qué problemas o parámetros de configuración deben ajustarse para mejorar el rendimiento

Si bien estas estadísticas están todas disponibles en MySQL, creo que esta herramienta las proporciona de una manera mucho más fácil de entender. Si bien es importante tener en cuenta que YMMV con respecto a las recomendaciones, he encontrado que generalmente son bastante precisas. Solo asegúrate de haber hecho un buen trabajo ejercitando la base de datos de antemano con tráfico realista.