Requête update très lente

**thierryRoussel** · 19/02/2018, 17h41

Bonjour,

je suis depuis 8 jours sur un problème de requête Update très lente.

En fait c'est une table avec un index sur l'identifiant, l'update se fait avec un where sur l'identifiant. Il y a 5000 enregistrements dans la table. Les 5000 sont updatés en lisant un csv.

Quand je fais cela chez moi sur un ordi de text (osx) cela dure 2 secondes maxi. Quand je le fait sur le VPS (4 coeurs, 8G de RAM) chez l'hébergeur, cela dure 30 à 40 secondes.

J'ai passé mysqltuner, et fait les modifs. Mais ça ne change absolument rien. Le innodb buffer est à 1G.

Qu'est-ce qui pourrait ralentir Mysql. 30 secondes pour 5000 updates simples c'est énorme.

Merci.

**Artemus24** · 21/02/2018, 18h17

Salut thierryRoussel.

Il nous faut :
--> votre fichier my.ini,
--> un exemple d ce que vous charger (fichier csv),
--> le load data local infile
--> votre table mysql

En général, je charge le fichier csv directement dans une table de travail puis ensuite je répartie les données dans les tables définitives.
Cela nécessite de créer un script mysql qui va gérer le tout.

Etes-vous seul sur l'ordinateur de travail quand vous faites votre manipulation?
Sinon à combien êtes-vous ?

@+

**thierryRoussel** · 22/02/2018, 01h11

Bonjour,

J'ai oublié de préciser, c'est un outil online, dans un wordpress, à destination des secrétaires de notre asso , mais j'ai loggé le début de traitement des updates, et la fin, et les 40 secondes sont bien pendant les updates. Donc ce n'est pas le temps de chargement du fichier, ni wordpress qui prend du temps. Ce sont uniquement les updates. Si je fais des inserts, c'est très rapide.

Autre précision, le csv est chargé dans un array, et le temps de log est calculé a partir de l'array, donc ce n'est pas la lecture du csv qui est lente.

voici le server.cnf :

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
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#
 
# this is read by the standalone daemon and embedded servers
[server]
 
# this is only for the mysqld standalone daemon
[mysqld]
 
 performance-schema=OFF
# default-storage-engine=MyISAM
# innodb_file_per_table=1
# max_allowed_packet=268435456
 
# max_connections=20
# max_user_connections=10
# symbolic-links=0
 tmp_table_size=256M
 join_buffer_size=256K
 query_cache_limit=16M
 query_cache_type=0
 query_cache_size=0
 query_cache_type=0
 max_heap_table_size=256M
 key_buffer_size=256M
 innodb_buffer_pool_size=2G
 innodb_file_per_table=ON
# innodb_log_file_size=256M
innodb_buffer_pool_instances=2
#table_open_cache=1024
#table_definition_cache=1024
thread_cache_size=4
#tmp_table_size=24M
#max_heap_table_size=24M
#open_files_limit=10000
 
#local-infile=0
# tmpdir = /run/mariadb
 
 
# this is only for embedded server
[embedded]
 
# This group is only read by MariaDB-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-5.5]
 
# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
# key_buffer_size=64M
# innodb_buffer_pool_size=128M
 
[mariadb-5.5]

La table est du style id,numéro de membre,nom,prenom,date de naissance,date d'inscription,genre(masculin 0, feminin 1),region (region de france) en innodb
index sur id et index sur numéro de membre.

et le csv numéro de membre,nom,prenom,date de naissance,date d'inscription,genre(masculin 0, feminin 1),region (region de france)

Le php lit le csv, si le numéro de membre n'existe pas, il le crée (les insert ne durent pas longtemps).

Si il n'y a pas le membre on écrase les données sans écraser l'id, car il y a des foreign key ailleurs.

Je fait un update avec des cast pour les dates, peut être est ce cela qui prend du temps :

Code :

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

1
2
UPDATE Membres SET  prenom=csv_prenom , nom=csv_nom , date_naissance=CAST(STR_TO_DATE(csv_date_naissance] , '%d/%m/%Y') AS DATETIME) , date_inscription=CAST(STR_TO_DATE(csv_date_inscription], '%d/%m/%Y') AS DATETIME) , genre=csv_genre  
WHERE numero_membre = csv_numero_membre ;

Comme dit plus haut , sur mon osx avec le même sql , moins de 2 seconde, pour le même programme. Sur le VPS 40 seconde pour 5000 updates.

J'ai testé la nuit, et il n'y avait personne en accès sur le serveur (log apache vide). Pas de cron.

Par contre c'est un VPS, donc je ne sais pas si un autre VPS sur le même serveur ne monopolise pas le disque dur.

**escartefigue** · 22/02/2018, 08h59

bonjour,

Une piste possible : index cluster très désorganisé, faites une réorg puis repassez le traitement

**thierryRoussel** · 22/02/2018, 09h59

le VPS est nouveau, l'intall de mariadb via yum, la db est toute nouvelle et chargée par un dump. L'optimize a été passé juste aprés le dump. et la première chose testée est ce programme, qui dés le début à pris 30 seconde.

**Artemus24** · 22/02/2018, 20h02

Salut thierryRoussel.

Envoyé par thierryRoussel

j'ai loggé le début de traitement des updates, et la fin, et les 40 secondes sont bien pendant les updates.

Des updates ? Je ne comprends pas.

Envoyé par thierryRoussel

Autre précision, le csv est chargé dans un array, et le temps de log est calculé a partir de l'array, donc ce n'est pas la lecture du csv qui est lente.

Si je comprends bien, vous chargez, à partir de php, votre fichier ".csv" dans un tableau.
Puis pour chaque occurrence de votre tableau, vous effectuez un "update" ???

Ce n'est pas ainsi que l'on travaille. Voici un exemple de chargement un fichier ".csv" avec répartition des données dans d'autres tables :

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
"quatre","Anglais","four"
"quatre","Allemand","vier"
"quatre","Italien","quattro"
"trois","anglais","three"
"cinq","Allemand","fünf"
"cinq","Italien","cinque"
"cinq","Anglais","five"
"\N","\N","vingt-quatre"
"six","Anglais","six"
"six","Allemand","sechs"
"six","Italien","sei"
"un","Espagnol","uno"
"deux","Espagnol","dos"
"trois","Espagnol","tres"
"quatre","Espagnol","cuatro"
"cinq","Espagnol","cinquo"
"six","Espagnol","seis"

Et voici le script mysql :

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
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS trav
--------------
 
--------------
CREATE TABLE trav (
  `id`    integer unsigned not null auto_increment primary key,
  `col1`  varchar(255)         null,
  `col2`  varchar(255)         null,
  `col3`  varchar(255)         null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA LOCAL INFILE 'fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ','
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (`col1`,`col2`,`col3`)
--------------
 
--------------
select * from trav
--------------
 
+----+--------+----------+--------------+
| id | col1   | col2     | col3         |
+----+--------+----------+--------------+
|  1 | quatre | Anglais  | four         |
|  2 | quatre | Allemand | vier         |
|  3 | quatre | Italien  | quattro      |
|  4 | trois  | anglais  | three        |
|  5 | cinq   | Allemand | fünf         |
|  6 | cinq   | Italien  | cinque       |
|  7 | cinq   | Anglais  | five         |
|  8 | NULL   | NULL     | vingt-quatre |
|  9 | six    | Anglais  | six          |
| 10 | six    | Allemand | sechs        |
| 11 | six    | Italien  | sei          |
| 12 | un     | Espagnol | uno          |
| 13 | deux   | Espagnol | dos          |
| 14 | trois  | Espagnol | tres         |
| 15 | quatre | Espagnol | cuatro       |
| 16 | cinq   | Espagnol | cinquo       |
| 17 | six    | Espagnol | seis         |
+----+--------+----------+--------------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS pere
--------------
 
--------------
CREATE TABLE pere
( `id`      integer unsigned not null auto_increment primary key,
  `nombre`  varchar(255)     not null,
  index `idx` (`nombre`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `pere` (`nombre`) value ('un'),('deux'),('trois')
--------------
 
--------------
select * from pere
--------------
 
+----+--------+
| id | nombre |
+----+--------+
|  2 | deux   |
|  3 | trois  |
|  1 | un     |
+----+--------+
--------------
insert into `pere` (`nombre`)
  select  distinct t.col1
    from  `trav` as t
   where  not exists (select 1 from `pere` as p where p.nombre = t.col1)
     and  t.col1 is not null
--------------
 
--------------
select * from pere
--------------
 
+----+--------+
| id | nombre |
+----+--------+
|  5 | cinq   |
|  2 | deux   |
|  4 | quatre |
|  6 | six    |
|  3 | trois  |
|  1 | un     |
+----+--------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS mere
--------------
 
--------------
CREATE TABLE mere
( `id`      integer unsigned not null auto_increment primary key,
  `langue`  varchar(255)     not null,
  index `idx` (`langue`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `mere` (`langue`) value ('Allemand'), ('Italien'), ('Anglais')
--------------
 
--------------
select * from mere
--------------
 
+----+----------+
| id | langue   |
+----+----------+
|  1 | Allemand |
|  3 | Anglais  |
|  2 | Italien  |
+----+----------+
--------------
insert into `mere`  (`langue`)
   select distinct  t.col2
              from  `trav` as t
  where not exists  (select 1 from `mere` as m  where m.langue = t.col2)
               and  t.col2 is not null
--------------
 
--------------
select * from mere
--------------
 
+----+----------+
| id | langue   |
+----+----------+
|  1 | Allemand |
|  3 | Anglais  |
|  4 | Espagnol |
|  2 | Italien  |
+----+----------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS fille
--------------
 
--------------
CREATE TABLE fille
( `id`          integer unsigned not null auto_increment primary key,
  `pere_id`     integer unsigned not null,
  `mere_id`     integer unsigned not null,
  `traduction`  varchar(255)     not null,
  unique index `idx` (`pere_id`,`mere_id`),
  CONSTRAINT `FK_PERE` FOREIGN KEY (`pere_id`) REFERENCES `pere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_MERE` FOREIGN KEY (`mere_id`) REFERENCES `mere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `fille` (`pere_id`,`mere_id`,`traduction`) value
  (1, 1, 'eins'), (2, 1, 'zwei'), (3, 1, 'drei'),
  (1, 2, 'uno'),  (2, 2, 'due'),  (3, 2, 'tre'),
  (1, 3, 'one'),  (2, 3, 'two'),  (3, 3, 'tree')
--------------
 
--------------
select * from fille
--------------
 
+----+---------+---------+------------+
| id | pere_id | mere_id | traduction |
+----+---------+---------+------------+
|  1 |       1 |       1 | eins       |
|  2 |       2 |       1 | zwei       |
|  3 |       3 |       1 | drei       |
|  4 |       1 |       2 | uno        |
|  5 |       2 |       2 | due        |
|  6 |       3 |       2 | tre        |
|  7 |       1 |       3 | one        |
|  8 |       2 |       3 | two        |
|  9 |       3 |       3 | tree       |
+----+---------+---------+------------+
--------------
insert into `fille` (`pere_id`,`mere_id`,`traduction`)
    select  p.id,
            m.id,
            t.col3
      from  `trav` as t
 
inner join  `pere` as p
        on  p.nombre = t.col1
 
inner join  `mere` as m
        on  m.langue = t.col2
 
     where  t.col3 is not null
 
        on  duplicate key update traduction = values(traduction)
--------------
 
--------------
select * from fille
--------------
 
+----+---------+---------+------------+
| id | pere_id | mere_id | traduction |
+----+---------+---------+------------+
|  1 |       1 |       1 | eins       |
|  2 |       2 |       1 | zwei       |
|  3 |       3 |       1 | drei       |
|  4 |       1 |       2 | uno        |
|  5 |       2 |       2 | due        |
|  6 |       3 |       2 | tre        |
|  7 |       1 |       3 | one        |
|  8 |       2 |       3 | two        |
|  9 |       3 |       3 | three      |
| 10 |       4 |       3 | four       |
| 11 |       4 |       1 | vier       |
| 12 |       4 |       2 | quattro    |
| 13 |       5 |       1 | fünf       |
| 14 |       5 |       2 | cinque     |
| 15 |       5 |       3 | five       |
| 16 |       6 |       3 | six        |
| 17 |       6 |       1 | sechs      |
| 18 |       6 |       2 | sei        |
| 19 |       1 |       4 | uno        |
| 20 |       2 |       4 | dos        |
| 21 |       3 |       4 | tres       |
| 22 |       4 |       4 | cuatro     |
| 23 |       5 |       4 | cinquo     |
| 24 |       6 |       4 | seis       |
+----+---------+---------+------------+
--------------
alter table `fille` drop   column `id`
--------------
 
--------------
alter table `fille` add    column `id` integer first
--------------
 
--------------
set @id:=0
--------------
 
--------------
update `fille` set id=(@id:=@id+1) order by mere_id, pere_id
--------------
 
--------------
alter table `fille` change column `id` `id` integer unsigned auto_increment primary key first
--------------
 
--------------
select * from fille
--------------
 
+----+---------+---------+------------+
| id | pere_id | mere_id | traduction |
+----+---------+---------+------------+
|  1 |       1 |       1 | eins       |
|  2 |       2 |       1 | zwei       |
|  3 |       3 |       1 | drei       |
|  4 |       4 |       1 | vier       |
|  5 |       5 |       1 | fünf       |
|  6 |       6 |       1 | sechs      |
|  7 |       1 |       2 | uno        |
|  8 |       2 |       2 | due        |
|  9 |       3 |       2 | tre        |
| 10 |       4 |       2 | quattro    |
| 11 |       5 |       2 | cinque     |
| 12 |       6 |       2 | sei        |
| 13 |       1 |       3 | one        |
| 14 |       2 |       3 | two        |
| 15 |       3 |       3 | three      |
| 16 |       4 |       3 | four       |
| 17 |       5 |       3 | five       |
| 18 |       6 |       3 | six        |
| 19 |       1 |       4 | uno        |
| 20 |       2 |       4 | dos        |
| 21 |       3 |       4 | tres       |
| 22 |       4 |       4 | cuatro     |
| 23 |       5 |       4 | cinquo     |
| 24 |       6 |       4 | seis       |
+----+---------+---------+------------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS poubelle
--------------
 
--------------
CREATE TABLE poubelle
( `id`    integer unsigned not null auto_increment primary key,
  `col1`  varchar(255)         null,
  `col2`  varchar(255)         null,
  `col3`  varchar(255)         null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `poubelle` (`col1`,`col2`,`col3`)
    select  col1,
            col2,
            col3
      from  `trav`
 
     where  col1 is null
        or  col2 is null
--------------
 
--------------
select * from poubelle
--------------
 
+----+------+------+--------------+
| id | col1 | col2 | col3         |
+----+------+------+--------------+
|  1 | NULL | NULL | vingt-quatre |
+----+------+------+--------------+
--------------
COMMIT
--------------
 
--------------
drop table `trav`
--------------
 
--------------
select      m.langue,
            p.nombre,
            f.traduction
      from  `fille` as f
 
inner join  `pere` as p
        on  p.id = f.pere_id
 
inner join  `mere` as m
        on  m.id = f.mere_id
--------------
 
+----------+--------+------------+
| langue   | nombre | traduction |
+----------+--------+------------+
| Allemand | un     | eins       |
| Allemand | deux   | zwei       |
| Allemand | trois  | drei       |
| Allemand | quatre | vier       |
| Allemand | cinq   | fünf       |
| Allemand | six    | sechs      |
| Italien  | un     | uno        |
| Italien  | deux   | due        |
| Italien  | trois  | tre        |
| Italien  | quatre | quattro    |
| Italien  | cinq   | cinque     |
| Italien  | six    | sei        |
| Anglais  | un     | one        |
| Anglais  | deux   | two        |
| Anglais  | trois  | three      |
| Anglais  | quatre | four       |
| Anglais  | cinq   | five       |
| Anglais  | six    | six        |
| Espagnol | un     | uno        |
| Espagnol | deux   | dos        |
| Espagnol | trois  | tres       |
| Espagnol | quatre | cuatro     |
| Espagnol | cinq   | cinquo     |
| Espagnol | six    | seis       |
+----------+--------+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 0
--------------
 
Appuyez sur une touche pour continuer...

Je vous communique mon fichier "my.ini" qui est plus complet que le votre.
A vous de prendre ce dont vous avez besoin :

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
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
# ********************* #
#                       #
#     Sphere "Bleu"     #
#                       #
# ********************* #
 
# ==================== #
#     MySql Client     #
# ==================== #
 
[client]
port   = 3306
socket = mysql
 
# ----------------------- #
#     access features     #
# ----------------------- #
 
user     = ????
password = ????
host     = 127.0.0.1
 
# --------------- #
#     Charset     #
# --------------- #
 
default-character-set = latin1
 
# ------------------------------ #
#     Load data local infile     #
# ------------------------------ #
 
loose-local-infile = on
 
# ==================== #
#     MySql Server     #
# ==================== #
 
[wampmysqld]
port   = 3306
socket = mysql
 
# -------------- #
#     Master     #
# -------------- #
 
server-id = 1
 
# ---------------#
#     Buffer     #
# ---------------#
 
join-buffer-size      = 512M
read-buffer-size      = 512M
read-rnd-buffer-size  = 512M
sort-buffer-size      = 512M
 
# --------------- #
#     Charset     #
# --------------- #
 
character-set-server     = latin1
collation-server         = latin1_general_ci
character-set-filesystem = latin1
 
init-connect             = 'SET collation_connection = latin1_general_ci; SET NAMES latin1 COLLATE latin1_general_ci'
 
# ------------------- #
#     Date & Time     #
# ------------------- #
 
default-time-zone = SYSTEM
 
explicit-defaults-for-timestamp = on
 
# ------------------- #
#     Directories     #
# ------------------- #
 
basedir = f:/Wamp/bin/mysql/mysql8.0.3/
datadir = f:/Wamp/bin/mysql/mysql8.0.3/data
tmpdir  = f:/Wamp/tmp
 
# -------------------------#
#     Full Text Search     #
# -------------------------#
 
ft-max-word-len  = 10
ft-min-word-len  = 1
ft-stopword-file = ""
 
# ---------------#
#     InnoDB     #
# ---------------#
 
default-storage-engine              = InnoDB
default-tmp-storage-engine          = InnoDB
 
innodb-adaptive-hash-index          = on
 
innodb-buffer-pool-dump-now         = off
innodb-buffer-pool-dump-at-shutdown = off
innodb-buffer-pool-instances        = 1
innodb-buffer-pool-load-at-startup  = OFF
innodb-buffer-pool-size             = 128M
 
innodb-data-file-path               = ibdata1:12M:autoextend
innodb-data-home-dir                = f:/Wamp/bin/mysql/mysql8.0.3/data/ibdata/
 
innodb-default-row-format           = compact
innodb-doublewrite                  = on
#skip-innodb_doublewrite
 
innodb-file-per-table               = on
 
innodb-flush-log-at-trx-commit      = 1
innodb-flush-method                 = normal
#innodb-force-recovery               = 1
 
innodb-ft-enable-stopword           = off
innodb-ft-max-token-size            = 10
innodb-ft-min-token-size            = 0
 
innodb-io-capacity                  = 2000
innodb-lock-wait-timeout            = 600
 
innodb-log-buffer-size              = 16M
innodb-log-file-size                = 20M
innodb-log-files-in-group           = 2
innodb-log-group-home-dir           = f:/Wamp/bin/mysql/mysql8.0.3/data/ibdata/
 
innodb-max-dirty-pages-pct          = 90
innodb-optimize-fulltext-only       = on
innodb-page-size                    = 16K
innodb-purge-threads                = 10
innodb-read-io-threads              = 4
innodb-stats-on-metadata            = 0
innodb-strict-mode                  = off
innodb-thread-concurrency           = 16
innodb-undo-log-truncate            = on
innodb-write-io-threads             = 4
 
# ---------------- #
#     Language     #
# ---------------- #
 
lc-messages   = fr_FR
lc-time-names = fr_FR
 
# ------------------------------ #
#     Load data local infile     #
# ------------------------------ #
 
local-infile = 1
 
# -------------------#
#     Binary Log     #
# -------------------#
 
#log-slave-updates     = off
 
#log-bin              = f:/Wamp/logs/mysql_binary_log
#log-bin-index        = f:/Wamp/logs/mysql_binary_log.index
 
#binlog-cache-size    = 10M
#binlog-format        = mixed
#binlog-do-db         = test
 
skip-log-bin
skip-log-slave-updates
 
# --------------------#
#     General Log     #
# --------------------#
 
#general-log         = 1
#general-log-file    = f:/Wamp/logs/mysql_general.log
#log_output          = 'FILE';
 
# ------------#
#     Log     #
# ------------#
 
log-error                              = f:/Wamp/logs/mysql_error.log
log-error-verbosity                    = 3
 
log-queries-not-using-indexes          = 1
log-slow-admin-statements              = 1
log-throttle-queries-not-using-indexes = 60
 
# ------------#
#     Max     #
# ------------#
 
max-allowed-packet     = 16M
max-connections        = 20
max-connect-errors     = 10
max-error-count        = 64
max-heap-table-size    = 1G
max-user-connections   = 0
max-sp-recursion-depth = 255
 
# ---------------#
#     MyIsam     #
# ---------------#
 
bulk-insert-buffer-size   = 64M
 
key-buffer-size           = 2G
 
myisam-max-sort-file-size = 1G
myisam-recover-options    = 1
myisam-repair-threads     = 1
myisam-sort-buffer-size   = 128M
myisam-stats-method       = nulls_unequal
myisam-use-mmap           = OFF
 
# -------------------#
#     Networking     #
# -------------------#
 
back-log           = 50
 
bind-address       = 0.0.0.0
 
net-buffer-length  = 8K
net-read-timeout   = 60
net-write-timeout  = 60
 
# -----------------#
#     Password     #
# -----------------#
 
default-password-lifetime         = 0
disconnect-on-expired-password    = 0
 
validate-password-check-user-name = off
validate-password-length          = 4
validate-password-policy          = LOW
 
# ---------------------------#
#     Performance-Schema     #
# ---------------------------#
 
# performance-schema                                = OFF
# performance-schema-events-waits-history-size      = 20
# performance-schema-events-waits-history-long-size = 1500
 
# ----------------#
#     Pluggin     #
# ----------------#
 
early-plugin-load =
# early-plugin-load = keyring_file.dll
 
# --------------#
#     Query     #
# --------------#
 
query-alloc-block-size = 8M
 
# -------------#
#     Skip     #
# -------------#
 
skip-external-locking
skip-federated
 
# skip-grant-tables
# skip-innodb-adaptive-hash-index
# skip-innodb-doublewrite
 
# skip-log-warnings
# skip-networking
# skip-slave-start
# skip-ssl
 
# --------------------#
#     Long & Slow     #
# --------------------#
 
long-query-time      = 0
 
slow-query-log       = 1
slow-query-log-file  = f:/Wamp/logs/mysql_slow.log
 
# ------------#
#     SSL     #
# ------------#
 
ssl
ssl-ca     = f:/Wamp/bin/apache/apache2.4.29/conf/certificat/chez.jcz.fr/Ca-crt.pem
ssl-capath = f:/Wamp/bin/apache/apache2.4.29/conf/certificat/chez.jcz.fr
ssl-cert   = f:/Wamp/bin/apache/apache2.4.29/conf/certificat/chez.jcz.fr/chez.jcz.fr-crt.pem
ssl-key    = f:/Wamp/bin/apache/apache2.4.29/conf/certificat/chez.jcz.fr/chez.jcz.fr-key.pem
 
# --------------#
#     Table     #
# --------------#
 
table-definition-cache = 512
table-open-cache       = 512
 
# -------------------#
#     Temp Table     #
# -------------------#
 
tmp-table-size = 1G
 
# ---------------#
#     Thread     #
# ---------------#
 
thread-cache-size = 8
thread-stack      = 256K
 
# ------------------------------#
#     Timeout Configuration     #
# ------------------------------#
 
connect-timeout     = 600
interactive-timeout = 600
wait-timeout        = 600
 
# --------------------#
#     Transaction     #
# --------------------#
 
transaction-isolation = SERIALIZABLE
 
# ---------------#
#     Tuning     #
# ---------------#
 
concurrent-insert = 2
open-files-limit  = 8192
 
secure-file-priv  = f:/wamp/tmp
# secure-file-priv  =
 
# =================== #
#     MySql Admin     #
# =================== #
 
[mysqldadmin]
force
 
 
# ================== #
#     MySql Dump     #
# ================== #
 
[mysqldump]
add-drop-database
add-drop-table
add-drop-trigger
 
default-character-set = utf8
 
flush-logs
flush-privileges
 
lock-all-tables
max-allowed-packet    = 16M
no-autocommit
order-by-primary
quick
routines
 
skip-comments
skip-opt
skip-set-charset
skip-tz-utc
 
triggers
 
 
# ===================== #
#     My Isam Check     #
# ===================== #
 
[myisamchk]
key-buffer-size  = 128M
sort-buffer-size = 128M
read-buffer      = 2M
write-buffer     = 2M
 
 
# ====================== #
#     MySql Hot Copy     #
# ====================== #
 
[mysqlhotcopy]
interactive-timeout
 
 
# ===================== #
#     MySql Monitor     #
# ===================== #
 
[mysql]
no-auto-rehash
#safe-updates
skip-reconnect
table
 
 
# ================== #
#     MySql Safe     #
# ================== #
 
[mysqld_safe]
open-files-limit = 8192
 
log-error        = f:/Wamp/logs/mysql_error.log

@+

**thierryRoussel** · 22/02/2018, 23h15

Non, en fait la trésorière charge un CSV venant de son logiciel de compta, ou il y a tout les membres dedans, certains sont modifié, certains sont nouveaux.
Elle peut faire cela, n'importe quand. Voir dés fois deux fois par jour, si un membre n'arrive pas à s'identifier, et qu'elle a fait une erreur.

Donc avec la fonction csv to array, le fichier uploadé est chargé dans un array avec clef (nom de la colonne).

Aprés pour chaque membre, il faut tester si il existe déjà dans la base. Si oui, sur l'ancien serveur qui était un dédié, ils écrasaient avec les données donc update.
Si le membre n'existe pas il est créé.

Donc si elle envoie un fichier avec que des anciens membres, car elle en a modifier un, ça fait 5600 update.

J'ai modifié le programme, maintenant je n'update que les membres dont les données sont modifiées (du coup 2secondes de traitement).

Par contre je suis obligé de faire un update, car il y a d'autres champ qui ne sont pas dans le csv et que le membres peut renseigner via un intranet (nb d'enfants, devise personnelle, etc ...).

Par contre celà ne m'explique pas pourquoi en testant sur mon ordi, et sur un autre serveur dédié, les 5000 updates font 2 secondes, alors que sur le VPS ils font 40 secondes.

De plus tout les sites wordpress du VPS sont lent car Mysql est lent.

Sur le dédié que ou j'ai testé, mariadb à été installé et pas du tout configuré, my.cnf vide, et le traitement dure 2 secondes. Parreil sur mon MAC, juste un install via Port, et pas de config : 2 secondes. Sur le VPS 40 secondes.

Dans votre code, je ne vois pas le code php pour le csv ? je suis obligé de passer par Wordpress, donc programmation d'une extension.

Si j'utilise la méthode de votre exemple, je vais perdre les infos des row qui ne sont pas dans le csv, et les id de ligne qui sont des foreign key d'autre table non renseignées par le csv, mais par les membres dans des applications à part.

**Artemus24** · 23/02/2018, 03h02

Salut thierryRoussel.

Envoyé par thierryRoussel

Donc avec la fonction csv to array, le fichier uploadé est chargé dans un array avec clef (nom de la colonne).

J'ai bien compris que vous passiez par un array en php pour charger votre fichier csv.
Et je vous ai répondu que l'on ne procède pas ainsi en MySql.
On passe par un "load data local infile" comme dans mon exemple, où on stock temporairement le fichier csv dans une table de travail sous MySql.
Puis ensuite, selon ce que vous devez faire, vous répartissez vos données depuis la table de travail dans toutes vos autres tables.
Je vous assuez que c'est beaucoup plus rapide de procéder ainsi ! Pourquoi ?
Parce que traiter ligne par ligne n'est pas à proprement parlez, faire une approche ensembliste.

Envoyé par thierryRoussel

Par contre celà ne m'explique pas pourquoi en testant sur mon ordi, et sur un autre serveur dédié, les 5000 updates font 2 secondes, alors que sur le VPS ils font 40 secondes.

La raison peut être multiple. Selon moi, votre VPS (virtual private server) est mal configuré.

Envoyé par thierryRoussel

De plus tout les sites wordpress du VPS sont lent car Mysql est lent.

Etes-vous certain que c'est MySql qui est lent et non votre VPS ?
D'ailleurs MySql se paramètre aussi pour l'usage que vous désirez en faire.

Envoyé par thierryRoussel

Dans votre code, je ne vois pas le code php pour le csv ?

C'est un script mysql. Il n'y a pas de php dedans.

Envoyé par thierryRoussel

je suis obligé de passer par Wordpress, donc programmation d'une extension.

A priori, WordPress utilise le serveur MySql. Donc pourquoi ne pas insérer vos lignes directement dans votre base de données ?
Pourquoi est-il nécessaire de passer par WordPress ?

Envoyé par thierryRoussel

Si j'utilise la méthode de votre exemple, je vais perdre les infos des row qui ne sont pas dans le csv

Ce que je vous ai donné est juste une suggestion.

Vous dites perdre vos infos qui ne sont pas dans votre CSV. Ou se trouvent-il ?

Envoyé par thierryRoussel

En fait c'est une table avec un index sur l'identifiant, l'update se fait avec un where sur l'identifiant. Il y a 5000 enregistrements dans la table. Les 5000 sont updatés en lisant un csv.

J'ai répondu à votre question, à savoir comment mettre à jour vos tables à partir d'un fichier csv.
Si maintenant vous changez vos explications en cours de route, il sera difficile de vous suivre et de répondre avec précision.

Envoyé par thierryRoussel

Par contre c'est un VPS, donc je ne sais pas si un autre VPS sur le même serveur ne monopolise pas le disque dur.

C'est tout à fait possible et dans ce cas là, vous ne pouvez rien faire d'autre.

Pour tout ce qui concerne les notions de performances, on ne peut que vous suggérer quelques conseils.
Mais toutes les réponses ne se trouvent pas dans MySql, mais aussi dans votre VPS.
Par exemple augmenter la taille mémoire, obtenir un processeur multi-cœur, avoir un disque dur plus rapide, ...

@+

Requête update très lente

Administration MySQL

Discussions similaires

Partager

Partager