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.

Parsing Mysql Slow Query into Logstash / Kibana

Les « slow-query » logs sont des données importantes dans le but d’optimiser les applications et de diagnostiquer les requêtes dites « lentes ».
Ces fichiers sont présents sur chaque serveur SQL à condition de les avoir activées et lisibles avec n’importe quel éditeur mais il n’est pas nécessaire de rappeler que les données brutes sont difficilement exploitables sauf si nous faisons du cas par cas.

Pour répondre à cette problématique, qui est de pouvoir les lire de façon simple, en agrégeant les requêtes les plus lentes, en pouvant les trier et en les rassemblant en un endroit unique, nous avons procédé à deux méthodes que nous allons détailler ici.

La première consiste à parser directement le flow des requêtes et de les afficher dans Logstash. Quant à la seconde, il s’agit d’abord d’utiliser « pt-query-digest » contenu dans le Percona Toolkit puis de les parser et de les afficher dans Logstash

Mysql Slow Query Log into logstash

La première étape consiste à récupérer les logs via Rsyslog et Syslog-ng, sur le serveur qui contient l’ensemble des logs de nos serveurs. Cette étape a été expliquée dans les articles suivants http://bazarchic.io/?p=16 et http://bazarchic.io/?p=31.

Voici un exemple de requête lente dans son intégralité :

# Time: 161122 16:45:24
# User@Host: user[user] @ [1.2.3.4] Id: 437554863
# Query_time: 0.000546 Lock_time: 0.000141 Rows_sent: 3 Rows_examined: 18
use my_database;
SET timestamp=1479829524;
SELECT *
 FROM langues
 LEFT JOIN pays_langues ON (pays_langues.idlangue = langues.idlangue)
 LEFT JOIN pays_iso ON (pays_iso.idpaysiso = pays_langues.idpays)
 LEFT JOIN categories_exclusion ON (pays_iso.idpaysiso = categories_exclusion.idpaysiso AND categories_exclusion.idcat = '875601')
 WHERE
 pays_iso.status = "on"
 AND categories_exclusion.idpaysiso IS NULL;

Nous partons du fait que les logs sont déjà récupérés depuis les serveurs concernés.
L’étape suivante consiste en la modification de la configuration de Logstash.
Comme à notre habitude, il faut définir une nouvelle section file dans le block input permettant de savoir quels logs nous allons traiter.

# Server001 MySQL Slow LOG
  file {
    path => [ "/srv/syslog/external/server001/*/*/slowlog.log" ]
    type => "server001-slowlog"
    codec => multiline {
        pattern => "^# User@Host:"
        negate => true
        what => "previous"
    }
  }

Ici les logs à traiter sont dans /srv/syslog/external/server001/*/*/slowlog.log, les étoiles correspondent à tous les répertoires entre server001 et le fichier de log.
Nous lui appliquons un type server001-slowlog qui nous permettra de les filtrer.
Nous définissons le format Logstash de log en prenant en compte que les logs à traiter sont redondant et sur plusieurs lignes avec multiline. La partie pattern spécifie l’expression regulière à partir de laquelle le log se répète, quand negate est fixé à true une ligne qui n’est pas matchée avec l’expression régulière est déclassée et non matchée avec le reste des logs. Quant à what, il peut-être previous ou next et indique la relation aux lignes multiples.

La seconde partie à modifier dans le fichier de configuration logstash est la partie filter ou nous allons définir une nouvelle condition en fonction du type.

if [type] == "server001-slowlog" {
  if [message] =~ "^# Time:.*$" {
    drop {}
  }

  grok {
    match => [
    "message", "^# User@Host: %{GREEDYDATA:user}\[%{GREEDYDATA}\] @ %{GREEDYDATA:client_ip} Id: %{GREEDYDATA:id}\s*# Query_time: %{NUMBER:query_time:float} Lock_time: %{NUMBER:query_lock_time:float} Rows_sent: %{NUMBER:query_rows_sent:int} Rows_examined: %{NUMBER:query_rows_examined:int}\s*(?m)SET timestamp=%{NUMBER:timestamp};%{GREEDYDATA:mysql_query}"
    ]
  }

  date {
    match => ["log_timestamp","UNIX"]
  }

  mutate {
    remove_field => "log_timestamp"
    remove_field => "message"
  }
}

La première sous condition permet d’écarter les lignes commençant par # Time:.
La partie grok permet de décomposer le logs sous forme de grande expression regulière avec des patterns pré configurés de Logstash, comme par exemple %{GREEDYDATA:client_ip}, où le pattern GREEDYDATA s’il est matché sera conservé dans le champs client_ip. et ainsi de suite.
La partie date permet de matcher la date avec le format UNIX.
La partie mutate ici permet de reformater les patterns matchés et de les supprimer, de les renommer etc… Dans notre cas de figure, nous les supprimons car la partie message serait redondante étant donné qu’il s’agit du logs brut.
Il est possible de prendre tel quel tout le pattern car il correspond à une slow-query-log de MySQL.

La configuration de logstash est suffisante comme ceci, sa prise en compte nécessite un redémarrage de Logstash.

Dans Elasticsearch il est possible maintenant de consulter les logs avec le parsing adapté.

elasticsearch_logstash

A partir de là, il ne reste plus qu’à construire le dashboard Kibana

MySQL Slow Query Into Logstash with pt-query-digest

Le principe reste le même mais le procédé est différent dans le traitement post réception depuis les serveurs concernés.
En effet, une fois les logs reçus, nous les traiterons avec l’outil de Percona : pt-query-digest. Il est nécessaire de le télécharger et de l’installer à partir des sources officielles : https://www.percona.com/software/database-tools/percona-toolkit

Nous partons également du fait que les fichiers de slow-query logs sont déjà présents sur le serveur de logs.

Voici un exemple de log dans son intégralité :

# Query 1: 2.50 QPS, 0.08x concurrency, ID 0x248E2EADBDB8070F at byte 3489226319
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2016-12-01 00:00:07 to 2016-12-07 11:03:45
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 44 1393180
# Exec time 20 46392s 326us 228ms 33ms 56ms 13ms 30ms
# Lock time 22 147s 54us 5ms 105us 167us 40us 93us
# Rows sent 0 2.25M 0 15 1.69 2.90 1.00 0.99
# Rows examine 0 22.15M 8 94 16.67 27.38 5.03 15.25
# Query size 23 738.52M 553 556 555.85 537.02 0 537.02
# String:
# Databases my_database
# Hosts 1.2.3.4 (1393179/99%), localhost (1/0%)
# Users user (1393179/99%), root (1/0%)
# Query_time distribution
# 1us
# 10us
# 100us #
# 1ms #
# 10ms ################################################################
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `my_database` LIKE 'dictionnaires_langues'\G
# SHOW CREATE TABLE `my_database`.`dictionnaires_langues`\G
# SHOW TABLE STATUS FROM `my_database` LIKE 'dictionnaires'\G
# SHOW CREATE TABLE `my_database`.`dictionnaires`\G
# SHOW TABLE STATUS FROM `my_database` LIKE 'caracteristiques'\G
# SHOW CREATE TABLE `my_database`.`caracteristiques`\G
# SHOW TABLE STATUS FROM `my_database` LIKE 'produits_group_caracteristiques'\G
# SHOW CREATE TABLE `my_database`.`produits_group_caracteristiques`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT *
 FROM dictionnaires_langues dl
 LEFT JOIN dictionnaires d ON dl.iddictionnaire = d.iddictionnaire
 LEFT JOIN caracteristiques c ON c.iddictionnaire_cle = d.iddictionnaire OR c.iddictionnaire_valeur = d.iddictionnaire
 LEFT JOIN produits_group_caracteristiques pgc ON c.idcaracteristique = pgc.idcaracteristique
 WHERE pgc.idproduit_group = '825316' AND dl.idlangue = '1' AND c.iddictionnaire_cle = '7' AND d.cle = "non"
 ORDER BY c.iddictionnaire_cle ASC, d.cle ASC, pgc.position ASC\G

Nous allons d’abord procéder au parsing des logs avec la commande suivante par exemple

pt-query-digest slowlog.log > parsed_log.log

Le parsing prend plus ou moins de temps en fonction du poids du fichier de log.

/srv/syslog/external/server001/2016/12/slowlog.log: 3% 14:28 remain
/srv/syslog/external/server001/2016/12/slowlog.log: 6% 14:19 remain
/srv/syslog/external/server001/2016/12/slowlog.log: 10% 12:30 remain
[ ... ]
/srv/syslog/external/server001/2016/12/slowlog.log: 91% 00:57 remain
/srv/syslog/external/server001/2016/12/slowlog.log: 96% 00:21 remain

Une fois le fichier formaté, les première lignes ne nous intéressent pas dans le flow que nous voulons traiter.
Pour cela, nous coupons le fichier et conservons uniquement ce qui nous intéresse avec la commande suivante, autrement dit le détail des requêtes.

sed -n '/# Query 1/,$p' parsed_log > server001_slow.log

Nous nous retrouvons ainsi avec un fichier qui correspond à ce que nous voulons.

Libre à vous d’automatiser en cron par exemple ces 2 étapes pour reproduire le principe du flow de la première partie.

A partir de ce moment là, nous reproduisons la configuration du fichier Logstash en prenant en compte le nouveau fichier.

# Server001 MySQL Slow LOG via pt-query-digest
  file {
    path => [ "/srv/syslog/external/server001/*/*/server001_slow.log" ]
    type => "server001-slowlog-pt"
    codec => multiline {
        pattern => "^# Query[ ]{1}[1-9]{1,2}"
        negate => true
        what => "previous"
    }
  }

On remarque que le pattern qui détermine la redondance change car il ne s’agit pas du même type de log.

Voici le block filter qui correspond au nouveau format de log :

if [type] == "bazarchic220-slowlog-pt" {
  grok {
  match => [
    "message", "^# Query %{NUMBER:query_id:int}: %{NUMBER:qps} QPS, %{NUMBER:multiplicator}x concurrency, ID 0x(?<hexa_id>[^ ]*) at byte %{NUMBER:byte}\s*# This item.*$\s*# Scores: V/M = %{NUMBER:vm}\s*# Time range.*$\s*# Attribute.*$\s*# ===.*$\s*# Count[ ]*%{NUMBER:count_pct}.*%{NUMBER:count_total:int}$\s*# Exec time[ ]*(?<exec_time_pct>[^ ]*)[ ]*(?<exec_time_total>[^ ]*)[ ]*(?<exec_time_min>[^ ]*)[ ]*(?<exec_time_max>[^ ]*)[ ]*(?<exec_time_avg>[^ ]*)[ ]*(?<exec_time_95centil>[^ ]*)[ ]*(?<exec_time_stddev>[^ ]*)[ ]*(?<exec_time_median>[^ ]*)\s*# Lock time[ ]*(?<lock_time_pct>[^ ]*)[ ]*(?<lock_time_total>[^ ]*)[ ]*(?<lock_time_min>[^ ]*)[ ]*(?<lock_time_max>[^ ]*)[ ]*(?<lock_time_avg>[^ ]*)[ ]*(?<lock_time_95centil>[^ ]*)[ ]*(?<lock_time_stddev>[^ ]*)[ ]*(?<lock_time_median>[^ ]*)[ ]*\s*# Rows sent[ ]*(?<rows_sent_pct>[^ ]*)[ ]*(?<rows_sent_total>[^ ]*)[ ]*(?<rows_sent_min>[^ ]*)[ ]*(?<rows_sent_max>[^ ]*)[ ]*(?<rows_sent_avg>[^ ]*)[ ]*(?<rows_sent_95centil>[^ ]*)[ ]*(?<rows_sent_stddev>[^ ]*)[ ]*(?<rows_sent_median>[^ ]*)\s*# Rows examine[ ]*(?<rows_examine_pct>[^ ]*)[ ]*(?<rows_examine_total>[^ ]*)[ ]*(?<rows_examine_min>[^ ]*)[ ]*(?<rows_examine_max>[^ ]*)[ ]*(?<rows_examine_avg>[^ ]*)[ ]*(?<rows_examine_95centil>[^ ]*)[ ]*(?<rows_examine_stddev>[^ ]*)[ ]*(?<rows_examine_median>[^ ]*)\s*# Query size[ ]*(?<query_size_pct>[^ ]*)[ ]*(?<query_size_total>[^ ]*)[ ]*(?<query_size_min>[^ ]*)[ ]*(?<query_size_max>[^ ]*)[ ]*(?<query_size_avg>[^ ]*)[ ]*(?<query_size_95centil>[^ ]*)[ ]*(?<query_size_stddev>[^ ]*)[ ]*(?<query_size_median>[^ ]*)\s*# String:\s*# Databases[ ]*(?<databases_database>[^ ]*)\s*# Hosts[ ]*(?<hosts_repartition>.*$)\s*# Users[ ]*(?<users_users>.*$)\s*# Query_time distribution\s*#.*\s*#.*\s*#.*\s*#.*\s*#.*\s*#.*\s*#.*\s*#.*\s*# Tables\s*(?m)%{GREEDYDATA:mysql_query}"
  ]
  }

  mutate {
    remove_field => "message"
  }
}

P.S. : On remarque tout de suite que le pattern permettant de filtrer le log est beaucoup plus conséquent. La petite nuance ici est que nous définisson certains valeur de champs comme des entiers et non des chaines de caractères, car sur Kibana si nous voulons effectuer un tri, les nombres seraient triés comme des chaines de caractère et non des entiers. (1, 10, 101, 2…) au lieu de (1, 2, 10, 101).

La partie « message » est également supprimée car elle serait aussi redondante.

elasticsearch_pt

elasticsearch_pt2

Nous avons automatiser la génération des logs toutes les heures en utilisant pt-query-log depuis les serveurs de base de données.
Nous avons donc établi un dashboard prévu à cet effet et lissé sur 1 heure afin d’avoir toutes les dernières durées à jour.

En espérant que cet article vous ai plus et vous ai servi.

Nicolas Blattmann

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

ElasticSearch, Logstash et Kibana, de la centralisation au traitement des logs. (2/2)

La suite ELK : Logstash

Logstash est un outil disponible sur le site https://www.elastic.co/products/logstash, il permet une centralisation des processus data, une normalisation des schémas de données pour la destination de son choix et une personnalisation du format des logs.

Dans notre cas de figure, c’est lui qui va faire le lien entre syslog-ng et elasticsearch. Son installation nécessite quelques pré-requis comme OpenJDK et des sources sur lesquelles on peut s’appuyer, ici les sources importées de syslog-ng.

L’installation en elle-même et après avoir configuré les bonnes sources, s’effectue via notre utilitaire de paquet préféré. La configuration de logstash se découpe en 3 partie, « input », « filter », « output ».

  • INPUT correspond à la configuration des fichiers en entrée, typiquement ceux stockées par syslog-ng et ainsi lui spécifier un type.
  • FILTER permet d’appliquer des règles comme un reformatage des logs par exemple en fonction du type qu’on lui aura défini.
  • OUTPUT spécifie avec quel procotole et vers quel hôte envoyer les logs une fois reformatés.
input { # Block Input
 file { # Block file
 path => [ "/my/path/to/log/app.log" ] # Chemin vers le fichier de logs
 type => "application-logger-serv100" # Le type qu'on lui attribue et qui sera affiché dans Elasticsearch
 codec => "json" # Spécifie le format à l'entrée des logs dans Logstash
 }
 }
 filter { # Block Filter
 if [type] == "application-logger-serv100" { # Condition par rapport au type
 grok { # Block permettant de parser
 match => [ "message", "(.*?)\[(?<date>(.*?))\] (?<channel>(.*?)).(?<level>(DEBUG|INFO|NOTICE|WARNING|ERROR|CRITICAL|ALERT|EMERGENCY)): (?<logmessage>(.*?)) (?<context>\{\"(.*?)\"\:\"(.*?)\}) \{\"(.*?)\"\:\"(?<session_id>(.*?))-(?<uniq_id>(.*?))\"" ]} # Expression regulière
 }
 }
 output { # Block Output
 elasticsearch_http { # Type de destination sur la quelle on envoi les logs une fois parsés
 host => "127.0.0.1" # Destination sur laquelle on envoi les logs une fois parsés} }

 

Il y aura toujours un seul block input, filter et output.
En revanche, il est possible :

  • D’additionner les fichiers en entrée pour la partie input en dupliquant le block « file »
  • De multiplier les conditions avec le « if »

Il est possible pour Logstash de stocker ses propres patterns pour les filtres, basés sur des expressions régulières que nous pouvons utiliser dans le block « filter »

Exemple de pattern stocké dans le répertoire de configuration de logstash :

 USERNAME [a-zA-Z0-9._-]+
 USER %{USERNAME}
 INT (?:[+-]?(?:[0-9]+))
 BASE10NUM (?<![0-9.+-])(?>[+-]?(?:(?:[0-9]+(?:\.[0-9]+)?)|(?:\.[0-9]+)))
 NUMBER (?:%{BASE10NUM})

L’appel d’un pattern s’effectue de cette manière : « %{MYPATTERN} » comme une sorte de variable et va l’interpréter.

Dans la mesure où Logstash s’appui sur une JVM, il est important de régler la quantité de mémoire utilisée dans /etc/default/logstash

Toute modification implique de redémarrer Logstash.

La suite ELK : Elasticsearch

Elasticsearch fait aussi partie de la suite ELK et est disponible sur https://www.elastic.co/fr/.
Elasticsearch permet entre autres de disposer de données en temps réel, d’une scalabilité horizontale, d’une haute disponibilité, de stockage de documents en Json et dispose d’une API RESTful.
Il dispose des mêmes pré-requis que Logstash, notamment de la JVM.

Son installation est décrite sur le site offciel : https://www.elastic.co/guide/en/elasticsearch/reference/current/setup-repositories.html et une fois les sources ajoutées, s’effectue via notre utilitaire de gestion des paquets préféré.

Au même titre que Logstash, il est important de fixer la mémoire utilisée et cette configuration se fait cette fois dans /etc/default/elasticsearch, le paramètre à modifier est : ES_HEAP_SIZE et s’exprime en Giga. (g)

Concernant la gestion et la visualisation des données, il existe un plugin intéressant permettant de naviguer, de passer des requêtes à la volée. Ce plugin se nomme « plugin Head » et s’installe de cette manière :

 cd /usr/share/ && elasticsearch/bin/plugin -install mobz/elasticsearch-head

 

L’URL d’accès : my.url.com:9200/_plugin/head/

Spécifiquement dans notre projet, cette brique permet à la fois de stocker les dashboards Kibana et également les données récupérées depuis le serveur de log et en format Json.

A ce stade, nous n’avons pas d’autres étapes à réaliser pour notre stack ELK.

syslog_indexex
Les données arrivent en temps réel et sont visibles directement via le plugin dans la partie « navigateur ».

Un redémarrage du service Elasticsearch est requis pour pouvoir fonctionner avec les nouvelles modifications.

La suite ELK : Kibana

Kibana est une interface permettant d’interpréter les données json stockées dans Elasticsearch

Il suffit de télécharger l’archive sur le site https://www.elastic.co/downloads/kibana et de suivre les instructions donné sur le site.

La configuration est assez simple et se limite à la modification de ces 3 lignes dans le fichier config.js du dossier extrait, petites explications :

elasticsearch : "http://syslog.bazarchic.com:9200", /* Spécifie le serveur Elasticsearch sur le port 9200 */
default_route : '/dashboard/file/default.json', /* Spécifie le chemin du dashboard par défaut */
kibana_index: "kibana-int", /* Spécifie Le nom des dashboards stockées dans Elasticsearch */

Il est possible de configurer apache2 et un virtualhost pour accéder à Kibana

<VirtualHost *:80>
 ServerName monurl.domaine.com
DocumentRoot /var/www/kibana
 <Directory "/var/www/kibana">
 Order allow,deny
 Allow from all
 Options -Indexes FollowSymLinks MultiViews
AuthType Basic
 AuthUserFile "/etc/apache2/.htpasswd"
 AuthName "Authentication required"
 require valid-user
</Directory>
 ErrorLog /var/log/apache2/kibana/error.log
 CustomLog /var/log/apache2/kibana/access.log combined
</VirtualHost>

 

Pensez à prendre en compte la configuration de Apache2 (/etc/init.d/apache2 reload). Vous pouvez maintenant accéder à Kibana. A la première connexion, un message sur l’interface indique qu’il n’arrive pas à récupérer de dashboard. Il faudra simplement en créer un et l’enregistrer.

Si vous ne voyez pas de message d’erreur alors vous pouvez retrouver votre dashboard sur l’interface « head » d’Elasticsearch.

En revanche, si l’enregistrement du dashboard a échoué, vous vous êtes trompé dans la configuration de Kibana dans le fichier config.js.

Pensez à vérifier que tous les services tournent en regardant dans la liste des processus lancés.

Nicolas Blattmann

ElasticSearch, Logstash et Kibana, de la centralisation au traitement des logs. (1/2)

Pour cet article sur le blog technique de Bazarchic, nous allons nous intéresser à un sujet que nous n’abordons pas souvent, la gestion et la centralisation de nos logs.

En effet, nous avons l’habitude de laisser gérer nos démons préférés comme rsyslog, mais on comprend vite que lorsque nous voulons répondre à davantage de problématique, comme la centralisation et l’organisation des logs au sein d’un centre de données, de l’application de filtres complexes ou encore une représentation visuelle de ces derniers, ces outils deviennent inadaptés.

Nous avons alors choisi ces différents outils :

  • Rsyslog
  • Syslog-ng
  • Elasticsearch
  • Logstash
  • Kibana

Avant de vous les présenter et leur manière de s’interfacer entre eux, nous aimerions vous expliquer la raison de ce choix.

La problématique était la suivante :

Nous avions besoin d’un outil, une interface web de préférence, où nous pourrions consulter nos logs à la fois systèmes et applicatifs, où nous pourrions les organiser comme nous voulons sous forme de filtres applicables « à la volée » et où nous pourrions les visualiser sous forme de graphes et/ou de statistiques, le tout en temps réel.

Après quelques recherches et aux vues des expériences passées, nous avons rapidement opté pour la « suite ELK » (Elasticsearch-Logstash-Kibana) à laquelle nous avons ajouté des composants.

Comment s’interfacent-ils entre eux ?

syslog_archi_final

 

Intéressons nous maintenant à la mise en place de ces outils.

1°) Syslog-NG et Rsyslog

Syslog-ng s’installe directement via les dépôts officiels de Debian.

Dans les 4 sections principales de syslog-ng qui sont, la source, la destination, les filtres et logs, il est préférable pour chaque nouvelle « source » de créer un fichier dans le sous répertoire prévu à cet effet /etc/syslog-ng/conf.d/ pour plus de clarté.

Exemple de source :

source s_clients {
             tcp(ip(0.0.0.0) port("514") max-connections(100) );
};

Petit exemple pour centraliser les logs apache :

template t_template_name {
	template("$MSG\n");
    template_escape(no);
};

destination d_apache {
	file("/path/to/file/$HOST/$YEAR/$MONTH/error.apache.log" template(t_template_name));
};

filter f_apache {
	level(err);
};
log {
	source(s_clients); filter(f_apache); destination(d_apache);
};

Dans cet exemple, nous filtrons les logs apache, nous les stockons dans /path/to/file/$HOST/$YEAR/$MONTH/ et le nommons error.apache.log.
La partie filtre ici permet de ne garder que les logs d’une criticité égal ou supérieure à « Error »
La partie « template » ici est un peu plus spécifique et permet de retirer le timestamp ajouté par rsyslog lors de son envoi vers le serveur de logs principal.

Une prise en compte de la configuration est nécessaire, c’est à dire un redémarrage du service.

Partie client, au niveau de rsyslog, il est déjà installé par défaut sur les environnements Debian.
Les clients n’ont maintenant plus qu’à paramétrer leur gestionnaire de logs local comme rsyslog sur le port 514 par défaut.

$ModLoad imfile
$InputFileName /var/log/apache2/error.log
$InputFileTag apache-error
$InputFileStateFile stat-apache-error
$InputFileSeverity error
$InputFileFacility local6
$InputRunFileMonitor

local6.* @@123.123.123.123:514

$ActionResumeInterval 10
$ActionQueueSize 100000
$ActionQueueDiscardMark 97500
$ActionQueueHighWaterMark 80000
$ActionQueueType LinkedList
$ActionQueueFileName myqueue
$ActionQueueCheckpointInterval 100
$ActionQueueMaxDiskSpace 2g
$ActionResumeRetryCount -1
$ActionQueueSaveOnShutdown on
$ActionQueueTimeoutEnqueue 10
$ActionQueueDiscardSeverity 0

Les paramètres importants ici sont :

  • InputFileName qui définit quel fichier doit être traité
  • InputFileTag qui met une étiquette sur le fichier
  • InputFileSeverity qui détermine le niveau de criticité du log à traiter
  • InputFileFacility qui détermine le canal de communication à utiliser

Le reste de la configuration dans l’exemple permet d’affiner quelques paramètres comme la taille de la file d’attente.

Une prise en compte de la configuration est nécessaire aussi à ce niveau.

D’ores et déjà, lorsqu’une entrée est ajoutée dans le fichier /var/log/apache2/errog.log sur le serveur client, une entrée devrait également s’écrire dans le fichier /path/to/file/$HOST/$YEAR/$MONTH/error.apache.log sur le serveur de logs.

S’il n’y a pas d’écriture dans les logs, vous pouvez vous aider de tcpdump pour vérifier la bonne réception des paquets entre vos serveurs par exemple.

Nicolas Blattmann