|
When MaxScale retrieves the database users from the backend, it calculates a SHA1 checksum for the data. This data is called the userdata and it is used to identify if the database users have changed.
Currently this is done locally inside MaxScale and provides very little benefit because the query will always be executed. An improvement to this would be to use a stored function to calculate the SHA1 checksum for the data and only query for the hash. This would reduce the amount of data that would need to be sent over the network and it would also remove a large part of the processing required on MaxScale's side.
A crude example of a function which could do it:
delimiter //
|
create function fn() returns text reads sql data
|
begin
|
DECLARE done INT DEFAULT FALSE;
|
DECLARE str VARCHAR (120);
|
DECLARE curs CURSOR FOR (SELECT DISTINCT
|
concat(user.user,user.host,user.password,
|
IF((user.Select_priv+0)||find_in_set('Select',Coalesce(tp.Table_priv,0)),'Y','N') ,
|
COALESCE( db.db,tp.db, '')) AS userdata
|
FROM
|
mysql.user LEFT JOIN
|
mysql.db ON user.user=db.user AND user.host=db.host LEFT JOIN
|
mysql.tables_priv tp ON user.user=tp.user AND user.host=tp.host
|
WHERE user.user IS NOT NULL AND user.user <> '');
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
|
|
OPEN curs;
|
|
SET @rval = "";
|
rd_loop: LOOP
|
FETCH curs INTO str;
|
IF done THEN
|
LEAVE rd_loop;
|
END IF;
|
SET @rval = CONCAT(@rval, str);
|
END LOOP;
|
|
CLOSE curs;
|
|
RETURN sha1(@rval);
|
end; //
|
delimiter ;
|
|