¿Qué opina Daniel sobre el diseño de soluciones de optimización de SQL?
Antes de optimizar MySQL, debe comprender el proceso de consulta de MySQL. Gran parte del trabajo de optimización de consultas consiste en seguir algunos principios para que el optimizador de MySQL pueda ejecutarse de la manera esperada y razonable.
1. La filosofía de la optimización
Nota: La optimización es riesgosa, así que tenga cuidado al involucrarse
1.
La optimización no siempre se lleva a cabo en un entorno simple, sino que también puede ser un sistema complejo que se ha puesto en producción; los métodos de optimización tienen inherentemente grandes riesgos, pero no es posible darse cuenta ni preverlos. Cualquier tecnología puede resolver un problema, pero existe el riesgo de que se produzca un problema de optimización. Los problemas causados por la solución del problema deben controlarse dentro de un rango aceptable para que sea fructífero; de lo contrario, ocurrirá algo peor; ¡La situación es un fracaso! 2. ¿Necesidades de optimización?
La estabilidad y la sostenibilidad del negocio suelen ser más importantes que la optimización del rendimiento; inevitablemente implica cambios, y los cambios son riesgosos; la optimización mejora el rendimiento, mientras que el mantenimiento y el deterioro son eventos con la misma probabilidad, recuerde optimizar; un trabajo colaborativo y conjunto de todos los departamentos. ¡Ningún departamento por sí solo puede optimizar la base de datos!
¡Así que el trabajo de optimización está impulsado por las necesidades del negocio! 3. ¿Quién participa en la optimización?
Al realizar la optimización de la base de datos, debe ser realizada por administradores de bases de datos, representantes del departamento comercial, arquitectos de aplicaciones, diseñadores de aplicaciones, desarrolladores de aplicaciones, administradores de sistemas y hardware, administradores de almacenamiento, etc., todo el personal relevante está involucrado. .
2. Ideas de optimización
1. Hay dos aspectos principales en la optimización de bases de datos: seguridad y rendimiento.
Seguridad->Sostenibilidad de los datos; Rendimiento->Acceso a los datos de alto rendimiento. 2. ¿Cuál es el alcance de la optimización? Aspectos de almacenamiento, host y sistema operativo:
Estabilidad de la arquitectura del host; planificación y configuración de E/S; intercambio de parámetros del kernel del sistema operativo y problemas de red.
Aspectos de la aplicación:
Estabilidad de la aplicación; rendimiento de la declaración SQL; acceso en serie a los recursos; gestión de sesiones de rendimiento deficiente; si esta aplicación es adecuada para MySQL.
Optimización de la base de datos:
Memoria; estructura de la base de datos (física y lógica); Nota: Ya sea un sistema de diseño, un problema de posicionamiento u optimización, se puede ejecutar en este orden. 3. ¿Optimizar las dimensiones?
Existen cuatro dimensiones para la optimización de bases de datos:
Hardware, configuración del sistema, estructura de tablas de bases de datos, SQL e índices.
Selección de optimización:
Coste de optimización: Hardware>Configuración del sistema>Estructura de tablas de base de datos>SQL e índice. Efecto de optimización: hardware 3. ¿Cuáles son las herramientas de optimización? 1. ¿Nivel de base de datos? Herramientas comunes para comprobar problemas: 1) MySQL 2) msyqladmin: cliente MySQL, que puede realizar operaciones de gestión 3) mysqlshow: potente comando de visualización del shell 4) show [SESSION GLOBAL] variables: ver información de parámetros de la base de datos 5) MOSTRAR ESTADO [SESIÓN | GLOBAL]: Ver la información de estado de la base de datos 6) esquema_información: Método para obtener metadatos 7) MOSTRAR ESTADO DE INNODB DEL MOTOR: Todo el estado del motor Innodb 8) MOSTRAR LISTA DE PROCESOS: Ver el estado de sesión actual de todos conexiones 9) explicar: obtener el plan de ejecución de la declaración de consulta 10) mostrar índice: ver la información del índice de la tabla 11) registro lento: registrar la declaración de consulta lenta 12) mysqldumpslow: analizar el archivo de registro lento Herramientas poco utilizadas pero útiles: 1) Zabbix: monitorea hosts, sistemas y bases de datos (implementa la plataforma de monitoreo zabbix) 2) pt-query-digest: analiza registros lentos 3) MySQL slap: analiza registros lentos 4) sysbench: herramienta de prueba de estrés 5) Creación de perfiles MySQL: una herramienta estadística para el estado general de la base de datos 6) Esquema de rendimiento: datos para estadísticas del estado de rendimiento de MySQL 7) banco de trabajo: herramientas de administración, respaldo, monitoreo, análisis y optimización (relativamente recursos costosos) Acerca de la referencia de Zabbix: blogs.com/clsn/p/7885990.html 2. ¿Ideas para resolver problemas a nivel de base de datos? Ideas generales de ajuste de emergencia: para escenarios en los que el procesamiento comercial repentino se retrasa, el procesamiento comercial normal no se puede realizar y debe resolverse de inmediato. 1) mostrar lista de procesos; 2) explicar seleccionar id, nombre de stu donde nombre = 'clsn'; # TODOS id nombre edad sexo seleccionar id, nombre de stu donde id = 2-1 conjunto de resultados de función; >30; mostrar índice de la tabla; 3) A juzgar por el plan de ejecución, hay problemas de índice (si los hay, si son razonables) o la declaración en sí 4) mostrar estado como '%lock%'; estado de bloqueo kill SESSION_ID # Kill Elimina la sesión en cuestión. Ideas generales de ajuste: para retrasos comerciales periódicos, por ejemplo, el negocio es extremadamente lento entre las 10 y las 11 en punto todos los días, pero aún se puede usar y estará bien después de este período. 1) Ver el registro lento, analizar el registro lento y analizar las declaraciones de consulta lentas 2) Verificar todas las declaraciones lentas una por una de acuerdo con una determinada prioridad 3) Analizar el SQL superior, realizar una depuración explicativa; y verifique el tiempo de ejecución de la declaración; 4) Ajuste el índice o la declaración misma. 3 ¿Nivel del sistema? Cpu: vmstat, sar top, htop, nmon, mpstat; Memoria: gratuita, ps-aux <; /p> Dispositivos IO (disco, red): iostat, ss, netstat, iptraf, iftop, lsof; descripción del comando vmstat: 1) Procs:r muestra cuántos procesos están esperando tiempo de CPU. b Muestra el número de procesos en suspensión ininterrumpida. Esperando E/S. 2) Memoria: swpd muestra la cantidad de bloques de datos intercambiados en el disco. El número de bloques de datos no utilizados, bloques de datos del búfer de usuario y bloques de datos utilizados por el sistema operativo. 3) Intercambio: la cantidad de bloques de datos que el sistema operativo intercambia del disco a la memoria y de la memoria al disco por segundo. Lo mejor es que s1 y s0 sean 0. 4) Io: la cantidad de bloques de datos escritos en el dispositivo b0 que se leen desde el dispositivo b1 por segundo. Refleja la E/S del disco. 5) Sistema: Muestra el número de interrupciones (in) y el número de cambios de contexto (cs) que ocurren por segundo. 6) Cpu: muestra el tiempo de CPU utilizado para ejecutar el código de usuario, el código del sistema, el tiempo de inactividad y la espera de E/S. Descripción del comando iostat: Comando de ejemplo: iostat -dk 1 5 iostat -d -k -x 5 (Ver uso del dispositivo (%util) y tiempo de respuesta (espera)) 1 ) tps: El número de transmisiones por segundo del dispositivo. "Una transferencia" significa "una solicitud de E/S". Se pueden combinar varias solicitudes lógicas en "una solicitud de E/S". 2) iops: cuando el hardware sale de fábrica, el fabricante define un número máximo de IO por segundo. 3) Se desconoce el tamaño de la solicitud de "una transferencia". 4) kB_read/s: la cantidad de datos leídos desde el dispositivo (expresados en la unidad) por segundo; 5) KB_wrtn/s: la cantidad de datos escritos en el dispositivo (expresados en la unidad) por segundo; datos leídos; 7) kB_wrtn: la cantidad total de datos escritos en estas unidades es Kilobytes. 4. ¿Cuáles son las soluciones a los problemas a nivel de sistema? ¿Crees que es mejor tener una carga alta o una carga baja? En la producción real, generalmente se cree que mientras la CPU no supere el 90%, no habrá ningún problema. Por supuesto, no se excluyen las siguientes situaciones especiales: Alta carga de CPU y baja carga de IO: 1) Memoria insuficiente 2) Rendimiento deficiente del disco; 3) Problema de SQL ---> Vaya a la capa de base de datos para solucionar más problemas de SQL 4) Problemas de IO (el disco alcanza la criticidad, diseño de raid deficiente, degradación de raid, bloqueos, tps demasiado altos por unidad de tiempo 5) tps también); alto Alto: una gran cantidad de E/S de datos pequeños, una gran cantidad de escaneos completos de tablas. Alta carga de IO, baja carga de CPU: 1) Una gran cantidad de operaciones de escritura de IO pequeñas: confirmación automática, que genera una gran cantidad de IO/PS pequeñas, una fija; Valor del disco, el hardware se envía desde la fábrica, un número máximo de IO por segundo definido por el fabricante. 2) Una gran cantidad de operaciones de escritura de IO de gran tamaño: la probabilidad de problemas de SQL es relativamente alta Las cargas de IO y CPU son muy altas: El hardware no es suficiente o hay un problema con SQL. 4. Optimización básica 1. Localice el problema: Hardware-->Sistema-->Aplicación-->Base de datos-->Arquitectura (alta disponibilidad, separación de lectura y escritura, subbase de datos y subtabla). Dirección del procesamiento: aclarar los objetivos de optimización, comprometer el rendimiento y la seguridad y prevenir problemas antes de que ocurran. 2. ¿Optimización de hardware? Aspecto del host: Según el tipo de base de datos, selección de CPU del host, selección de capacidad de memoria, selección de disco: 1) Equilibra los recursos de memoria y disco 2) E/S aleatoria y secuencial; E/S; 3) La BBU (Unidad de respaldo de batería) de la tarjeta RAID del host está cerrada. Selección de CPU: Los dos factores clave de la CPU: número de núcleos y frecuencia principal se seleccionan según los diferentes tipos de negocio: 1) Uso intensivo de CPU: más cálculos, OLTP - CPU con alta frecuencia y más núcleos 2) IO-intensivo: comparación de consultas, OLAP - más núcleos pero no necesariamente alta frecuencia Selección de memoria: La base de datos de tipo OLAP requiere más memoria, lo cual es relacionado con el nivel de adquisición de datos. La memoria general para datos de tipo OLTP es de 2 a 4 veces la cantidad de núcleos de CPU. No existe una práctica recomendada. Almacenamiento: 1) Elija diferentes dispositivos de almacenamiento según los diferentes tipos de datos almacenados; 2) Configure un nivel RAID razonable (raid5, raid10, disco de repuesto activo 3); el sistema operativo, no es necesario hacer elecciones demasiado especiales. Lo mejor es hacer redundancia (raid1) (ssd, sas, sata). 4) Tarjeta RAID: selección de la tarjeta RAID del host: logra la redundancia del disco del sistema operativo (raid1); equilibra la memoria y los recursos del disco; E/S aleatoria y secuencial BBU (Unidad de respaldo de batería) del cierre de la tarjeta RAID del host. Equipo de red: Utilice el tráfico para admitir equipos de red superiores (conmutadores, enrutadores, cables de red, tarjetas de red, tarjetas HBA) Nota: los planes anteriores Esto debe considerarse al diseñar inicialmente el sistema. 3 ¿Optimización del hardware del servidor? 1) Luz de estado físico 2) Viene con equipo de gestión: tarjeta de control remoto (equipo FENCE: ipmi ilo idarc), encendido y apagado y monitoreo de hardware. 3) El software y el equipo de monitoreo de terceros (snmp, agente) monitorean las instalaciones físicas. 4) Dispositivo de almacenamiento: plataforma de monitoreo incorporada. EMC2 (adquirida por HP), Hitachi (hds), discos duros OEM de gama baja de IBM y almacenamiento de alta gama son sus propias tecnologías, el almacenamiento de Huawei. 4. ¿Optimización del sistema? Cpu: Básicamente no se necesita ningún ajuste, solo céntrese en la selección del hardware. Memoria: Básicamente no se necesita ningún ajuste, solo concéntrate en la selección del hardware. SWAP: MySQL intenta evitar el uso de swap. El intercambio predeterminado en los servidores de Alibaba Cloud es 0. IO: raid, sin estrategia de programación lvm, ext4 o xfs, ssd, IO. Ajuste de intercambio (no use partición de intercambio) Cambie el contenido de /proc/sys/vm/swappiness a 0 (temporal), agregue vm.swappiness a /etc/sysctl .conf =0 (permanente) Este parámetro determina si Linux tiende a utilizar swap o liberar el caché del sistema de archivos. En situaciones en las que la memoria es escasa, los valores más bajos tienden a liberar la memoria caché del sistema de archivos. Por supuesto, este parámetro solo puede reducir la probabilidad de usar swap, pero no puede evitar que Linux use swap. Modifique el parámetro de configuración del método innodb_flush_ de MySQL y habilite el modo O_DIRECT: En este caso, el grupo de buffers de InnoDB omitirá directamente el caché del sistema de archivos para acceder al disco, pero el El registro de rehacer seguirá ahí. Se utilizará la memoria caché del sistema de archivos. Vale la pena señalar que el registro de rehacer está en modo de sobrescritura. Incluso si se utiliza el caché del sistema de archivos, no ocupará demasiado. Política de programación de IO: #echo fecha límite>/sys/block/sda/queue/scheduler se modifica temporalmente hasta la fecha límite Modificado permanentemente vi /boot/grub/grub.conf cambie al siguiente contenido: kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ lift=deadline rhgb quiet 5 ¿Ajuste de parámetros del sistema? Optimización de los parámetros del kernel del sistema Linux: vim/etc/sysctl.confnet.ipv4.ip_local_port_range = 1024 65535: # Rango de puertos de usuario net.ipv4.tcp_max_syn_backlog = 4096 net.ipv4. tcp_fin_timeout = 30 fs.file-max=65535: # El identificador máximo de archivos del sistema controla el número máximo de archivos abiertos Parámetros de restricción de usuario (MySQL no necesita establecer la siguiente configuración): vim/etc/security/limits.conf * soft nproc 65535* hard nproc 65535* soft nofile 65535* hard nofile 65535 6. Las aplicaciones empresariales y las aplicaciones de bases de datos son independientes; Firewall: iptables, selinux y otros servicios inútiles (cerrado): chkconfig --level 23456 acpid off chkconfig - nivel 23456 anacron desactivado chkconfig --nivel 23456 autofs desactivado chkconfig --nivel 23456 avahi-daemon desactivado chkconfig --nivel 23456 bluetooth desactivado chkconfig --nivel 23456 tazas desactivado chkconfig --nivel 23456 firstboot desactivado chkconfig --nivel 23456 haldaemon desactivado chkconfig --nivel 23456 hplip apagado chkconfig --nivel 23456 ip6tables apagado chkconfig --nivel 23456 iptables apagado chkconfig --nivel 23456 isdn apagado chkconfig --nivel 23456 pcscd apagado chkconfig --nivel 23456 sendmail apagado chkconfig --nivel 23456 yum-updatesd off No inicie el nivel de ejecución 3 de la interfaz gráfica en un servidor donde esté instalada una interfaz gráfica. Además, pensar si nuestro negocio realmente necesita MySQL en el futuro, o si deberíamos utilizar otro tipo de bases de datos. El estado más alto del uso de una base de datos es no usar una base de datos. 5. Optimización de la base de datos Dirección de optimización de SQL: plan de ejecución, índice, reescritura de SQL. Dirección de optimización de la arquitectura: arquitectura de alta disponibilidad, arquitectura de alto rendimiento, subbase de datos y subtabla. 1. ¿Optimización de los parámetros de la base de datos? Ajustar la instancia general (optimización avanzada, expansión): thread_concurrency: # Número de subprocesos simultáneos sort_buffer_size: # Ordenar caché read_buffer_size: # Caché de lectura secuencial read_rnd_buffer_size: # Caché de lectura aleatoria key_buffer_size : # Índice de caché thread_cache_size: # (1G?>8, 2G?>16, 3G?>32, >3G?>64) Capa de conexión (optimización básica) Establecer clientes de conexión razonables y métodos de conexión: max_connections # Número máximo de conexiones, establecido de acuerdo con el número de transacciones max_connect_errors # Número máximo de conexiones de error, lo más grande posible connect_timeout # Tiempo de espera de conexión max_user_connections # Número máximo de usuarios conexiones skip -name-resolve # Saltar resolución de nombre de dominio wait_timeout # Esperar tiempo de espera back_log # Número de conexiones que pueden estar en la pila Capa SQL (optimización básica) query_cache_size: Consulta caché >>> Base de datos de tipo OLAP, debe concentrarse en aumentar esta memoria caché, pero generalmente no excederá los GB. Para los datos que se modifican con frecuencia, el caché dejará de ser válido inmediatamente. Podemos utilizar bases de datos en memoria (redis, memecache) para reemplazar sus funciones. 2. ¿Capa del motor de almacenamiento (parámetros de optimización básicos de innodb)? default-storage-engineinnodb_buffer_pool_size # No hay un tamaño fijo, valor de prueba del 50%, verifique la situación y luego ajuste. Pero intente configurarlo para que no exceda el 70% de la memoria física innodb_file_per_table=(1,0)innodb_flush_log_at_trx_commit=(0,1,2) # 1 es el más seguro, 0 es el rendimiento más alto, 2 es un compromiso binlog_syncInnodb_flush_method=(O_DIRECT, fdatasync)innodb_log_buffer_size # 100M El siguiente innodb_log_file_size # Por debajo de 100M innodb_log_files_in_group # Menos de 5 miembros, generalmente 2-3 son suficientes (iblogfile0-N) innodb_max_dirty_pages_pct # Cuando alcance el 75%, vacíe las páginas sucias de memoria en el disco. log_binmax_binlog_cache_size # No es necesario configurar max_binlog_size # No es necesario configurar innodb_additional_mem_pool_size # Para máquinas con menos de 2G de memoria, el valor recomendado es 20M. 32G de memoria y más de 100M ¿Cómo piensa Daniel sobre el diseño de soluciones de optimización de SQL? Etiqueta: el disco es fácil de usar errores de expansión de amplificadorlvm interruptor importante mysq