未加星标

UNDO logs in InnoDB system tablespace ibdata1

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二03 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

We see sometimes at customers that they have very big InnoDB system tablespace files ( ibdata1 ) although they have set innodb_file_per_table = 1 .

So we want to know what else is stored in the InnoDB system tablespace file ibdata1 to see what we can do against this unexpected growth.

First let us check the size of the ibdata1 file:

# ll ibdata1 -rw-rw---- 1 mysql mysql 109064486912 Dez 5 19:10 ibdata1

The InnoDB system tablespace is about 101.6 Gibyte in size. This is exactly 6'656'768 InnoDB blocks of 16 kibyte block size.

So next we want to analyse the InnoDB system tablespace ibdata1 file. For this we can use the tool innochecksum :

# innochecksum --page-type-summary ibdata1 Error: Unable to lock file:: ibdata1 fcntl: Resource temporarily unavailable

But... the tool innochecksum throughs an error. It seems like it is not allowed to analyse the InnoDB system tablespace with a running database. So then let us stop the database first and try it again. Now we get a useful output:

# innochecksum --page-type-summary ibdata1 File::ibdata1 ================PAGE TYPE SUMMARY============== #PAGE_COUNT PAGE_TYPE =============================================== 349391 Index page 5.25% <strong> 6076813 Undo log page 91.29%</strong> 18349 Inode page 0.28% 174659 Insert buffer free list page 2.62% 36639 Freshly allocated page 0.55% 405 Insert buffer bitmap 0.01% 98 System page 1 Transaction system page 1 File Space Header 404 Extent descriptor page 0.01% 0 BLOB page 8 Compressed BLOB page 0 Other type of page ------------------------------------------------------- 6656768 Pages total 100.00% =============================================== Additional information: <strong>Undo page type: 3428 insert, 6073385 update, 0 other Undo page state: 1 active, 67 cached, 249 to_free, 1581634 to_purge, 0 prepared, 4494862 other</strong>

So we can see that about 91% (about 92 Gibyte) of the InnoDB system tablespace ibdata1 blocks are used by InnoDB UNDO log pages. To avoid growing of ibdata1 you have to create a database instance with separate InnoDB UNDO tablespaces: Undo Tablespaces .

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

代码区博客精选文章
分页:12
转载请注明
本文标题:UNDO logs in InnoDB system tablespace ibdata1
本站链接:https://www.codesec.net/view/620956.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(66)