Aller au contenu | Aller au menu | Aller à la recherche
jeudi 29 mars 2012
--dump-rows
Cette option va regénérer un code sql dans une fichier.
Ce code SQL va regénérer une table basée sur nom de la source d'indexation et en y insérant ce qui a été lu par l'indexation.
donc
si j'ai
source ma_source {
(...)
sql_query = "Select id, nom, prenom, age From ma table_source"
}
ca va donner
Une table rows_ma_source
avec 4 colonnes dans la quelle on voit un insert des valeurs récoltées.
mardi 27 mars 2012
Dans la rubrique Mysql
C'est tout simple avec --print-queries on voit directement à l'écran les requêtes SQL qui sont exécutées.
C'est bien pratique pour le debug.
Ca me fait penser à une autre astuce, non spécifique à Sphinx, mais que j'ai commencé à utiliser avec Sphinx.
Dans mes requêtes sql , je met toujours derrière le select un commentaire par exemple
Select /* blablah */ champs1 From matable
Pourquoi ?
pour la reconnaître facilement dans mytop.
Vous ne connaissez pas mytop ? et vous utilisez mysql ?
Faite donc vite un
sudo apt-get install mytop
et lisez ceci : http://wiki.goldzoneweb.info/mytop
Dans la rubrique Mysql
source : http ://sphinxsearch.com/docs/2.0.4/ref-indexer.html
--sighup-each
Problème en indexant plusieurs indexs avec un seul appel ca me donne
indexer --config sphinx.conf --rotate --quiet chunk-1 chunk-2 chunk-3 chunk-4 chunk-5 chunk-6
Il fallait attendre que le chunk-6 soit fini pour que le chunk1 indexé depuis un certain temps soit en ligne. Dommage.
du coup j'avais refait
./indexer --config sphinx.conf --rotate --quiet chunk-1 && ./indexer --config sphinx.conf --rotate --quiet chunk-2 && ./indexer --config sphinx.conf --rotate --quiet chunk-3 && ./indexer --config sphinx.conf --rotate --quiet chunk-4 && ./indexer --config sphinx.conf --rotate --quiet chunk-5 && ./indexer --config sphinx.conf --rotate --quiet chunk-6
indigeste mais ca fonctionne mais indigeste quand même
Grâce à --sighup-each on peut resimplifier ca
indexer --sighup-each --config sphinx.conf --rotate --quiet chunk-1 chunk-2 chunk-3 chunk-4 chunk-5 chunk-6
Donne le même résultat juste avec une option en plus.
mardi 14 février 2012
Dans la rubrique Mysql
Mes données liées à des nœuds ou feuilles d'un arbre et je veux pouvoir faire une recherche en filtrant sur un nœud en espérant trouver toutes les entrées liés à ce nœud ou a sa descendance.
Mes données sont donc
* id_ressource * id_category * ...
Et j'ai un arbre stocké dans une structure classique
* id_category * id_category_parent * ...
1 null 2 null 3 1 4 3 5 3 6 2 7 6 8 6
J'indexe les données sans me préoccuper de l'arbre, juste en stockant l'id_category d'appartenance.
Lorsque que je veux chercher un nœud, je récupère la liste de ses enfants et puis je cherche tous les objets appartenance à un de ces catégories
donc pour la catégorie 2
;filter:id_category,2,6,7,8
Le problème avec cette solution, c'est qu'avec un arbre imposant, on se retrouve avec des filtres très grands.
Pour mon cas, avec un arbre de 20000 catégorie, on se retrouvait avec des filtres de plus de 5000 valeurs !!!
stocker tout le chemin
donc pour l'objet qui est dans la catégorie 8
on stocke 2, 6, 8
3 valeurs à stocker -> 2 possibilités
J'ai donc crée autant de champs catégorie qu'il n'y a de profondeur dans mon arbre (ici 3)
pour chercher les éléments dans le nœud 2 je fais
;filter:id_category_lvl1,2
pour chercher les éléments dans le nœud 6 je fais
;filter:id_category_lvl2,6
pour chercher les éléments dans le nœud 3 je fais
;filter:id_category_lvl2,3
Suite à une nouvelle demande je viens de penser à une solution tout simple et encore plus performante.
La demande était toute simple : pouvoir chercher dans plusieurs nœud en même temps. par exemple 3 et 2
Ma solution 2 ne va pas en effet
;filter:id_category_1,2;filter:id_category_2,3
ca ne retournerait rien puisqu'aucun objet ne peut être dans 2 ET dans 3
Ma solution 1, empirerait le défaut qu'elle a déjà car je me retrouverai avec encore plus de catégories.
En fait quand j'ai mis en place la solution 2 il y a un type d'attribut sphinx que je ne maitrisait pas vraiment, c'est le mva. mais pour le cas qui nous occupe il est parfait.
Je disais en solution 2, 3 valeurs à stocker -> 2 possibilités Je me trompais. Et mva porte bien son nom.
Il me suffit de stocker les 3 valeurs dans un champs MVA
et ma requête redevient
;filter:id_category,2,3
Mieux encore, la puissance de sphinx fait que je peux fabrique la valeur de remplissage de ce champs à l'indexation, par une "autre" requete Sql
C'est expliqué dans la doc.
Note il est un peu tard, je ne suis peut-être pas très clair.
N'hésitez pas à poser vos question et je retravaillerai le texte en conséquence.
dimanche 15 janvier 2012
Dans la rubrique Mysql
samedi 31 décembre 2011
Dans la rubrique Mysql
Puisque sphinx ne connaît que des valeurs scalaires pour faire un tri alphabétique, il faut transformer les chaînes de caractères en nombre.
sql_attr_str2ordinal sert à ça.
Sauf que .... ce qu'on stocke c'est un n° d'ordre dans l'index, c'est pas une représentation numérique de la chaîne.
Le nom est bien 2ordinal et pas 2num
Pour confirmation cet extrait de la doc :
9.1.20. sql_attr_str2ordinal (...) When indexing ordinals, string values are fetched from database, temporarily stored, sorted, and then replaced by their respective ordinal numbers in the array of sorted strings. So, the ordinal number is an integer such that sorting by it produces the same result as if lexicographically sorting by original strings. by string values lexicographically.
Donc pour les recherches sur plusieurs indexs ça coince.
En effet si ma chaîne est 3eme dans un index, ca ne veut pas dire qu'elle serait avant celle qui est 3ème, 5ème,100ème dans l'autre index.
Bref en multi index, ça foire.
C'est un problème facile à repérer parce que vos résultats arrivent en petites séries de blocs triés. Comme un 45T dont l'aiguille saute 
C'est bien dommage parce qu'une manière d’accélérer vos indexations et vos recherches c'est de découper les indexs en "portions".
Que faire quand on veut profiter des avantages de ce découpage et des possibilités de trier alphabétiquement ?
Allez Louya, on a un nouveau type de champs. coûteux mais efficace. sql_field_string
En créant un attribut de ce type, et en l'utilisant dans "sort" sur vos chunk... ca marche.
Et en bonus, vous pouvez en récupérer le contenu. C'est-à-dire que si l'info vous suffit , vous ne devez plus aller la rechercher dans votre source.
Transformez vous même la chaîne en nombre 
Si vous utilisez une source Mysql, j'ai une solution. Bof bof pour ca. Utiliser la fonction HEX de mysql.
J'ai expliqué cela il y a quelque temps : StrToNum en Mysql ou comment convertir une chaine en nombre ?
Reste encore 2 autres problèmes à régler mais ça sera pour une autre fois.
le "œ" est-il bien entre "n" et "p" ?
samedi 18 juin 2011
Dans la rubrique Mysql
Cette semaine j'ai réussi à faire un truc avec sphinx que je n'ai pas spécialement trouvé bien documenté donc je le raconte ici.
Quand on utilise une table mysql avec le plugin sphinxSE, on doit obligatoirement commencer par les colonnes
CREATE TABLE t1 ( id INTEGER UNSIGNED NOT NULL, weight INTEGER NOT NULL, query VARCHAR(3072) NOT NULL, group_id INTEGER, INDEX(query) ) ENGINE=SPHINX CONNECTION="sphinx://localhost:9312/test";
mais on peut lui ajouter des colonnes
on a les colonnes qui représentent les attributs scalaires mais aussi des colonnes "virtuelles"
c'est à dire
CREATE TABLE t1 ( id INTEGER UNSIGNED NOT NULL, weight INTEGER NOT NULL, query VARCHAR(3072) NOT NULL, group_id INTEGER, _sph_groupby INTEGER NOT NULL, _sph_count INTEGER NOT NULL, _sph_distinct INTEGER NOT NULL, INDEX(query) ) ENGINE=SPHINX CONNECTION="sphinx://localhost:9312/test";
Si vous utilisez un
WHERE query='test;groupby=attr:nomdunattributscalaire;';
_sph_groupby contiendra la valeur et _sph_count le nombre de matches
Pour donner une "idée" de la vitesse.
J'ai fait une recherche avec un terme courant avec un group by sur un critère qui peut contenir un chiffre de 0 à 9. Dans une collection de plus de 45M rows. Il y a entre 50.000 et 160.000 matches pour chaque ligne de mon group by Et il me donnait le résultat en 300ms
Avec un terme moins répandu et 500x moins de résultats j'avais un temps de réponse de 15ms
mardi 5 avril 2011
Dans la rubrique Mysql
L'attribut Multi-valued attribute (MVA) de sphinx est une petite merveille que j'ai enfin appréhendé.
Je pars sur un exemple fictif.
Si j'ai des fiches étudiants à indexer. Je peux ajouter des attribut pour les filtrer.
Dans tous ces attributs, pour une ressource donnée (pour un étudiant), je n'ai qu'une valeur possible.
pour n'avoir que les garçons j'aurais
filter=genre,1;
pour n'avoir que les sections math (code 4) et sciences (code 9) j'aurais
filter=section,4,9
Si je mets
filter=genre,1;filter=section,4,9
j'aurais les garçons d'une de ces sections.
Maintenant selon la section j'ai jusqu'à 15 tranches horaires en cours à options (à la carte).
Comme ces cours à option ont des durées variables, l'étudiant doit remplir ses 15 périodes.
En composant avec les 40 options proposées, il peut se retrouver avec 15 cours d'une période ou 3 de 5 périodes ou 2 de 5h + 5 de une ...
Comment indexer cela si dans ma recherche je veux pouvoir trouver les étudiants qui ont pris une option de type "langue"
Imaginant que dans la liste j'en ai 4 :
Avec sphinx on en peut pas passer plusieurs filtres et dire matcher ce qui passe au moins un de ces filtres
Avec sphinx tous les filtres seront appliqués
Donc je ne peux pas créer 15 attributs (un par tranche) et dire
filter=attribut_1,15,18,19,21;filter=attribut_2,15,18,19,21;filter=attribut_3,15,18,19,21;....
Parce qu'il faudrait que TOUS les attributs soient matchant pour la liste et donc ne me donnerai que ceux qui ont pris les 4 options et que celles là
Je ne peux pas non plus créer 40 attributs (un par option).
filter=attribut_15,1;filter=attribut_18,1;filter=attribut_19,1;filter=attribut_21,1;
ne me donnerai que ceux qui ont pris les 4 options
C'est là que les MVA entrent en jeux.
On va avoir 1 attribut "options" et pour chaque ressource l'attribut va contenir plusieurs valeurs
Lors de la recherche on passe dans la query
filter=attribut_options,15,18,19,21;
Sphinx va matcher toutes les ressources qui ont au moins une de ces valeurs parmis celles qui lui sont attribuées
Maintenant qu'on a la façon de "chercher" il faut encore voir comment indexer cela.
Un petit coup d'oeil sur la doc.
sql_attr_multi = ATTR-TYPE ATTR-NAME 'from' SOURCE-TYPE \ [;QUERY] \ [;RANGE-QUERY]where ATTR-TYPE is 'uint' or 'timestamp' SOURCE-TYPE is 'field', 'query', or 'ranged-query' QUERY is SQL query used to fetch all ( docid, attrvalue ) pairs RANGE-QUERY is SQL query used to fetch min and max ID values, similar to 'sql_query_range'
Si j'ai bloqué tout un temps c'est parce que c'était trop simple.
J'utilise sql_query pour ma source d'indexation.
Il suffit de produire un champs avec des valeurs séparée par des virgules.
puis on met
sql_attr_multi = unit lenomduchamps from field
Et nous y voilà.
Il y a aussi moyen de demander une sous-requete avec FROM query ou ranged-query
sql_attr_multi = uint tag from query; SELECT id, tag FROM tags
En voici un autre.
J'ai une liste d'enregistrements, ces enregistrements sont qualifiés par des tags.
C'est à dire qu'on est pas dans un classement par catégories dans un arbre tout simple.
Chaque enregistrement peut être associés à plusieurs tags.
Admettons que j'aie les tag grand, beau, bleu, jaune, ....
J'ai besoin de trouver tous les grands et bleus de ma base
id name--- 5 bleu 11 grand
je vais faire une MVA pour pouvoir faire une recherche filter:tag_id,5,11
lundi 14 février 2011
Dans la rubrique Mysql
Je fait pas mal de tests sur sphinx pour le moment.
En lisant PHP: SphinxClient::query - Manual
[php] public array SphinxClient::query ( string $query [, string $index = "*" [, string $comment = "" ]] )
Mon attention a été attirée par le 3ème paramètre. (supporté par sphinx depuis la Version 0.9.8 du 14 juillet 2008)
En fait ce paramètre accepte une chaine de caractère qui se retrouve dans le query.log
-
-![[T]](http://static.technorati.com/pix/icn-talkbubble.gif)
[php]
$result = $hSphinx->query('leMotQueJeCherche', '*', 'mon commentaire');
Ce paramètre existe aussi en SphinxSE mais ce n'est pas documenté,
Il suffit d'ajouter ';comment=mon test' dans la chaine query,
query='leMotQueJeCherche;comment=mon commentaire';
Ce qui est très pratique avec un tail -f et un grep pour ne voir que les rêquetes faisant partie du test,
mercredi 6 octobre 2010
Dans la rubrique Mysql
J'avais présenté un simple index et une Approche avec Main + delta. Voici maintenant 3 techniques un peu plus avancées.
Vous aurez besoin de cette configuration si vous disposez de plusieurs source (même hétérogène) et que vous voudrez mettre en place une recherche sur l'ensemble.
Ceci est également utile si vous faite du sharding avec votre base de données. Lorsque vous générez votre index, vous pouvez réunir vos informations en utilisant un index distribués et donc avoir une recherche unique.
Cette technique permet de faire une recherche sur une partie ou sur plusieurs ou sur toutes.
La pierre d’achoppement de cette technique est de garantir que les ids toute source confondue soient uniques
Recherche simple entre plusieurs sources de données, même sur des serveurs différents.
Note: Depuis la version 1.10-beta Sphinx supporte le multithreading par lui-même, ce qui devrait résoudre les problèmes de performance dont nous parlons dans cette approche, la rendant potentiellement caduque.
Pour utiliser la pleine puissance d'un CPU multi-core il est logique d'exécuter plusieurs instances du Sphinx.
Pour cela, vous devez appliquer une structure d'indexs multiples où chaque index est responsable uniquement d'une partie des données.
Par exemple, pour utiliser un processeur à quatre cœurs, une des options est de couvrir les données avec 4 index où chaque index est utilisé par un processus distinct searchd. Ensuite, chaque instance Sphinx va utiliser l'un des cœurs de processeurs.
Il faudra donc adapter le sql de collecte des informations pour répartir les données sur autant de fichier d'index que désiré.
Avec quelque chose du style
[sql] where (id Mod 1) =0
On a ensuite besoin d'un sphinx master qui s'occupe de répondre au query et de l'assemblage des résultats
Ce master qui prendra en charge la requête peut aussi prendre en charge un des index partiels.
Une autre façon de mettre cela en œuvre consiste à utiliser les agents de Sphinx accédant à l'instance de Sphinx. De cette façon, vous obtiendrez de multi-traitement dont nous avons besoin ici.
Astuce: Mettre les différents index sur des disques physiques distincts peut donner encore plus de performance en vitesse de lecture de données
Un cluster Sphinx est un ensemble de plusieurs serveurs avec la configuration décrite juste avant.
Dans cette configuration, chaque serveur possède son master Sphinx par exemple et l'un des serveurs est choisi en tant que collecteur. Il revient à ce dernier la tâche de distribuer les requêtes entre tous les serveurs utilisant chacun l'index qu'ils hébergent.
Utiliser un cluster Sphinx va faire grimper le trafic réseau au point que votre réseau puisse devenir le goulot d'étranglement.
Cette configuration permet de mettre une recherche rapide pour de très grandes quantités de données, et de manière très scalaire mais elle n'est vraiment pas simple à mettre en œuvre - vous aurez besoin d'élaborer un cadre solide pour la distribution des données entre toutes les instances dans le cluster.
Je n'ai pas pondu tout ceci.
Ces 2 posts sont fortement inspiré de five ways to configure sphinx search engine
Comme pour la première partie, j'ai l'intention de compléter ces 2 posts avec des exemples de configuration.
Dans la rubrique Mysql
Si vous avez déjà utilisé Sphinx vous avez essayé une des 5 config que je vais présenter. Chacune correspond à un type de projet. Voyons les 2 premières en détail.
Avant de commencer il y a quelques questions à se poser
Dans chaque solution seront reprises les avantages et inconvénients.
samedi 25 septembre 2010
Dans la rubrique Mysql
SELECT EXPORT_SET(5,'oui','non','|',7);
Créer une chaîne exprimant la représentation littérale de la valeur binaire de 5,
les 1 seront représentés par des oui et les 0 seront représentés par des non
le tout séparé par des |
Attention la chaîne est "inversée"
1 -> 1 -> oui 2 -> 10 -> non|oui 3 -> 11 -> oui|oui 4 -> 100 -> non|non|oui 5 -> 101 -> oui|non|oui
Le dernier paramètre permet de complèter
EXPORT_SET(5,'oui','non','|',7)--- oui|non|oui|non|non|non|non
MAKE_SET(valeur numérique,chaine1,chaine2,chaine3,chaine4)
Avec export_Set on avait une "oui" pour 1 "non" pour 0 Ici on a "chaine1" si le bit le plus a droite est à 1
Si plusieurs bit sont à 1 on aura donc plusieurs chaines. Celles-ci seront séparées par des virgules ,
Donc avec
12 -> 1100
on inverse
0011 abcd
SELECT MAKE_SET(4,'a','b','c','d');- c,d
On peut s'amuser
SELECT CONCAT('Je suis ',MAKE_SET(RAND()*5,'beau','grand','méchant','passionné'),' et fort')
Notez qu'on peut utilisé le "ou" ou le "et"
SELECT MAKE_SET(1|4,'a','b','c','d');- a,c
MAKE_SET(4&5,'a','b','c','d')- c
Tout ceci , sans php.
vendredi 10 septembre 2010
Dans la rubrique Mysql
Dans Sphinx, les filtres doivent être scalaires. J'avais donc un problème pour obtenir une version numérique d'un code pays.
ASCII(str) transforme le premier caractère de votre chaine en nombre.
Mais comment faire pour avoir un nombre différent pour 2 chaines ayant le même premier caractère ?
J'étais parti dans un délire :
ASCII(code)-65 + (ASCII(substr(code,1,0)-65+26) ...
Puis j'ai un peu regratté la doc et j'ai trouvé HEX()
HEX(N_or_S)
Si N_OR_S est une chaîne de caractères, cette fonction retournera une chaîne de caractères hexadécimale de N_OR_S où chaque caractère de N_OR_S est converti en 2 chiffres hexadécimaux.
edit: Je n'ai par contre pas encore trouvé d'équivalent PHP.
En PHP on a bin2hex. pour obtenir la même chose.
SELECT HEX('hello'),HEX('ZZ'),HEX('0A'),HEX(0xA);
HEX('hello') HEX('ZZ') HEX('0A') HEX(0xA)
------------ --------- --------- --------
68656C6C6F 5A5A 3041 0A
HEX('php') HEX('PHP') HEX('Php')
---------- ---------- ----------
706870 504850 506870
HEX('aa') HEX('ab') HEX('AA') HEX('AB') HEX('ZZ')
--------- --------- --------- --------- ---------
6161 6162 4141 4142 5A5A
Edit (2010-09-30)
Visiblement mon blog est très lu : je dit une grosse connerie et personne le remarque.
Mon objectif présenté ici est de transformer une chaîne en nombre et la solution que je présente ne donne pas le résultat escompté.
En effet HEX('ZZ') -> 5A5A reste une chaine (0x5A5A est un nombre)
Donc pour finaliser le travail il faut reconvertir le hex en dec
avec CONV(leresultat, 16, 10) en mysql et avec hexdec() en php (et pas hex2dec )
mardi 7 septembre 2010
Dans la rubrique Mysql
Un petit post pour dire que dans la 5.2 de MariaDB on aura droit a des colonnes virtuelles
Fonctionnalité disponible dans oracle depuis sa version 11 et inexistante chez Mysql.
[sql]
create table table1 (
a int not null,
b varchar(32),
c int as (a mod 10) virtual,
d varchar(5) as (left(b,5)) persistent);
Les colonnes C et D sont virtuelles, et ne représentent que des calculs (à la volée pour C à l'insertion pour D).
Les PERSISTENT peuvent même être utilisées pour des index (pas primary)
samedi 4 septembre 2010
Dans la rubrique Mysql
Je parlais il y a 2 jours de sphinx en expédiant une explication en 2 secondes.
Le lendemain, je vois sur Planet Mysql que Shlomi Noach publiait un article pour remettre les pendules à l'heure sur ce qu'est sphinx
Je vais donc en dire un peu plus.
Sphinx est un moteur d'indexation/recherche open-source sous GPLv2.
On utilise indexer pour scanner ce qu'il faut indexer, cela génère des fichiers que searchd rend consultables.
Pour attaquer searchd, on a search en ligne de commande.
Mais on a aussi
"hello world" @title "example program"~5 @body python -(php|perl) @* code
J'en passe bien sur.
Le service searchd est consultable via réseau (donc si vous utilisez SphinxSE il n'est pas embarqué dans la DB il peut/ils peuvent être ailleurs) avec ssl et tout le brol.
La première chose à distinguer c'est qu'il n'est pas limité à mysql ni même à des bases de données. On peut indexer tout ce qui peut être parsé, moyennant une conversion vers un fichier xml.
On peut indexer des sources hétérogènes les mélanger comme on peut indexer partiellement une source monolithique.
On peut indexer "sur le coté" pendant que l'index précédent reste actif et swapper à la fin (très difficile à faire : il faut ajouter un --rotate)
On peut indexer par petit morceaux et exploiter les partitions ou les merger
On peut répartir les indexs sur plusieurs serveurs
Brefs il y a moyen de distribuer la recherche et l'indexation.
Il y a des techniques de RealTime indexing mais c'est dans la 1.10-beta
mercredi 1 septembre 2010
Dans la rubrique Mysql
Sphinx
est un moteur de recherche full-text
On peut l'interroger au travers de son api, SphinxQL, en ligne de commande ou avec l'engine MySql SphinxSE
J'utilise MySqlSE;
SELECT * from INFORMATION_SCHEMA.ENGINES; ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS ------ ------- --------------------------- ------------ ------ ---------- ... SPHINX YES Sphinx storage engine 0.9.9 NO NO NO
La question du jour était : Y a-t-il un moyen de connaître le nombre total de résultats quand on utilise un limit ?
En Mysql simple, il y a SQL_CALC_FOUND_ROWS
mysql> SELECT SQL_CALC_FOUND_ROWS *
-> FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
Le second SELECT retourne un nombre indiquant combien de lignes le premier SELECT aurait retourné s'il n'avait pas été écrit avec une clause LIMIT.
Mais avec avec SPHINX
En testant sur ma table tbl_name_sphinx qui contient 2410 rows.
select SQL_CALC_FOUND_ROWS * from tbl_name_sphinx WHERE query='' LIMIT 10; SELECT FOUND_ROWS();
FOUND_ROWS()
------------
20
20 parce que c'est la valeur par défaut du limit de sphinx
# limit - amount of matches to retrieve from result set, default is 20;
(ref)
En effet si je force ce limit à 10
select SQL_CALC_FOUND_ROWS * from tbl_name_sphinx WHERE query=';limit=10'; SELECT FOUND_ROWS();
FOUND_ROWS() -> 10
Pourquoi ? parce que c'est sphinx qui fait la vraie recherche et remonte son résultat à MySql
Donc quand je fait
select SQL_CALC_FOUND_ROWS * from tbl_name_sphinx WHERE query=';limit=1000' LIMIT 10;
Je reçois 10 résultats sur 2410 réels et sur les 1000 que sphinx a remonté
Donc
SELECT FOUND_ROWS(); -> affiche 1000 et pas 2410.
Donc je vais monter mon limit à 1000000.
Gloups, je viens de demander à Sphinx de me préparer en résultat de 100K rows, tout renvoyer à mysql dans une table temporaire qui me retournera uniquement les 10 premiers.... Fameux gaspillage
Quand on sait que sphinx ne me retourne que les id et que donc il faut faire un join avec la table de données, ca fait mal.
SHOW ENGINE SPHINX STATUS;
On oublie le 'SQL_CALC_FOUND_ROWS'
Et on remplace FOUND_ROWS() par SHOW ENGINE SPHINX STATUS;
On remet le limit 10 au niveau de sphinx;
select * from tbl_name_sphinx WHERE query=';limit=10'; SHOW ENGINE SPHINX STATUS; Type Name Status ------ ------ ------------------------------------------------- SPHINX stats total: 1000, total found: 2410, time: 0, words: 0
Bingo j'ai mon info, planquée dans une "chaine" mais je l'ai.
re bingo
SHOW STATUS LIKE 'sphinx_%';
Mais je préfère INFORMATION_SCHEMA.
SELECT * from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME like 'SPHINX%';
VARIABLE_NAME VARIABLE_VALUE ------------------ -------------- SPHINX_ERROR 208409 SPHINX_TIME 0 SPHINX_TOTAL 1000 SPHINX_TOTAL_FOUND 2410 SPHINX_WORD_COUNT 0 SPHINX_WORDS
Et je suis un heureux.
Si vous êtes intéressés par Sphinx, voici un bon article pour l'installer.
Si vous vous êtes déjà intéressé à Zend_Search_Lucene, (bien décrit ici) il me semble avoir que celui-ci peut utiliser Sphinx comme backend. je corrigerai si je retrouve la source.
samedi 14 août 2010
Dans la rubrique Mysql
Petite anti-astuce :Utiliser des fonctions dans les conditions sur un champs indexé, annule l'utilisation de l'index
mysql> EXPLAIN SELECT * FROM film WHERE title LIKE 'Tr%'\\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: range possible_keys: idx_title key: idx_title key_len: 767 ref: NULL rows: 15 Extra: Using where
Une stratégie d'accès rapide à un range est choisi par l'optimiseur, et l'index sur le titre est utilisé pour réduire la quantité d'enregistrements à examiner (ici 15)
mysql> EXPLAIN SELECT * FROM film WHERE LEFT(title,2) = 'Tr' \\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 951 Extra: Using where
Une analyse complète des enregistrement, donc lente, (c'est la stratégie d'accès ALL) est utilisée car une fonction (LEFT) opére sur la colonne de titre dans la condition.
dimanche 18 avril 2010
Dans la rubrique Mysql
Jeudi en buvant un verre avec un ami, il me demande la différence entre un Datetime et un Timestamp.
J'ai répondu mais je me doutais que je n'étais pas exhaustif.
J'ai donc un peu relu. Ca fait toujours du bien.
Si on a pas besoin de stocker une date et une heure, on a d'autres possibilités.
Timestamp peut être mis à jour automatiquement à la date serveur lors d'une création ou mise à jour de l'enregistrement.
Cela peut-être désactivé avec certains modes.
Bien que les valeurs TIMESTAMP soient stockées avec une précision d'une seconde, la seule fonction qui travaille directement avec ces valeurs est la fonction UNIX_TIMESTAMP(). Les autres fonctions opèrent sur des valeurs lues et formatées.
[1] En fait c'est 2037 mais pas jusqu'au 31 décembre.
vendredi 18 décembre 2009
Dans la rubrique Mysql
J'ai eu la maladresse de croire que Le type ENUM de Mysql pouvait servir de garde fou.
En effet
[SQL]
create table `test_enum`(
`id` int UNSIGNED NOT NULL AUTO_INCREMENT ,
`monEnum` enum('a','brol','7') ,
`monAutreEnum` enum('a','brol','7') NOT NULL ,
PRIMARY KEY (`id`)
) ;
Je ne veux que 'a','brol' ou '7', éventuellement NULL dans monEnum
insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'a','a'); # (1 row(s) affected) insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,NULL,'a'); # (1 row(s) affected) insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'a',NULL); # Error Code : 1048 # Column 'monAutreEnum' cannot be null insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,NULL,NULL); # Error Code : 1048 # Column 'monAutreEnum' cannot be null
id monEnum monAutreEnum
------ ------- ------------
1 a a
2 a a
3 (NULL) a
Là ca allait.
Mais maintenant
[SQL] insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'','a'); # (1 row(s) affected, 1 warning(s)) insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'a',''); # (1 row(s) affected, 1 warning(s)) insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'',''); # (1 row(s) affected, 2 warning(s)) insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'Z','a'); # (1 row(s) affected, 1 warning(s)) insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'a','Z'); # (1 row(s) affected, 1 warning(s)) insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'Z','Z'); # (1 row(s) affected, 2 warning(s))
On voit que les "vides" et les "Z" ne bloquent pas l'ajout de l'enregistrement. et sont remplacés par "vide" alors que ce n'est pas une valeur acceptée.
En effet le manuel dit ceci.
Si vous insérez une valeur illégale dans une énumération ENUM (c'est à dire, une chaîne qui n'est pas dans la liste de valeurs autorisées), la chaîne vide est insérée pour représenter une erreur. Cette chaîne peut être distinguée d'une chaîne vide 'normale' par le fait que cette chaîne à la valeur numérique 0. Nous reviendrons sur ce point plus tard.
Par exemple, une colonne créée comme ENUM("un", "deux", "trois") peut prendre n'importe quelle valeur ci-dessous. L'index de chaque valeur est aussi présenté :
Valeur Index --------- -------- NULL NULL "" 0 "un" 1 "deux" 2 "trois" 3
[Resultat]
id monEnum monAutreEnum
------ ------- ------------
1 a a
2 a a
3 (NULL) a
4 a
5 a
6
7 a
8 a
9
Notez que si j'ajoute une valeur par défaut, ca n'y change rien
[SQL]
alter table `test_enum`
change `monEnum` `monEnum` enum('a','brol','7') character set latin1 collate latin1_swedish_ci default 'a' NULL ,
change `monAutreEnum` `monAutreEnum` enum('a','brol','7') character set latin1 collate latin1_swedish_ci default 'a' NOT NULL;
///[SQL]
insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'','a');
# (1 row(s) affected, 1 warning(s))
insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'a','');
# (1 row(s) affected, 1 warning(s))
insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'','');
# (1 row(s) affected, 2 warning(s))
insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'Z','a');
# (1 row(s) affected, 1 warning(s))
insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'a','Z');
# (1 row(s) affected, 1 warning(s))
insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'Z','Z');
# (1 row(s) affected, 2 warning(s))
id monEnum monAutreEnum
------ ------- ------------
1 a a
2 a a
3 (NULL) a
4 a
5 a
6
7 a
8 a
9
10 a
11 a
12
13 a
14 a
15
En fait tout dépends du mode sql en cours
[SQL] select @@SQL_MODE; SET SESSION sql_mode='STRICT_ALL_TABLES'; select @@SQL_MODE; #@@SQL_MODE #----------------- #STRICT_ALL_TABLES insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'brol','z'); #Error Code : 1265 #Data truncated for column 'monAutreEnum' at row 1 insert into `test_enum`(`id`,`monEnum`,`monAutreEnum`) values ( NULL,'z','brol'); #Error Code : 1265 #Data truncated for column 'monEnum' at row 1 SET SESSION sql_mode=''; select @@SQL_MODE;
Là mes 2 inserts sont bloqués avec le message
lundi 12 octobre 2009
Dans la rubrique Mysql
| ADD | ALL | ALTER |
| ANALYZE | AND | AS |
| ASC | ASENSITIVE | BEFORE |
| BETWEEN | BIGINT | BINARY |
| BLOB | BOTH | BY |
| CALL | CASCADE | CASE |
| CHANGE | CHAR | CHARACTER |
| CHECK | COLLATE | COLUMN |
| CONDITION | CONNECTION | CONSTRAINT |
| CONTINUE | CONVERT | CREATE |
| CROSS | CURRENT_DATE | CURRENT_TIME |
| CURRENT_TIMESTAMP | CURRENT_USER | CURSOR |
| DATABASE | DATABASES | DAY_HOUR |
| DAY_MICROSECOND | DAY_MINUTE | DAY_SECOND |
| DEC | DECIMAL | DECLARE |
| DEFAULT | DELAYED | DELETE |
| DESC | DESCRIBE | DETERMINISTIC |
| DISTINCT | DISTINCTROW | DIV |
| DOUBLE | DROP | DUAL |
| EACH | ELSE | ELSEIF |
| ENCLOSED | ESCAPED | EXISTS |
| EXIT | EXPLAIN | FALSE |
| FETCH | FLOAT | FLOAT4 |
| FLOAT8 | FOR | FORCE |
| FOREIGN | FROM | FULLTEXT |
| GOTO | GRANT | GROUP |
| HAVING | HIGH_PRIORITY | HOUR_MICROSECOND |
| HOUR_MINUTE | HOUR_SECOND | IF |
| IGNORE | IN | INDEX |
| INFILE | INNER | INOUT |
| INSENSITIVE | INSERT | INT |
| INT1 | INT2 | INT3 |
| INT4 | INT8 | INTEGER |
| INTERVAL | INTO | IS |
| ITERATE | JOIN | KEY |
| KEYS | KILL | LABEL |
| LEADING | LEAVE | LEFT |
| LIKE | LIMIT | LINES |
| LOAD | LOCALTIME | LOCALTIMESTAMP |
| LOCK | LONG | LONGBLOB |
| LONGTEXT | LOOP | LOW_PRIORITY |
| MATCH | MEDIUMBLOB | MEDIUMINT |
| MEDIUMTEXT | MIDDLEINT | MINUTE_MICROSECOND |
| MINUTE_SECOND | MOD | MODIFIES |
| NATURAL | NOT | NO_WRITE_TO_BINLOG |
| NULL | NUMERIC | ON |
| OPTIMIZE | OPTION | OPTIONALLY |
| OR | ORDER | OUT |
| OUTER | OUTFILE | PRECISION |
| PRIMARY | PROCEDURE | PURGE |
| RAID0 | READ | READS |
| REAL | REFERENCES | REGEXP |
| RELEASE | RENAME | REPEAT |
| REPLACE | REQUIRE | RESTRICT |
| RETURN | REVOKE | RIGHT |
| RLIKE | SCHEMA | SCHEMAS |
| SECOND_MICROSECOND | SELECT | SENSITIVE |
| SEPARATOR | SET | SHOW |
| SMALLINT | SONAME | SPATIAL |
| SPECIFIC | SQL | SQLEXCEPTION |
| SQLSTATE | SQLWARNING | SQL_BIG_RESULT |
| SQL_CALC_FOUND_ROWS | SQL_SMALL_RESULT | SSL |
| STARTING | STRAIGHT_JOIN | TABLE |
| TERMINATED | THEN | TINYBLOB |
| TINYINT | TINYTEXT | TO |
| TRAILING | TRIGGER | TRUE |
| UNDO | UNION | UNIQUE |
| UNLOCK | UNSIGNED | UPDATE |
| UPGRADE | USAGE | USE |
| USING | UTC_DATE | UTC_TIME |
| UTC_TIMESTAMP | VALUES | VARBINARY |
| VARCHAR | VARCHARACTER | VARYING |
| WHEN | WHERE | WHILE |
| WITH | WRITE | X509 |
| XOR | YEAR_MONTH | ZEROFILL |
lundi 31 août 2009
Dans la rubrique Mysql
Une explication pour démarrer à partir de zéro un cluster MySQL 7.0.7 (ou supérieur) avec 2 noeuds de gestion. C'est dans l'article How to start MySQL Cluster 7.0 with 2 magement nodes? de notre compatriote Geert.
samedi 13 juin 2009
Dans la rubrique Mysql
Comme expliqué ici.
Incompatible change: For utf8 columns, the full-text parser incorrectly considered several nonword punctuation and whitespace characters as word characters, causing some searches to return incorrect results. The fix involves a change to the full-text parser in MySQL 5.1.12, so as of 5.1.12, any tables that have FULLTEXT indexes on utf8 columns must be repaired with REPAIR TABLE:
REPAIR TABLE tbl_name QUICK;
Il sera nécéssaire de réindexer les fulltext
Incompatible change: The structure of FULLTEXT indexes has been changed in MySQL 5.1.6. After upgrading to MySQL 5.1.6 or greater, any tables that have FULLTEXT indexes must be repaired with REPAIR TABLE:
REPAIR TABLE tbl_name QUICK;
SELECT `TABLE_NAME` FROM `STATISTICS` WHERE `INDEX_TYPE`= 'FULLTEXT' ;
lundi 27 avril 2009
Pour faire le point sur Timestamp : un bel article sur le sujet.
timestamp, ou Unix Timestamp, correspond au nombre de secondes écoulées depuis le 1er Janvier 1970.
A lire aussi : La page dans le manuel
mardi 24 février 2009
Dans la rubrique Mysql
Petite expérience sur mysql 5.1
4 tables de même structure mais 4 stockages différents : MyISAM et archive, avec et sans partition. un peu plus de 500 enregistrements ....
MyISAM 52Ko ->Archive : 20 Ko
Myisam 63 Ko -> Archive 12.1Ko
Là je suis étonné, l'archive avec partition est 40% plus petite.
J'ai ajouté 31000 enregistrements
Je reste quand même étonné que le partitionnement d'une table archive réduit le stockage.
Voir la suite pour plus de détails
vendredi 20 février 2009
Dans la rubrique Mysql
Ce post est à moitié une manière de faire , et une autre une façon de mieux le faire?
Donc, vous voulez construire un système qui effectue des tâches. Vous voulez que le travail puisse être organisé en parallèle pour la vitesse, mais aussi pour la redondance. Ce système doit être coordonnée de façon, par exemple, les mêmes tâches ne sont pas faites deux fois, le statut de chaque tâche est facile à voir, et de multiples serveurs peuvent effectuer les tâches simplement en interrogeant la source centrale.
Voici la traduction de Creating a Job queue in Innodb Comment peut-on construire cela avec innodb pour avoir MySQL comme système central de notre système?
[MYSQL]
CREATE TABLE IF NOT EXISTS job_queue(
id int(10) not null auto_increment,
updated timestamp not null,
started timestamp not null,
state ENUM('NEW', 'WORKING', 'DONE', 'ERROR' ) default 'NEW',
PRIMARY KEY ( id ),
KEY( STATE )
) ENGINE=Innodb;
Dans ce schéma, notre table de tâches a un identifiant unique, une heure de démarrage et de mise à jour et un statut.
Dans un système réel, il y aura sans doute un peu plus de méta-données ici sur la nature des tâches.
Lorsque de nouvelles tâches doivent être programmées, les lignes sont insérées dans la table (avec started à NOW (), et updated est mis à jour automatiquement)
[MYSQL] insert into job_queue set started=NOW();
Maintenant, en perl, nous écrivons un contrôleur de tâches. Ce programme peut être exécuté sur chaque serveur qui pourrait accomplir les tâches.
[perl]
#!/usr/bin/perl
# Don't leave zombies around, shamelessly stolen from 'man perlipc'
use POSIX ":sys_wait_h";
sub REAPER {
my $child;
while (($child = waitpid(-1,WNOHANG)) > 0) {
$Kid_Status{$child} = $?;
}
$SIG{CHLD} = \&REAPER;
}
$SIG{CHLD} = \&REAPER;
use DBD::mysql;
my $dbh = DBI->connect(
'DBI:mysql:database=test;host=127.0.0.1;port=3306',
'test', 'test',
{ RaiseError => 1, AutoCommit => 0 }
);
while( 1 ) {
my $row;
eval {
my $sth = $dbh->prepare( "select id from job_queue where state='NEW' li
mit 1 for update" );
$sth->execute();
$row = $sth->fetchrow_hashref();
$sth->finish();
};
if( $@ or !$row ) {
# Couldn't lock or lock wait timeout
$dbh->commit();
sleep 10;
next;
}
# Got one, change state, commit, and fork a worker
$dbh->do( "update job_queue set state='WORKING' where id=" . $row->{id} );
$dbh->commit();
# Fork a worker
if( fork ) {
# Parent, let the child have the old connection and reconnect to
# the db.
$dbh->{InactiveDestroy} = 1;
$dbh = DBI->connect(
'DBI:mysql:database=test;host=127.0.0.1;port=3306',
'test', 'test',
{ RaiseError => 1, AutoCommit => 0 }
);
} else{
# fils
print "Création du verrou\n";
$dbh->do( "select * from job_queue where id=" . $row->{id}
. " for update " );
print "Travail en cours\n";
#On simule un traitement
srand( time );
sleep rand 30;
$dbh->do( "update job_queue set STATE='DONE' where
id=" . $row->{id} );
print "On Commit\n";
$dbh->commit();
$dbh->disconnect();
exit;
}
sleep 1;
}
Maintenant, ce n'est pas mauvais. Mais il a au moins une chose que je n'aime pas. Si une des tâches n'aboutit pas, il n'existe aucun moyen pour réaffecter la tâche.
Ideally each worker would have a lock on his job row inside of a transaction, which it is doing now, but, instead of the job being in the 'WORKING' state first, I'd rather it was 'NEW' before the transaction started. If that were the case, if a worker died, it's unfinished transaction would be rolled back, and the job would be unlocked and NEW again.
Idéalement, chaque travailleur aurait un verrou sur son travail dans une transaction, ce qui est fait maintenant, mais, au lieu du travail en cours dans le «travail» d'abord, je préfère c'est "NOUVEAU" avant l'opération a commencé . Si tel était le cas, si un travailleur est mort, il est inachevé transaction serait annulée, et la tâche serait déverrouillé et NEW nouveau.
However, because of the way SELECT ... FOR UPDATE works, Innodb will deadlock waiting for 'NEW' jobs to unlock from the workers (or wait until they are done). This is sub-optimal, since my parent process could be forking new jobs in the meantime. What would fix this is a SELECT ... FOR UPDATE that skipped locked rows without blocking. Toutefois, en raison de la façon SELECT ... MISE À JOUR DE œuvres, InnoDB va impasse d'attente pour les «nouveaux» pour déverrouiller l'emploi des travailleurs (ou d'attendre jusqu'à ce qu'ils soient fait). Cela est sous-optimale, car mon père pourrait être forking de nouveaux emplois dans l'intervalle. Qu'est-ce que ce correctif est un SELECT ... MISE À JOUR DE verrouillé ignoré que des lignes sans blocage.
If anyone knows a good way to achieve this, please let me know!
However, assuming we have no better alternatives, we can create a reaper process like this:
[perl]
#!/usr/bin/perl
use DBD::mysql;
my $dbh = DBI->connect(
'DBI:mysql:database=test;host=127.0.0.1;port=3306',
'test', 'test',
{ RaiseError => 1, AutoCommit => 0 }
);
while( 1 ) {
my $row;
eval {
my $sth = $dbh->prepare( "select id from job_queue where state='WORKING' limit 1 for update" );
$sth->execute();
$row = $sth->fetchrow_hashref();
$sth->finish();
};
if( $@ or !$row ) {
# Couldn't lock or lock wait timeout
$dbh->commit();
sleep 10;
next;
}
# Got one, change state, commit, and fork a worker
$dbh->do( "update job_queue set state='NEW' where id=" . $row->{id} ); $dbh->commit();
sleep 1;
}
This will find the first WORKING row and try to lock it. Since normal jobs will be moved to DONE before they are committed, this should only ever find jobs that are in WORKING and unlocked, which means the worker died.
However this isn't perfect. Because SELECT ... FOR UPDATE will try to lock a row already locked, we have to wait until all jobs before our stalled job are complete, getting deadlocks along the way (be sure to set your innodb_lock_wait_timeout fairly low!. Even further, I've seen dead worker processes leave behind idle mysql connections that hold onto their row locks.
Is there any smoother way to do this? I'd love to hear other people's advice.
Les propos tenus ici sont sous la responsabilité de leurs auteurs respectifs.
