Performance_schema

Table of contents

• Review
• One month later
• Size of the tables
• Process list
• Global variables
• Metadata Lock and InnoDB Transaction Lock
• Global status

Review

After we introduced dbstat for MariaDB (and MySQL) a good 5 weeks ago, we naturally also rolled it out on our systems to test the behaviour in daily use (eat your own dog food).

This went quite well until we came up with the idea of activating dbstat on the passive dbstat node on our MariaDB active/passive master/master replication cluster (a similar …

Table of contents

• Functionality of dbstat
• How does dbstat work
• How to install dbstat
• Query dbstat
  • table_size
  • processlist
  • trx_and_lck
  • metadata_lock
  • global_variables
  • global_status
• Testing
• Sources

An idea that I have been thinking about for a long time and have now, thanks to a customer, finally tackled is dbstat for MariaDB/MySQL. The idea is based on sar/sysstat by Sebastien Godard:

sar - Collect, report, or save system activity information.

and Oracle Statspack:

Statspack is a performance tuning …

The MySQL sys Schema is a cool thing and was mostly developed by Mark Leith and Jesper Wisborg Krogh from Oracle/MySQL. The sys Schema is based on the somehow cryptic MySQL PERFORMANCE_SCHEMA which was developed by Oracle/MySQL as well.

The sys Schema is: “A collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.”

Unfortunately the sys Schema was not ported from MySQL to MariaDB by the company MariaDB Corp. so far. So a good opportunity …

MySQL has introduced the PERFORMANCE_SCHEMA (P_S) in MySQL 5.5 and made it really usable in MySQL 5.6 and added some enhancements in MySQL 5.7 and 8.0.

Unfortunately the PERFORMANCE_SCHEMA was not really intuitive for the broader audience. Thus Mark Leith created the sys Schema for an easier access for the normal DBA and DevOps and Daniel Fischer has enhanced it further. Fortunately the sys Schema up to version 1.5.1 is available on GitHub. So we can adapt and use it for MariaDB as well. The version of the …

The FOSDEM 2016 in Brussels (Belgium) January 29/30 is over and was very interesting and IMHO a big success.

For all those who could not participate at FOSDEM 2016 our presentation slides about PERFORMANCE_SCHEMA and sys schema are available here:

PERFORMANCE_SCHEMA and sys schema - What can we do with it? (PDF, 406 kbyte)

Table of Contents

• Accounts not properly closing connections
• Unused indexes
• Who created temporary (disk) tables
• Accounts which never connected since last start-up
• Users which never connected since last start-up
• Totally unused accounts (never connected since last restart and not used to check Stored Program or View privileges) since last start-up
• SHOW FULL PROCESSLIST
• Storage Engines per schema
• Tables without a Primary Key
• Bad SQL queries of users
• SHOW PROFILE in PERFORMANCE_SCHEMA
• SELECT, INSERT, UPDATE and …