usage mysqld high php mysql insert nginx cpu-usage

php - mysqld - mysql using 100 cpu usage



MySql inserta alta carga de CPU (11)

Tengo nginx recibiendo solicitudes POST y un pequeño script PHP que coloca el cuerpo de la solicitud en MySql. El problema es el uso de CPU MySQL muy alto cuando tengo 300 POST por segundo. Esperaba que MySql fuera una cosa rápida que pudiera manejar mucho más que 300 insertos por segundo. Utilizo la instancia pequeña de Amazon EC2, Amazon Linux.

top - 18:27:06 up 3 days, 1:43, 2 users, load average: 4.40, 5.39, 5.76 Tasks: 178 total, 4 running, 174 sleeping, 0 stopped, 0 zombie Cpu(s): 24.6%us, 13.4%sy, 0.0%ni, 0.0%id, 1.1%wa, 0.0%hi, 4.9%si, 56.0%st Mem: 1717480k total, 1640912k used, 76568k free, 193364k buffers Swap: 917500k total, 5928k used, 911572k free, 824136k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 7677 mysql 20 0 313m 153m 6124 S 39.0 9.2 393:49.11 mysqld 16529 nginx 20 0 157m 151m 820 R 15.2 9.0 28:36.50 nginx 29793 php 20 0 36780 3240 1896 S 2.5 0.2 0:00.34 php-fpm 29441 php 20 0 36780 3204 1892 S 2.2 0.2 0:00.78 php-fpm 29540 php 20 0 36780 3204 1900 S 2.2 0.2 0:00.82 php-fpm 29603 php 20 0 36780 3220 1892 S 2.2 0.2 0:00.61 php-fpm 29578 php 20 0 36780 3200 1900 S 1.9 0.2 0:00.42 php-fpm 29950 php 20 0 36780 3192 1900 S 1.9 0.2 0:00.48 php-fpm 30030 php 20 0 36780 3180 1888 S 1.9 0.2 0:00.08 php-fpm 30025 php 20 0 36780 3200 1888 S 1.6 0.2 0:00.11 php-fpm 29623 php 20 0 36780 3184 1892 S 1.3 0.2 0:00.49 php-fpm 29625 php 20 0 36780 3236 1900 S 1.3 0.2 0:00.46 php-fpm 29686 php 20 0 36780 3364 1900 R 1.3 0.2 0:00.51 php-fpm 29863 php 20 0 36780 3184 1892 S 1.3 0.2 0:00.23 php-fpm 30018 php 20 0 36780 3192 1892 S 1.3 0.2 0:00.19 php-fpm 29607 php 20 0 36780 3224 1900 S 1.0 0.2 0:00.42 php-fpm 29729 php 20 0 36780 3180 1888 R 1.0 0.2 0:00.41 php-fpm

Aquí está mi código PHP:

<?php $mysqli=new mysqli("localhost", "root", "", "errorreportsraw"); $project_id=$_REQUEST["project_id"]; $data=$_REQUEST["data"]; $date=date("Y-m-d H-i-s"); $mysqli->query("insert into rawreports(date, data, project_id) values (''$date'', ''$data'', ''$project_id'')") ?>

Probé mysql_connect, mysql_pconnect, mysqli ("localhost", ...), mysqli ("p: localhost", ...) - sigue siendo el mismo. No se están ejecutando consultas en la base de datos, excepto estas inserciones.

Aquí está mi mesa:

CREATE TABLE `rawreports` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `date` datetime NOT NULL, `data` mediumtext NOT NULL, `project_id` varchar(100) NOT NULL, PRIMARY KEY (`id`) );

Es bastante simple, sin índices, solo para almacenar los datos POST para su posterior procesamiento. El campo ''datos'' es algo así como 3 kilobytes en la mayoría de los casos. Probé innodb y myisam, sigue igual.

Aquí está mi SHOW PROCESSLIST, nada excepto insertos múltiples:

mysql> show processlist; +---------+----------------------+-----------+-----------------+---------+------+------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +---------+----------------------+-----------+-----------------+---------+------+------------------+------------------------------------------------------------------------------------------------------+ | 3872248 | root | localhost | NULL | Query | 0 | NULL | show processlist | | 3901991 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902003 | root | localhost | errorreportsraw | Sleep | 0 | | NULL | | 3902052 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902053 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902054 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902055 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902056 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902057 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902058 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902059 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902060 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"protocol_version":" | | 3902061 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902062 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902063 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902064 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902065 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902066 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902067 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902068 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902069 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902070 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902071 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902072 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902073 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902074 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902075 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902076 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902077 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902078 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902079 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902080 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902081 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902082 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902083 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902084 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902085 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902086 | root | localhost | errorreportsraw | Query | 0 | update | insert into rawreports(date, data, project_id) values (''2012-05-05 17-01-37'', ''{"exceptions":[{"stac | | 3902087 | unauthenticated user | localhost | NULL | Connect | NULL | Reading from net | NULL | +---------+----------------------+-----------+-----------------+---------+------+------------------+------------------------------------------------------------------------------------------------------+ 39 rows in set (0.00 sec)

Aquí está el PERFIL cuando hago la misma inserción manualmente mientras el servidor todavía está bajo presión:

set profiling=1; insert into rawreports(date, data, project_id) values(''2012-05-04 00:58:08'',''[3000-chars-data-here]'',''5''); show profile ALL for query 1; Status Duration CPU_user CPU_system Context_voluntary Context_involuntary Block_ops_in Block_ops_out Messages_sent Messages_received Page_faults_major Page_faults_minor Swaps Sourc starting 0.000231 0.000000 0.000000 0 0 0 0 0 0 0 0 0 NULL NULL NULL checking permissions 0.000030 0.000000 0.000000 0 0 0 0 0 0 0 0 0 check_access sql_parse.cc 4745 Opening tables 0.000057 0.001000 0.000000 0 0 0 0 0 0 0 0 0 open_tables sql_base.cc 4836 System lock 0.000030 0.000000 0.000000 0 0 0 0 0 0 0 0 0 mysql_lock_tables lock.cc 299 init 0.000037 0.000000 0.000000 0 0 0 0 0 0 0 0 0 mysql_insert sql_insert.cc 721 update 0.075716 0.001999 0.011998 166 2 0 0 0 0 0 0 0 mysql_insert sql_insert.cc 806 Waiting for query cache lock 0.000087 0.000000 0.000000 0 0 0 0 0 0 0 0 0 lock sql_cache.cc 552 update 0.000037 0.000000 0.000000 0 0 0 0 0 0 0 0 0 NULL NULL NULL end 0.000024 0.000000 0.000000 0 0 0 0 0 0 0 0 0 mysql_insert sql_insert.cc 1049 query end 0.000042 0.000000 0.000000 0 0 0 0 0 0 0 0 0 mysql_execute_command sql_parse.cc 4434 closing tables 0.000031 0.000000 0.001000 0 0 0 0 0 0 0 0 0 mysql_execute_command sql_parse.cc 4486 freeing items 0.000126 0.000000 0.000000 0 1 0 0 0 0 0 0 0 mysql_parse sql_parse.cc 5634 logging slow query 0.000030 0.000000 0.000000 0 0 0 0 0 0 0 0 0 log_slow_statement sql_parse.cc 1460 cleaning up 0.000024 0.000000 0.000000 0 0 0 0 0 0 0 0 0 dispatch_command sql_parse.cc 1416

Yo uso MySql 5.5.20. Intenté tanto InnoDB como MyISAM, ambas cosas iguales.
Aquí está mi salida de iostat:

# iostat -x Linux 3.2.12-3.2.4.amzn1.i686 05/15/2012 _i686_ (1 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 23.67 0.03 18.39 4.09 52.87 0.95 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util xvdap1 0.00 1.74 0.03 0.78 1.50 25.12 32.85 0.01 14.03 5.92 0.48 xvdap3 0.00 0.00 0.01 0.01 0.05 0.08 10.47 0.00 5.72 1.09 0.00 xvdf 0.40 18.59 23.14 117.55 753.12 3465.25 29.98 0.53 3.74 2.38 33.46

Lo más obvio es insertar por lotes y juntarlos todos juntos, uno por uno. Pero no puedo hacer eso porque cada inserción es una solicitud POST por separado, una ejecución de script PHP por separado. Todos son ejecutados simultáneamente y no interfieren entre sí.

Parece ser una tarea bastante simple, ¿qué está haciendo realmente tan difícil mi CPU? No tengo mucha experiencia con mysql, php, linux. Probablemente extraño algo. Gracias por cualquier idea!

¿Intentó INSERTAR RETRASADO?

Leí que tiene 300 posts / seg de clientes diferentes, pero de todos modos puede usar el paquete para insertar varias filas a la vez.

Lógica separada que recopila publicaciones e inserta filas. Por lo tanto, necesita 2 scripts, el primero solo recopila todas las publicaciones y guarda los datos en un archivo, por ejemplo. La segunda se ejecuta periódicamente una vez por segundo e inserta todos los datos en una tabla.

Por cierto: puede usar NOW () para insertar la fecha actual -> insertar en valores raw (datos de fecha, datos, project_id) (NOW (),

sin índices,

Eso no es del todo cierto.
Quitaría un índice en primer lugar.
En una tabla de tipo log, no tiene ningún sentido.

Por cierto, también puedes usar registros textuales. Para el procesamiento posterior.

Para obtener información detallada, puede ejecutar estos comandos desde la consola de mysql mientras su servidor está bajo su carga habitual:

> SET profiling = 1; > INSERT an example query > SHOW PROFILE ALL

también intento tonto

SHOW PROCESSLIST;

que dudosamente revelará algo útil, pero al menos vale la pena intentarlo

¿Estás ejecutando ese código PHP en un bucle? Puede insertar más de una fila a la vez en una sola consulta, y esto puede ayudar a reducir la carga de la CPU. Todo lo que necesita hacer es proporcionar una lista de valores separados por comas, algo como esto:

insert into rawreports(date, data, project_id) values ((''$date1'', ''$data1'', ''$project_id1''),(''$date2'', ''$data2'', ''$project_id2''), .....)")

también si está ejecutando un ciclo, no necesita repetir el new mysqli() para cada iteración.

Puede probar PDO con una declaración preparada para aprovechar la comunicación binaria entre el cliente y el servidor.

Simplemente cambie los parámetros cada vez y ejecútelos.

Además de la menor sobrecarga en la transmisión al servidor, esto podría ayudar con la contención de CPU porque MySQL no tendría que analizar la consulta cada vez.

Tendría que agrupar (poner en cola) los datos para aprovechar esto.

Aunque estoy de acuerdo en que utilizar un archivo de registro de texto sin formato o una base de datos NoSQL son mejores opciones, si va a utilizar MySQL, creo que su cuello de botella es PHP. ¿Estás usando conexiones persistentes y declaraciones preparadas? Si no, es una gran pérdida de tiempo.

Puede intentar conectar nginx directamente a MySQL usando el módulo hsock .

También tenga en cuenta que una instancia pequeña de EC2 no tiene un buen rendimiento de E / S. Necesita actualizar a un tamaño grande para obtener el tipo de rendimiento de E / S necesario para admitir 300 publicaciones por segundo.

De todas las respuestas puedo ver que puede tener las siguientes opciones para tratar el problema después del análisis:

1) Amazon EC2 o cualquier servidor en la nube tendrá una velocidad de escritura inferior a la del servidor dedicado habitual, ya que los iops de HDD se comparten entre todos los servidores virtuales alojados en un nodo físico. Así que tener una máquina separada para DB es una buena idea e incluso podrías probar Amazon Simple DB. Vale la pena intentarlo.

2) Puede intentar usar el documento base NoSQL más simple como MongoDB, que tiene multitud de velocidades en las operaciones de escritura en comparación con MySQL. Lo he comparado en mi máquina local y he encontrado resultados sorprendentes. Por lo tanto, en lugar de hacer un enfoque CSV, realmente podría almacenar su base de datos en NoSQL DB y, si fuera necesario, enviarla a MySQL para cualquier consulta relacional mediante el trabajo por lotes.

3) Use cualquier código de operación / acelerador PHP si no está usando uno.

Pero creo que MySQL se mantiene y se ralentiza e incluso aumenta el uso de la CPU debido a la velocidad de escritura del espacio de disco ...

Vea si el artículo a continuación puede ser de alguna ayuda para usted: http://kevin.vanzonneveld.net/techblog/article/improve_mysql_insert_performance/

No use datos de inserción directamente en MySQL. En su lugar, crea dos archivos csv. Diga even_rawreports.csv y odd_rawreports.csv . Ahora durante las horas pares, digamos (entre 2:00 o 2:59), siga registrando cada solicitud POST en even_rawreports.csv y durante las horas impares, inicie sesión en odd_rawreports.csv .

Escriba un trabajo cron que se ejecute cada hora y lea even_rawreports.csv en horas impares y odd_rawreports.csv en incluso horas.

En el cron Use la siguiente consulta para cargar datos a mysql directamente desde el archivo CSV en una consulta.

LOAD DATA INFILE ''even_rawreports.csv'' INTO TABLE rawreports (col1,col2,...) FIELDS TERMINATED BY ''|'' OPTIONALLY ENCLOSED BY ''"'';

Por procesamiento "posterior", ¿posiblemente se refiere a 1 hora o 1 día después? Si ese es el caso, entonces escribiría la información en un archivo CSV que rotaría una vez cada hora y luego, cuando necesite hacer su procesamiento "posterior", podrá cargar los archivos en MySQL utilizando LOAD DATA INFILE

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

He recibido LOAD DATA INFILE cargando cientos de MB de información en menos de un minuto, este método sería una excelente manera de acelerar tu respuesta web.

Basado en iostat, usted en 5.5, creo que está limitado por la CPU y las E / S de disco de su instancia de Amazon. ¿Puede cambiar temporalmente a una instancia más rápida, medir los resultados? No hay mucho margen de mejora. La fase de actualización de 75 ms de MySQL muestra que MySQL te está matando, no PHP. Me sorprende que 300 sea el límite también. No estoy seguro de los requisitos de integridad de datos, pero MongoDB puede funcionar bien para usted e incluso tiene una biblioteca PHP compatible.

La introducción de otra base de datos / archivo binario para admitir MySQL parece una opción de último recurso. Antes de que llegue, me gustaría que considere esto:

Cree una tabla usando el MEMORY Storage Engine que descarga a intervalos regulares en su almacenamiento principal.