Archives du mot-clé MySQL

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.

Gitlab (Sources) To Gitlab-Omnibus

Migration

 

Cet article permet d’envisager la migration des données d’un Gitlab installé avec les sources sur une instance de Gitlab avec Omnibus.
Dans notre cas de figure, nous avions une instance Gitlab avec MySQL, et nous souhaitions migrer toutes nos données sur Omnibus sachant que le type de base de données cible était PostgreSQL.

Nous estimons que l’installation du nouveau Gitlab au moins avec les sources a été fait : https://about.gitlab.com/downloads/#debian8

Sur l’ANCIEN serveur gitlab

Arrêt du service Gitlab

service gitlab stop

Sauvegarde de l’environnement et création d’un répertoire temporaire

cd /home/git/gitlab
sudo -u git -H bundle exec rake gitlab:backup:create RAILS_ENV=production
mkdir /tmp/backups/postgresql/
mv /home/git/gitlab/tmp/backups/<BACKUP>.tar /tmp/backups/postgresql/

Dump de la base  de données en la rendant compatible avec PostgreSQL

sudo -u git -H mysqldump --compatible=postgresql --default-character-set=utf8 -r gitlabhq_production.mysql -u <user> gitlabhq_production -p<password>

Récupération du repository git pour la conversion du dump

sudo -u git -H git clone https://github.com/gitlabhq/mysql-postgresql-converter.git -b gitlab

Conversion du dump et modification des indexes

mkdir db
sudo -u git -H python mysql-postgresql-converter/db_converter.py gitlabhq_production.mysql db/database.sql
sudo -u git -H ed -s db/database.sql < mysql-postgresql-converter/move_drop_indexes.ed

A ce stade, il faut vérifier si le fichier databases.sql contient en début de ligne au moins les lignes suivantes :

-- Converted by db_converter
START TRANSACTION;
SET escape_string_warning= 'off';
SET standard_conforming_strings = 'off';
SET backslash_quote = 'on';
SET CONSTRAINTS ALL DEFERRED;

Ainsi que les lignes contenant les DROP sont de cette forme là :

[...]
DROP INDEX IF EXISTS "index_audit_events_on_author_id";
[...]

Extraction et réinsertion du database.sql dans le fichier de sauvegarde

cd /tmp/backups/postgresql/ && tar -xf <timestamp>_gitlab_backup.tar
rm -f db/database.sql.gz
cd /tmp/backups/postgresql/db/ && gzip -c database.sql > database.sql.gz

Reconstruction de l’archive tar

sudo -u git -H tar rf <NEWBACKUP>_gitlab_backup.tar artifacts.tar.gz backup_information.yml builds.tar.gz db lfs.tar.gz repositories uploads.tar.gz

Si les deux instances sont sur deux serveurs différents, procédez à son transfert.

Sur le NOUVEAU serveur gitlab

Insertion du backup dans le nouveau gitlab

cd /var/opt/gitlab/backups/ && sudo gitlab-rake gitlab:backup:restore BACKUP=<timestamp>

Reconfiguration et redémarrage des services

sudo gitlab-ctl reconfigure
sudo gitlab-ctl restart
sudo gitlab-runner restart

Optionnel : Gitlab-runner

L’enregistrement doit être fait avec gitlab-runner pour fonctionner.

gitlab-ci-multi-runner register

Une URL et un Token sera demande, ils sont disponible sur l’interface du nouveau Gitlab dans /admin/runners

Redémarrage du service Gitlab-runner

service gitlab-runner restart

A ce stade, tout a été réimporté, les utilisateurs, la base de données, les projets, les répository…

Nicolas Blattmann