right ejemplos attribute sql mysql performance query-cache

sql - ejemplos - title html



¿Por qué una consulta de inserción ocasionalmente tarda tanto en completarse? (11)

Este es un problema bastante simple. Insertar datos en la tabla normalmente funciona bien, excepto en algunas ocasiones en las que la consulta de inserción tarda unos segundos. (No estoy intentando insertar datos a granel). Por lo tanto, configuré una simulación para el proceso de inserción para descubrir por qué la consulta de inserción ocasionalmente tarda más de 2 segundos en ejecutarse. Joshua sugirió que el archivo de índice puede estar siendo ajustado; Eliminé el id (campo de clave principal), pero la demora aún ocurre.

Tengo una tabla MyISAM: daniel_test_insert (esta tabla comienza completamente vacía):

create table if not exists daniel_test_insert ( id int unsigned auto_increment not null, value_str varchar(255) not null default '''', value_int int unsigned default 0 not null, primary key (id) )

Inserto datos en él y, a veces, una consulta de inserción tarda más de 2 segundos en ejecutarse. No hay lecturas en esta tabla: solo se escribe, en serie, mediante un único programa de subprocesos.

Ejecuté exactamente la misma consulta 100.000 veces para descubrir por qué la consulta ocasionalmente lleva mucho tiempo. Hasta ahora, parece ser una ocurrencia aleatoria.

Esta consulta, por ejemplo, tardó 4.194 segundos (un tiempo muy largo para una inserción):

Query: INSERT INTO daniel_test_insert SET value_int=12345, value_str=''afjdaldjsf aljsdfl ajsdfljadfjalsdj fajd as f'' - ran for 4.194 seconds status | duration | cpu_user | cpu_system | context_voluntary | context_involuntary | page_faults_minor starting | 0.000042 | 0.000000 | 0.000000 | 0 | 0 | 0 checking permissions | 0.000024 | 0.000000 | 0.000000 | 0 | 0 | 0 Opening tables | 0.000024 | 0.001000 | 0.000000 | 0 | 0 | 0 System lock | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | 0 Table lock | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0 init | 0.000029 | 0.000000 | 0.000000 | 1 | 0 | 0 update | 4.067331 | 12.151152 | 5.298194 | 204894 | 18806 | 477995 end | 0.000094 | 0.000000 | 0.000000 | 8 | 0 | 0 query end | 0.000033 | 0.000000 | 0.000000 | 1 | 0 | 0 freeing items | 0.000030 | 0.000000 | 0.000000 | 1 | 0 | 0 closing tables | 0.125736 | 0.278958 | 0.072989 | 4294 | 604 | 2301 logging slow query | 0.000099 | 0.000000 | 0.000000 | 1 | 0 | 0 logging slow query | 0.000102 | 0.000000 | 0.000000 | 7 | 0 | 0 cleaning up | 0.000035 | 0.000000 | 0.000000 | 7 | 0 | 0

(Esta es una versión abreviada del comando SHOW PROFILE, arrojé las columnas que eran todas cero).

Ahora la actualización tiene una cantidad increíble de interruptores de contexto y fallas de página menores. Opened_Tables aumenta aproximadamente 1 por 10 segundos en esta base de datos (no se está quedando sin espacio de caché de tabla)

Estadísticas:

  • MySQL 5.0.89

  • Hardware: 32 Gigs de ram / 8 núcleos a 2.66 GHz; atacar 10 discos duros SCSI (SCSI II ???)

  • He consultado los discos duros y el controlador de incursión: no se informaron errores. Las CPU tienen un 50% de inactividad.

  • iostat -x 5 (informa menos del 10% de utilización para discos duros) promedio de carga del informe superior de aproximadamente 10 por 1 minuto (lo normal para nuestra máquina db)

  • El espacio de intercambio tiene 156k usado (32 gigas de ram)

No entiendo por qué está causando este retraso en el rendimiento. Esto NO ocurre en nuestros esclavos de carga baja, solo en nuestro maestro de carga alta. Esto también ocurre con la memoria y las tablas innodb. ¿Alguien tiene alguna sugerencia? (Este es un sistema de producción, ¡así que nada exótico!)

¿Puedes crear una tabla más con 400 columnas (no nulas) y ejecutar tu prueba nuevamente? Si el número de inserciones lentas aumenta, esto podría indicar que MySQL está perdiendo el tiempo escribiendo sus registros. (No sé cómo funciona, pero puede estar alocando más bloques, o moviendo algo para evitar la fragmentación ... realmente no lo sé)

¿Puedes verificar las estadísticas en el subsistema del disco? ¿Está saturada la E / S? Esto suena como un trabajo interno de DB que va a enjuagar cosas en el disco / registro.

Actualizamos a MySQL 5.1 y durante este evento, la caché de consultas se convirtió en un problema con muchos "elementos gratuitos". estados de hilos Luego eliminamos el caché de consultas.

O bien, la actualización a MySQL 5.1 o la eliminación de la caché de consultas resolvió este problema.

FYI, para futuros lectores.

-daniel

He notado el mismo fenómeno en mis sistemas. Las consultas que normalmente toman un milisegundo tomarán de repente 1-2 segundos. Todos mis casos son simples, INSERT / UPDATE / REPLACE de una sola tabla --- no en ningún SELECT. Sin carga, bloqueo o acumulación de hilo es evidente.

Sospeché que se debía a borrar páginas sucias, cambios de enrojecimiento en el disco, o algún mutex oculto, pero todavía tengo que reducirlo.

También descartado

  • Carga del servidor: sin correlación con alta carga
  • Motor: sucede con InnoDB / MyISAM / Memory
  • MySQL Query Cache - sucede ya sea que esté activado o desactivado
  • Rotaciones de registros: sin correlación en eventos

La única observación que tengo en este momento se deriva del hecho de que estoy ejecutando el mismo DB en varias máquinas. Tengo una aplicación de lectura pesada, así que estoy usando un entorno con replicación: la mayor parte de la carga está en los esclavos. Me di cuenta de que a pesar de que hay una carga mínima en el maestro, el fenómeno ocurre más allí. Aunque no veo problemas de bloqueo, tal vez Innodb / Mysql tenga problemas con la concurrencia (de subprocesos). Recuerde que las actualizaciones en el esclavo serán de un solo hilo.

MySQL Verion 5.1.48

Actualizar

Creo que tengo una ventaja para el problema en mi caso. En algunos de mis servidores, noté este fenómeno en más que los demás. Al ver qué era diferente entre los diferentes servidores y ajustar las cosas, me condujeron a la variable del sistema innodb de MySQL innodb_flush_log_at_trx_commit .

Encontré el documento un poco incómodo de leer, pero innodb_flush_log_at_trx_commit puede tomar los valores de 1,2,0:

  • Para 1, el búfer de registro se vacía en el archivo de registro para cada confirmación, y el archivo de registro se vacía en el disco para cada confirmación.
  • Para 2, el búfer de registro se vacía en el archivo de registro para cada confirmación, y el archivo de registro se vacía en el disco aproximadamente cada 1-2 segundos.
  • Para 0, el búfer de registro se vacía en el archivo de registro cada segundo y el archivo de registro se vacía en el disco cada segundo.

Efectivamente, en el orden (1,2,0), según lo informado y documentado, se supone que debe obtener un mayor rendimiento en el comercio para un mayor riesgo.

Habiendo dicho eso, encontré que los servidores con innodb_flush_log_at_trx_commit=0 estaban funcionando peor (es decir, que tenían 10-100 veces más "actualizaciones largas") que los servidores con innodb_flush_log_at_trx_commit=2 . Además, las cosas mejoraron inmediatamente en las malas instancias cuando lo cambié a 2 (tenga en cuenta que puede cambiarlo sobre la marcha).

Entonces, mi pregunta es, ¿a qué se dedicará la tuya? Tenga en cuenta que no culpo a este parámetro, sino que resalto que su contexto está relacionado con este problema.

La primera sugerencia que le daría es que desactive la funcionalidad de confirmación automática y que se confirme manualmente.

LOCK TABLES a WRITE; ... DO INSERTS HERE UNLOCK TABLES;

Esto beneficia el rendimiento porque el búfer de índice se vacía al disco una sola vez, después de que se hayan completado todas las instrucciones INSERT. Normalmente, habrá tantas descargas de búfer de índice como INSERT.

Pero probablemente sea lo mejor que puede hacer, y si eso es posible en su aplicación, debe hacer una inserción masiva con una sola selección.

Esto se hace a través de Vector Binding y es la manera más rápida que puede ir.

Instead of: "INSERT INTO tableName values()" DO "INSERT INTO tableName values(),(),(),().......(n) " ,

Pero considere esta opción solo si el enlace vectorial de parámetros es posible con su controlador mysql que esté utilizando.

De lo contrario, tendería a la primera posibilidad y BLOQUEAR la tabla por cada 1000 inserciones. No lo bloquee para inserciones de 100k, porque obtendrá un desbordamiento de búfer.

Llamamos exactamente el mismo problema e informamos aquí: http://bugs.mysql.com/bug.php?id=62381

Estamos usando 5.1.52 y todavía no tenemos solución. Es posible que necesitemos apagar el control de calidad para evitar este golpe de rendimiento.

Para comprobar si su disco se comporta mal y si está en Windows, puede crear un archivo cmd por lotes que cree 10.000 archivos:

@echo OFF FOR /L %%G IN (1, 1, 10000) DO TIME /T > out%%G.txt

guárdelo en un directorio temporal, como test.cmd

Habilite las extensiones de comando que ejecutan CMD con el parámetro / E: ON

CMD.exe /E:ON

Luego ejecute su lote y vea si el tiempo entre el primer y el último archivo difiere en segundos o segundos.

En Unix / Linux puede escribir un script de shell similar.

Por casualidad, ¿hay una unidad SSD en el servidor? Algunas unidades SSD sufren de ''studder'', que podría causar su síntoma.

En cualquier caso, trataría de averiguar si la demora está ocurriendo en MySQL o en el subsistema del disco.

¿Qué sistema operativo es su servidor y en qué sistema de archivos están los datos de MySQL?

Tuve este problema al usar tablas INNODB. (y los índices INNODB son aún más lentos para reescribir que MYISAM)

Supongo que está haciendo muchas otras consultas en otras tablas, por lo que el problema sería que MySQL tiene que manejar las grabaciones de disco en archivos que se hacen más grandes y necesita asignar espacio adicional a esos archivos.

Si usa tablas MYISAM, recomiendo usar

LOAD DATA INFILE ''file-on-disk'' INTO TABLE `tablename`

mando; MYISAM es sensacionalmente rápido con esto (incluso con claves principales) y el archivo puede formatearse como csv y puede especificar los nombres de columna (o puede poner NULL como el valor para el campo de autoincrement).

Ver el documento MYSQL aquí .

si está utilizando la inserción múltiple en uno usando el ciclo for, entonces tome un descanso después de cada ciclo utilizando la función sleep de PHP ("tiempo en segundos").