我拿了一个现有的MySQL数据库，并在新主机上设置了一个副本。
新主机上某些表的文件大小比旧主机上的对应文件小1-3％。
我很好奇为什么会这样。
我的猜测是，旧主机的文件随着时间的推移而增长，并且在该文件的b树结构中，存在更多碎片。 而新主机，因为它是从头开始创建文件（通过二进制日志），避免了这种碎片。
在b树结构本身内是否存在碎片甚至是否有意义？ （在数据库层中说话，而不是关于操作系统文件系统层）我原本认为“不”，但是再一次，压缩数据库文件的DBA任务的基础是不是这样的碎片？
我想知道这可能只是文件系统层的工件。 即新主机的磁盘驱动器大部分都是空的，因此较少的碎片会导致新文件的分配。 然后，我不认为碎片会出现在报告的文件大小（Linux OS）中。
I took an existing MySQL database, and set up a copy on a new host.
The file size for some tables on the new host are 1-3% smaller than their counterpart files on the old host.
I am curious why that is.
My guess is, the old host's files have grown over time, and within the b-tree structure for that file, there is more fragmentation. Whereas the new host, because it was creating the file from scratch (via a binary log), avoided such fragmentation.
Does it even make sense for there to be fragmentation within the b-tree structure itself? (Speaking within the database layer, and not with regards to the OS file system layer) I originally thought "no", but then again, isn't such fragmentation the basis for the DBA task of compressing your database files?
I'm wondering maybe if this is simply an artifact of the file system layer. i.e. the new host has a mostly empty disk drive, hence less fragmentation would result in the allocation of a new file. Then again, I didn't think that fragmentation would show up in the reported file size (Linux OS).
最满意答案
MySQL数据文件或索引文件肯定存在碎片。 这很常见，甚至是故意的 。
也就是说，存储引擎可能会故意留下一些额外的空间，因此当您更改值时，它可以适应行，而无需重新排序整个数据文件。 甚至可以使用服务器属性来配置要分配的slop空间量。
我甚至不会眨眼文件差异1-3％。
There can certainly be fragmentation in MySQL data files or index files. This is common, even deliberate.
That is, the storage engine may deliberately leave some extra space here and there so when you change values, it can fit the rows in without having to reorder the whole data file. There are even server properties you can use to configure how much of this slop space to allocate.
I wouldn't even blink at a file discrepancy of 1-3%.
更多推荐
发布评论