Query to show blocking information

memsql

#1

Hi ,

How can we check who is blocking who through memsql query?

Thanks,
Asrar


#2

Hi Asrar

I am not quite sure I understand your question – what do you mean by who is blocking who?

Thanks!


#3

Hi Jacky,

Just need the information related to query blocking.

Which query is getting blocked currently in the concurrent queries,
Which query is the culprit to cause blocking ,

Thanks!

Asrar


#4

To see the state of queries (i.e. executing/queued etc), run

select * from information_schema.processlist;

The STATE column will specify the state of the queries.

E.g.

memsql> select id, user, command, state, info from information_schema.processlist;
+-----+-------------+---------+-----------+---------------------------------------------------------------------------+
| id  | user        | command | state     | info                                                                      |
+-----+-------------+---------+-----------+---------------------------------------------------------------------------+
| 134 | root        | Query   | queued    | select "This query gets queued", sleep(1000000)                           |
| 110 | root        | Query   | executing | select "This query runs", sleep(1000000)                                  |
|  23 | root        | Query   | executing | select id, user, command, state, info from information_schema.processlist |
|  21 | distributed | Sleep   |           | NULL                                                                      |
+-----+-------------+---------+-----------+---------------------------------------------------------------------------+

To understand which query is causing other queries to get queued, can you send the output of following queries (when your system is in a state where queries are queued):
SHOW WORKLOAD MANAGEMENT STATUS;
select pc.plan_id, pc.optimizer_notes, pl.info from information_schema.plancache as pc join information_schema.processlist as pl on pl.plan_id = pc.plan_id;

Are you using resource pools? (I am assuming you are not).