Discussion :

[dewey01]

Bonjour à tous,

je post aujourd'hui pour demander un petit coup de main sur un problème MySQL. Malgré mes recherches sur le net je n'arrive pas a solutionner le soucis.

Le contexte: nous avons un base de donnée MySQL avec des millions d'enregistrement. Je fais le ménage tous les 2 ans en exportant la base et en l'important dans une base d'archive (sur un autre serveur).
Une fois l'archive effectuée, je fais du DELETE de masse (requête qui dure 9/10 heures). Il y a eu un problème d'espace sur le serveur et MySQL attendait que de l'espace se libère pour continuer sa requête.
Bref, d’après mes calculs nous devions être a 75% de DELETE effectués lorsque que le problème est apparu. Malheureusement pour moi, l'admin du serveur a annulée la requête, redémarré le serveur et nettoyé les fichiers logs (nom_serveur-bin.00XX).

Problèmes: tous les enregistrements sont encore présent dans la table. Lorsque j'essaie de faire un DELETE d'un de ces enregistrements, j'obtiens l'erreur 1205 (Lock wait timeout exceeded).
J'ai regardé avec un "show processlist" si il y avait un thread bloquant, ce n'est pas le cas et je ne trouve pas d'autre solution. Vous trouverez ci-dessous le résultat du "show engine innodb status" :

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
 
=====================================
2020-01-24 11:09:21 0xbb8 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 6416 srv_active, 0 srv_shutdown, 0 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3534091
--Thread 5112 has waited at btr0sea.cc line 1197 for 0 seconds the semaphore:
X-lock on RW-latch at 0000015CDCABB028 created in file btr0sea.cc line 202
a writer (thread id 6872) has reserved it in mode  exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0sea.cc line 1101
Last time write locked in file G:\ade\build\sb_0-37170812-1575922901.99\mysql-8.0.19\storage\innobase\btr\btr0sea.cc line 1197
OS WAIT ARRAY INFO: signal count 1909366
RW-shared spins 468780, rounds 1126789, OS waits 656976
RW-excl spins 2022124, rounds 85805081, OS waits 2849993
RW-sx spins 594, rounds 17510, OS waits 560
Spin rounds per wait: 2.40 RW-shared, 42.43 RW-excl, 29.48 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 170025
Purge done for trx's n:o < 169996 undo n:o < 0 state: running but idle
History list length 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 282972207017376, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 282972207018224, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 282972207022464, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 282972207021616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 282972207016528, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 168250, ACTIVE 6642 sec recovered trx
ROLLING BACK 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 47628997
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: complete io for buf page (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 1; buffer pool: 1
3743843 OS file reads, 10373001 OS file writes, 942222 OS fsyncs
434.47 reads/s, 16384 avg bytes/read, 1469.61 writes/s, 143.92 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 3, free list len 14054, seg size 14058, 3 merges
merged operations:
 insert 45, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 3603 buffer(s)
Hash table size 276707, node heap has 3770 buffer(s)
Hash table size 276707, node heap has 4735 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 1 buffer(s)
Hash table size 276707, node heap has 3864 buffer(s)
102964.50 hash searches/s, 23766.52 non-hash searches/s
---
LOG
---
Log sequence number          526483417383
Log buffer assigned up to    526483417383
Log buffer completed up to   526483416870
Log written up to            526483416870
Log flushed up to            526483416870
Added dirty pages up to      526483416870
Pages flushed up to          526456627340
Last checkpoint at           526456627340
7289208 log i/o's done, 1033.79 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1098907648
Dictionary memory allocated 485518
Buffer pool size   65536
Free buffers       8103
Database pages     41456
Old database pages 15175
Modified db pages  2590
Pending reads      0
Pending writes: LRU 3, flush list 73, single page 0
Pages made young 8274, not young 1924731429
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3743623, created 159, written 3005314
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 795 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 41456, unzip_LRU len: 0
I/O sum[255376]:cur[2664], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   8192
Free buffers       1020
Database pages     5187
Old database pages 1895
Modified db pages  317
Pending reads      0
Pending writes: LRU 1, flush list 0, single page 0
Pages made young 1024, not young 239877624
0.00 youngs/s, 0.00 non-youngs/s
Pages read 469083, created 8, written 378472
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 793 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5187, unzip_LRU len: 0
I/O sum[31922]:cur[333], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   8192
Free buffers       969
Database pages     5237
Old database pages 1914
Modified db pages  313
Pending reads      0
Pending writes: LRU 1, flush list 0, single page 0
Pages made young 900, not young 241556179
0.00 youngs/s, 0.00 non-youngs/s
Pages read 469342, created 2, written 377870
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 740 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5237, unzip_LRU len: 0
I/O sum[31922]:cur[333], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   8192
Free buffers       1030
Database pages     5153
Old database pages 1883
Modified db pages  319
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 914, not young 240114468
0.00 youngs/s, 0.00 non-youngs/s
Pages read 467524, created 13, written 375942
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 795 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5153, unzip_LRU len: 0
I/O sum[31922]:cur[333], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   8192
Free buffers       1025
Database pages     5146
Old database pages 1879
Modified db pages  431
Pending reads      0
Pending writes: LRU 0, flush list 73, single page 0
Pages made young 859, not young 236110382
0.00 youngs/s, 0.00 non-youngs/s
Pages read 465656, created 1, written 372851
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 891 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5146, unzip_LRU len: 0
I/O sum[31922]:cur[333], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   8192
Free buffers       1032
Database pages     5175
Old database pages 1892
Modified db pages  245
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1101, not young 239780438
0.00 youngs/s, 0.00 non-youngs/s
Pages read 469782, created 1, written 376850
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 957 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5175, unzip_LRU len: 0
I/O sum[31922]:cur[333], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   8192
Free buffers       1024
Database pages     5175
Old database pages 1892
Modified db pages  340
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1004, not young 244612477
0.00 youngs/s, 0.00 non-youngs/s
Pages read 467839, created 64, written 375166
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 957 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5175, unzip_LRU len: 0
I/O sum[31922]:cur[333], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   8192
Free buffers       983
Database pages     5202
Old database pages 1928
Modified db pages  332
Pending reads      0
Pending writes: LRU 1, flush list 0, single page 0
Pages made young 1157, not young 242716480
0.00 youngs/s, 0.00 non-youngs/s
Pages read 467934, created 65, written 374855
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 898 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5202, unzip_LRU len: 0
I/O sum[31922]:cur[333], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   8192
Free buffers       1020
Database pages     5181
Old database pages 1892
Modified db pages  293
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1315, not young 239963381
0.00 youngs/s, 0.00 non-youngs/s
Pages read 466463, created 5, written 373308
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 385 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5181, unzip_LRU len: 0
I/O sum[31922]:cur[333], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1412, Main thread ID=0000000000000334 , state=sleeping
Number of rows inserted 16, updated 0, deleted 10, read 1344843
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 1, updated 315, deleted 0, read 6554
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Si quelqu'un a une idée de ce que je pourrais faire je suis preneur.

Merci d'avance pour votre aide.

Dewey.

[dewey01]

Bonjour,

il semblerait que le serveur ai rendu la main sur les enregistrements, je peux maintenant les supprimer.
Je pense que c’était simplement une question de délai, vu le nombre d'enregistrement il a fallu attendre quelques heures.

je passe le sujet en résolu.

[Artemus24]

Salut dewey01.

Je pense que votre approche de la suppression de masse n'est pas bonne.
Si vous devez effectuer de l'archivage sur un critère, autant créer des tables de partitions.
Cela sera plus simple et plus rapide à faire puisque vous pouvez recopier ces fichiers en dehors du serveur mysql.

D'autre part, si les tables de partitions ne sont pas ce qui vous intéresse, vous devez envisager de faire des suppressions avec un point de reprises (ou de sauvegarde).
Cela consiste à créer un traitement qui va supprimer N lignes, valider la suppression et stocker un point de reprises.
Cela permet de mieux gérer vos plantages et de ne pas perdre du temps inutilement.
En cas de plantage, vous repartez sur le dernier point de reprise valide.
Il vous faut évaluer N pour que ce traitement ne vous soit pas trop contraignant.
Ainsi vous pourrez l'automatiser.

Et enfin, effectuer une optimisation de vos tables pour récupérer de la place et reconstituer vos index.

@+