Configurer et utiliser ProxySQL

Le contexte

Chez Bazarchic, nous utilisons depuis de nombreuses années, la solution master/slaves pour la gestion des bases de données MYSQL, que ce soit coté site, ou coté back-office. Historiquement, c’est l’application qui gère la répartition lecture/écriture des données sur les différentes machines de base de données.  Cependant, cette répartition n’est pas optimum, le master récupérant au final beaucoup de SELECT. Certains doivent rester absolument sur le master (la réplication étant asynchrone) , d’autres par contre, du fait de données non critiques peuvent très bien s’exécuter sur des slaves.
Nous regardons donc depuis pas mal de temps, les répartiteurs de charge pour MYSQL pour soulager l’application de cette tâche.

Nous avons utilisé Nginx recompilé en TCP, uniquement pour faire du failover, mais pas de répartition de charge à proprement parler. De même, Maxscale ne nous a pas donné entière satisfaction, sa mise en oeuvre a été compliquée de notre coté.

Nous avons découvert ProxySQL, qui lui correspond bien à cette problématique de répartition des lectures/écritures sur un pool de master/slaves. La simplicité de mise en oeuvre est aussi évidente, par rapport à d’autres produits sur le marché.

 

Configurer ProxySQL en R/W splitting

Je ne vais pas détailler ici le fonctionnement ni l’installation de ProxySQL, vous pouvez vous rendre sur le site officiel pour cela. Je me suis grandement inspiré du tuto officiel dispo ici.

Considérons que nous avons donc 1 master, ainsi que 2 slaves. Il y a plusieurs notions importantes sur la configuration. Il y a globalement 4 notions différentes

Les hostgroups (regroupement de serveurs)
Les serveurs
Les users
Les régles qui aiguillent les requetes SQL.

Configuration des serveurs

Tout d’abord, on considère que :
– le hostgroup 1 accueille les écritures, (donc un ou plusieurs master)
– le hostgroup 1 accueille les lectures, (donc un ou plusieurs slaves)

On insére en sql donc nos serveurs dans la table adéquate

insert into mysql_servers (hostgroup_id,hostname,max_latency_ms) values (1,'master',200);
insert into mysql_servers (hostgroup_id,hostname,max_latency_ms) values (2,'slave1',200);
insert into mysql_servers (hostgroup_id,hostname,max_latency_ms) values (2,'slave2',200);

update mysql_servers set max_replication_lag=1 where hostname='slave2';
update mysql_servers set max_replication_lag=10 where hostname='slave1';

On définir alors le temps maximum qu’un slave peut avoir comme retard (en secondes) avant de l’exclure du pool.

On commit nos modifications, sur le fichier de conf et on applique en même temps la configuration au service. (pour cela se rapporter à la doc de ProxySQL)

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

 

Configuration des USERS

Comme tout proxy sql qui se respecte, ProxySql monitore le temps de réplication des différents slaves. Si un slave prend trop de retard, il sera exclu temporairement des hostgroup, et il n’y aura pas d’impact sur votre application. Il est préférable de créer un user spécifique (pas le root on vous dit) sur les serveurs mysql avec les droits de réplication.

 

CREATE USER 'monitor-proxy-sql'@'ip-de-la-machine-proxy-sql' IDENTIFIED BY 'votre-mot-de-passe-compliqué' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
GRANT REPLICATION SLAVE ON *.* TO 'monitor-proxy-sql'@'ip-de-la-machine-proxy-sql';




Maintenant, il faut déclarer ce user au niveau du ProxySql. Il y a une table qui contient toutes les variables

select * from global_variables;

+----------------------------------------+-----------------------------------------+
| variable_name | variable_value |
+----------------------------------------+-----------------------------------------+
| mysql-shun_on_failures | 5 |
| mysql-shun_recovery_time_sec | 10 |
| mysql-query_retries_on_failure | 1 |
| mysql-connect_retries_delay | 1 |
| mysql-connection_max_age_ms | 0 |
| mysql-connect_timeout_server_max | 10000 |
| mysql-eventslog_filename | |
| mysql-eventslog_filesize | 104857600 |
| mysql-default_charset | utf8 |
| mysql-free_connections_pct | 10 |
| mysql-session_idle_ms | 1000 |
| mysql-client_found_rows | true |
| mysql-monitor_enabled | true |
| mysql-monitor_connect_timeout | 200 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_username | monitor-proxy-sql |
| mysql-monitor_password | toto |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_writer_is_also_reader | true |
| mysql-max_allowed_packet | 4194304 |
| mysql-max_transaction_time | 14400000 |
| mysql-multiplexing | true |
| mysql-enforce_autocommit_on_reads | false |
| mysql-threshold_query_length | 524288 |
| mysql-threshold_resultset_size | 4194304 |
| mysql-query_digests_max_digest_length | 2048 |
| mysql-query_digests_max_query_length | 65000 |
| mysql-wait_timeout | 28800000 |
| mysql-max_stmts_per_connection | 20 |
| mysql-max_stmts_cache | 10000 |
| mysql-default_max_latency_ms | 1000 |
| mysql-query_processor_iterations | 0 |
| mysql-long_query_time | 1000 |
| mysql-query_cache_size_MB | 256 |
| mysql-ping_interval_server_msec | 10000 |
| mysql-poll_timeout_on_failure | 100 |
| mysql-server_capabilities | 45578 |
| mysql-session_idle_show_processlist | false |
| mysql-query_digests | true |
| mysql-query_digests_lowercase | false |
| mysql-servers_stats | true |
| mysql-default_reconnect | true |
| mysql-ssl_p2s_ca | (null) |
| mysql-ssl_p2s_cert | (null) |
| mysql-ssl_p2s_key | (null) |
| mysql-ssl_p2s_cipher | (null) |
| mysql-init_connect | (null) |
| mysql-default_sql_mode | |
| mysql-default_time_zone | SYSTEM |
| admin-stats_credentials | stats:stats |
| admin-telnet_admin_ifaces | (null) |
| admin-telnet_stats_ifaces | (null) |
| admin-refresh_interval | 2000 |
| admin-read_only | false |
| admin-hash_passwords | true |
| admin-version | 1.3.6-0-g434b376 |
| admin-admin_credentials | admin:admin |
| admin-mysql_ifaces | 127.0.0.1:6032;/tmp/proxysql_admin.sock |
| mysql-threads | 4 |
| mysql-max_connections | 2048 |
| mysql-default_query_delay | 0 |
| mysql-default_query_timeout | 36000000 |
| mysql-have_compress | true |
| mysql-poll_timeout | 2000 |
| mysql-interfaces | 0.0.0.0:3306;/tmp/proxysql.sock |
| mysql-default_schema | information_schema |
| mysql-stacksize | 1048576 |
| mysql-server_version | 5.5.30 |
| mysql-connect_timeout_server | 3000 |
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_read_only_interval | 1500 |
| mysql-monitor_read_only_timeout | 500 |
| mysql-ping_timeout_server | 500 |
| mysql-commands_stats | true |
| mysql-sessions_sort | true |
| mysql-connect_retries_on_failure | 10 |
| mysql-session_debug | (null) |
+----------------------------------------+-----------------------------------------+
84 rows in set (0.01 sec)

Admin>

Il faut donc mettre à jour les valeurs mysql-monitor_username et mysql-monitor_password.

UPDATE global_variables SET variable_value='monitor-proxy-sql' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='votre-mot-de-passe-complique' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

On va vérifier maintenant, que le monitoring fonctionne sans soucis, toujours sur l’interface d’administration de ProxySQL

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

Configuration du suivi de la réplication

On associe maintenant aux différents hostgroup, quels roles ils ont.

INSERT INTO mysql_replication_hostgroups VALUES (1,2,'replication');

Cela va permettre au monitor, de s’assurer de façon régulière que les slaves sont à l’heure par rapport au master. L’intervalle de mise à jour, est configurable avec les variables globales de proxysql (Pour avoir le listing c’est ici).
Toujours dans la database monitor, on regarde que le check fonctionne correctement.

select * from mysql_server_replication_lag_log ;
+---------------+------+------------------+-----------------+----------+-------+
| hostname | port | time_start_us | success_time_us | repl_lag | error |
+---------------+------+------------------+-----------------+----------+-------+
| slave1 | 3306 | 1495115294834290 | 460 | 0 | NULL |
+---------------+------+------------------+-----------------+----------+-------+

Si on « s’amuse à faire un stop slave », on a le résultat suivant

+---------------+------+------------------+-----------------+----------+-------+
| hostname | port | time_start_us | success_time_us | repl_lag | error |
+---------------+------+------------------+-----------------+----------+-------+
| slave1 | 3306 | 1495115294834290 | 460 | 0 | NULL |
| slave1 | 3306 | 1495115304834375 | 466 | 0 | NULL |
| slave1 | 3306 | 1495115314834422 | 458 | 0 | NULL |
| slave1 | 3306 | 1495115324834424 | 467 | 0 | NULL |
| slave1 | 3306 | 1495115334834581 | 548 | 60 | NULL |
+---------------+------+------------------+-----------------+----------+-------+

Un petit select sur la table runtime des serveurs, nous permet de voir que le slave1 n’est plus en prod

select * from runtime_mysql_servers;
+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | master | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 200 | |
| 2 | slave1 | 3306 | SHUNNED | 1 | 0 | 1000 | 1 | 0 | 200 | |
+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

Configuration des règles de routage

Maintenant, on peut passer directement aux règles de routage des requêtes.Pour cela il existe la table mysql_query_rules.
Il peut avoir différentes approchent concernant l’aiguillages des requêtes en lecture/écriture.

La première, qui est simple à mettre en oeuvre au niveau de ProxySQL, mais qui peut avoir des impacts non négligeables coté appli, avec des effets de bord notable, si vous n’êtes pas en réplication synchrone.

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,active)
VALUES
(1,1,'^SELECT.*FOR UPDATE$',1,1),
(2,1,'^SELECT',2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Ici, c’est simple, tous les SELECT arrivent sur le hostgroup2, les SELECT FOR UPDATE sur le hostgroup1, et le reste sur le hostgroup1. L’inconvénient majeure sur cette approche, concerne la réplication asynchrone. Si vous insérer une donnée, et que vous la relisez tout de suite après, il se peut que la donnée ne soit pas présente sur le slave. Il faut soit gérer l’erreur au niveau de l’application et réessayer sur le master, soit avoir une réplication semi-synchrone ou syncrhone, avec les inconvénients que cela implique.

L’autre méthode pour scinder les lectures / écritures, est d’indiquer quels sont les requetes à transferer sur tel ou tel hostgroup. Pour cela, ProxySql aggrège les requêtes dans la table suivante

stats_mysql_query_digest

Il suffit ensuite de faire une requete sur le TOP 5 des select

SELECT digest,SUBSTR(digest_text,0,50),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 5;

On obtient ensuite un Hash de la requête, qu’il suffit ensuite de transférer sur le hostgroup qui nous intéresse, ici le hostgroup 2 des slaves.

INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,active)
VALUES
(1,1,'0x38BE36BDFFDBE638',2,1);

De cette manière, les effets de bord sont nettement plus limités dans votre application, et vous avez la main sur l’aiguillage des requêtes. Ce travail est certes plus fastidieux, mais il est nécessaire.

Conclusion

Au final, l’appréhension de cet outil, a duré environ 2 journées,  uniquement sur les aspects lectures/écritures de ProxySQL. Les tests en environnement de pré-production ont pris environ 1 journée, en jouant un peu avec les serveurs sql et benchmarké les performances.
Sa mise en production a été faite en une matinée.

Il existe d’autres fonctionnalités lié à ProxySQL, comme la mise en cache des requêtes, la réécriture des requêtes qui seront abordés dans un autre article.

Il faut aussi prendre en considération que la machine qui accueillera ce proxy, devra être dimensionnée de façon adéquate, et dépendra fortement du nombre de requêtes de votre application. De même, en rajoutant une étape supplémentaire, il faudra s’assurer de la qualité du réseau entre votre application et vos serveurs base de données.

Laisser un commentaire