InnoDB磁盘空间利用率的查看_MySQL
bitsCN.com
查看InnoDB的磁盘空间利用率 page利用率主要是指btee里面每个page的使用被使用的空间大小。我们知道InnoDB默认一个page大小是16k。但实际使用情况不会总用满我们定义为所有page的总使用字节除以总字节数。在理论分析之前，我们要先弄个工具，查一下。 实例统计写了一个简单的工具，读ibd文件上的每个page，算出每个page的实际使用字节，可以得到利用率。我们找了线上一个库来模拟。表中有1个自增主键和3个非聚簇索引。不影响结论地简化为如下：CREATE TABLE `ctu_factor_risk_99_03` (`seq_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`a` varchar(32) DEFAULT NULL,`b` varchar(32) DEFAULT NULL,`c` varchar(32) DEFAULT NULL,KEY a (a),KEY bc (b,c),KEY cb (c,b),) ENGINE=InnoDB DEFAULT CHARSET=utf8; 插入数据中a,b,c均为长度为30字节的随机字符串。 显然主键和其他索引应该分开统计。统计结果发现，主键page利用率71%，其他索引利用率约52%。 简单分析上面的结果很好理解。因为按照主键递增顺序插入数据，因此主键上数据“紧凑”。 而其他三个索引，则都是随机更新，需要不停地作索引节点分裂。 如何提升磁盘空间利用率回到最开始的问题。其实我们关心的，是InnoDB为了保存相同的数据，用了多少空间。所以我们的问题变成，存储相同的数据，如何让占用的磁盘空间更小。 有一个很直观的结论。把这些索引删了重建，必然会减少空间消耗。因为这个操作之后，在新的数据插入之前，这些索引也变成“紧凑的”。 再建了一个与cb相同的索引，再跑，利用率居然高达98%。这个原理大家应该都知道了，只是量化一下而已。 工具附后， 用法 ./ibd_used tb.ibd N1 N2 >/tmp/r 最后几行为各个索引的利用率统计值 （附件）ibd_used.rar http:///uploadfile/2012/0429/20120429085005514.rar 作者 丁林.tb bitsCN.com
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Numerical distance based on machine learning: the distance between points in space
Apr 11, 2023 pm 11:40 PM
This article is reprinted from the WeChat public account "Living in the Information Age". The author lives in the information age. To reprint this article, please contact the Living in the Information Age public account. In machine learning, a basic concept is how to judge the difference between two samples, so that the similarity and category information between the two samples can be evaluated. The measure to judge this similarity is the distance between two samples in the feature space. There are many measurement methods based on different data characteristics. Generally speaking, for two data samples x, y, define a function d(x, y). If it is defined as the distance between the two samples, then d(x, y) needs to satisfy the following basic properties : Non-negativity: d(x, y)>=0 Identity: d(x, y)=0 ⇔ x=y pair
How to add swap space on Ubuntu 22.04 LTS
Feb 20, 2024 am 11:12 AM
Swap space plays an important role in Linux systems, especially when the system is low on memory. It acts as a backup memory storage space that helps the system run smoothly and maintain stability even under high load. This article provides you with a detailed guide to adding swap space on Ubuntu 22.04LTS to ensure that your system performance is optimized and can handle various workloads. Understanding Swap Space Swap space provides virtual memory that is used to supplement the system's physical RAM. When the system is low on RAM, the kernel swaps data to disk to prevent out-of-memory and system crashes. Linux systems commonly use swap space to handle this situation. Run multiple memory-intensive applications simultaneously to process very large files or data
What is the appropriate amount of remaining space on the c drive?
Jun 27, 2023 pm 02:51 PM
The remaining space on the c drive is 50-80G which is more suitable. Since the system will generate junk files, cache files, etc. in the future, it is recommended to reserve at least 50GB-80GB of space for the C drive; if you are not used to choosing a path when installing software and do not clean your computer frequently, then at least 100GB is required. .
Introduction to the C drive space required for upgrading win11
Dec 23, 2023 am 08:57 AM
As we all know, if the system disk occupied is too large after the system installation is completed, it may cause system lags, delays, and even file loss. Therefore, before you install the win11 system, you need to know how much C drive space is required to upgrade win11. Let’s take a look with the editor. How much C drive space is required to upgrade win11: Answer: Upgrading win11 requires 20-30GB of C drive space. 1. According to Microsoft’s win11 configuration requirements, you can see that win11 installation requires 64GB of hard drive space. 2. But in fact, generally speaking, there is no need for such a large space. 3. According to feedback from users who have already installed win11, the win11 upgrade requires about 20-30GB of C drive space. 4. But if our door only has
How to free up space on Xbox Series S or Xbox Series X
Apr 19, 2023 pm 01:46 PM
The Xbox console has a huge selection of games to download and play. Coupled with Microsoft's Xbox Game Pass subscription, the fun never stops with your game collection. However, there is the issue of space available for games, which is 356GB on Xbox Series S and 850GB on Xbox Series X. While this was fine in previous versions of the game, the maximum size of the game was probably between 20 and 45GB, which isn't the case with recent games. Recently available games end up taking up a lot of space on the disk, leaving us less space to download other games. ForzaHorizon5 and Halo5Guardians and more
iOS 17.2: How to record spatial video on iPhone 15 Pro
Dec 15, 2023 pm 01:09 PM
If you have an iPhone 15 or iPhone 15 Pro Max, iOS 17.2 lets you record spatial video for viewing in the Photos app on Apple's upcoming Vision Pro headphones. Here's how you do it. Apple's VisionPro headphones are expected to be released around February 2024. Until then, one way you can prepare for this is to use your iPhone to record video in a special format called spatial video, which can be viewed on Apple's headphones. Spatial videos appear as normal videos when viewed on an iPhone, but they offer near three-dimensionality on VisionPro
A review of artificial intelligence technology in cyberspace security
Apr 11, 2023 pm 04:10 PM
1. Introduction Due to the explosive growth of current computer networks, the ensuing problem is the rapidly increasing number of network attacks. Various sectors of our society, from government departments to various critical infrastructures in society, are heavily dependent on computer networks and information technology. Apparently they are also vulnerable to cyberattacks. Typical network attacks disable the target computer, take services offline, or access the target computer's data. The number and impact of cyberattacks has increased significantly since the 1990s. Network security refers to a set of technologies used to protect network device activities and measures to protect them from all possible threats. In traditional network security technology, most of them are static access management, and the security control system will protect based on preset definitions.
What to do if Baidu Cloud is out of space
Mar 25, 2024 pm 11:01 PM
Cloud storage has become an indispensable part of our daily life and work. As the number of files continues to increase, the demand for cloud storage space has become increasingly prominent. As the leading cloud storage service provider in China, Baidu Cloud has won the favor of users with its powerful storage functions and convenient operating experience. However, many users may have encountered the problem of insufficient space during use, so this tutorial will provide you with detailed solutions to expand capacity. I hope it can help you! 1. Log in to your account in Baidu Cloud. (You can log in with a Baidu account or a QQ account) 2. After logging in, click "Receive 2048G capacity for free" on the left side of Baidu Cloud. 3. Download Baidu Cloud mobile client, click to download for Android phones
