- Monitoring Processes with Performance Schema in MySQL 5.6 by Mark
- SHOW PROCESSLIST in MySQL 5.6 by Baron
Server version: 5.6.8-rc-log MySQL Community Server (GPL)
MySQL 5.6.8 RC> show processlist\G
*************************** 1. row ***************************
Id: 20007
User: root
Host: localhost
db: test
Command: Query
Time: 0
State: executing
Info: select 10000
*************************** 2. row ***************************
Id: 4
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
2 rows in set (0.00 sec)
That is quick and easy. It give you the ID, user , host , state and time running. It also shows the command so your able to take review any obvious issues or take it and execute an explain so you can check for indexes. In a normal database you will have a lot more rows and have to parse out information accordingly to find what your after. How many rows are acceptable is dependant on the application and database. A simple start by using"pager" is one way to at least be able to see the information available, when you might have 200+ rows of information for example.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
MySQL 5.6.8 RC> pager more
PAGER set to 'more' # Turns it on
MySQL 5.6.8 RC> show processlist\G
MySQL 5.6.8 RC> pager
Default pager wasn't set, using stdout. # turns it off
You can less as well but this is just an example and a starting point.
In Mark's blog he points how issues with SHOW PROCESSLIST like stealing connections, non-blocking and views. He talks about creating a view with information from the tables within the performance_schema and information_schema so we can gather more information in a non-blocking way. So if started to just dig around some might think that the events_statements_current would be a replacement ( seen below). But as you can see below it is not, lots of information but not a direct replacement.
MySQL 5.6.8 RC> select * from events_statements_current\G
*************************** 2. row ***************************
THREAD_ID: 90033
EVENT_ID: 334676
END_EVENT_ID: NULL
EVENT_NAME: statement/com/Query
SOURCE: mysqld.cc:913
TIMER_START: 3807813684782000
TIMER_END: NULL
TIMER_WAIT: NULL
LOCK_TIME: 0
SQL_TEXT: select a+a+a+a+a+a+a+a+a+34673 from bench1
DIGEST: NULL
DIGEST_TEXT: NULL
CURRENT_SCHEMA: test
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
2 rows in set (0.00 sec)
So instead of digging across all the tables, while taking phone calls and etc... I will just check out the ps_helper information that Mark has available.
First per Mark's blog update the setup_consumers table;
MySQL 5.6.8 RC> update setup_consumers set ENABLED = 'YES' WHERE NAME IN ('events_stages_current','events_stages_history','events_statements_history','events_waits_current','events_waits_history');
MySQL 5.6.8 RC> SELECT * FROM setup_consumers;
+--------------------------------+---------+
| NAME | ENABLED |
+--------------------------------+---------+
| events_stages_current | YES |
| events_stages_history | YES |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_waits_current | YES |
| events_waits_history | YES |
| events_waits_history_long | NO |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
+--------------------------------+---------+
12 rows in set (0.00 sec)
mysql -p < /tmp/ps_helper_56.sql_.txt
( Btw I had to take the processlist_full view from Mark's blog as was not in the ps_helper_56.sql_.txt file at the time I wrote this blog. )
MySQL 5.6.8 RC> use ps_helper
MySQL 5.6.8 RC> select * from processlist_full \G *************************** 2. row ***************************
thd_id: 180043
conn_id: 180024
user: root@localhost
db: test
command: Query
state: freeing items
time: 0
current_statement: select a+a+a+a+a+a+a+a+a+53833 from bench1
last_statement: NULL
last_statement_latency: NULL
lock_latency: 33.00 µs
rows_examined: 2
rows_sent: 2
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: YES
last_wait: wait/io/table/sql/handler
last_wait_latency: 618.26 ns
source: handler.cc:2715
I know have the information I was reviewing before with additional information. I like the "full_scan" field included into this so you can see if indexes are being used right away.
So the perks overall are, by taking the ps_helper code you should be able to easily install the views and not have to piece all of the different information together. All of that work has been done for you. Would I use the views all the time? No. SHOW PROCESSLIST will be just fine for me a lot of the time for now. I will take this and have it available, because when you do need it, it will be great information to have. I also think that once installed and available it will be a tool that will slowly be used more and more for debugging issues and concerns. Great work guys.
Some other show processlist references to note:
