Configurer SphinxSE pour MariaDB

Afin d’améliorer les performances de recherche sur l’outil APM (une plateforme de consultation des CV des consultants d’Ingéniance), il a été décidé de greffer à la base de données MariaDB d’APM l’outil SphinxSE, une alternative au moteur embarqué de recherche full-text de MariaDB.

Cet article utilise un conteneur sur un dépôt Git privé, merci de nous contacter pour récupérer l’image ou accéder au projet.

Ce conteneur est basé sur une image docker Ubuntu Xenial. Il peut être exécuté sur Linux et Windows.

Fichier de configuration Sphinx

Nous utiliserons un indice simple pour APM (car il est plus facile à utiliser, plus adapté à nos besoins / approche et les indices temps réel ne sont pas vraiment stables pour le moment).
Cette approche impose la déclaration d’une source de données dans notre fichier sphinx.conf. C’est ici que nous déclarons la requête SQL à utiliser pour créer l’index.

source src_apm
{
  type          = mysql
  
  sql_host      = 127.0.0.1
  sql_user      = root
  sql_pass      =
  sql_db        = portailManager
  sql_port      = 3306
  
  sql_query_pre = SET NAMES utf8
  sql_query_pre = SET SESSION query_cache_type=OFF
  
  sql_query     = \
    SELECT ais.id, ais.poste_actuel, we.titre, we.description, aef.valeur, aef.libelle \
    FROM applicant_information_sheet AS ais \
    INNER JOIN work_experience AS we ON we.fiche_candidat_id = ais.id \
    LEFT JOIN ais_extra_fields AS aef ON aef.fiche_candidat_id = ais.id \
    LEFT JOIN extra_fields_nomenclature AS efn ON efn.id = aef.nomenclature_champ_sup_id \
    AND aef.nomenclature_champ_sup_id IN (7,11,13,16,19,21,23,25)
  
  sql_field_string = poste_actuel
  sql_field_string = titre
  sql_field_string = description
  sql_field_string = valeur
  sql_field_string = libelle
}
index apm_candidate_index
{
  source            = src_apm
  path              = /var/lib/sphinxsearch/data/apm_candidate_index
  docinfo           = extern # Needed for full-scan
  min_infix_len     = 3 # Partial word matching min substring
  # English and French letters
  charset_table = 0..9, A..Z->a..z, _, -, a..z, \
    U+002D, U+00C0..U+00CF->U+00E0..U+00EF, U+00E0..U+00EF, U+00D4->U+00F4, U+00F4, \
    U+00D6->U+00F6, U+00F6, U+00D9->U+00F9, U+00F9, U+00DB->U+00FB, U+00FB
}
searchd
{
  listen            = 9306:mysql41
  log               = /var/log/sphinxsearch/searchd.log
  query_log         = /var/log/sphinxsearch/query.log
  read_timeout      = 5
  max_children      = 30
  pid_file          = /var/run/sphinxsearch/searchd.pid
  seamless_rotate   = 1
  preopen_indexes   = 1
  unlink_old        = 1
  binlog_path       = /var/lib/sphinxsearch/data
}

Si vous avez besoin de plus d’informations sur le fichier de configuration (si celui-ci ne fonctionne pas ou si vous voulez l’adapter à vos besoins), allez dans ce tutoriel.
Ou vérifiez ce fichier de configuration annoté pour plus d’options.

En utilisant un index simple, nous devons recréer l’index régulièrement afin de le garder à jour. Cela peut être atteint via un cron job ou une boucle infinie dans un script bash par exemple. Le fichier de configuration peut être modifié sans redémarrer le deamon Sphinx, les modifications sont automatiquement récupérées (il en va de même pour les modifications dans la base de données bien sûr).

Spécification Windows (uniquement pour le développement)

Veillez à toujours utiliser Windows PowerShell au lieu de l’invite de commande classique.

À l’aide de Windows PowerShell, tapez:

ipconfig

Veuillez trouver la carte réseau virtuelle appelée DockerNAT puis copier coller l’adresse IPv4 dans le champ sql_host dans le fichier sphinx.conf décrit précédemment.

Avant d’aller plus loin dans le guide, veuillez suivre ces quelques instructions. Connectez-vous à votre MariaDB et tapez:

CREATE USER 'root'@'10.0.75.2' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.75.2' WITH GRANT OPTION;

10.0.75.2 devrait être l’adresse IP de votre conteneur. Veillez à remplir sql_pass dans le fichier sphinx.conf par le mot de passe définit à votre utilisateur root.

Attention à ne pas télécharger le dépôt en utilisant Git Clone. A la place, téléchargez-le sous forme d’archive (ZIP ou RAR) directement. Cela résout un problème de format que Docker n’est pas capable d’interpréter.

Basic setup

SphinxSE

Tout d’abord, assurez-vous que le moteur SphinxSE est installé dans votre base de données MariaDB en utilisant:

INSTALL SONAME 'ha_sphinx';

Vérifiez l’installation avec SHOW ENGINES. Ce moteur est un client intégré qui permet au serveur MySQL de dialoguer avec searchd, d’exécuter des requêtes de recherche et d’obtenir des résultats de recherche.

Docker image

Vous pouvez soit cloner le dépôt de GitLab, et construire l’image à partir de la racine du projet:

git clone git@gitlab.com:ingeniance/Infrastucture/docker-images/sphinx-image.git
docker build -t 'sphinx:latest' sphinx-image/

Ou vous pouvez construire l’image directement à partir de l’uri du dépôt de GitLab:

docker build -t 'sphinx:latest' \
 https://gitlab.com/ingeniance/Infrastucture/docker-images/sphinx-image.git

Vous pouvez également extraire la dernière image créée automatiquement à partir du registre de conteneur:

docker pull registry.gitlab.com/ingeniance/infrastucture/docker-images/sphinx:latest

Pour cette configuration, nous allons utiliser une image docker personnalisée de Sphinx basée sur https://hub.docker.com/r/centurylink/sphinx/ et https://hub.docker.com/r/romeoz/docker-sphinxsearch/. Cette image utilise le fichier de configuration ci-dessus pour créer un index puis, en utilisant une boucle infinie, recréer l’index toutes les 5 secondes.

Ce script (et l’image Docker) peut évidemment être changé / amélioré pour mieux répondre à nos besoins à l’avenir. N’hésitez pas à me contacter (afontaine@ingeniance.fr) si vous souhaitez que des fonctionnalités soient ajoutées et / ou modifiées.

Run Sphinx

Dans le répertoire courant (a.k.a $PWD) nous appelons la commande, assurez-vous d’avoir le fichier config / sphinx.conf (ainsi que les dossiers data, log et run).

docker run -v $PWD/config:/etc/sphinxsearch -v $PWD/data:/var/lib/sphinxsearch/data -v $PWD/log:/var/log/sphinxsearch -v $PWD/run:/var/run/sphinxsearch -d -p 9312:9312 -p 9306:9306 sphinx:latest ./indexall.sh

Ensuite, pour se connecter au deamon Sphinx et interroger l’index, procédez comme suit:

mysql -h127.0.0.1 -P9306
 
Ou à partir d'un processus Java utilisant le connecteur MySQL JDBC:
 
conn = DriverManager.getConnection("jdbc:mysql:" +
"//127.0.0.1:9306?characterEncoding=utf8&maxAllowedPacket=512000","","");

Plus d’informations à ce sujet peuvent être trouvées sur le wiki de Sphinx.

Basic query

Voici quelques exemples de requêtes de recherche simple:

SELECT * FROM <index_name> WHERE MATCH('<extended_query>') \
OPTION <option1>=<option1_value>, <option2>=<option2_value>;
  
/*Look for the "cript" substring in the field "description".*/
SELECT * FROM apm_candidate_index WHERE MATCH('@description *cript*');
  
/*Do a full-scan of the index.*/
SELECT * FROM apm_candidate_index WHERE MATCH('');

Search tips

Pour plus d’informations sur la syntaxe / utilisation de SELECT dans Sphinx, cliquez ici. Pour plus d’informations sur la syntaxe de requête étendue de Sphinx, cliquez là.

De nombreuses requêtes peuvent atteindre les mêmes résultats. Par exemple: SELECT * FROM apm_candidate_index WHERE MATCH('@titre "*tes* *eoli*"~1'); est plus rapide que SELECT * FROM apm_candidate_index WHERE MATCH('@titre "*tes*" "*eoli*"'); étant donné que dans la première requête la zone de recherche est plus réduite. Soyez précis !