MySQL可能需要一个多小时才能启动 [英] MySQL can take more than an hour to start

问题描述

我有一个超过100万张表的mysql(Percona)5.7实例. 当我启动数据库时，可能需要一个多小时才能启动. 错误日志没有显示任何内容，但是当我跟踪mysqld_safe时，我发现MySQL正在数据库中的每个文件上获取统计信息.

I have a mysql (Percona) 5.7 instance with over 1Million tables. When I start the database, it can take more than an hour to start. Errorlog doesn't show anything, but when I trace mysqld_safe, I found out that MySQL is getting a stat on every file in the DB.

有人知道为什么会发生这种情况吗? 另外，请不要提出修复我的架构的建议，这是一个黑匣子.

Any idea why this may happen? Also, please no suggestion to fix my schema, this is a blackbox.

谢谢

推荐答案

原来是2个问题(除了数百万个表)！

This turned out to be 2 issues (other than millions of tables)!

1. 当MySQL启动并需要崩溃恢复时，从5.7.17开始，它需要遍历datadir来构建其字典.这将在将来的版本(8.0)中取消，因为MySQL将拥有自己的目录，并且不再依赖于datadir内容. Doc 指出这是不再做了.是对是错.它不会读取ibd文件的第一页，但会读取文件状态. 归档的错误
2. 完成(1)后，它将启动一个新过程，正在执行'SELECT * FROM INFORMATION_SCHEMA.TABLES;'.使用不赞成使用的分区引擎来获取表列表.".那当然会再次打开所有文件.如果您认为不需要，请使用disable-partition-engine-check. 文档

1. When MySQL start, and a crash recovery is needed, as of 5.7.17, it needs to traverse your datadir to build it's dictionary. This will be lifted in future releases(8.0), as MySQL will have it's own catalog, and will not rely on datadir content anymore. Doc states that this isn't done anymore. It's true and false. It doesn't read the 1st page of ibd files, but It does a file stat. Filed Bug
2. Once it finished (1), it starts a new process, "Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine.". That of course open all the files again. Use disable-partition-engine-check if you think you don't need it. Doc

使用sysdig可以观察到所有这些情况.像工具一样强大的便捷dtrace.

All this can be observed using sysdig. very powerful handy dtrace like tool.

sysdig proc.name = mysqld | grep"open fd ="

sysdig proc.name=mysqld | grep "open fd="

好吧，现在该减少文件数量了.

Ok, now, it's time to reduce the number of files.

这篇关于MySQL可能需要一个多小时才能启动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！