Discussion :

[peurpeur]

Bonsoir à tous,

Je suis loin d'être un spécialiste de MySql.

J'utilise un pc en local:
Windows XP / 2 Gigas / 100 Giga DD / x86 / avec hébergement apache-php / MySql 5.1

et un serveur dédié distant:
Windows Server 2008 / 8 Gigas / 1 Téra DD / x64 / avec hébergement apache-php / MySql 5.7

Alors voici mon souci:

Une importation en ligne de commande d'un fichier de 40 Mo.

Code :

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

 mysql -h localhost -u administrateur --password=lemauvaispass base_world_1  < c:\a_importer.sql

Sous XP, l'importation mets 2 minutes.
Sur Serveur, l'importation mets 6/7 minutes.

Malgré deux jours d'essai à modifier la config Sql sur le serveur, je n'ai toujours pas d'idée sur le problème.

La seul différence que j'ai pu trouvé été la taille de la BDD.
En local j'ai 3/4 bases mais sur le serveur il y a 32 bases. A savoir que mon importation ne s'adresse qu'a une seul et même base.


Si une personne à une étincelle, qu'il poste !! merci bien à vous de prendre le temps de me lire.

Fred

[Artemus24]

Salut PeurPeur.

Je suis loin d'être un spécialiste de MySql.

Etes-vous un homme ou une femme ?

Sous XP, l'importation mets 2 minutes. Sur Serveur, l'importation mets 6/7 minutes.

Soit environ 3,5 fois plus.

La seul différence que j'ai pu trouvé est la taille de la BDD. En local j'ai 3/4 bases mais sur le serveur il y a 32 bases.

Déjà, je ne comprends pas trop ce que vous essayez de migrer de votre serveur local vers votre serveur dédié.
Normalement, vous devez transférer une et une seule base de données à la fois. Alors que signifie 3/4 en local et 32 sur le dédié ?

Il serait intéressant que vous nous donniez le nom de ces 3/4 et 32 bases !

Il est possible que vous ayez transféré aussi le cœur de votre SGBD : "mysql", "test", "performance_schema", "phpmyadmin", et sys".

A savoir que mon importation ne s'adresse qu'a une seul et même base.

Là, c'est plus clair, enfin je crois. La question que je me pose est de savoir comment avez-vous fait l'export de votre base sous phpmyadmin ?

On commence par sélectionner la base puis ensuite, on fait l'export en précisant le paramétrage que l'on désire avoir.
Entre autre, on peut préciser de mettre ou non dans le fichier sql la création de la base de données.
Si la création n'est pas présente dans le fichier, vous devez dans phpmyadmin de votre serveur dédié, créer la base de données, puis ensuite faire l'importation.

Il est important de créer et sélectionner la base si elle n'existe pas au préalable, avant de faire l'importation.
Si la base est précisée dans le fichier sql, vous avez juste à faire l'importation.

Une importation en ligne de commande d'un fichier de 40 Mo.

Avez-vous jetez un coup d'oeil dans votre fichier sql afin de savoir si c'est bien ce que vous désirez exporter vers votre serveur dédié ?
Il doit contenir que vos tables + insert de votre base de données.

Il serait intéressant de me communiquer votre fichier sql afin que je voie ce que vous avez fait ==> artemus @ jcz.fr !

@+

[peurpeur]

Bonjour Artemus24,

Déjà, je ne comprends pas trop ce que vous essayez de migrer de votre serveur local vers votre serveur dédié.
Normalement, vous devez transférer une et une seule base de données à la fois. Alors que signifie 3/4 en local et 32 sur le dédié ?

Il serait intéressant que vous nous donniez le nom de ces 3/4 et 32 bases !

Il est possible que vous ayez transféré aussi le cœur de votre SGBD : "mysql", "test", "performance_schema", "phpmyadmin", et sys".

- J'ai crée un programme qui permets d'analyser des statistiques, ensuite celui-ci crée un ou plusieurs fichiers SQL qui sont injectés dans la BDD.
Je fais mes tests et débogages sur le PC local XP. Ensuite quand tout fonctionne correctement j'implante le programme sur le serveur.

- C'est en vérifiant le programme implanté sur le serveur que je me suis aperçu qu'il mettait beaucoup plus de temps à injection le fichier SQL crée que sur le local XP.

- J'ai donc fait des tests: Mettant le programme de coté, je n'ai pris que le fichier SQL crée par le programme et je l'ai implanté manuellement sur le XP et sur le Serveur. Et c'est la que je me suis aperçu de la lenteur de l'injection sur le serveur.

Les tests sur serveur:
-Injection via phpMyAdmin,
-Injection via ligne de command mysql,
-Des centaines de test en modifiant la configuration de mysql (avec redémarrage),
-Modification de la structure du fichier SQL.
-J'ai exporté via phpMyAdmin la base concerné qui venait d’être injecter par XP en 2 minutes pour ensuite l'injecter sur le serveur.
- Ajout de Lock table, creat table dans le fichier SQL, etc..
Rien n'y fait.
En local toujours moins de 2 minutes et 5/6 minutes sur le serveur.


Ci-joint:
le fichier SQL
etat sql pendant l'injection sur le serveur
etat1 pendant l'injection sur le serveur

Etes-vous un homme ou une femme ?

Si cela peux aider, je peux vous le dire par MP (C etait la petite pointe d humour)

Amicalement,
Fred

[Artemus24]

Salut PeurPeur.

Amicalement,
Fred

Je me doutais que vous étiez un homme, mais à coté du drapeau français, vous avez sélectionné le symbole de la planète Venus qui indique que vous êtes une femme.
Vous feriez bien de corriger cela !

Je ne suis pas arrivé à dézipper votre fichier sql ==> error 0x80004005.
Pouvez-vous m'envoyer le fichier non compressé à l'adresse email que je vous ai communiqué ? 40 Mo c'est pas énorme.

Je voie que vous avez la dernière version MySql --> 5.7.17 version 64 bits.

@+

[peurpeur]

Je viens de l'envoyer à l'instant.

Par hasard en cliquant sur ton site, je viens de me rendre compte que c'est celui-ci que j'ai le plus utilisé pour essayer de résoudre mon souci. Comme quoi ..... le monde restera petit.

[Artemus24]

Salut PeurPeur.

J'ai bien reçu votre email !

J'ai dû installé "7z" car je n'ai que unzip.exe pour dézipper les fichiers compressés.
La taille du fichier est de 46,2 Mo sur mon disque.

J'ai importé votre fichier SQL depuis phpmyadmin et cela m'a pris environ 4 minutes 40 secondes de chargement.
C'est plus du double que sur votre ordinateur Windows XP.

Au total 1300 requêtes exécutées et j'ai vérifié, il y a 234 tables.
Et j'ai qu'une seule base de données que j'ai créé avant l'import sous le nom de Fred, comme cela était indiqué dans le fichier sql.

Vous utilisez la version phpmyadmin 4.0.10.18. La dernière version est la 4.6.6.
J'ai jeté un coup d’œil au fichier SQL, je n'ai rien vu de particulier, mais il y a des manques.

1) il n'est pas nécessaire de mettre la colonne "position" dans les insert, car vous auriez pu transformer cette colonne en "auto_increment".
Cela aurait permis de réduire un peu la volumétrie de votre fichier SQL.

2) pour gagner un peu de temps, vu que c'est un chargement, vous pouvez désactiver certains contrôles qui prennent du temps.

Code :

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

1
2
3
4
SET AUTOCOMMIT = 0;
SET SESSION query_cache_type   = off;
SET SESSION unique_checks      = 0;
SET SESSION foreign_key_checks = 0;

Et après le chargement, vous pouvez les remettre en fonction :

Code :

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

1
2
3
4
SET AUTOCOMMIT = 1;
SET SESSION query_cache_type   = on;
SET SESSION unique_checks      = 1;
SET SESSION foreign_key_checks = 1;

3) normalisez un peu mieux vos tables :

Code :

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

1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS `test`;
 
CREATE TABLE `test`
( `id`       integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
 ...
) ENGINE=InnoDB
  DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
  ROW_FORMAT=COMPRESSED;

Je n'ai pas vu de clef primaire dans vos tables.

4) idem mais sur la base de données.

Code :

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

1
2
3
4
5
6
7
DROP DATABASE IF EXISTS `fred`;
 
CREATE DATABASE `fred`
	DEFAULT CHARACTER SET `utf8`
	DEFAULT COLLATE       `utf8_general_ci`;
 
USE `fred`;

5) testez la différence entre un "autocommit" à 1 et un "autocommit" à 0.
Il sera peut-être nécessaire de mettre des commit afin de réduire le stockage dans les journaux.

@+

[Artemus24]

Salut PeurPeur.

Pourquoi ne pas fusionner vos tables en une seule ?
Voici une procédure stockée qui réalise cela. A vous de la normaliser à votre convenance.

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `point`
--------------
 
--------------
CREATE TABLE `point`
( `num`       smallint unsigned NOT NULL,
  `ident`     smallint unsigned NOT NULL,
  `position`  integer  unsigned NOT NULL,
  `points`    integer  unsigned NOT NULL,
  `pays`      char(02)          NOT NULL,
  `etat`      char(04)          NOT NULL,
  `id_count`  integer  unsigned NOT NULL,
  `id_pa`     integer  unsigned NOT NULL,
  primary key (`num`,`position`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
drop procedure if exists `load_table`
--------------
 
--------------
create procedure `load_table` ()
DETERMINISTIC
NO SQL
BEGIN
DECLARE _fin   INTEGER UNSIGNED DEFAULT 1;
 
DECLARE _line  VARCHAR(255)    DEFAULT "";
DECLARE _line1 VARCHAR(255)    DEFAULT "insert into `point` (`num`,`ident`,`position`,`points`,`pays`,`etat`,`id_count`,`id_pa`) select ";
DECLARE _line2 VARCHAR(255)    DEFAULT ",position,points,pays,etat,id_count,id_pa  from fred.";
 
DECLARE _name  VARCHAR(255);
DECLARE _num   INTEGER UNSIGNED;
DECLARE _rang  INTEGER UNSIGNED;
 
DECLARE _tab     CURSOR FOR SELECT table_name, cast(concat(substr(table_name,8,2), right(concat('00',substr(table_name,11,2)), 2)) as unsigned) as num
                 FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA = 'fred' ORDER BY table_name;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
 
 
SET   _rang = 1;
OPEN  _tab;
FETCH _tab INTO _name, _num;
WHILE (_fin)
DO
  SET @line = concat(_line1, _rang, ',', _num, _line2, _name, ';');
 
             prepare stmt from @line;
             execute stmt;
  deallocate prepare stmt;
 
  SET _rang = _rang + 1;
  FETCH _tab INTO _name, _num;
END WHILE;
CLOSE _tab;
END
--------------
 
--------------
CALL load_table()
--------------
 
--------------
select * from point limit 20
--------------
 
+-----+-------+----------+-----------+------+------+----------+-------+
| num | ident | position | points    | pays | etat | id_count | id_pa |
+-----+-------+----------+-----------+------+------+----------+-------+
|   1 |     0 |        1 | 276007310 | en   |      |   149311 |   117 |
|   1 |     0 |        2 | 254238427 | de   | v    |   158181 |   115 |
|   1 |     0 |        3 | 188812762 | pl   |      |   152918 |   117 |
|   1 |     0 |        4 | 167926906 | pl   | v    |   153346 |   117 |
|   1 |     0 |        5 | 145092014 | en   |      |   149591 |   117 |
|   1 |     0 |        6 | 131578049 | fr   | v    |   155773 |   119 |
|   1 |     0 |        7 | 123976590 | fr   |      |   121003 |   119 |
|   1 |     0 |        8 | 112836423 | de   |      |   101898 |   105 |
|   1 |     0 |        9 | 109529870 | es   |      |   112285 |   106 |
|   1 |     0 |       10 | 108126945 | en   |      |   149596 |   117 |
|   1 |     0 |       11 | 102640762 | de   |      |   108505 |   105 |
|   1 |     0 |       12 | 101374657 | fr   |      |   132023 |   123 |
|   1 |     0 |       13 |  98328084 | pl   |      |   153342 |   117 |
|   1 |     0 |       14 |  97842241 | de   |      |   108255 |   105 |
|   1 |     0 |       15 |  97629294 | en   | v    |   149810 |   117 |
|   1 |     0 |       16 |  97560494 | es   |      |   146939 |   106 |
|   1 |     0 |       17 |  97536327 | tr   |      |   100250 |   116 |
|   1 |     0 |       18 |  96715217 | fr   |      |   165655 |   106 |
|   1 |     0 |       19 |  96174100 | pl   |      |   153038 |   117 |
|   1 |     0 |       20 |  95251681 | fr   |      |   125176 |   106 |
+-----+-------+----------+-----------+------+------+----------+-------+
--------------
ANALYZE TABLE `point`
--------------
 
+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| base.point | analyze | status   | OK       |
+------------+---------+----------+----------+
--------------
SHOW INDEX FROM `point`
--------------
 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| point |          0 | PRIMARY  |            1 | num         | A         |         219 |     NULL | NULL   |      | BTREE      |         |               |
| point |          0 | PRIMARY  |            2 | position    | A         |     1210490 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
--------------
SHOW TABLE STATUS like '%point%'
--------------
 
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------------+---------+
| Name  | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options        | Comment |
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------------+---------+
| point | InnoDB |      10 | Compressed | 1210490 |             25 |    31244288 |               0 |            0 |   4718592 |           NULL | 2017-02-11 20:55:20 | 2017-02-11 20:56:08 | NULL       | latin1_general_ci |     NULL | row_format=COMPRESSED |         |
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------------+---------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

La colonne "rang" est incrémenté de 1 selon l'ordre des tables.
La colonne "num" est la partie numéroque du nom de votre table.
Exemple : "points_22_17" donnera "2217". et "points_11_3" donnera "1103".

@+

[peurpeur]

Bonjour Artemus24,

Tout d’abord un grand merci pour l'aide et le suivi.

Les paramètres :

Code :

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

1
2
3
4
SET AUTOCOMMIT = 0;
SET SESSION query_cache_type   = off;
SET SESSION unique_checks      = 0;
SET SESSION foreign_key_checks = 0;

m'ont permis de baisser légèrement la durée d'insertion.

A défaut,
mon programme mettait (5 minutes de calculs et 5 minutes d'injection) X 9 = 90 minutes

J'ai donc rectifié mes algorithmes pour qu'ils soient plus rapides, ajouté tes paramètres et au lieu d'injecter qu'un seul ou deux fichiers, j'injecte en même temps 10 fichiers qui ont été découpés au préalable.

mon programme mets maintenant (2 minutes** de calculs et 3 minutes d'injection) X 9 = 45 minutes.

Je vais donc rester avec cette solution. Mais je suis un peu frustré donc je reviendrais sur ce sujet.




** Oui, ok passer de 5 minutes à 2 minutes, j’avais vraiment fait un programme de fainéant :-)


Encore merci à toi pour ton dévouement.

Fred

[peurpeur]

Re hello à vous tous,

J'ai enfin trouvé la solution:

Import fichier avec innodb_file_per_table=1 = 127 secondes.
Import fichier avec innodb_file_per_table=0 = 37 secondes.

En fin de compte sur l'ordinateur XP, c etait mis en innodb_file_per_table=0 par défaut et sur le serveur c'est mis à 1.

De la ma question:

Est-il possible de définir une base en innodb_file_per_table=0 et d'autres bases en innodb_file_per_table=1.

[Artemus24]

Salut peurpeur.

Je ne crois pas que cela soit une bonne idée de procéder ainsi.
La variable système "innodb_file_per_table" permet de dissocier les index et la table de l'espace système de MySql.
Autrement dit, vous avez la possibilité de créer un tablespace pour chaque table et index.

En mettant "0", vous ne pouvez pas répartir sur plusieurs disques, vos tables de plusieurs bases de données.
Et d'avoir la possibilité d'optimiser les accès à vos données, dans le cas des partitions.
Ou encore avoir la possibilité de comprimer les données afin qu'ils occupent moins de place sur le disque.
Il y a en général un seul descripteur par tablespace, ce qui peut avoir une grosse influence sur la performance des accès.

Je vous conseille de lire la documentation mysql à ce sujet : https://dev.mysql.com/doc/refman/5.7...blespaces.html

Est-il possible de définir une base en innodb_file_per_table=0 et d'autres bases en innodb_file_per_table=1.

Non, ce n'est pas possible.

Au lieu de vous concentrer sur quelque chose que vous faites de temps en temps, vous feriez mieux de normaliser vos tables en une seule et de transférer les données que sur ce qui est nouveau.
Ainsi vous allez réduire considérablement le temps de transfert.

@+

[Fabien Celaia]

J'ai fait l'export/import d'une base innodb avec les 2 options (innodb_file_per_table activé ou non) et quelques tables de plusieurs dizaines de millions de lignes
La différence n'est pas énorme...

PAR CONTRE, l'import via mysql est calamiteux (ben oui... il travaille lignes à lignes...)

export = 40 minutes
import = 14 heures