¿cómo personalizar 'mostrar lista de procesos' en mysql?
Quiero ordenar por Tiempo,pero parece que no hay manera de hacer eso ?
mysql> show processlist;
+--------+-------------+--------------------+------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-------------+--------------------+------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+
| 1 | system user | | NULL | Connect | 226953 | Waiting for master to send event | NULL |
| 2 | system user | | v3 | Connect | 35042 | Locked | update postings a
left join cities b on b.id=a.job_city_id
left join states h on h.id=b.stat |
| 313888 | irnadmin | 172.19.0.239:40136 | v3 | Sleep | 0 | | NULL |
| 314075 | irnadmin | 172.19.0.239:41113 | v3 | Sleep | 0 | | NULL |
| 314118 | irnadmin | 172.19.0.239:41282 | v3 | Query | 34978 | freeing items | SELECT id, screen_name, type, active, bound, LastLogin, robotno, protocol FROM accounts WHERE email_ |
| 314686 | irnadmin | 172.19.0.239:43251 | v3 | Sleep | 0 | | NULL |
| 314732 | irnadmin | 172.19.0.239:43436 | v3 | Query | 34978 | freeing items | SELECT id, screen_name, type, active, bound, LastLogin, robotno, protocol FROM accounts WHERE email_ |
| 314984 | irnadmin | 172.19.0.239:44366 | v3 | Sleep | 2 | | NULL |
| 315051 | irnadmin | 172.19.0.239:44713 | v3 | Query | 0 | NULL | NULL |
| 315198 | irnadmin | 172.19.0.239:51569 | v3 | Sleep | 2 | | NULL |
| 315280 | irnadmin | 172.19.0.239:51849 | v3 | Query | 34978 | freeing items | SELECT id, email_address, type, closed, robotno FROM accounts WHERE screen_name = 'ShantanuS' |
| 315320 | irnadmin | 172.19.0.239:52045 | v3 | Query | 34978 | freeing items | SELECT id, screen_name, type, active, bound, LastLogin, robotno, protocol FROM accounts WHERE email_ |
| 315384 | irnadmin | 172.19.0.239:52463 | v3 | Sleep | 1 | | NULL |
| 452248 | irnadmin | 172.19.0.28:54899 | v3 | Query | 34978 | freeing items | SELECT id, email_address, type, closed, robotno FROM accounts WHERE screen_name = 'LIZW0218' |
| 452291 | irnadmin | 172.19.0.28:55045 | v3 | Sleep | 1 | | NULL |
| 452316 | irnadmin | 172.19.0.28:55144 | v3 | Sleep | 0 | | NULL |
| 452353 | irnadmin | 172.19.0.28:55278 | v3 | Sleep | 0 | | NULL |
| 452382 | irnadmin | 172.19.0.28:55371 | v3 | Query | 34978 | freeing items | SELECT o.account_id FROM online o JOIN accounts a ON a.id=o.account_id WHERE o.server_id IS NULL AND |
| 452413 | irnadmin | 172.19.0.28:55479 | v3 | Sleep | 1 | | NULL |
| 452541 | irnadmin | 172.19.0.28:55946 | v3 | Query | 34978 | freeing items | SELECT o.account_id FROM online o JOIN accounts a ON a.id=o.account_id WHERE o.server_id IS NULL AND |
| 452626 | irnadmin | 172.19.0.28:56215 | v3 | Sleep | 2 | | NULL |
| 452711 | irnadmin | 172.19.0.28:39916 | v3 | Sleep | 0 | | NULL |
| 452781 | irnadmin | 172.19.0.28:40161 | v3 | Sleep | 1 | | NULL |
| 452904 | irnadmin | 172.19.0.28:40955 | v3 | Query | 34978 | freeing items | select a.id, aa.screen_name, i.requester from interview_requests i left join accounts aa on aa.id=i. |
| 453014 | irnadmin | 172.19.0.28:41291 | v3 | Query | 34978 | freeing items | SELECT o.account_id FROM online o JOIN accounts a ON a.id=o.account_id WHERE o.server_id IS NULL AND |
| 453057 | irnadmin | 172.19.0.28:41377 | v3 | Query | 34978 | freeing items | select a.id, aa.screen_name, i.requester from interview_requests i left join accounts aa on aa.id=i. |
| 453084 | irnadmin | 172.19.0.28:41441 | v3 | Sleep | 0 | | NULL |
| 453112 | irnadmin | 172.19.0.28:41536 | v3 | Sleep | 0 | | NULL |
| 453156 | irnadmin | 172.19.0.28:41653 | v3 | Query | 34978 | freeing items | SELECT protocol FROM accounts WHERE email_address= '***@gtalk.jabber.jobirn.c |
| 453214 | irnadmin | 172.19.0.28:41800 | v3 | Sleep | 5 | | NULL |
| 453243 | irnadmin | 172.19.0.28:41991 | v3 | Sleep | 0 | | NULL |
| 453313 | irnadmin | 172.19.0.28:42255 | v3 | Query | 34978 | freeing items | SELECT o.account_id FROM online o JOIN accounts a ON a.id=o.account_id WHERE o.server_id IS NULL AND |
| 453396 | irnadmin | 172.19.0.28:53718 | v3 | Sleep | 2 | | NULL |
| 453476 | irnadmin | 172.19.0.28:54019 | v3 | Sleep | 0 | | NULL |
| 453561 | irnadmin | 172.19.0.28:54352 | v3 | Sleep | 3 | | NULL |
| 453594 | irnadmin | 172.19.0.28:54456 | v3 | Sleep | 0 | | NULL |
| 453727 | irnadmin | 172.19.0.28:55166 | v3 | Query | 34978 | freeing items | SELECT id, screen_name, type, active, bound, LastLogin, robotno, protocol FROM accounts WHERE email_ |
| 453786 | irnadmin | 172.19.0.28:55320 | v3 | Sleep | 4 | | NULL |
| 610140 | irnadmin | 172.19.0.28:33848 | v3 | Query | 34978 | freeing items | select a.id, aa.screen_name, i.requester from interview_requests i left join accounts aa on aa.id=i. |
| 685119 | irnadmin | 172.19.0.27:37251 | v3 | Query | 34980 | Sending data | select postings.id id,category, job_desc_title,
IF(c1.name is not null,c1.name,IF(c2.name is not n |
| 685226 | irnadmin | 172.19.0.139:57274 | v3 | Query | 34735 | Locked | SELECT job_desc_title,job_desc,job_state_name,job_city_name,company_categories.name,postings.categor |
| 685229 | irnadmin | 172.19.0.139:57278 | v3 | Query | 34735 | Locked | SELECT job_desc_title,job_desc,job_state_name,job_city_name,company_categories.name,postings.categor |
| 685232 | irnadmin | 172.19.0.139:57283 | v3 | Query | 34734 | Locked | select job_desc_title,job_desc from postings where id=287650 |
| 685233 | irnadmin | 172.19.0.139:57286 | v3 | Query | 34734 | Locked | SELECT accounts.screen_name,postings.url url, accounts.type owner_type, postings.id ID, postings.job |
| 685235 | irnadmin | 172.19.0.28:37502 | v3 | Query | 34734 | Locked | SELECT accounts.screen_name,postings.url url, accounts.type owner_type, postings.id ID, postings.job |
| 686496 | irnadmin | 172.19.0.239:33306 | v3 | Query | 32589 | Locked | SELECT accounts.screen_name,postings.url url, accounts.type owner_type, postings.id ID, postings.job |
| 686503 | irnadmin | 172.19.0.28:54051 | v3 | Query | 32588 | Locked | SELECT job_desc_title, job_desc, IF(postings.category IS NOT NULL, postings.category, job_categories |
| 709550 | root | localhost | v3 | Query | 0 | NULL | show processlist |
| 710084 | irnadmin | 172.19.0.27:53285 | NULL | Query | 0 | removing tmp table | show status where Variable_name='Threads_running' |
+--------+-------------+--------------------+------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+
49 rows in set (0.00 sec)
147
6 answers
Las versiones más recientes de SQL soportan la lista de procesos en information_schema:
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
Usted puede ORDENAR POR en cualquier forma que desee.
El INFORMATION_SCHEMA.La tabla PROCESSLIST fue agregada en MySQL 5.1.7. Puedes averiguar con qué versión estás usando:
SELECT VERSION()
276
Author: Andomar,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-05-30 12:09:46
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-05-30 12:09:46
El comando
show full processlist
Puede sustituirse por:
SELECT * FROM information_schema.processlist
Pero si vas con la última versión puedes añadir la cláusula WHERE:
SELECT * FROM information_schema.processlist WHERE `INFO` LIKE 'SELECT %';
Para más información visita esto
38
Author: Reza S,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-05-08 17:56:30
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-05-08 17:56:30
Otra herramienta útil para esto desde la interfaz de línea de comandos, es el comando pager.
Eg
pager grep -v Sleep | more; show full processlist;
Entonces puedes hojear los resultados.
También puede buscar ciertos usuarios, IPs o consultas con grep o sed de esta manera.
El comando pager es persistente por sesión.
6
Author: wcndave,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-01-30 12:31:30
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-01-30 12:31:30
Simplemente puedes capturar la salida y pasarla a través de un filtro, algo así como:
mysql show processlist
| grep -v '^\+\-\-'
| grep -v '^| Id'
| sort -n -k12
Los dos greps eliminan las líneas de encabezado y remolque (otras pueden ser necesarias si hay otras líneas que no contienen información útil) y la ordenación se realiza en función del campo numérico número 12 (creo que es correcto).
Este funciona para su salida inmediata:
mysql show processlist
| grep -v '^\+\-\-'
| grep -v '^| Id'
| grep -v '^[0-9][0-9]* rows in set '
| grep -v '^ '
| sort -n -k12
3
Author: paxdiablo,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-08-27 23:53:32
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-08-27 23:53:32
Si usas una versión antigua de MySQL siempre puedes usar \P combinado con algún buen fragmento de código awk. Ejemplo interesante aquí
Http://www.dbasquare.com/2012/03/28/how-to-work-with-a-long-process-list-in-mysql /
¿No es exactamente lo que necesitas?
2
Author: Dereck84,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-04-04 00:11:01
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-04-04 00:11:01
...Todavía no tenemos una versión más reciente de MySQL, así que pude hacer esto (solo funciona en UNIX):
host=maindb
echo "show full processlist\G" | mysql -h$host | grep -B 6 -A 1 Locked
Lo anterior buscará todas las sesiones bloqueadas, y devolverá la información y el SQL que está involucrado.
So So-suponiendo que quería consultar las sesiones que estaban durmiendo:
host=maindb
echo "show full processlist\G" | mysql -h$host | grep -B 6 -A 1 Sleep
O, suponiendo que necesite proporcionar parámetros de conexión adicionales para MySQL:
host=maindb
user=me
password=mycoolpassword
echo "show full processlist\G" | mysql -h$host -u$user -p$password | grep -B 6 -A 1 Locked
Con un par de ajustes, estoy seguro de que se podría crear fácilmente un script de shell para consultar el processlist la forma en que lo desee.