Discussion :

[thom33]

Bonsoir,

J'ai quelques problèmes de lenteur avec MySQL. J'ai pour projet de générer quelques statistiques à partir des données d'une jeu par navigateur.

Le jeu met à disposition un fichier .sql qui me permet de générer tous les jours une table de ce type là avec pour le moment 23k entrées qui vont grimper assez vite vers 40k je pense :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
CREATE TABLE IF NOT EXISTS `x_world` (
  `id` int(9) unsigned NOT NULL DEFAULT '0',
  `x` smallint(3) NOT NULL DEFAULT '0',
  `y` smallint(3) NOT NULL DEFAULT '0',
  `tid` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `vid` int(9) unsigned NOT NULL DEFAULT '0',
  `village` varchar(20) NOT NULL DEFAULT '',
  `uid` int(9) NOT NULL DEFAULT '0',
  `player` varchar(20) NOT NULL DEFAULT '',
  `aid` int(9) unsigned NOT NULL DEFAULT '0',
  `alliance` varchar(8) NOT NULL DEFAULT '',
  `population` smallint(5) unsigned NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Je fais tourner ça en local pour le moment, après ça ira peut-être sur un petit hébergement pas cher.
Mon PC est relativement performant (i5 540 2x 2.5GHz, 8Go de RAM, wamp x64)

Premier problème :
la récupération des données

Le fichier .sql se présente sous la forme de 22k lignes avec sur chaque ligne une requête INSERT INTO.

12 minutes pour charger les valeurs en utilisant l'importation via la console mysql
c'est un temps un peu affreux je trouve.

Je dois exécuter l'importation une seule fois par jour, ça peut prendre un peu de temps, mais pas 12 minutes.
De plus je voudrais à terme faire mes stats pour chaque monde du jeu (à la louche une 10aine) donc pas possible de prendre autant de temps pour chaque importation.

Second problème :
Et là quand on commence à faire des requêtes sérieuses pour générer les tables qui serviront aux stats à partir des archives et de la nouvelle table c'est la galère, je n'ai même pas eu le courage de laisser aller au bout de l'exécution de toutes les requêtes.

Je génère une unique table finale assez lourde et très riche d'informations (avec une info redondante la 'poptot' d'un joueur) afin de ne pas avoir à faire des jointures par la suite lors de l'utilisation de ces données. Je n'ai que des lectures à faire et donc ça me permettra (je pense) d'accélérer mes requêtes.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- creation de la table village :
-- ------------
 
-- la liste des vivi + évolution de pop
DROP TABLE IF EXISTS vivitemp ;
 
CREATE TABLE vivitemp AS
(
SELECT
 
x_world.id AS id,
x_world.vid AS vid,
x_world.village AS village,
x_world.x AS x,
x_world.y AS y,
x_world.tid AS tid,
x_world.uid AS uid,
x_world.player AS player,
x_world.aid AS aid,
x_world.alliance AS alliance,
x_world.population AS population,
(CAST(`x_world`.`population` AS signed) - CAST(`x_world08022013`.`population` AS signed)) AS popevo5,
(CAST(`x_world`.`population` AS signed) - CAST(`x_world06022013`.`population` AS signed)) AS popevo7
 
FROM `x_world` LEFT OUTER JOIN `x_world08022013`
ON `x_world`.`vid` = `x_world08022013`.`vid` 
LEFT OUTER JOIN `x_world06022013`
ON `x_world`.`vid` = `x_world06022013`.`vid` 
);
 
ALTER TABLE `vivitemp` ADD UNIQUE (`vid`);
 
UPDATE `vivitemp` SET `vivitemp`.`popevo5` = `vivitemp`.`population` WHERE `vivitemp`.`popevo5` IS NULL ;
UPDATE `vivitemp` SET `vivitemp`.`popevo7` = `vivitemp`.`population` WHERE `vivitemp`.`popevo7` IS NULL ;
 
-- les joueurs pour avoir la pop totale
 
DROP TABLE IF EXISTS joueurstemp ;
 
CREATE TABLE joueurstemp AS
(
 
SELECT
x_world.uid AS uid2,
SUM(x_world.population) AS poptot
 
FROM `x_world`
 
GROUP BY uid2
);
 
ALTER TABLE `joueurstemp` ADD UNIQUE (`uid2`);
 
-- table finale
 
DROP TABLE IF EXISTS villages ;
 
CREATE TABLE villages AS
(
SELECT *
 
FROM
vivitemp, joueurstemp
 
WHERE
vivitemp.uid=joueurstemp.uid2
);
 
ALTER TABLE `villages` ADD UNIQUE (`vid`);

La première requête (CREATE TABLE vivitemp) en remplaçant les 3 tables différentes (xworld, xworld080213 et xworld060213) par 3 fois la même (3x xworld) met plus d'1/2h pour s’exécuter. J'ai testé rapidement avec MS SQL Server 2008 et 18 secondes pour la même requête....

Ai-je raté quelque chose dans la config de MySQL pour qu'il y ai une différence pareille ?
Comment puis-je optimiser mon histoire pour rester si possible avec MySQL (plus pratique à utiliser et surtout possibilité de passer le tout sur linux pour une utilisation en prod) ?

Merci d'avance pour vos conseils et vos réponses !

[ericd69]

salut,

pourquoi générer ce .sql plutôt que d'exécuter directement les actions sur mysql? la console c'est bon pour des tests...
tu peux gagner encore en faisant des insert multiple...

le temps de création de ta super table consolidé par rapport à des jointures sur des tables bien indexées... ça se discute...

[thom33]

tout d'abord merci de ta réponse,

le .sql c'est ce qu'on me fourni je n'ai pas la main sur son format.
Je suis obligé d'importer mes données comme ça.
L'import se fait via un page php (pour l'interface utilisateur) qui récupère le fichier et qui exécute chaque ligne du fichier comme requête, une jolie boucle while avec des requêtes donc.

ensuite pour la table consolidée tu penses que ça peut -être plus lent que des jointures bien indexées ?
Même avec les calculs ?

Et puis en fait je ne vois pas comment je peux récupérer l'info joueur poptot à partir de ma table de départ en une seule requête.

[ericd69]

par exemple, avec un:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select uid,sum(population) as poptot
from x_world
group by uid # ou player si uid ne le représente pas

[thom33]

un joueur a plusieurs villages, 1 entrée par village dans la table

donc si je veux faire la somme des populations des villages pour chaque joueur en utilisant un group by je n'aurai pas 1 ligne pour chaque village, mais 1 ligne pour chaque joueur. Et ce qui m'intéresse c'est d'avoir une réponse pour chaque village.

pour en revenir à la question initiale, la lenteur de l'importation ou la lenteur de l’exécution de la requête sont-elles dues à une mauvaise config de MySQL ou simplement parce que les perf de MySQL sont pas terribles sur les jointures externes ?

Pourtant mes tables ne sont pas si grosses, 2.5Mo/table c'est rien du tout, ou c'est moi qui ne me rend pas du tout compte des limites de MySQL ?

[ericd69]

à l'importation il n'y a pas de jointure :mouarf

pour l'importation c'est bizarre que ça te prenne autan de temps vu que j'ai déjà fais des imports plus lourd plus vite...

essaye de l'importer via phpmyadmin

le cache console est peut-être la cause c'est quoi ta commande d'import pour le fichier?

[thom33]

ouais c'est vrai, pas de problème de jointure pour l'importation

en console j'ai utilisé la commande, et lors d'un nouveau test (à l'instant) je n'en ai eu que pour 1min :
mysql -u root nomdemabdd < map.sql

Je viens de tester via phpmyadmin => environ 1min

Via mon script php (joint ci dessous) j'ai environ 2min

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
mysql_query("CREATE TABLE `x_world` (`id` int(9) unsigned NOT NULL default '0',  `x` smallint(3) NOT NULL default '0',  `y` smallint(3) NOT NULL default '0',  `tid` tinyint(1) unsigned NOT NULL default '0',  `vid` int(9) unsigned NOT NULL default '0',  `village` varchar(20) NOT NULL default '',  `uid` int(9) NOT NULL default '0',  `player` varchar(20) NOT NULL default'', `aid` int(9) unsigned NOT NULL default '0', `alliance` varchar(8) NOT NULL default '',  `population` smallint(5) unsigned NOT NULL default '0',  UNIQUE KEY `id` (`id`))", $connection)  ;
$mapurl = "http://$s.travian.$lang/map.sql$gz"; // le fichier
$lignes = file($mapurl, FILE_TEXT &&  FILE_SKIP_EMPTY_LINES); 
        for ($i=0; $i<count($lignes);$i++){ //requêtes ligne à ligne
                @mysql_query($lignes[$i], $connection)  ;
        }

les temps me paraissent normaux là du coup... pourtant pas beaucoup de différence avec le test précédent, si ce n'est que j'ai la version 32bits de wamp au lieu de la 64bits (dual boot de 2 windows) j'ai peut-être raté un truc dans l'installation de la version 64bits...

j'ai essayé la requête de création de la table 'vivitemp' (les 2 jointures)

résultat = très long

en revanche j'ai réfléchi un peu à l'histoire de jointure avec les bons index dont tu as parlé et changé la condition sur la jointure pour ne pas utiliser 'vid' mais 'id' (les 2 sont uniques pour chaque entrée dans la table, mais id est la clé primaire, et il n'y a pas d'index sur vid)

=> résultat de la requête quasi-instantané

la morale de cette histoire bien choisir ses index !

dernière question à propos de l'importation du coup.
Une idée pour accélérer la chose ? 2min c'est pas mal, mais si ça peut s'améliorer je suis preneur de toute idée.

[Exia_]

Bonsoir,

tu peut drop les index le temps de l'insertion et les construires après avoir fini l'insertion.

[thom33]

tu peut drop les index le temps de l'insertion et les construires après avoir fini l'insertion.

Merci pour l'idée, je viens de tester

création de table sans l'index, puis insertions, puis création d'index :
Temps d'exécution : 67.26 secondes

création de table + index, puis insertions :
Temps d'exécution : 68.36 secondes

le volume de données est peut-être pas assez gros pour que ça ai un intérêt réel.
mais bon c'est toujours une optimisation bonne à prendre

[ericd69]

oui c'est pas très significatif ça le sera peut-être plus quand tu seras à 40k lignes...

oui vaut mieux utiliser la version 64 bits et tu peux avoir eu un problème passager...

[thom33]

En tout cas merci pour le temps consacré à mon problème.

je marque ça comme résolu

[Fred_34]

Tu pourrais essayer de grouper tes inserts.
Au lieu de faire 40K inserts d'une ligne, essaie d'en faire par exemple 40 de 1000 lignes.
Tu seras obligé de retraiter ton .sql dans PHP, mais ça vaut peut être le coup.

[thom33]

Ha oui ça vaut terriblement le coup !
le traitement du fichier est très rapide et le temps d'insertion passe de 65 à 11 secondes !

merci du conseil !

Par contre la seule solution que j'ai trouvée (pour le moment) pour importer le nouveau fichier est de passer par un exec pour importer le fichier .sql et ça c'est un peu dégueuu comme méthode.

edit :

bon bah trouvé, au lieu de générer un fichier je case tout dans une chaine de caractère. Je pensais faire péter la limite de taille de variable de php mais non en fait.

Voilà le code final permettant de récupérer le fichier sql, de le traiter et de faire les insertions, si ça intéresse des gens :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
        $lignes = file($mapurl, FILE_TEXT &&  FILE_SKIP_EMPTY_LINES); // directement en tableau
 
		$masque[0] = "#;\n#";
		$masque[1] = "#INSERT INTO `x_world` VALUES#";
 
		$remplacement[0] = ", ";
		$remplacement[1] = "";
 
		$nb_lignes = count($lignes) ;
		$max_ligne_par_insertion = 500;
 
		for ($i=0; $i<$nb_lignes;$i++)
		{
			if($i%$max_ligne_par_insertion == ($max_ligne_par_insertion-1)){ //dernière ligne
				$nouveau .= preg_replace($masque[1],$remplacement[1],$lignes[$i]);
				@mysql_query($nouveau, $connection)  ;
			}
			else if($i%$max_ligne_par_insertion != 0) // ligne normale
				$nouveau .= preg_replace($masque,$remplacement,$lignes[$i]);
			else //première ligne
				$nouveau = preg_replace($masque[0],$remplacement[0],$lignes[$i]);
		}

Temps d'exécution : 6.68 secondes

Merci encore pour les conseils !