performances pour sauvegarder le résultat d'une grosse requête

**ben500fr** · 11/10/2019, 14h59

Bonjour a tous,

Je dois sauvegarder dans un fichier le résultat d'un requête mySql qui me renvoie 3 millions de lignes.
Voici le code que j'utilise:

Code :

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

1
2
3
4
5
6
7
8
9
10
 
$fh = fopen($csv_files[$i], 'w');
$sql = "SELECT UNIX_TIMESTAMP(`TIME2`) AS the_time2, `speed` AS speed, `friction` AS friction FROM `$theMnemo` WHERE `time` >= '$starts[$i]' AND `time` <= '$stops[$i]' AND NOT `speed` = 0 AND NOT `friction` = 0";
$req = mysql_unbuffered_query($sql);
while( $data = mysql_fetch_assoc($req) )
{
	fwrite($fh, $data['the_time2']."\t".$data['speed']."\t".$data['friction']."\n");
	$nb_points_array[$i]++;
}
fclose($fh);

Cela fonctionne très bien, mais ça prend presque une minute. La question est simple: avez vous une idée pour améliorer les performances?

Etant sous linux, j'ai essayé d’écrire le fichier dans la RAM (tmpfs) plutôt que sur le disque mais ça ne change rien.
Pendant que le code PHP tourne, le processus MySQL a le statut "Sending data".
Les serveurs sql et http/php sont sur la même machine.

Merci d'avance!!!

**Celira** · 11/10/2019, 16h24

A titre indicatif, sur mon poste de dev en local, générer un fichier texte de 3 millions de lignes me prend 30 secondes (avec une boucle for qui écrit "Ligne n°$i") Tu peux éventuellement tester le temps que ça prend sur ton serveur.

Donc, basiquement, on a deux pistes :
- la requête proprement dite : ça c'est du ressort de la gestion de la base (indexes, tout ça...)
- la lecture des données côté PHP et l'écriture du fichier. En dehors du fait que tu utilises une fonction de l'API mysql (qui est supprimée en PHP 7 et qui va te poser problème à plus ou moins long terme), je ne suis pas sûre qu'on puisse faire mieux

**rawsrc** · 11/10/2019, 19h30

Salut,

je pense que c'est optimisable :

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
$sql = <<<sql
SELECT CONCAT(UNIX_TIMESTAMP(`TIME2`), "\t", `speed`, "\t", `friction`, "\n") AS txt
  FROM `{$theMnemo}` 
 WHERE `time` >= '{$starts[$i]]' 
       AND `time` <= '{$stops[$i]}' 
       AND NOT `speed` = 0 
       AND NOT `friction` = 0
sql;
$req  = mysql_unbuffered_query($sql);
$data = [];
while ($row = mysql_fetch_assoc($req)) {
    $data[] = $row['txt'];
    $nb_points_array[$i]++;
}
file_put_content($csv_files[$i], implode('', $data));

**ben500fr** · 14/10/2019, 11h25

Envoyé par Celira

A titre indicatif, sur mon poste de dev en local, générer un fichier texte de 3 millions de lignes me prend 30 secondes (avec une boucle for qui écrit "Ligne n°$i") Tu peux éventuellement tester le temps que ça prend sur ton serveur.

Voici mon petit code de test:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
<?php
$time_1 = microtime(true);
$fh = fopen('test_3_million', 'w');
for ($i=0; $i<3000000; $i++)
{
	fwrite($fh, "Lign $i\n");
}
fclose($fh);
$time_2 = microtime(true);
echo "Duration: ".round(($time_2-$time_1)*1, 3)." seconds";
?>

Quand je crée le fichier sur le disque local: 7 secondes.
Quand je crée le fichier sur un disque virtuel monte dans la ram: 5 secondes.
Pour info le serveur est virtualise. Son hote VMware est un gros bébé qui virtualise dans les 150 ordinateurs (serveurs et postes clients).

Envoyé par Celira

En dehors du fait que tu utilises une fonction de l'API mysql

De quelle fonction parles tu? Je suis toujours preneur d'une amélioration

En utilisant le code de rawsrc:

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
<?php
include("config.php");
$db = mysql_connect($db_host, $db_user, $db_passwrod);
 
$time_1 = microtime(true);
$sql = <<<sql
SELECT CONCAT(UNIX_TIMESTAMP(`TIME2`), "\t", `speed`, "\t", `friction`, "\n") AS txt
 FROM `M083`.`RW4_05`
 WHERE `time` >= '2018-01-01 00:00:00'
       AND `time` <= '2019-10-04 00:00:00'
       AND NOT `speed` = 0 
       AND NOT `friction` = 0
sql;
$req  = mysql_unbuffered_query($sql);
$data = [];
while ($row = mysql_fetch_assoc($req)) {
    $data[] = $row['txt'];
}
file_put_contents('test_1', implode('', $data));
$time_2 = microtime(true);
echo "Duration test 1: ".round(($time_2-$time_1)*1, 3)." seconds\n<br />";
 
 
$time_1 = microtime(true);
$fh = fopen('test_2', 'w');
$sql = "SELECT UNIX_TIMESTAMP(`TIME2`) AS the_time2,
	`speed` AS speed,
	`friction` AS friction
	FROM `M083`.`RW4_05`
	WHERE `time` >= '2018-01-01 00:00:00'
	AND `time` <= '2019-10-04 00:00:00'
	AND NOT `speed` = 0
	AND NOT `friction` = 0";
$req = mysql_unbuffered_query($sql);
while( $data = mysql_fetch_assoc($req) )
{
	fwrite($fh, $data['the_time2']."\t".$data['speed']."\t".$data['friction']."\n");
}
fclose($fh);
$time_2 = microtime(true);
echo "Duration test 2: ".round(($time_2-$time_1)*1, 3)." seconds\n<br />";
 
mysql_close();
 
?>

Resultat:

Code :

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

1
2
Duration test 1: 47.614 seconds 
Duration test 2: 47.55 seconds

Donc c'est kifkif mais bien tenté

**mathieu** · 14/10/2019, 13h01

Envoyé par ben500fr

De quelle fonction parles tu? Je suis toujours preneur d'une amélioration

vous pouvez trouver plus d'informations sur la remarque de Celira sur la page suivante :
https://www.php.net/manual/fr/intro.mysql.php

en ce qui concerne votre question initiale je ne suis pas sur d'avoir bien compris.
vous voulez économiser le temps de calcul de la requête en stockant le résultat dans un genre de fichier de cache ?

**ben500fr** · 14/10/2019, 13h42

Envoyé par mathieu

vous pouvez trouver plus d'informations sur la remarque de Celira sur la page suivante :
https://www.php.net/manual/fr/intro.mysql.php

Merci! Vous me conseillez mysqli ou pdo?

Envoyé par mathieu

en ce qui concerne votre question initiale je ne suis pas sur d'avoir bien compris.
vous voulez économiser le temps de calcul de la requête en stockant le résultat dans un genre de fichier de cache ?

Je récupère 3 millions de points dans une table SQL, je les stockent dans un fichier, et je les donnent au logiciel gnuplot qui en fait un graph pour mon interface html.
En gros j'ai fait une page web qui permet de rejouer les telemetries de satellites. Mon besoin initial etait definit ici: https://www.developpez.net/forums/d1...s-issus-mysql/

**Celira** · 14/10/2019, 15h46

Personnellement, je préfère PDO, de loin. ça donnerait quelque chose comme ça (en utilisant le script de connexion de

PDO une soupe et au lit !)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
$db = include 'db_mysql.php';
 
$sql = "SELECT UNIX_TIMESTAMP(`TIME2`) AS the_time2, `speed` AS speed, `friction` AS friction FROM `$theMnemo` WHERE `time` >= :start AND `time` <= :stop AND NOT `speed` = 0 AND NOT `friction` = 0";
 
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); // unbuffered
 
$stmt = $db->prepare($sql);
$stmt->execute([':start' => $starts[$i], ':stop' => $stops[$i]]);
 
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// ...

Aucune idée de si ça a un impact sur les performances.

performances pour sauvegarder le résultat d'une grosse requête

PHP & Base de données

Vue hybride

Discussions similaires

Partager

Partager