My2Collector (my2) is a simple, self contained MySQL statistics collector
Most intresting MySQL performance data is available in the GLOBAL_STATUS system table,
but MySQL does not mantain any history of it.
My2Collector (my2) is a simple, self contained MySQL statistics collector.
my2 creates the my2.status table that contains the history of performance statistics.
Every 10 minutes my2 automatically executes a Stored Routine to collect data.
To install my2 execute the following command on Your MySQL database:
mysql --user=root -pXXX < my2.sql
For security reasons the user my2 creation is commented out: change the password and create the user! my2 user creation is in the last 3 lines of the script.
my2.status table has the same columns of MySQL GLOBAL_STATUS:
- variable_name
- variable_value
my2.status adds a third column timest with the timestamp
my2.status table collects several performance statistics:
- All numeric GLOBAL_STATUS variables
- All statement execution counters ("statement/sql/%" from events_statements_summary_global_by_event_name)
- Some PROCESSLIST information (eg. USER, HOST, COMMAND, STATE)
- Some summary statistic (eg. sum_timer_wait from events_statements_summary_global_by_event_name)
- Some GLOBAL_VARIABLE variables
- Delta values for most used counters
- Database size (this statistic is collected daily and not every 10 minutes)
- And other useful stats...
SELECT variable_value+0 as value, timest as time_sec
FROM my2.status
WHERE variable_name='THREADS_CONNECTED'
ORDER BY timest ASC;
my2 can connect to any version of MySQL, MariaDB, Percona, or other forks but... with old MySQL releases many statistics not available. my2 uses a Scheduled Job which is available since MySQL 5.1 (2008). PROCESSLIST table is available since 5.1.7 while GLOBAL_STATUS is available since 5.1.12. The PERFORMANCE_SCHEMA was introduced in 5.5 version and greatly enhanched in 5.6 version. There are many little differences between different MySQL versions: My2 is aware of them and tries to collect all the information available. For MySQL 8.0 a different script is provided. my2 gives its best with MySQL 5.7, MySQL 8.0 and MariaDB 10.x with performance schema enabled.