[MDEV-15642] Add new GLOBAL STATUS for MariaDB 10.4 Created: 2018-03-23  Updated: 2018-12-29

Status: Open
Project: MariaDB Server
Component/s: Server
Fix Version/s: None

Type: Task Priority: Major
Reporter: Aurélien LEQUOY Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

The goal is to increase efficiently of tool's monitoring and get 

SHOW /*!40003 GLOBAL*/ VARIABLES;

only when we need it.

The goal is to add a new GLOBAL STATUS with name : Com_set_global_variables (who only affected when we update this, for all server like with SET GLOBAL XXXXX=YY;

I know there is Com_set_option, but when we do 

SET NAMES utf8;

it's update to +1 to Com_set_option and I don't want this because most of ORM and Apps made this. (like AUTOCOMMIT)

 Comments   
Comment by Aurélien LEQUOY [ 2018-08-10 ]

any news ?

the goal is to ask Variables only when we need it and not each time and compare we previous request.

This is ask for new feature

Comment by Sergei Golubchik [ 2018-08-10 ]

You mean, you will do SHOW STATUS LIKE 'Com_set_global_variables' and when it changes you do SHOW GLOBAL VARIABLES?

Technically, you can get a similar functionality without any server changes by comparing checksum of all global variables. You can generate the checksum with, for example, SELECT MD5(GROUP_CONCAT(VARIABLE_VALUE)) FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES;

The complete solution might look like (untested):

DECLARE new_com_set_option INT;
 
SELECT variable_value INTO new_com_set_option FROM information_schema.global_status WHERE variable_name='com_set_option';
 
IF @com_set_option = new_com_set_option IS FALSE
 
THEN
 
  SET @com_set_option=new_com_set_option;
 
  DECLARE new_checksum VARCHAR(64);
 
  SELECT MD5(GROUP_CONCAT(variable_value)) INTO new_checksum FROM information_schema.global_variables;
 
  IF @sysvar_checksum = new_checksum IS FALSE
 
  THEN
 
    SET @sysvar_checksum = new_checksum;
 
    SHOW GLOBAL STATUS;
 
  END IF;
 
END IF;

Comment by Aurélien LEQUOY [ 2018-09-17 ]

i rewrited as :

DROP TABLE IF EXISTS `checksum`;
 
create table `checksum`(md5 char(32));
 
 
 
insert into checksum values('xfgb');
 
 
 
DELIMITER //
 
DROP PROCEDURE IF EXISTS get_variables //
 
CREATE PROCEDURE get_variables()
 
BEGIN
 
DECLARE old_checksum CHAR(32) DEFAULT '';
 
DECLARE new_checksum CHAR(32) DEFAULT '';
 
 
 
SELECT md5 INTO old_checksum FROM `checksum`;
 
SELECT MD5(GROUP_CONCAT(variable_value)) INTO new_checksum FROM information_schema.global_variables WHERE Variable_name NOT IN ('gtid_binlog_pos','gtid_binlog_state','gtid_current_pos', 'gtid_slave_pos');
 
 
 
IF new_checksum = old_checksum IS FALSE
 
THEN
 
  UPDATE `checksum` SET `md5`=@new_checksum;
 
  SHOW GLOBAL STATUS;
 
END IF;
 
 
 
END
 
//
 
DELIMITER ;
 
 
 
call get_variables();

Generated at Thu Feb 08 08:22:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.