Optimiser requete phpbb

Bonjour,

J'ai de très grosses tables search_wordmatch et search_wordlist c'est pourquoi je cherche la meilleure solution pour effectuer cette requete :

Code:

1
2
3
4
5
6
7
8
SELECT m.word_id 
				FROM phpbb_search_wordmatch m, phpbb_search_wordlist w 
				WHERE w.forumid = 5021
          AND w.word_text IN ('ardente', 'bref', 'cause', 'cest', 'effectivement', 'euh', 'inspirant', 'plus', 'très', 'une', 'vivifiante')
          AND m.forumid = w.forumid  
					AND m.word_id = w.word_id 
				GROUP BY m.word_id 
				HAVING COUNT(m.word_id) > 1636;

Elle met 13 secondes à s'exécuter sur mon dédié.

Description des tables :
phpbb_search_wordlist
1 240 649 enregistrements MyISAM latin1_swedish_ci 68,9 Mo

phpbb_search_wordmatch 4 159 965 enregistrements MyISAM latin1_swedish_ci 152 Mo

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
 
CREATE TABLE `phpbb_search_wordlist` (
  `word_text` varchar(50) character set latin1 collate latin1_bin NOT NULL default '',
  `word_id` mediumint(8) unsigned NOT NULL auto_increment,
  `word_common` tinyint(1) unsigned NOT NULL default '0',
  `forumid` int(9) NOT NULL default '0',
  PRIMARY KEY  (`word_text`,`forumid`,`word_id`),
  KEY `word_id` (`word_id`),
  KEY `forumid` (`forumid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=211837 ;
 
 
 
CREATE TABLE `phpbb_search_wordmatch` (
  `post_id` mediumint(8) unsigned NOT NULL default '0',
  `word_id` mediumint(8) unsigned NOT NULL default '0',
  `title_match` tinyint(1) NOT NULL default '0',
  `forumid` int(9) NOT NULL default '0',
  KEY `post_id` (`post_id`),
  KEY `word_id` (`word_id`),
  KEY `forumid` (`forumid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Avez vous une solution pour ce type de requetes ?

Merci :D

Bonjour,

Peux-tu faire un EXPLAIN sur la requête pour voir si les index sont utilisés correctement ?

Et le contenu de ton my.cnf/my.ini ça serait bien aussi :)

Merci bcp pour ta réponse.

Je viens de réexécuter la requete, 19 secondes :oops:

Voici le EXPLAIN :

Code:

1
2
3
4
5
6
7
equête SQL: EXPLAIN SELECT m.word_id FROM phpbb_search_wordmatch m, phpbb_search_wordlist w WHERE w.forumid = 5021 AND w.word_text IN ('ardente', 'bref', 'cause', 'cest', 'effectivement', 'euh', 'inspirant', 'plus', 'très', 'une', 'vivifiante') AND m.forumid = w.forumid AND m.word_id = w.word_id GROUP BY m.word_id HAVING COUNT(m.word_id) > 1636;
Enregistrements: 2
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
 
1 	SIMPLE 	w 	range 	PRIMARY,word_id,forumid 	PRIMARY 	54 	NULL 	105 	Using where; Using index; Using temporary; Using filesort
 
1 	SIMPLE 	m 	ref 	word_id,forumid 	word_id 	3 	monforumV2.w.word_id 	60 	Using where

Fichier My.cnf

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
socket = /var/lib/mysql/mysql.sock
query_cache_size = 41984
#skip-external-locking
#skip-innodb
#key_buffer_size = 256M
#max_allowed_packet = 1M
#table_cache = 2048
#sort_buffer_size = 16M
#read_buffer_size = 16M
#thread_cache_size = 8
#thread_concurrency = 8
#myisam_sort_buffer_size = 64M
#connect_timeout = 10
max_connections = 200
connect_timeout = 5
#long_query_time = 30
wait_timeout=5
#interactive_timeout=60
#thread_stack = 128k
long_query_time = 3
log-slow-queries = /var/log/mysql/slow_queries.log log-error = /var/log/mysql/error.loglog-error = /var/log/mysql/error.log #max_allowed_packet = 500M

Quand il y a pas mal de requetes en attente, j'ai remarqué que j'ai des requêtes qui apparaissent en LOCKED, je ne sais par contre pas ce que c'est

Hmmm, sinon, j'ai une solution alternative : tu t'arranges pour que Google référence complètement ton forum et tu zappes totalement la fonction de recherche, ou bien tu la restreins aux forums privés (ceux que G ne référencera pas).
Je t'assure que ça donne de très bons résultats !

Sinon, tu peux probablement aider MySQL en optimisant toi-même la requête :
Code:

1 2 3 4 5 6 7 8 9 SELECT m.word_id FROM phpbb_search_wordmatch m INNER JOIN phpbb_search_wordlist w ON m.forumid = w.forumid AND m.word_id = w.word_id WHERE w.forumid = 5021 AND w.word_text IN ('ardente', 'bref', 'cause', 'cest', 'effectivement', 'euh', 'inspirant', 'plus', 'très', 'une', 'vivifiante') GROUP BY m.word_id HAVING COUNT(m.word_id) > 1636;
C'est peu mais voilà...

J'ai déjà ajouté un formulaire de recherche passant par google mais malheureusement il n'a pas indexé l'ensemble des forums.

Supprimer la recherche émbeterait beaucoup mes membres...

J'ai essayé la requete que tu m'as donnée, je l'ai killée au bout de 25 secondes :cry:

Avec un bon URL Rewriting, je te promets que G indexera tout ton site. Il l'a fait pour le mien en quelques jours / semaines, là je crois qu'il a à peu près tout, davantage que ce à quoi je m'attendais en tout cas.

Faut vraiment que je publie ma version de l'URLR, ça pourrait t'être utile...

Je ne comprends pas pourquoi ma proposition de requête est plus lente, cela devrait être l'inverse !
M'aurait-on menti sur le INNER JOIN ?

Citation:

Envoyé par compu

Quand il y a pas mal de requetes en attente, j'ai remarqué que j'ai des requêtes qui apparaissent en LOCKED, je ne sais par contre pas ce que c'est

Ce sont des requêtes bloquées en attendant qu'une autre finisse d'écrire dans la table (avec MyISAM le verrouillage est au niveau table).

Apparemment les index sont utilisés donc pas de souci à ce niveau-là.

Dans le fichier de conf tu pourrais spécifier un join_buffer_size et surtout un key_buffer_size (cf http://florian.developpez.com/mysql/page2.php)

Peux-tu nous donner le résultat d'un SHOW STATUS ?

La ca fonctionne bien avec 50 connectés.
Il doit y avoir une requete qui pète, je pense lors de la recherche.

Voici le show status :

Citation:

mysql> show status;
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| Aborted_clients | 54 |
| Aborted_connects | 5 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 77174415 |
| Bytes_sent | 661626744 |
| Com_admin_commands | 1 |
| Com_alter_db | 0 |
| Com_alter_table | 1 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 227 |
| Com_change_db | 19537 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 196 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 17030 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 27623 |
| Com_insert_select | 215 |
| Com_kill | 13 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 8 |
| Com_savepoint | 0 |
| Com_select | 302747 |
| Com_set_option | 458 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 64 |
| Com_show_charsets | 114 |
| Com_show_collations | 114 |
| Com_show_column_types | 0 |
| Com_show_create_db | 10 |
| Com_show_create_table | 7 |
| Com_show_databases | 19 |
| Com_show_errors | 0 |
| Com_show_fields | 13 |
| Com_show_grants | 8 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 6 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 62 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 131 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 285 |
| Com_show_variables | 228 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_send_long_data | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_close | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 19172 |
| Com_update_multi | 0 |
| Connections | 19177 |
| Created_tmp_disk_tables | 4059 |
| Created_tmp_files | 49 |
| Created_tmp_tables | 29383 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 115762 |
| Handler_discover | 0 |
| Handler_read_first | 31065 |
| Handler_read_key | 3426431 |
| Handler_read_next | 78467528 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 442282 |
| Handler_read_rnd_next | 9883112 |
| Handler_rollback | 105 |
| Handler_update | 771475 |
| Handler_write | 845365 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 7248 |
| Key_read_requests | 19324626 |
| Key_reads | 689809 |
| Key_write_requests | 846048 |
| Key_writes | 216979 |
| Max_used_connections | 40 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 107 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 4466 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 413225 |
| Rpl_status | NULL |
| Select_full_join | 539 |
| Select_full_range_join | 74 |
| Select_range | 1189 |
| Select_range_check | 0 |
| Select_scan | 17046 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slave_retried_transactions | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 322 |
| Sort_merge_passes | 23 |
| Sort_range | 79422 |
| Sort_rows | 820698 |
| Sort_scan | 27075 |
| Table_locks_immediate | 496115 |
| Table_locks_waited | 2298 |
| Threads_cached | 0 |
| Threads_connected | 3 |
| Threads_created | 19176 |
| Threads_running | 2 |
| Uptime | 34690 |
+----------------------------+-----------+

Concernant le blocage des tables, je n'ai pas intéret a changer le format de mes tables ? Est ce que je n'aurais pas des interblocages si une requête bloque la table et nécessite beaucoup de temps pour être exécutée ?

Je regarde ton lien. Merci bcp

La machine ramait, je viens de killer ces requetes :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT m.word_id
FROM phpbb_search_wordmatch m, phpbb_search_wordlist w
WHERE w.forumid =2958
AND m.forumid =2958
AND w.word_text
IN (
'1er', '5', '5e', 'a', 'alliance', 'ally', 'alors', 'arm�', 'art�facts ', 'assez ', 'aura ', 'aurait ', 'avait ', 'avant ', 'avec ', 'avoir ', 'beau ', 'beaucoup ', 'bien ', 'bon ', 'bonne ', 'camaraderie ', 'candidature ', 'car ', 'ceci ', 'certain ', 'certaine ', 'certe ', 'ces ', 'cest ', 'ceux ', 'chaque ', 'chose ', 'coalition ', 'co
IN ', 'comma ', 'comme ', 'commence ', 'cot�', 'courage ', 'croivent ', 'dabord ', 'dans ', 'dautre ', 'deau ', 'debut ', 'decider ', 'depuis ', 'derriere ', 'des ', 'detre ', 'detruire ', 'deux ', 'developper ', 'dici ', 'dillusion ', 'dire ', 'discours ', 'dit ', 'donc ', 'donne ', 'd�bord�', 'd�j� ', 'elements ', 'en ', 'encore ', 'espoir ', 'essaye ', 'est ', 'etc ', 'eux ', 'faire ', 'fas ', 'font ', 'forum ', 'gagne ', 'goute ', 'gr
AND ', 'grandavus ', 'gros ', 'grosse ', 'guerrier ', 'hirad ', 'jaurai ', 'jaurais ', 'je ', 'jediterez ', 'jen ', 'jeu ', 'jour ', 'jours ', 'juste ', 'la ', 'lally ', 'les ', 'lo
IN ', 'long ', 'lui ', 'l�', 'mais ', 'malheuresement ', 'mare ', 'mateja ', 'meilleurs ', 'meme ', 'message ', 'met ', 'meure ', 'meurt ', 'mieu ', 'moi ', 'monde ', 'naurait ', 'nest ', 'nez ', 'nimporque ', 'non ', 'notre ', 'not�', 'nous ', 'on', 'orki ', 'oui', 'par', 'pari ', 'parl�', 'pars', 'part', 'participer ', 'partir ', 'pas', 'passe ', 'passer ', 'personne ', 'personnes ', 'peut', 'place ', 'plein ', 'plus', 'point ', 'poster ', 'potentiel ', 'pour', 'pourra ', 'pourrait ', 'precisez ', 'pret', 'ptin', 'putain ', 'quand ', 'que', 'quelle ', 'quelque ', 'quelques ', 'quelquun ', 'qui', 'quon ', 'raconte ', 'recruter ', 'redonner ', 'reponse ', 'reste ', 'roll', 'se', 'semaines ', 'serait ', 'serte ', 'sien', 'son', 'sous ', 'suive ', 'suivre ', 'tien', 'tiens ', 'toi', 'top ', 'tous ', 'tout ', 'tres ', 'trop ', 'trote ', 'truc', 'une', 'va ', 'va IS ', 'valeureux ', 'vase', 'vers', 'veux', 'vie', 'vivre ', 'voil�', 'voir ', 'vois ', 'vont ', 'vous ', 'wink ', 'y ', '�', '�tait ')
AND m.word_id = w.word_id
GROUP BY m.word_id
HAVING COUNT( m.word_id ) >100

Elle tournait depuis 60 secondes et elle était en état : Copying to tmp table

J'ai regardé les ratios dont ils parlent dans la page sur développez.com

Read / Request : 0,035695853 => Normalement < à 0,1
Write / Request : 0,256461808 => Normalement = 1

Est ce qu'avoir "trop" d'index peut être un problème ? J'en ai ajouté pas mal dans les tables utilisées par la recherche

Citation:

Envoyé par compu

Concernant le blocage des tables, je n'ai pas intéret a changer le format de mes tables ? Est ce que je n'aurais pas des interblocages si une requête bloque la table et nécessite beaucoup de temps pour être exécutée ?

Si les accès en écriture sont très nombreux, InnoDB peut effectivement se révéler plus efficace puisque le verrouillage se fait au niveau ligne. Si par interblocage tu entends deadlocks, ce genre d'anomalies est détecté et réparé par le serveur automatiquement.
Citation:
Envoyé par compu
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT m.word_id FROM phpbb_search_wordmatch m, phpbb_search_wordlist w WHERE w.forumid =2958 AND m.forumid =2958 AND w.word_text IN ( '1er', '5', '5e', 'a', 'alliance', 'ally', 'alors', 'arm�', 'art�facts ', 'assez ', 'aura ', 'aurait ', 'avait ', 'avant ', 'avec ', 'avoir ', 'beau ', 'beaucoup ', 'bien ', 'bon ', 'bonne ', 'camaraderie ', 'candidature ', 'car ', 'ceci ', 'certain ', 'certaine ', 'certe ', 'ces ', 'cest ', 'ceux ', 'chaque ', 'chose ', 'coalition ', 'co IN ', 'comma ', 'comme ', 'commence ', 'cot�', 'courage ', 'croivent ', 'dabord ', 'dans ', 'dautre ', 'deau ', 'debut ', 'decider ', 'depuis ', 'derriere ', 'des ', 'detre ', 'detruire ', 'deux ', 'developper ', 'dici ', 'dillusion ', 'dire ', 'discours ', 'dit ', 'donc ', 'donne ', 'd�bord�', 'd�j� ', 'elements ', 'en ', 'encore ', 'espoir ', 'essaye ', 'est ', 'etc ', 'eux ', 'faire ', 'fas ', 'font ', 'forum ', 'gagne ', 'goute ', 'gr AND ', 'grandavus ', 'gros ', 'grosse ', 'guerrier ', 'hirad ', 'jaurai ', 'jaurais ', 'je ', 'jediterez ', 'jen ', 'jeu ', 'jour ', 'jours ', 'juste ', 'la ', 'lally ', 'les ', 'lo IN ', 'long ', 'lui ', 'l�', 'mais ', 'malheuresement ', 'mare ', 'mateja ', 'meilleurs ', 'meme ', 'message ', 'met ', 'meure ', 'meurt ', 'mieu ', 'moi ', 'monde ', 'naurait ', 'nest ', 'nez ', 'nimporque ', 'non ', 'notre ', 'not�', 'nous ', 'on', 'orki ', 'oui', 'par', 'pari ', 'parl�', 'pars', 'part', 'participer ', 'partir ', 'pas', 'passe ', 'passer ', 'personne ', 'personnes ', 'peut', 'place ', 'plein ', 'plus', 'point ', 'poster ', 'potentiel ', 'pour', 'pourra ', 'pourrait ', 'precisez ', 'pret', 'ptin', 'putain ', 'quand ', 'que', 'quelle ', 'quelque ', 'quelques ', 'quelquun ', 'qui', 'quon ', 'raconte ', 'recruter ', 'redonner ', 'reponse ', 'reste ', 'roll', 'se', 'semaines ', 'serait ', 'serte ', 'sien', 'son', 'sous ', 'suive ', 'suivre ', 'tien', 'tiens ', 'toi', 'top ', 'tous ', 'tout ', 'tres ', 'trop ', 'trote ', 'truc', 'une', 'va ', 'va IS ', 'valeureux ', 'vase', 'vers', 'veux', 'vie', 'vivre ', 'voil�', 'voir ', 'vois ', 'vont ', 'vous ', 'wink ', 'y ', '�', '�tait ') AND m.word_id = w.word_id GROUP BY m.word_id HAVING COUNT( m.word_id ) >100
8O C'est quoi cette requête ?

Citation:

Envoyé par compu

Est ce qu'avoir "trop" d'index peut être un problème ? J'en ai ajouté pas mal dans les tables utilisées par la recherche

Oui, ça va ralentir les écritures. A toi d'arbitrer entre le temps gagné en lecture via l'utilisation de l'index et le temps perdu en écriture. Ceci dit ton ratio read/read request n'est pas si mauvais puisque seules 3% des lectures de blocs d'index se font sur le disque.

Par ailleurs je remarque que ton query_cache n'est pas du tout utilisé. Il faudrait lui mettre une taille plus grande (voir http://maximilian.developpez.com/mysql/queryCache/)

Cf aussi http://rudd-o.com/archives/2006/03/02/tuning-a-mysql-server-in-5-minutes/ et http://ebergen.net/wordpress/?p=104

C'est une requete effectuée lors d'une recherche je suppose :cry:

Je viens de mettre en place le cache avec 150 Mo.
Comment faites vous pour des forums comme développez.com pour gérer les recherches ?

Oui, peut-être par un robot indexeur de moteur de recherche alors ?

Il y a peut-être un autre moyen d'améliorer cette requête : changer l'ordre des parties de la clé primaire de phpbb_search_wordlist (PRIMARY KEY (`word_text`,`forumid`,`word_id`)).

En effet dans un index multiple comme celui-là on stocke les valeurs concaténées des 3 parties de la clé. Par exemple pour la ligne où word_text='toto', forumid=12 et word_id=8, la valeur dans l'index sera : toto128.
Lors d'une recherche dans l'index, c'est la partie gauche (word_text) qui va être examinée en premier. Or dans ta requête cette partie gauche peut prendre beaucoup de valeurs différentes :

Code:

w.word_text IN ('ardente', 'bref', 'cause', 'cest', 'effectivement', 'euh', 'inspirant', 'plus', 'très', 'une', 'vivifiante')

Donc la recherche sera probablement plus lente que si tu mets par exemple, forumid en première partie de la clé. Dans ce cas-là la recherche sera d'entrée de jeu plus restrictive donc a priori plus rapide.

Je ne sais pas si je me fais comprendre...

J'ai tout à fait compris, merci beaucoup, je vais modifier et voir si je ne peux pas faire la meme chose sur d'autres tables