MariaDB and MySQL PERFORMANCE_SCHEMA My Notes

Sometimes memory fails me, here are my notes of valuable queries. As I don’t used them too often I put all of them in one place.

Happy copy+paste, if you know what are you doing and what are you looking for in MySQL/MariaDB :)

Unused indexes

 SELECT DISTINCT s.table_schema, s.table_name, s.index_name
  FROM information_schema.statistics AS s
  LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage AS i
         ON (s.table_schema = i.object_schema AND s.table_name = i.object_name AND s.index_name = i.index_Name)
 WHERE s.table_schema NOT IN ('mysql', 'performance_schema', 'sys', 'information_schema')
   AND s.index_name != 'PRIMARY'
   AND i.count_star = 0
 ORDER BY s.table_schema, s.table_name, s.index_name
;

In newest versions

SELECT * FROM sys.schema_unused_indexes;

Temporary tables on disk

SELECT user, host, event_name, count_star AS cnt
     , sum_created_tmp_disk_tables AS tmp_disk_tables, sum_created_tmp_tables AS tmp_tables
  FROM performance_schema.events_statements_summary_by_account_by_event_name
 WHERE sum_created_tmp_disk_tables > 0
    OR sum_created_tmp_tables > 0
;

SELECT schema_name, substr(digest_text, 1, 40) AS statement, count_star AS cnt
     , sum_created_tmp_disk_tables AS tmp_disk_tables, sum_created_tmp_tables AS tmp_tables
  FROM performance_schema.events_statements_summary_by_digest
 WHERE sum_created_tmp_disk_tables > 0
    OR sum_created_tmp_tables > 0
;

SHOW FULL PROCESSLIST

SELECT PROCESSLIST_ID AS id, PROCESSLIST_USER AS user, PROCESSLIST_HOST AS host, PROCESSLIST_DB AS db
     , PROCESSLIST_COMMAND AS command, PROCESSLIST_TIME AS time, PROCESSLIST_STATE AS state
     , LEFT(PROCESSLIST_INFO, 80) AS info
  FROM performance_schema.threads
 WHERE PROCESSLIST_ID IS NOT NULL
   AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump')
 ORDER BY PROCESSLIST_TIME ASC
;

Tables without a Primary Key

SELECT DISTINCT t.table_schema, t.table_name
  FROM information_schema.tables AS t
  LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema AND t.table_name = c.table_name
        AND c.column_key = "PRI"
 WHERE t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
   AND c.table_name IS NULL AND t.table_type NOT IN('VIEW', 'SEQUENCE')
;

Per table SELECT, INSERT, UPDATE, DELETE

SELECT object_type, object_schema, object_name
     , count_star, count_read, count_write, count_fetch
     , count_insert, count_update, count_delete
  FROM performance_schema.table_io_waits_summary_by_table
 WHERE count_star > 0
 ORDER BY count_star DESC
;

SELECT object_type, object_schema, object_name, index_name
     , count_star, count_read, count_write, count_fetch
     , count_insert, count_update, count_delete
  FROM performance_schema.table_io_waits_summary_by_index_usage
 WHERE count_star > 0
   AND index_name IS NOT NULL
 ORDER BY count_star DESC
;

Empty tables, without any row

SELECT t.table_schema, t.table_name, t.table_rows, tio.count_read, tio.count_write
  FROM information_schema.tables AS t
  JOIN performance_schema.table_io_waits_summary_by_table AS tio
    ON tio.object_schema = t.table_schema AND tio.object_name = t.table_name
 WHERE t.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
   AND tio.count_write = 0
 ORDER BY t.table_schema, t.table_name
;

Niech SELECT będzie z wami.

Tomasz Gintowt jest Architektem/DevOps/DBA/Trenerem, głównie skupiony na dostarczaniu rozwiązań składowania i przetwarzania danych. Nie są mu obce wszelkiej maści bazy danych, systemy real-time data i streamingu. Obecnie pracuje z Apache Kafka, RabbitMQ, Elastic Stack ,MySQL, PostgreSQL. Organizator spotkań DataOps Poland.

https://www.linkedin.com/in/tomasz-gintowt/

https://dataops-academy.pl — kursy i szkolenia.