Causes et solutions des erreurs d'E/S dans MySQL (avec suggestions d'optimisation)

Cet article est une analyse des causes des erreurs d'E/S lorsque MySQL supprime des tables. Tout d'abord, nous devons observer le phénomène problématique, puis étudier les problèmes associés. Nous pouvons conclure que le processus d'obtention de l'ibuf (espace, espace) par le thread principal. page) est différent de Il n'y a pas de verrou pour garantir l'exclusion mutuelle lors de l'exécution de l'opération de suppression. Seules l'opération de fusion et l'opération de suppression une fois les E/S asynchrones terminées s'excluent mutuellement. Toutes les explications sont décrites en détail dans cet article. apache php mysql

commencez !

Phénomène problématique

Récemment utilisé sysbench pour tester MySQL, en raison du test qui a pris beaucoup de temps, j'ai donc écrit un script à exécuter en arrière-plan dans l'ordre préparer->exécuter->nettoyer. Après l'exécution, j'ai vérifié le journal et trouvé un problème. Il y avait plusieurs rapports d'erreurs similaires aux informations suivantes dans le journal des erreurs du service MySQL :

[ERROR] InnoDB: Trying to do I/O to a tablespace which does not exist. I/O type: read, page: [page id: space=32, page number=57890], I/O length: 16384 bytes。

Il semble qu'il y ait eu une erreur d'E/S. , mais le processus MySQL n'a pas planté. Le client sysbench n'a signalé aucune erreur.

Le processus de découverte du problème

Sur la base de l'enregistrement temporel de l'erreur et de la comparaison des points temporels à chaque étape de la sortie du script, il a été déterminé que la commande exécutée par le script à ce moment-là était :

sysbench --tables=100 --table-size=4000000 --threads=50 --mysql-db=sbtest --time=300 oltp_delete cleanup

J'ai à nouveau exécuté ce cas d'utilisation manuellement, mais la même situation ne s'est plus reproduite. Cependant, ce message d'erreur peut toujours être trouvé lors de l'exécution du script. Le premier soupçon est que l'intervalle entre l'exécution et le nettoyage ne peut pas être trop long pour déclencher ce problème. Étant donné qu'il faut beaucoup de temps pour exécuter 100 Go de données une fois et que le coût de reproduction est élevé, essayez d'abord de réduire la quantité de données de cas d'utilisation. Modifiez -table-size=4000000 en 2000000. Lorsque le script est exécuté à ce moment, ce problème ne sera pas déclenché. Enfin, -table-size=3000000 peut être déclenché de manière stable et réduire une partie du temps de récurrence. Afin de confirmer si l'intervalle est trop long et s'il ne peut pas être reproduit, j'ai modifié le script pour qu'il se mette en veille pendant 10 secondes entre les étapes d'exécution et de nettoyage. Comme prévu, ce message d'erreur ne sera pas déclenché. S'il est mis en veille pendant 5 secondes, il peut toujours être déclenché, mais le nombre de rapports d'erreurs a été réduit.

Enquête sur le problème

En regardant le code de la version correspondante mysql5.7.22, nous avons constaté que cette erreur ne se produit qu'à un seul endroit : dans la fonction fil_io() sur ligne 5578 du fichier fil0fil.cc. Utilisez gdb pour déboguer directement, ajoutez un point d'arrêt à cet emplacement et exécutez le script reproductible pour obtenir la pile suivante :

(gdb) bt
#0  fil_io (type=..., sync=sync@entry=false, page_id=..., page_size=..., byte_offset=byte_offset@entry=0, len=16384, buf=0x7f9ead544000, message=message@entry=0x7f9ea8ce9c78) at mysql-5.7.22/storage/innobase/fil/fil0fil.cc:5580
#1  0x00000000010f99fa in buf_read_page_low (err=0x7f9ddaffc72c, sync=<optimized out>, type=0, mode=<optimized out>, page_id=..., page_size=..., unzip=true) at mysql-5.7.22/storage/innobase/buf/buf0rea.cc:195
#2  0x00000000010fc5fa in buf_read_ibuf_merge_pages (sync=sync@entry=false, space_ids=space_ids@entry=0x7f9ddaffc7e0, page_nos=page_nos@entry=0x7f9ddaffc7a0, n_stored=2) at mysql-5.7.22/storage/innobase/buf/buf0rea.cc:834
#3  0x0000000000f3a86c in ibuf_merge_pages (n_pages=n_pages@entry=0x7f9ddaffce30, sync=sync@entry=false) at mysql-5.7.22/storage/innobase/ibuf/ibuf0ibuf.cc:2552
#4  0x0000000000f3a94a in ibuf_merge (sync=false, sync=false, n_pages=0x7f9ddaffce30) at mysql-5.7.22/storage/innobase/ibuf/ibuf0ibuf.cc:2656
#5  ibuf_merge_in_background (full=full@entry=false) at mysql-5.7.22/storage/innobase/ibuf/ibuf0ibuf.cc:2721
#6  0x000000000102bcf4 in srv_master_do_active_tasks () at mysql-5.7.22/storage/innobase/srv/srv0srv.cc:2132
#7  srv_master_thread (arg=<optimized out>) at mysql-5.7.22/storage/innobase/srv/srv0srv.cc:2383
#8  0x00007fa003eeddc5 in start_thread () from /lib64/libpthread.so.0
#9  0x00007fa002aab74d in clone () from /lib64/libc.so.6

Évidemment, il s'agit du thread d'arrière-plan qui effectue l'opération de fusion de tampon d'insertion. À ce stade, il s'avère que space->stop_new_ops est vrai, c'est-à-dire que l'espace auquel appartient la page à traiter est en cours de suppression. Pourquoi souhaitez-vous exploiter l'espace qui est en cours de suppression ? Cela nécessite d'étudier la fonction de tampon d'insertion, le processus de fusion de tampon d'insertion et le processus de suppression de table.

insérer des connaissances de base sur le tampon

insérer le tampon est une structure de données spéciale (arborescence B+) qui change lorsque la page d'index auxiliaire n'est pas dans le pool de tampons mis en cache puis fusionné lorsque la page est chargée dans le pool de mémoire tampon par d'autres opérations de lecture. Lorsque MySQL a introduit cette fonctionnalité pour la première fois, il ne pouvait mettre en cache que les opérations d'insertion, c'est pourquoi on l'appelait insert buffer. Désormais, ces opérations peuvent être INSERT, UPDATE ou DELETE (DML), elles sont donc appelées change buffer (cet article est toujours décrit comme insert). buffer), mais le code source utilise toujours ibuf comme identifiant. Cette fonction met en cache plusieurs mises à jour sur la même page, les fusionne en opérations de mise à jour uniques, réduit les E/S et convertit les E/S aléatoires en E/S séquentielles. Cela peut éviter les pertes de performances causées par les E/S aléatoires et améliorer les performances d'écriture de la base de données.

Logique de fusion de tampon d'insertion associée

Lorsque la page tampon est lue dans le pool de tampons, une fusion de tampon d'insertion sera effectuée. Il existe plusieurs scénarios principaux dans lesquels le processus de fusion se produira :

1. Lorsque la page est lue dans le pool de mémoire tampon, une fois la lecture terminée, la fusion ibuf est effectuée en premier, puis le la page est disponible ;

2. L'opération de fusion est effectuée en tâche de fond. Le paramètre innodb_io_capacity peut définir la limite supérieure du nombre de pages dans chaque processus de fusion de la tâche en arrière-plan InnoDB

3. Pendant la période de récupération après crash, lorsque la page d'index est lue dans le tampon ; pool, le tampon d'insertion de la page correspondante sera exécuté par fusion

4. Le tampon d'insertion est persistant et les plantages du système ne le rendront pas invalide. Après le redémarrage, l'opération d'insertion de fusion de tampon reviendra à la normale ;

5. Vous pouvez utiliser —innodb-fast-shutdown = 0 pour forcer une fusion complète d'ibuf lorsque le serveur est arrêté.

Notre problème cette fois-ci appartient évidemment à la deuxième situation. Le thread principal innodb (svr_master_thread) effectuera activement une opération de fusion du tampon d'insertion toutes les secondes. Déterminez d'abord s'il y a eu une activité sur le serveur au cours des 1 dernières années (insertion de tuples dans des pages, annulation d'opérations de ligne sur la table, etc.). Si tel est le cas, le nombre maximum de pages fusionnées est de 5 % du paramètre innodb_io_capacity. Sinon, le nombre maximum de pages à fusionner est la valeur définie par innodb_io_capacity.

Le processus principal de fusion du thread principal innodb (svr_master_thread) est le suivant :

1. Le thread principal lit le numéro de page et le numéro d'espace à partir du nœud feuille de l'ibuf tree, et l'enregistre dans un Dans le tableau binaire (déverrouillé);

2. Le thread principal vérifie si l'espace dans le tableau binaire est dans le cache de l'espace table. Si ce n'est pas le cas, cela signifie qu'il a été supprimé. Supprimez l'enregistrement ibuf correspondant

3. Le thread principal détermine s'il faut effectuer une opération de lecture asynchrone sur un espace en cours de suppression. l'enregistrement ibuf correspondant est supprimé. Passez au processus 2 pour continuer le jugement d'un élément du tableau ;

4. 如果一切判断正常，主线程发出async io请求，async读取需要被merge的索引页面；

5. I/O handler 线程，在接受到完成的async I/O之后，进行merge操作；

6. 进行merge的时候调用fil_space_acquire对space->n_pending_ops进行自增。避免删除操作并发；

7. 执行完毕后调用fil_space_release对space->n_pending_ops进行自减。

相关删除表的逻辑

1. 对fil_system->mutex加锁，设置sp->stop_new_ops = true，标记space正在删除，不允许对它进行新操作，然后对fil_system->mutex解锁；

2. 对fil_system->mutex加锁，检测space->n_pending_ops，对fil_system->mutex解锁。如果检测到大于0，意味着还有依赖的操作未完成，睡眠20ms后重试；

3. 对fil_system->mutex加锁，检测space->n_pending_flushes和(*node)->n_pending ，对fil_system->mutex解锁。如果检测到大于0，意味着还有依赖的I/O未完成，睡眠20ms后重试；

4. 此时认为已经没有冲突的操作了，刷出所有脏页面或删除所有给定的表空间的页面；

5. 从表空间缓存删除指定space的记录；

6. 删除对应数据文件。

问题结论

情况很明确了，主线程获取ibuf的（space，page）的过程与删除操作执行的过程并没有锁保证互斥，只有async I/O完成之后的merge操作与删除操作才有互斥。如果后台线程开始ibuf merge并已经执行过了第2步的检测，但还没有执行到第3步检测，此时用户线程开始做删除表的操作，并设置好stop_new_ops标记但还没有执行到第5步删除表空间缓存，就会出现这个错误信息。两线程的交互如下图所示：

不出意外的话，在打中断点时必然有线程在执行对应表的删除操作。果然我们可以发现如下堆栈：

Thread 118 (Thread 0x7f9de0111700 (LWP 5234)):
#0  0x00007fa003ef1e8e in pthread_cond_broadcast@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#1  0x0000000000f82f41 in broadcast (this=0xd452ef8) at mysql-5.7.22/storage/innobase/os/os0event.cc:184
#2  set (this=0xd452ef8) at mysql-5.7.22/storage/innobase/os/os0event.cc:75
#3  os_event_set (event=0xd452ef8) at mysql-5.7.22/storage/innobase/os/os0event.cc:483
#4  0x00000000010ec8a4 in signal (this=<optimized out>) at mysql-5.7.22/storage/innobase/include/ut0mutex.ic:105
#5  exit (this=<optimized out>) at mysql-5.7.22/storage/innobase/include/ib0mutex.h:690
#6  exit (this=<optimized out>) at mysql-5.7.22/storage/innobase/include/ib0mutex.h:961
#7  buf_flush_yield (bpage=<optimized out>, buf_pool=<optimized out>) at mysql-5.7.22/storage/innobase/buf/buf0lru.cc:405
#8  buf_flush_try_yield (processed=<optimized out>, bpage=<optimized out>, buf_pool=<optimized out>) at mysql-5.7.22/storage/innobase/buf/buf0lru.cc:449
#9  buf_flush_or_remove_pages (trx=<optimized out>, flush=<optimized out>, observer=<optimized out>, id=<optimized out>, buf_pool=<optimized out>) at mysql-5.7.22/storage/innobase/buf/buf0lru.cc:632
#10 buf_flush_dirty_pages (buf_pool=<optimized out>, id=<optimized out>, observer=<optimized out>, flush=<optimized out>, trx=<optimized out>) at mysql-5.7.22/storage/innobase/buf/buf0lru.cc:693
#11 0x00000000010f6de7 in buf_LRU_remove_pages (trx=0x0, buf_remove=BUF_REMOVE_FLUSH_NO_WRITE, id=55, buf_pool=0x31e55e8) at mysql-5.7.22/storage/innobase/buf/buf0lru.cc:893
#12 buf_LRU_flush_or_remove_pages (id=id@entry=55, buf_remove=buf_remove@entry=BUF_REMOVE_FLUSH_NO_WRITE, trx=trx@entry=0x0) at mysql-5.7.22/storage/innobase/buf/buf0lru.cc:951
#13 0x000000000114e488 in fil_delete_tablespace (id=id@entry=55, buf_remove=buf_remove@entry=BUF_REMOVE_FLUSH_NO_WRITE) at mysql-5.7.22/storage/innobase/fil/fil0fil.cc:2800
#14 0x0000000000fe77bd in row_drop_single_table_tablespace (trx=0x0, is_encrypted=false, is_temp=false, filepath=0x7f9d7c209f38 "./sbtest/sbtest25.ibd", tablename=0x7f9d7c209dc8 "sbtest/sbtest25", space_id=55) at mysql-5.7.22/storage/innobase/row/row0mysql.cc:4189
#15 row_drop_table_for_mysql (name=name@entry=0x7f9de010e020 "sbtest/sbtest25", trx=trx@entry=0x7f9ff9515750, drop_db=<optimized out>, nonatomic=<optimized out>, nonatomic@entry=true, handler=handler@entry=0x0) at mysql-5.7.22/storage/innobase/row/row0mysql.cc:4741
#16 0x0000000000f092f3 in ha_innobase::delete_table (this=<optimized out>, name=0x7f9de010f5e0 "./sbtest/sbtest25") at mysql-5.7.22/storage/innobase/handler/ha_innodb.cc:12539
#17 0x0000000000801a30 in ha_delete_table (thd=thd@entry=0x7f9d7c1f6910, table_type=table_type@entry=0x2ebd100, path=path@entry=0x7f9de010f5e0 "./sbtest/sbtest25", db=db@entry=0x7f9d7c00e560 "sbtest", alias=0x7f9d7c00df98 "sbtest25", generate_warning=generate_warning@entry=true) at mysql-5.7.22/sql/handler.cc:2586
#18 0x0000000000d0a6af in mysql_rm_table_no_locks (thd=thd@entry=0x7f9d7c1f6910, tables=tables@entry=0x7f9d7c00dfe0, if_exists=true, drop_temporary=false, drop_view=drop_view@entry=false, dont_log_query=dont_log_query@entry=false) at mysql-5.7.22/sql/sql_table.cc:2546
#19 0x0000000000d0ba58 in mysql_rm_table (thd=thd@entry=0x7f9d7c1f6910, tables=tables@entry=0x7f9d7c00dfe0, if_exists=<optimized out>, drop_temporary=<optimized out>) at mysql-5.7.22/sql/sql_table.cc:2196
#20 0x0000000000c9d90b in mysql_execute_command (thd=thd@entry=0x7f9d7c1f6910, first_level=first_level@entry=true) at mysql-5.7.22/sql/sql_parse.cc:3589
#21 0x0000000000ca1edd in mysql_parse (thd=thd@entry=0x7f9d7c1f6910, parser_state=parser_state@entry=0x7f9de01107a0) at mysql-5.7.22/sql/sql_parse.cc:5582
#22 0x0000000000ca2a20 in dispatch_command (thd=thd@entry=0x7f9d7c1f6910, com_data=com_data@entry=0x7f9de0110e00, command=COM_QUERY) at mysql-5.7.22/sql/sql_parse.cc:1458
#23 0x0000000000ca4377 in do_command (thd=thd@entry=0x7f9d7c1f6910) at mysql-5.7.22/sql/sql_parse.cc:999
#24 0x0000000000d5ed00 in handle_connection (arg=arg@entry=0x10b8e910) at mysql-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:300
#25 0x0000000001223d74 in pfs_spawn_thread (arg=0x10c48f40) at mysql-5.7.22/storage/perfschema/pfs.cc:2190
#26 0x00007fa003eeddc5 in start_thread () from /lib64/libpthread.so.0
#27 0x00007fa002aab74d in clone () from /lib64/libc.so.6

解决办法

为buf_read_ibuf_merge_pages、buf_read_page_low、fil_io新增一个参数ignore_missing_space。表示忽略正在删除的space，默认为false，当ibuf_merge_pages调用的时候置为true。在fil_io报错处额外判断该参数是否为true，是则不报错，继续其他流程。

或者直接在buf_read_ibuf_merge_pages调用buf_read_page_low时传入IORequest::IGNORE_MISSING参数。

具体代码参考MariaDB commit：8edbb1117a9e1fd81fbd08b8f1d06c72efe38f44

影响版本

察看相关信息，这个问题是修改Bug#19710564时删除表空间版本引入的。

• MySQL Community Server 5.7.6引入，版本5.7.22尚未修复，版本8.0.0已修复。

• MariaDB Server 10.2受影响。MariaDB Server 10.2.9, 10.3.2已修复

优化建议

可优化一下性能：在buf_read_ibuf_merge_pages中记录下出错的space id，循环的时候判断下一个page的space id，如果space id是相同的，直接删除对应ibuf的记录（当前分配的最大space id记录在系统表空间，space id占4个字节，低于0xFFFFFFF0UL，分配时读取系统表空间保存的值，然后加一，具有唯一性）。

end：对于知识点我就介绍到这里了，写的有点快，可能有不足之处，还望多多交流指正，希望能帮到大家。

相关文章：

mysql1064错误原因及解决办法

MySQL常见问题及解决方案

相关视频：

AJAX跨域解决方案：JSONP视频教程

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!