Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Administration MySQL Discussion :

MySQL Erreur 1205


Sujet :

Administration MySQL

  1. #1
    Membre du Club
    MySQL Erreur 1205
    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.

  2. #2
    Membre du Club
    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.

  3. #3
    Expert éminent sénior
    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.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr