meob / my2collector

My2Collector (my2) is a simple, self contained MySQL statistics collector

my2Collector

My2Collector (my2) is a simple, self contained MySQL statistics collector

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.

Install my2

To install my2Collector execute the following command on Your MySQL database:

mysql --user=root -pXXX < my2.sql

For security reasons the creation of the user my2 is commented out: change the password and create the user!

my2 user creation is in the last 3 lines of the script.

Database structure

my2.status table has columns similar to the MySQL GLOBAL_STATUS system table:

• variable_name
• variable_value

my2.status adds a third column timest with the timestamp

Available statistics

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 using my2.current stage table
• Database size (collected daily and not every 10 minutes)
• And other useful stats...

Statistics usage

SELECT variable_value+0 as value, timest as time_sec
  FROM my2.status
 WHERE variable_name='THREADS_CONNECTED'
 ORDER BY timest ASC;

Version support

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.

my2collector's Issues

error updating options query error 400 bad request

Hi,

After updated a new revision of the dashboard but I received an error "error updating options query error 400 bad request.

Grafana version 8.1.2

Thanks

my2 import of my2.sql

Hi,

im kind of new to this but im trying to import the my2.sql file and gets error in the import. Tryed with the flat text and with the sql file.

DECLARE a datetime;
DECLARE v varchar(10);

is the part is stops regard the import.

Error from phpmyadmin, and i have tryed heidisql.

1064 - You have something wrong with your syntax close ';

CREATE PROCEDURE collect_stats ()
BEGIN
DECLARE and datetime;
DECLARE v varch 'in row 1

//Stefan

A lot of [Err] 1055

I'm using MySQL 5.7.24 in a percola cluster of 3 master-master.
When I run the script my2.sql I do have a lot of error 1055 like this one :
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Do you have any idea why?

error #1576 - Recursion of EVENT DDL statements is forbidden when body is present

hi. I´ve mysql 5.5, and i´ve this error when run my2.sql, after CREATE PROCEDURE collect_daily_stats().
problem with events into procedures in mysql version < 5.7 ?

Some dashboard not shown after upgrade Grafana ver 8

Hi, I have an issue with the dashboard when upgrading grafana version 8. the issue for Heatmap and Custom Statistic dashboard

Is there any update for the script or this method can not work for grafana version 8.

Thanks

my2 stops collecting after server restart....

It looks like my2 is no longer getting infromation anymore after a restart off the machine.
RHEL updates had caused a reboot of the machine and since then there is no new my2 data

Events and Procedures are not found after upgrade application

Hi,

I upgraded the application on my VM and suddenly the DB monitor doesn't work, I checked the events, and procedures are not found in my2 DB. Can you help me with how to fix it?

Thank you

How to get real-time data on graphs.

Hi, how to get real-time data on graphs. If now at 9 am, it has a gap time of about 6 hours.

[Question] Increase logging frequency

Looks awesome so far!

Only issue for me is that you can't really see the "current" state of the database. So for example if you have lots of new connections you might only see these after 10 minutes in the dashboard.

You think there are any issues with changing the execution time to something lower (like a minute or even every 10 seconds)?
Of course it would consume more storage, but besides that?

MySQL 8.0 full support

A new specific script for MySQL 8.0 has been added.

Previously some lines have to be commented out to have it working.

Can't run the my2.sql

The error is:
#1064 - A Szintaktikai hiba a ';
CREATE PROCEDURE collect_stats()
BEGIN
DECLARE a datetime;
DECLARE v va...'-hez kozeli a 1 sorban

I had run this through phpmyadmin's SQL interface.

The server is: 10.5.5-MariaDB

Can't work with Mysql 8.0

Tables GLOBAL_STATUS and GLOBAL_VARIABLES no support in this version 8.0

How to set enabled events without phpmyadmin

Hi, how to set enabled status on events without PHPMyAdmin

Thank you

Table 'performance_schema.global_status' doesn't exist

after importing my2 dashboard on grafana i get this err message on page load and nothing works.
I imported my2.sql with no issue. and also created a new datasource for my2 database with my2 user.

my mysql info: mysql Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

automatic creation does not work for me - 8.0

When I just run the import of my2.sql I get GLOBAL_STATUS does not exist on line 37; when I manually remove both else statements completely, all works fine.

No value on custom statistic

Hi Meob,

I have a question about the custom statistic graph, before the graph was shown the value but now is 0. is it normal?

Thanks

ERROR 1061 (42000) at line 26: Duplicate key name 'idx01'

Hi,

Thanks for building this wonderful dashboard content.
While executing below script, I am seeing below error. Can you check this again?
ERROR 1061 (42000) at line 26: Duplicate key name 'idx01'

Thanks,
Raghavendra Guttur

Need script for mariadb ver 5.5

Hi, Would you share a SQL script for MariaDB version 5.5 cause that version did not has tables performance_schema.global_status, performance_schema.events_statements_summary_global_by_event_name, and performance_schema.replication_connection_status.

Thank you