14
Dec
Learn how to display all the running process/queries in the mysql database
You can use mysqladmin processlist for get process list.
mysqladmin -u root -p processlist Enter password: +----+------+-----------+-------------------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-------------------+---------+------+-------+------------------+ | 75 | root | localhost | prodtheloopwebapp | Sleep | 945 | | | | 77 | root | localhost | prodtheloopwebapp | Sleep | 466 | | | | 83 | root | localhost | prodtheloopwebapp | Sleep | 106 | | | | 84 | root | localhost | prodtheloopwebapp | Sleep | 585 | | | | 85 | root | localhost | prodtheloopwebapp | Sleep | 6957 | | | | 86 | root | localhost | prodtheloopwebapp | Sleep | 226 | | | | 87 | root | localhost | prodtheloopwebapp | Sleep | 825 | | | | 88 | root | localhost | prodtheloopwebapp | Sleep | 346 | | | | 89 | root | localhost | prodtheloopwebapp | Sleep | 705 | | | | 90 | root | localhost | prodtheloopwebapp | Sleep | 7077 | | | | 92 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+-------------------+---------+------+-------+------------------+
You can use this command effectively to debug any performance issue and identify the query that is causing problems, by running the command automatically every 2 second as shown below.
mysqladmin -u root -p -i 2 processlist Enter password: +----+------+-----------+-------------------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-------------------+---------+------+-------+------------------+ | 75 | root | localhost | prodtheloopwebapp | Sleep | 78 | | | | 77 | root | localhost | prodtheloopwebapp | Sleep | 798 | | | | 83 | root | localhost | prodtheloopwebapp | Sleep | 438 | | | | 84 | root | localhost | prodtheloopwebapp | Sleep | 917 | | | | 85 | root | localhost | prodtheloopwebapp | Sleep | 198 | | | | 86 | root | localhost | prodtheloopwebapp | Sleep | 558 | | | | 87 | root | localhost | prodtheloopwebapp | Sleep | 1157 | | | | 88 | root | localhost | prodtheloopwebapp | Sleep | 678 | | | | 89 | root | localhost | prodtheloopwebapp | Sleep | 1037 | | | | 90 | root | localhost | prodtheloopwebapp | Sleep | 318 | | | | 93 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+-------------------+---------+------+-------+------------------+ +----+------+-----------+-------------------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-------------------+---------+------+-------+------------------+ | 75 | root | localhost | prodtheloopwebapp | Sleep | 80 | | | | 77 | root | localhost | prodtheloopwebapp | Sleep | 800 | | | | 83 | root | localhost | prodtheloopwebapp | Sleep | 440 | | | | 84 | root | localhost | prodtheloopwebapp | Sleep | 919 | | | | 85 | root | localhost | prodtheloopwebapp | Sleep | 200 | | | | 86 | root | localhost | prodtheloopwebapp | Sleep | 560 | | | | 87 | root | localhost | prodtheloopwebapp | Sleep | 1159 | | | | 88 | root | localhost | prodtheloopwebapp | Sleep | 680 | | | | 89 | root | localhost | prodtheloopwebapp | Sleep | 1039 | | | | 90 | root | localhost | prodtheloopwebapp | Sleep | 320 | | | | 93 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+-------------------+---------+------+-------+------------------+ +----+------+-----------+-------------------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-------------------+---------+------+-------+------------------+ | 75 | root | localhost | prodtheloopwebapp | Sleep | 82 | | | | 77 | root | localhost | prodtheloopwebapp | Sleep | 802 | | | | 83 | root | localhost | prodtheloopwebapp | Sleep | 442 | | | | 84 | root | localhost | prodtheloopwebapp | Sleep | 921 | | | | 85 | root | localhost | prodtheloopwebapp | Sleep | 202 | | | | 86 | root | localhost | prodtheloopwebapp | Sleep | 562 | | | | 87 | root | localhost | prodtheloopwebapp | Sleep | 1161 | | | | 88 | root | localhost | prodtheloopwebapp | Sleep | 682 | | | | 89 | root | localhost | prodtheloopwebapp | Sleep | 1041 | | | | 90 | root | localhost | prodtheloopwebapp | Sleep | 322 | | | | 93 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+-------------------+---------+------+-------+------------------+