TODO

** General information

1) The full, unedited MySQL error log. If it is very large, compress it before uploading it to the issue.

2) The latest my.cnf/my.ini file and if used the mysqld-auto.cnf file

** MySQL Status

3) Paste the below script into the MySQL command line client (mysql) on the problematic system *WHILE* the problem is occurring and upload the mysql_output.txt file. You should be logged in as a MySQL user with the SUPER privilege, for example the root user, when running the script. (If you rather go directly through your OS shell, use the -t option with the mysql client.)

TEE mysql_output.txt;

SELECT VERSION();

SELECT NOW();

/*!50503 SELECT ENGINE, COUNT(*), SUM(DATA_LENGTH) AS DATA, SUM(INDEX_LENGTH) AS IDXS, ((SUM(DATA_LENGTH)+SUM(INDEX_LENGTH))/(1024*1024)) AS TOTALMB FROM information_schema.TABLES GROUP BY ENGINE*/;

select benchmark(50000000,(1234*5678/37485-1298+8596^2)); #should take less than 20 seconds

SHOW GLOBAL VARIABLES;

SELECT * FROM INFORMATION_SCHEMA.PLUGINS;

SHOW MASTER STATUS;

SHOW SLAVE STATUS\G

SHOW GLOBAL STATUS;

XA RECOVER;

/*!50604 select processlist_state,count(*) from performance_schema.threads where processlist_state is not null group by processlist_state*/;

SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS\G

/*50613 SELECT COMMENT, COUNT, IM.* FROM INFORMATION_SCHEMA.INNODB_METRICS IM*/;

/*!50604 select processlist_state,count(*) from performance_schema.threads where processlist_state is not null group by processlist_state*/;

SHOW FULL PROCESSLIST;

-- Same query for 8.0

/*!80001 SELECT r.trx_wait_started AS wait_started,

TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,

TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,

CONCAT(sys.quote_identifier(rl.object_schema), '.', sys.quote_identifier(rl.object_name)) AS locked_table,

rl.object_schema AS locked_table_schema,

rl.object_name AS locked_table_name,

rl.partition_name AS locked_table_partition,

rl.subpartition_name AS locked_table_subpartition,

rl.index_name AS locked_index,

rl.lock_type AS locked_type,

r.trx_id AS waiting_trx_id,

r.trx_started as waiting_trx_started,

TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,

r.trx_rows_locked AS waiting_trx_rows_locked,

r.trx_rows_modified AS waiting_trx_rows_modified,

r.trx_mysql_thread_id AS waiting_pid,

sys.format_statement(r.trx_query) AS waiting_query,

rl.engine_lock_id AS waiting_lock_id,

rl.lock_mode AS waiting_lock_mode,

b.trx_id AS blocking_trx_id,

b.trx_mysql_thread_id AS blocking_pid,

sys.format_statement(b.trx_query) AS blocking_query,

bl.engine_lock_id AS blocking_lock_id,

bl.lock_mode AS blocking_lock_mode,

b.trx_started AS blocking_trx_started,

TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,

b.trx_rows_locked AS blocking_trx_rows_locked,

b.trx_rows_modified AS blocking_trx_rows_modified,

CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,

CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection

FROM performance_schema.data_lock_waits w

INNER JOIN information_schema.innodb_trx b ON b.trx_id = CAST(w.blocking_engine_transaction_id AS CHAR)

INNER JOIN information_schema.innodb_trx r ON r.trx_id = CAST(w.requesting_engine_transaction_id AS CHAR)

INNER JOIN performance_schema.data_locks bl ON bl.engine_lock_id = w.blocking_engine_lock_id

INNER JOIN performance_schema.data_locks rl ON rl.engine_lock_id = w.requesting_engine_lock_id

ORDER BY r.trx_wait_started*/\G

/*!50604 select processlist_state,count(*) from performance_schema.threads where processlist_state is not null group by processlist_state*/;

SHOW FULL PROCESSLIST;

/*!50503 select * from information_schema.innodb_trx order by trx_started*/\G

/*!50503 SELECT * FROM information_schema.INNODB_LOCKS*/\G

/*!80001 SELECT * FROM performance_schema.data_locks*/\G

/*!50708 SELECT * FROM performance_schema.metadata_locks*/\G

-- /*!50503 SELECT * FROM performance_schema.rwlock_instances*/\G

/*!50708 select * from sys.session order by thd_id */\G

/*!50503 select * from performance_schema.threads order by thread_id */\G

SELECT SLEEP(120);

SHOW MASTER STATUS;

SHOW SLAVE STATUS\G

SHOW GLOBAL STATUS;

XA RECOVER;

/*!50604 select processlist_state,count(*) from performance_schema.threads where processlist_state is not null group by processlist_state*/;

SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS\G

/*50613 SELECT COMMENT, COUNT, IM.* FROM INFORMATION_SCHEMA.INNODB_METRICS IM*/;

/*!50604 select processlist_state,count(*) from performance_schema.threads where processlist_state is not null group by processlist_state*/;

SHOW FULL PROCESSLIST;

-- Same query for 8.0

/*!80001 SELECT r.trx_wait_started AS wait_started,

TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,

TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,

CONCAT(sys.quote_identifier(rl.object_schema), '.', sys.quote_identifier(rl.object_name)) AS locked_table,

rl.object_schema AS locked_table_schema,

rl.object_name AS locked_table_name,

rl.partition_name AS locked_table_partition,

rl.subpartition_name AS locked_table_subpartition,

rl.index_name AS locked_index,

rl.lock_type AS locked_type,

r.trx_id AS waiting_trx_id,

r.trx_started as waiting_trx_started,

TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,

r.trx_rows_locked AS waiting_trx_rows_locked,

r.trx_rows_modified AS waiting_trx_rows_modified,

r.trx_mysql_thread_id AS waiting_pid,

sys.format_statement(r.trx_query) AS waiting_query,

rl.engine_lock_id AS waiting_lock_id,

rl.lock_mode AS waiting_lock_mode,

b.trx_id AS blocking_trx_id,

b.trx_mysql_thread_id AS blocking_pid,

sys.format_statement(b.trx_query) AS blocking_query,

bl.engine_lock_id AS blocking_lock_id,

bl.lock_mode AS blocking_lock_mode,

b.trx_started AS blocking_trx_started,

TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,

b.trx_rows_locked AS blocking_trx_rows_locked,

b.trx_rows_modified AS blocking_trx_rows_modified,

CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,

CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection

FROM performance_schema.data_lock_waits w

INNER JOIN information_schema.innodb_trx b ON b.trx_id = CAST(w.blocking_engine_transaction_id AS CHAR)

INNER JOIN information_schema.innodb_trx r ON r.trx_id = CAST(w.requesting_engine_transaction_id AS CHAR)

INNER JOIN performance_schema.data_locks bl ON bl.engine_lock_id = w.blocking_engine_lock_id

INNER JOIN performance_schema.data_locks rl ON rl.engine_lock_id = w.requesting_engine_lock_id

ORDER BY r.trx_wait_started*/\G

/*!50604 select processlist_state,count(*) from performance_schema.threads where processlist_state is not null group by processlist_state*/;

SHOW FULL PROCESSLIST;

/*!50503 select * from information_schema.innodb_trx order by trx_started*/\G

/*!50503 SELECT * FROM information_schema.INNODB_LOCKS*/\G

/*!80001 SELECT * FROM performance_schema.data_locks*/\G

/*!50708 SELECT * FROM performance_schema.metadata_locks*/\G

-- /*!50503!80001 SELECT * FROM performance_schema.rwlock_instances*/\G

/*!50708 select * from sys.session order by thd_id */\G

/*!50503 select * from performance_schema.threads order by thread_id */\G

STATUS;

SELECT benchmark(50000000,(1234*5678/37485-1298+8596^2)); #should take less than 20 seconds

/*!50503 SELECT * FROM performance_schema.setup_instruments WHERE name LIKE 'wait/sync%' AND (enabled='yes' OR timed='yes')*/; --if you see any results, it means performance overhead is likely.

/*!50503 SHOW ENGINE performance schema STATUS */;

XA RECOVER;

NOTEE;

NOTE: The TEE command will cause the output to be written to the file name given in the directory that the mysql client is called from. NOTEE stops the writing to the file.

** Linux Status

4) The file /tmp/server_output.txt created by running the following commands in the shell *WHILE* the MySQL data is being gathered:

script /tmp/server_output.txt

date

uname -a

uptime

dmidecode -s system-product-name

getenforce

sestatus

cat /proc/sys/vm/swappiness

df -Th

mount

lsblk -i

pvs

ls -lrt /dev/mapper

pvdisplay

vgdisplay

lvdisplay

free -m

vmstat 5 5

mpstat -P ALL 5 5

iostat -dx 5 5

ulimit -a

cat /proc/$(pidof mysqld)/limits

cat /proc/cpuinfo

dmesg | grep -i numa

numactl -H

numastat -czs mysqld

ps auxfww | grep mysql

top -b -n 5 -d 5 -o +%CPU

exit

NOTE: Please be aware that some of these commands may require a privileged user or super user in order to gather the information.