Details
-
Improvement
-
Status: Done
-
High
-
Resolution: Fixed
-
None
-
None
-
None
-
Yes
-
16
Description
Release 3.0.4 implemented https://jira.percona.com/browse/PT-90 which runs:
ps_prepared_statements() {
$CMD_MYSQL $EXT_ARGV -e "SELECT t.processlist_id, pse.* \
FROM performance_schema.prepared_statements_instances pse \
JOIN performance_schema.threads t \
ON (pse.OWNER_THREAD_ID=t.thread_id)\G"
}
On systems with lots of threads and prepared statements this does a fairly large scan for monitoring; From a processlist sample of a pt-stalk executed with this feature:
Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t Rows_sent: 99685 -- Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t Rows_sent: 70740 -- Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t Rows_sent: 37304 -- Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t Rows_sent: 0 -- Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t Rows_sent: 136412 -- Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t Rows_sent: 107733 -- Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t Rows_sent: 73900 -- Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t Rows_sent: 36115 -- Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t Rows_sent: 1 -- Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t Rows_sent: 107209 -- Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t Rows_sent: 72646 -- Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t Rows_sent: 14 -- Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t Rows_sent: 109057 --
But the real problem is that not only was scanning in excess of 100k rows, is that it was running concurrently 4 - 5 instances of the query at a time; From the same processlist samples, aggregated by "Info:"
2021_12_15_14_14_20-processlist-sample-02 389 Info: NULL 4 Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t 2 Info: SHOW ENGINE INNODB MUTEX ============================================= 2021_12_15_14_14_20-processlist-sample-03 390 Info: NULL 5 Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t 2 Info: SHOW ENGINE INNODB MUTEX ============================================= 2021_12_15_14_14_20-processlist-sample-04 392 Info: NULL 3 Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t 3 Info: SELECT SQL_NO_CACHE ============================================= 2021_12_15_14_14_20-processlist-sample-05 376 Info: NULL 3 Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t 3 Info: SELECT SQL_NO_CACHE ============================================= 2021_12_15_14_14_20-processlist-sample-06 369 Info: NULL 5 Info: SELECT SQL_NO_CACHE 3 Info: SELECT t.processlist_id, pse.* FROM performance_schema.prepared_statements_instances pse JOIN performance_schema.threads t ============================================= 2021_12_15_14_14_20-processlist-sample-07 392 Info: NULL 5 Info: SELECT SQL_NO_CACHE 4 Info: SELECT t.processlist_id, pse.*
We can also see there a query with SQL_NO_CACHE; That query also comes from pt-stalk and is the one looking at locking:
SELECT SQL_NO_CACHE CONCAT('thread ', b.trx_mysql_thread_id, ' from ', p.host) AS who_blocks, IF(p.command = "Sleep", p.time, 0) AS idle_in_trx, MAX(TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP)) AS max_wait_time, COUNT(*) AS num_waiters FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id GROUP BY who_blocks ORDER BY num_waiters DESC
Both queries are necessary, but running them concurrently makes them excessively heavy. My original intent was to request making the prepared_statements_instances query optional, but I think it makes more sense to look at having pt-stalk only ever execute the same query sequentially, even if this means degrading sampling quality. In other words: avoid having same query running concurrently.
Having a single connection for each type of query should be easiest way to achieve this, and would also prevent the overhead of the dozens of connections pt-stalk does.