mysql-innodb-引擎下ibd文件过大的问题排查记录
作者:快盘下载 人气:问题描述;
mysql版本;8.0.12 表存储引擎为innodb
数据库某张表中数据只有100条左右数据;但表文件;ibd文件;占用磁盘空间却达到了1.8G。
该表的表结构如下;
CREATE TABLE ;k8s_node_status; (
;id; int(11) NOT NULL AUTO_INCREMENT,
;master_ip; varchar(255) NOT NULL ,
;node_name; varchar(255) DEFAULT NULL,
;node_ip; varchar(255) DEFAULT NULL,
;node_status; tinyint(5) DEFAULT NULL ,
;update_time; timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
;create_time; timestamp NULL DEFAULT CURRENT_TIMESTAMP,
;delete_flag; tinyint(1) DEFAULT ;0; ,
;reason; varchar(100) DEFAULT NULL ,
;message; varchar(4000) DEFAULT NULL,
;labels; text ,
;annotations; text ,
;taints; tinyint(1) DEFAULT NULL ,
;cpu_total; decimal(16,3) DEFAULT NULL ,
;memory_total; decimal(16,3) DEFAULT NULL ,
;fs_total; decimal(16,3) DEFAULT NULL ,
;version; varchar(32) DEFAULT NULL ,
;yaml_content; text CHARACTER SET utf8 COLLATE utf8_general_ci,
;master; tinyint(1) DEFAULT NULL ,
;allow_pods; int(8) DEFAULT NULL ,
;uid; varchar(64) DEFAULT NULL,
;start_time; timestamp NULL DEFAULT NULL ,
;delete_time; timestamp NULL DEFAULT NULL ,
PRIMARY KEY (;id;) USING BTREE,
UNIQUE KEY ;unique_uid_deleteflag_masterip; (;uid;,;delete_flag;,;master_ip;) USING BTREE,
KEY ;idx_nodename; (;node_name;) USING BTREE,
KEY ;idx_nodeip; (;node_ip;) USING BTREE,
KEY ;idx_masterip; (;master_ip;) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;原因猜想1;数据页空缺造成的页空洞?
innodb存储引擎中;表数据是以B;树的数据结构存储的;树的每个叶子节点存储一行数据;而这些节点又是存储在数据页;page;上;innodb引擎默认数据页的大小是16Kb;多个连续的页又会组成区(extent)。其存储结构如下图;
数据在B;树的实例如下;
我们要删掉 R4 这个记录;InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时;可能会复用这个位置。但是;磁盘文件的大小并不会缩小。如果删除page A上的所有数据;那么这个页会被标记为可复用。
不止删除数据会造成页空洞;插入数据时;若主键值不是自增而是随机的;那么就有可能触发页分裂而造成页空洞。
更新数据时;若更新了索引值;那么会删除旧数据;插入一条新数据;也会造成页空洞。例如将R4记录的id从500更新为800;那么会将R4记录标记为删除;同时新增一条记录。
若没有更新索引值;只是更新某个字段;那么如果更新后的记录的长度;占用字节数;比原记录长;且原记录前后无空闲位置;那么会执行delete;update操作;可能会造成页空洞。;这个场景有待严格的验证;
结合以上可能造成数据页空洞的场景;我们的表并没有频繁删除/新增记录的操作;这一点通过自增主键可以看出来;该表的自增主键的值仅为100;AUTO_INCREMENT=100;;因此不会是删除和新增数据造成页空洞。那么那就有可能是更新造成的页空洞。
为进一步确定是否是频繁更新造成的页空洞;我们通过查看TABLES表数据;其中DATA_FREE字段表示被分配但未使用的字节数大小;这个值为4194304=4M;这部分空间是可以被分配的;包括上面所说的记录被删除后造成的空白页。显然页空洞并不是造成数据文件过大的原因。
原因猜想2:text频繁更新引起?
再次看我们的表结构;其中有3个text字段;分别是labels、annotations、yaml_content。其中labels、annotations字段实际存储的字符长度在100;500这个范围;不算太大。而yaml_content字段保存了yaml文件;字符长度竟达到的10000;的长度。而这个字段又是频繁变动更新的。因此猜测是yaml_content频繁更新引起的ibd文件过大。
在此之前需要先了解一下;text字段是如何存储的;innodb数据页大小为16KB=16384byte;也就是最多存16384个字节。每个页存储的记录是有限制的;最多存16KB/2-200行的记录;也就是7992行。最少存2行的记录。那么对于超出16384个字符长度的行记录该如何存储呢?对于Compact行格式;有以下结论;
结论一;当行长度发生溢出时;数据存放在页类型为Uncompress BLOB页;溢出页;中结论二;对于行溢出数据;其保留数据的前768个字节的前缀数据;之后偏移量指向溢出页结论三;为保证一个页中至少有2条行记录;单条行记录阈值长度为8098;若行长度超过这个阈值;则会发生行溢出;数据会保存在溢出页中;数据页只保留前缀768个字节。结论四;当InnoDB更新存储在溢出页中的大字段时;将不会在原来的位置更新。而是会在写一个新值到一个新的空间;并且不会删除旧的值。注;Dynamic行格式;列存储是否放到off-page页;主要取决于行大小;他会把行中最长的一列放到off-page;直到数据页能存放下两行。TEXT或BLOB列<=40bytes时总是存在于数据页。这种方式可以避免compact那样把太多的大列值放到B-tree Node;数据页中只存放20个字节的指针;实际的数据存放在Off Page中;之前的Compact 和 Redundant 两种格式会存放768个字前缀字节;。
实际上我们的k8s_node_status表中yaml_content字段长度在10000;字符;且频繁更新;触发了上面结论4;导致表文件;.ibd文件;持续膨胀。
我们在测试环境复制一张k8s_node_status表;此时表中无数据;然后插入一条数据如下;
INSERT INTO ;test;.;k8s_node_status;(;id;, ;master_ip;, ;node_name;, ;node_ip;, ;node_status;, ;update_time;, ;create_time;, ;delete_flag;, ;reason;, ;message;, ;labels;, ;annotations;, ;taints;, ;cpu_total;, ;memory_total;, ;fs_total;, ;version;, ;yaml_content;, ;master;, ;allow_pods;, ;uid;, ;start_time;, ;delete_time;) VALUES (9, ;test_default_default;, ;10.10.103.219-slave;, ;10.10.103.219;, 0, ;2022-03-24 08:57:52;, ;2022-01-18 11:42:43;, 0, ;;, ;;, ;os: linux;, ;kubeadm.alpha.kubernetes.io/cri-socket: /var/run/dockershim.sock,management.cattle.io/pod-limits: {;cpu;:;6782m;,;memory;:;14756Mi;},management.cattle.io/pod-requests: {;cpu;:;6718m;,;memory;:;9722Mi;,;pods;:;21;},node.alpha.kubernetes.io/ttl: 0,projectcalico.org/IPv4Address: 10.10.103.219/24,projectcalico.org/IPv4IPIPTunnelAddr: 192.168.24.64,volumes.kubernetes.io/controller-managed-attach-detach: true;, 0, 8.000, 16657936384.000, 157696397312.000, ;26815075;, ;apiVersion: v1;, 0, 110, ;8f5111b0-1edc-4e7c-bd22-11733693642b;, ;2022-01-07 03:08:13;, NULL);这条数据中的text字段设置为几个字符;总字符数在1000左右;这时分析表文件;ibd文件;;可以看到如下结果;
分析;插入一条数据时;表文件中只有4个数据页<B-tree Node>,没有溢出页。符合上述结论3
然后我们插入一条“正常”数据;这条数据的yaml_content字段长度达到10000个字符;再分析ibd文件如下;
INSERT INTO ;test;.;k8s_node_status;(;id;, ;master_ip;, ;node_name;, ;node_ip;, ;node_status;, ;update_time;, ;create_time;, ;delete_flag;, ;reason;, ;message;, ;labels;, ;annotations;, ;taints;, ;cpu_total;, ;memory_total;, ;fs_total;, ;version;, ;yaml_content;, ;master;, ;allow_pods;, ;uid;, ;start_time;, ;delete_time;) VALUES (13, ;test_default_default;, ;10.10.103.221-slave;, ;10.10.103.221;, 0, ;2022-03-24 08:58:57;, ;2022-01-18 11:42:43;, 0, ;;, ;;, ;beta.kubernetes.io/arch: amd64,beta.kubernetes.io/os: linux,kubernetes.io/arch: amd64,kubernetes.io/hostname: 10.10.103.221-slave,kubernetes.io/os: linux;, ;kubeadm.alpha.kubernetes.io/cri-socket: /var/run/dockershim.sock,management.cattle.io/pod-limits: {;cpu;:;4882m;,;memory;:;6589Mi;},management.cattle.io/pod-requests: {;cpu;:;4718m;,;memory;:;5895Mi;,;pods;:;12;},node.alpha.kubernetes.io/ttl: 0,projectcalico.org/IPv4Address: 10.10.103.221/24,projectcalico.org/IPv4IPIPTunnelAddr: 192.168.68.192,volumes.kubernetes.io/controller-managed-attach-detach: true;, 0, 8.000, 16657932288.000, 157696397312.000, ;26815328;, ;apiVersion: v1
kind: Node
metadata:
annotations:
kubeadm.alpha.kubernetes.io/cri-socket: /var/run/dockershim.sock
management.cattle.io/pod-limits: ;{;cpu;:;4882m;,;memory;:;6589Mi;};
management.cattle.io/pod-requests: ;{;cpu;:;4718m;,;memory;:;5895Mi;,;pods;:;12;};
node.alpha.kubernetes.io/ttl: ;0;
projectcalico.org/IPv4Address: 10.10.103.221/24
projectcalico.org/IPv4IPIPTunnelAddr: 192.168.68.192
volumes.kubernetes.io/controller-managed-attach-detach: ;true;
creationTimestamp: ;2022-01-07T03:08:02.000;08:00;
labels:
beta.kubernetes.io/arch: amd64
beta.kubernetes.io/os: linux
kubernetes.io/arch: amd64
kubernetes.io/hostname: 10.10.103.221-slave
kubernetes.io/os: linux
managedFields:
- apiVersion: v1
fieldsType: FieldsV1
fieldsV1:
f:metadata:
f:annotations:
f:kubeadm.alpha.kubernetes.io/cri-socket: {}
manager: kubeadm
operation: Update
time: ;2022-01-07T03:08:03.000;08:00;
- apiVersion: v1
fieldsType: FieldsV1
fieldsV1:
f:metadata:
f:annotations:
f:projectcalico.org/IPv4Address: {}
f:projectcalico.org/IPv4IPIPTunnelAddr: {}
f:status:
f:conditions:
k:{;type;:;NetworkUnavailable;}:
.: {}
f:lastHeartbeatTime: {}
f:lastTransitionTime: {}
f:message: {}
f:reason: {}
f:status: {}
f:type: {}
manager: calico-node
operation: Update
time: ;2022-01-07T03:09:23.000;08:00;
- apiVersion: v1
fieldsType: FieldsV1
fieldsV1:
f:metadata:
f:annotations:
f:management.cattle.io/pod-limits: {}
f:management.cattle.io/pod-requests: {}
manager: agent
operation: Update
time: ;2022-03-10T09:43:22.000;08:00;
- apiVersion: v1
fieldsType: FieldsV1
fieldsV1:
f:metadata:
f:annotations:
f:node.alpha.kubernetes.io/ttl: {}
f:spec:
f:podCIDR: {}
f:podCIDRs:
.: {}
v:;192.168.3.0/24;: {}
manager: kube-controller-manager
operation: Update
time: ;2022-03-22T09:47:38.000;08:00;
- apiVersion: v1
fieldsType: FieldsV1
fieldsV1:
f:metadata:
f:annotations:
.: {}
f:volumes.kubernetes.io/controller-managed-attach-detach: {}
f:labels:
.: {}
f:beta.kubernetes.io/arch: {}
f:beta.kubernetes.io/os: {}
f:kubernetes.io/arch: {}
f:kubernetes.io/hostname: {}
f:kubernetes.io/os: {}
f:status:
f:addresses:
.: {}
k:{;type;:;Hostname;}:
.: {}
f:address: {}
f:type: {}
k:{;type;:;InternalIP;}:
.: {}
f:address: {}
f:type: {}
f:allocatable:
.: {}
f:cpu: {}
f:ephemeral-storage: {}
f:hugepages-1Gi: {}
f:hugepages-2Mi: {}
f:memory: {}
f:pods: {}
f:capacity:
.: {}
f:cpu: {}
f:ephemeral-storage: {}
f:hugepages-1Gi: {}
f:hugepages-2Mi: {}
f:memory: {}
f:pods: {}
f:conditions:
.: {}
k:{;type;:;DiskPressure;}:
.: {}
f:lastHeartbeatTime: {}
f:lastTransitionTime: {}
f:message: {}
f:reason: {}
f:status: {}
f:type: {}
k:{;type;:;MemoryPressure;}:
.: {}
f:lastHeartbeatTime: {}
f:lastTransitionTime: {}
f:message: {}
f:reason: {}
f:status: {}
f:type: {}
k:{;type;:;PIDPressure;}:
.: {}
f:lastHeartbeatTime: {}
f:lastTransitionTime: {}
f:message: {}
f:reason: {}
f:status: {}
f:type: {}
k:{;type;:;Ready;}:
.: {}
f:lastHeartbeatTime: {}
f:lastTransitionTime: {}
f:message: {}
f:reason: {}
f:status: {}
f:type: {}
f:daemonEndpoints:
f:kubeletEndpoint:
f:Port: {}
f:images: {}
f:nodeInfo:
f:architecture: {}
f:bootID: {}
f:containerRuntimeVersion: {}
f:kernelVersion: {}
f:kubeProxyVersion: {}
f:kubeletVersion: {}
f:machineID: {}
f:operatingSystem: {}
f:osImage: {}
f:systemUUID: {}
manager: kubelet
operation: Update
time: ;2022-03-22T09:47:59.000;08:00;
name: 10.10.103.221-slave
resourceVersion: ;26815328;
selfLink: /api/v1/nodes/10.10.103.221-slave
uid: c8cac3c8-e229-40dc-93e5-219a372fc80e
spec:
podCIDR: 192.168.3.0/24
podCIDRs:
- 192.168.3.0/24
status:
addresses:
- type: InternalIP
address: 10.10.103.221
- type: Hostname
address: 10.10.103.221-slave
allocatable:
cpu: ;8;
ephemeral-storage: ;140969826890;
hugepages-1Gi: ;0;
hugepages-2Mi: ;0;
memory: 16165112Ki
pods: ;110;
capacity:
cpu: ;8;
ephemeral-storage: 152962052Ki
hugepages-1Gi: ;0;
hugepages-2Mi: ;0;
memory: 16267512Ki
pods: ;110;
conditions:
- type: NetworkUnavailable
lastHeartbeatTime: ;2022-03-22T09:46:31.000;08:00;
lastTransitionTime: ;2022-03-22T09:46:31.000;08:00;
message: Calico is running on this node
reason: CalicoIsUp
status: ;False;
- type: MemoryPressure
lastHeartbeatTime: ;2022-03-24T08:56:50.000;08:00;
lastTransitionTime: ;2022-03-22T09:45:55.000;08:00;
message: kubelet has sufficient memory available
reason: KubeletHasSufficientMemory
status: ;False;
- type: DiskPressure
lastHeartbeatTime: ;2022-03-24T08:56:50.000;08:00;
lastTransitionTime: ;2022-03-22T09:45:55.000;08:00;
message: kubelet has no disk pressure
reason: KubeletHasNoDiskPressure
status: ;False;
- type: PIDPressure
lastHeartbeatTime: ;2022-03-24T08:56:50.000;08:00;
lastTransitionTime: ;2022-03-22T09:45:55.000;08:00;
message: kubelet has sufficient PID available
reason: KubeletHasSufficientPID
status: ;False;
- type: Ready
lastHeartbeatTime: ;2022-03-24T08:56:50.000;08:00;
lastTransitionTime: ;2022-03-22T09:45:55.000;08:00;
message: kubelet is posting ready status
reason: KubeletReady
status: ;True;
daemonEndpoints:
kubeletEndpoint:
port: 10250
images:
- names:
- 10.1.11.205/test-tool/myjmeter;sha256:08f8e98cdb364567d59f76b67c8c4879d0963eacc558b7e958d4b91b4fedd9a3
- 10.10.102.120:8443/testapp/myjmeter;sha256:08f8e98cdb364567d59f76b67c8c4879d0963eacc558b7e958d4b91b4fedd9a3
- 10.1.11.205/test-tool/myjmeter:v1.0
- 10.10.102.120:8443/testapp/myjmeter:v1.0
sizeBytes: 842714693
- names:
- 10.1.11.205/k8s-deploy/bookdemo;sha256:e80a8011e56092ea1ac19f622984ff985e3d5ef7d7025b779743222a2eb0c8ab
- 10.1.11.205/k8s-deploy/bookdemo:v5
sizeBytes: 840693868
- names:
- 10.10.102.213:8443/cloudnevro-test/nephele;sha256:e53ccbb3397ce2da422d5b5644f178c3b65aacded64cdc9aeb997eb63689fbcd
- 10.10.102.213:8443/cloudnevro-test/nephele:v1.0.0
sizeBytes: 773951646
- names:
- 10.10.102.213:8443/cloudnevro-jxjk/nephele;sha256:c8a728f32113a22a2becf7b89b916de6d79dcfab2c51f43a75c4bf284b1f86c5
- 10.10.102.213:8443/cloudnevro-jxjk/nephele:v1.0.0
sizeBytes: 773943383
- names:
- 10.10.102.213:8443/cloudnevro-jxjk/nephele;sha256:8a8fd140e074a2a5da3ba64250a93cbb49e03830ace5562fed6d29ecd26a26f1
sizeBytes: 773937443
- names:
- 10.10.102.213:8443/cloudnevro-jxjk/nephele;sha256:d6155d7d8db24b7b2e0a65052cd27b00bbc89c78ad4fc426bb694d45f2b2c187
sizeBytes: 773927434
- names:
- 10.1.11.205/k8s-deploy-test2/nephele;sha256:8d345345366ed39c55b125eb4a343c375266b79e19608ee6d2221c0f380ca4b4
sizeBytes: 772415391
- names:
- 10.1.11.205/k8s-deploy-test2/nephele;sha256:a0e9754459b2733cf7514aa0c0d318933f3335ca58b4a7153bb0f312b1a2ea47
- 10.1.11.205/k8s-deploy-test2/nephele:v1.0.0
sizeBytes: 772415336
- names:
- 10.1.11.205/k8s-deploy-test2/nephele;sha256:0b7baac8E4A3125161c342948729e995dbad086dd69e3cea21bbc4c4bbea731d
sizeBytes: 772408699
- names:
- 10.1.11.205/practice/bookdemo;sha256:9c735080822acb751c0c48c56711b81fb33a3074247979db5cb5f63852af620e
- 10.1.11.205/practice/bookdemo:v0.1
sizeBytes: 769109585
- names:
- 10.1.11.205/k8s-deploy-test2/nephele;sha256:a7793d97feaafb76683dfca04cff48d4372f58ee5fd09062a105243ce5c8afaa
sizeBytes: 759450360
- names:
- 10.1.11.205/k8s-deploy-test2/nephele;sha256:090bc396c88f2d3ffb8fba4d71fae95c62830002dd3620a290cb549e59abcb30
sizeBytes: 759434977
- names:
- 10.1.11.205/k8s-deploy-test2/nginx-ingress-controller;sha256:f778a612096d158bcad7196f30099eaf3cbdf34780dcdbb618107ccd25e3647a
- 10.10.102.120:8443/cloudmonitor/nginx-ingress-controller;sha256:f778a612096d158bcad7196f30099eaf3cbdf34780dcdbb618107ccd25e3647a
- 10.1.11.205/k8s-deploy-test2/nginx-ingress-controller:0.24.1-hc3
- 10.10.102.120:8443/cloudmonitor/nginx-ingress-controller:0.24.1-hc3
sizeBytes: 690449615
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:85ba7f7ba225106797131c9dc428b35e18308b4edf12830717af3666f61a0690
- 10.1.11.205/k8s-deploy-test2/apm-es-server:v1.0.0
sizeBytes: 685758589
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:0295530a3b82a54200336841b741a69867fa4674ce7a8ae10130e3cbbd5a2669
sizeBytes: 685758449
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:0a05d018f439b25033d3dc811c3f77f028c6c1e45f80641a5803248fea5c6924
sizeBytes: 685758444
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:5a9f0205b470f81b8d57b95ccd2c96ec1a654386dbe0c0122dbe90b95696552d
sizeBytes: 685758441
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:28a8abb342aacfdab7d948dd84681cb1cb9432473b662fc3bc81bd2100444a0b
sizeBytes: 685758400
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:4d88565334294b4061c0a7070205f1ed3cdd39538fc44b3252d701e5e293c5a6
sizeBytes: 685757724
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:5e062ab920630698b66a8e491e38fa699fcad930f5fefb7573c05a94fbc9da2d
sizeBytes: 685737656
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:326d5fe5ccfa0018aa7fd8b96aa3e24b19dce5c9ef16d247a79a1a93ae712a28
sizeBytes: 685694315
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:4dadd93520765c3493adf02248727fe2ddee5207429aa46319de06dc51201114
sizeBytes: 685694273
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:0c1d4a33e3b87aeb918f8bac862270ec59eb7d78ea9d21b55d36e2f53a14a830
sizeBytes: 685694265
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:fc42f962222a286751096044a66f754139322e91102ac9423b854d8ecb761d41
sizeBytes: 685694260
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:b1a3871927a913afd1d907b3b7e47190f8b70f7e0d36c9f9f7fe28fabe07891b
sizeBytes: 685681834
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:c435c426df269c0ba15e00ca64bc68d5cdfaa6ebf38ff6ac6ecbd79d1def93c1
sizeBytes: 685681780
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:0fb5bef7bf5647bace810d26954028849afaafcd17fbc2bd07ca383ee8d373e0
sizeBytes: 685681780
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:a37d86030d7847dae6cc9ceb2e5e0f934853bad8f40ad4769fd91b18d85e6f9e
sizeBytes: 685681452
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:797bfab4d0e202be082ed3727dc7d1d86c47a25e1286eb8e82fd9702c87f9671
sizeBytes: 685681386
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:94099e785dc6caa22bb19bee541b551f8d40facd77eeecebb3eb60c6a2fa1e0d
sizeBytes: 685681319
- names:
- 10.1.11.205/k8s-deploy-test2/apm-es-server;sha256:9c99aa4bc12a94b0085a4df59e824d2f14d0a43975bde91dfc5842bb7094c034
sizeBytes: 685209709
- names:
- 10.10.102.213:8443/cloudnevro-jxjk/apm-es-server;sha256:ca00480579aa73d6fc276996c1be057f4ba5f12f6a620caab2c43ed8d7cc3b5f
- 10.10.102.213:8443/cloudnevro-jxjk/apm-es-server:v1.0.0
sizeBytes: 682980650
- names:
- 10.10.102.213:8443/cloudnevro-jxjk/apm-es-server;sha256:045d610323cd35ba63477567a80dde86995878abbfd3239817c1d0d81cb963e2
sizeBytes: 682980650
- names:
- 10.10.102.213:8443/cloudnevro-jxjk/apm-es-server;sha256:4c751ba0f68a92dc1cf02c2b483dd25b7238772e1ee23ad1a95f18f1c37c3ac8
sizeBytes: 682980589
- names:
- 10.10.102.213:8443/cloudnevro-jxjk/apm-es-server;sha256:e8a0b1446aa899ae5eebe34ed3af02b1c31bc330dcf2b96430616a4b042d48ed
sizeBytes: 682980589
- names:
- 10.10.102.213:8443/cloudnevro-jxjk/apm-es-server;sha256:f05c3143f5b5021a27f2f4e34a4f14f601bbebb10cc634d14424db3db06d213c
sizeBytes: 682978980
- names:
- 10.10.102.120:8443/cloudmonitor-jxjk/apm-es-server;sha256:ef220df4ab1ac0c1105dd84677ce14225163b881fbd55166ae7501a0561d60bf
- 10.10.102.120:8443/cloudmonitor-jxjk/apm-es-server:v1.0.0
sizeBytes: 682978922
- names:
- 10.10.102.120:8443/cloudmonitor-jxjk/apm-es-server;sha256:af0b690c1b52ee8ae0195a827cec6eefd1d91fa15161153fe3efe6824f9a94e7
sizeBytes: 682978729
- names:
- 10.10.102.120:8443/cloudmonitor-test/apm-es-server;sha256:212f05220ff1952d486b8c326ccfbfd115583d4d194fed88a4bb9ddcabeacbee
- 10.10.102.120:8443/cloudmonitor-test/apm-es-server:v1.0.0
sizeBytes: 682974277
- names:
- 10.1.11.205/k8s-deploy/bookdemo;sha256:c3fe72f35c6b36d9502b0d3a4125dac4f711a096aac13a300b9360fc5eab41dd
- 10.1.11.205/k8s-deploy/bookdemo:v6
sizeBytes: 646684901
- names:
- 10.10.102.213:8443/cloudnevro-test/application-monitor;sha256:a96c4f23cf22d4a1f87a078f8043d519e5f28026637ff76d53a0628aa28af856
sizeBytes: 631964747
- names:
- 10.10.102.213:8443/cloudnevro-test/application-monitor;sha256:f4854b35997fc11568f68f7d6a034ea0f167cbb96c9abd26aa5ce2ff34ccb180
- 10.10.102.213:8443/cloudnevro-test/application-monitor:v1.0.0
sizeBytes: 631964747
- names:
- 10.10.102.120:8443/cloudmonitor-test/application-monitor;sha256:f0ef6b6b2e42cdfa9aa3889a78c3e84c2af9871f601a10b4456710209b6ea626
- 10.10.102.120:8443/cloudmonitor-test/application-monitor:v1.0.0
sizeBytes: 631964747
- names:
- 10.1.11.205/k8s-deploy-test2/apm-config-server;sha256:c88c8d20ede63247447f73368f6ba3efd942937398fd7e92f12a5e69eefed40a
- 10.1.11.205/k8s-deploy-test2/apm-config-server:v1.0.0
sizeBytes: 628053443
- names:
- 10.1.11.205/k8s-deploy-test2/apm-config-server;sha256:21afc3ee1cb0aa5a2107657162a1bde2220ee26535378a4ab4f052b8d594fe81
sizeBytes: 628053404
- names:
- 10.1.11.205/k8s-deploy-test2/apm-config-server;sha256:8d8b85c838c128f6e1923894a434d09e7614100d4ba2397d7cbdf01205174986
sizeBytes: 628052411
- names:
- 10.1.11.205/k8s-deploy-test2/application-monitor;sha256:5305e429d4d80b190404247af9afac8240deb910dc056e8cef4def1bc6a8cd17
- 10.1.11.205/k8s-deploy-test2/application-monitor:v1.0.0
sizeBytes: 618764177
- names:
- 10.1.11.205/k8s-deploy-test2/application-monitor;sha256:793afed6053fd14c53d7cadbcdc0738c096632b2525be83a0af3ec9ecc8d681f
sizeBytes: 615885903
- names:
- 10.10.102.213:8443/cloudnevro-test/apm-alarm;sha256:4a0d7854da18903da541c940b36796a8c831c9c1115b6d641f55053bd685876e
- 10.10.102.213:8443/cloudnevro-test/apm-alarm:v1.0.0
sizeBytes: 599375621
- names:
- 10.10.102.213:8443/cloudnevro-test/bookdemo;sha256:ce6da0f0a92256a446a7427ebd4f5da65e36ddb524bebc8af2ca9f0b8685b405
- 10.10.102.213:8443/cloudnevro-test/bookdemo:v.2.0-okhttp-nomq-3
sizeBytes: 596488490
nodeInfo:
architecture: amd64
bootID: 358c7056-d168-4ff9-af0e-84d1af398df8
containerRuntimeVersion: docker://20.10.12
kernelVersion: 3.10.0-693.el7.x86_64
kubeProxyVersion: v1.18.1
kubeletVersion: v1.18.1
machineID: 8a33de0213194776a70fc54cd50c024e
operatingSystem: linux
osImage: CentOS Linux 7 (Core)
systemUUID: 1B0D1242-F65D-310E-E70D-7731ABAE59F0
;, 0, 110, ;c8cac3c8-e229-40dc-93e5-219a372fc80e;, ;2022-01-07 03:08:02;, NULL);
分析;此时因为数据长度超过溢出阈值;发生了页溢出;从上图可以看出新增了2个溢出页<pages of uncompressed LOB>.
我们将这个条数据更新100次;每次更新仅更新yaml_content字段;且每次在原yaml_content字段后随机增加0-100个字符;然后分析ibd文件;结果如下;
分析;此时虽更新100次;但数据页<B-tree Node>并未增加;而溢出页<pages of uncompressed LOB>持续增加。
同时ibd文件大小已从177K增加到224K大小。
这个结果验证了之前的结论;也就是大的text字段更新时;将不会在原来的位置更新。而是会在写一个新值到一个新的空间;并且不会删除旧的值。
解决方案;
1、重建表
由于表记录持续频繁更新;表文件也持续增大;我们可以通过重建表来释放空间;可执行以下语句;
alter table k8s_node_status engine=InnoDB;
这个语句首先会建一张临时表;然后将旧表数据迁移到临时表;迁移完之后;使用临时表替换旧表;最后释放旧表空间。并且这个过程是Online的;即迁移过程中表依旧可供业务增删改查;不影响业务。
因为k8s_node_status表数据量比较小;这个重建过程耗时也是比较短;重建之后表文件从之前的1.8G减小到7M大小。
该方案可作为一种临时方案;通过一个定时任务;每天/每周执行一次重建表过程;在表数据不大的情况下;重建过程对业务影响较小。
2、优化字段
对于数据比较大的text/blob/varchar字段;若字段不会频繁更新或不会更新;那么存储在mysql中没什么问题。若确实需要频繁更新;那么需要考虑这个字段是否需要存储这么长的字段;能否将字段长度保持在一定长度内;8089;;超出长度对字段进行截断。避免页溢出。
ref;
1、官方文档TABLES表说明MySQL :: MySQL 8.0 Reference Manual :: 26.3.38 The INFORMATION_SCHEMA TABLES Table
2、林晓斌;为什么表数据删掉一半;表文件大小不变?
3、MySQL技术内幕;InnoDB存储引擎;第四章
4、高性能MySQL
5、ibd文件分析工具;GitHub - SimonOrK/py_innodb_page_info_GUI: 查看mysql的ibd文件的工具;可以查看ibd文件中页的分布及类型等信息;本作品是重制版;添加了UI;并且支持中英文;添加了MYSQL8的新页类型。本作品只供学习交流使用;请勿用于商业用途查看mysql的ibd文件的工具;可以查看ibd文件中页的分布及类型等信息;本作品是重制版;添加了UI;并且支持中英文;添加了MYSQL8的新页类型。本作品只供学习交流使用;请勿用于商业用途 - GitHub - SimonOrK/py_innodb_page_info_GUI: 查看mysql的ibd文件的工具;可以查看ibd文件中页的分布及类型等信息;本作品是重制版;添加了UI;并且支持中英文;添加了MYSQL8的新页类型。本作品只供学习交流使用;请勿用于商业用途
https://github.com/SimonOrK/py_innodb_page_info_GUI.git
加载全部内容