背景描述
SonarQube 自去年使用开始，已运行一年有余
它上面的 Project 数量已超过 1000
因为每个应用的每个 git 分支的每次 push 事件都会触发 Sonar 分析任务
这样会有很多的数据都存储下来，从而导致数据量很大
目前 Mysql 数据库占用磁盘空间达到100G
并且随着时间推移会继续增加
于是在想是否可以进行数据清理，以降低所使用的磁盘空间
如何进行数据清理？
那么如何进行数据清理呢？
经过不断探索与实践，形成如下解决方案：
1、调小【数据库清理器】相关参数
2、设置 master 为长期分支，并修改【保留短期分支】参数
3、清理非主分支的长期分支
4、MySQL Innodb DATA_FREE 清理
调小【数据库清理器】相关参数
配置—>通用配置 —> 数据库清理器
调小【数据库清理器】相关参数，缩短数据保留时间，以便尽早释放空间
设置 master 为长期分支
配置—>通用配置 —> Branches
默认情况下，名称以 “branch” 或 “release” 开头的分支将被视为长期分支，长期分支系统不会做自动清理。
将 master 设置为长期分支，那么其它分支就是短期分支，短期分支系统会做自动清理。
当然已经存在的长期分支不会自动更改为短期分支，需要先删除它，然后重新在这个分支执行 sonar 分析任务，那么它就会变为短期分支
此外，修改【清除不活跃的短分支前的保留天数】参数，改为10
使用长期分支模式后，每个 Project 分支页面如下：
短期分支会显示和长期分支有差异的 issue
清理非主分支的长期分支
修改为长期分支模式的两个月后，清理非主分支的长期分支
这里使用了 SonarQube API 进行了批量清理，清理任务花费了「三个多小时」～
相关 python 脚本可以参考：https://github.com/donhui/python-sonarqube/blob/master/sonarqube_branches_clean.py
MySQL Innodb DATA_FREE 清理
对 SonarQube 数据库进行分析：
mysql> SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
+--------------------------+---------------+
| DB Name | DB Size in MB |
+--------------------------+---------------+
| dingtalk_develop_members | 0.1 |
| information_schema | 0.2 |
| sonar | 90477.8 |
| sonar_statistics | 5.1 |
+--------------------------+---------------+
4 rows in set (0.30 sec)
mysql> SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = 'sonar';
+--------------------------+------------+
| Table | Size in MB |
+--------------------------+------------+
| active_rule_parameters | 0.03 |
| active_rules | 0.42 |
| analysis_properties | 0.02 |
| ce_activity | 32.55 |
| ce_queue | 0.05 |
| ce_scanner_context | 619.05 |
| ce_task_characteristics | 51.19 |
| ce_task_input | 1.52 |
| default_qprofiles | 0.03 |
| duplications_index | 0.05 |
| es_queue | 0.03 |
| events | 6.20 |
| file_sources | 33973.41 |
| group_roles | 2.53 |
| groups | 0.02 |
| groups_users | 0.23 |
| internal_properties | 0.02 |
| issue_changes | 160.84 |
| issues | 372.92 |
| loaded_templates | 0.03 |
| manual_measures | 0.02 |
| metrics | 0.08 |
| notifications | 0.02 |
| org_qprofiles | 0.05 |
| organization_members | 0.05 |
| organizations | 0.03 |
| perm_templates_groups | 0.02 |
| perm_templates_users | 0.02 |
| perm_tpl_characteristics | 0.02 |
| permission_templates | 0.02 |
| plugins | 0.03 |
| project_branches | 1.80 |
| project_links | 4.52 |
| project_measures | 49670.27 |
| project_qprofiles | 0.02 |
| projects | 5483.33 |
| properties | 0.03 |
| qprofile_changes | 1.92 |
| qprofile_edit_groups | 0.02 |
| qprofile_edit_users | 0.02 |
| quality_gate_conditions | 0.02 |
| quality_gates | 0.02 |
| rule_repositories | 0.02 |
| rules | 3.70 |
| rules_metadata | 0.02 |
| rules_parameters | 0.08 |
| rules_profiles | 0.03 |
| schema_migrations | 0.02 |
| snapshots | 7.69 |
| user_roles | 0.78 |
| user_tokens | 0.05 |
| users | 0.14 |
| webhook_deliveries | 0.05 |
+--------------------------+------------+
53 rows in set (0.02 sec)
数据量大的表：
| ce_scanner_context | 619.05 |
| file_sources | 33973.41 |
| issue_changes | 160.84 |
| issues | 372.92 |
| project_measures | 49670.27 |
| projects | 5483.33 |
清理非主分支的长期分支后，发现 data_length + index_length 下降，但是磁盘空间并没有减少
向 DBA 求助，得到的解决方案为：清理数据库 DATA_FREE，回收表空间
查看表相关的元数据信息，包括 TABLE_NAME,ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE ，SQL 语句及输出如下：
mysql> SELECT TABLE_NAME,ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='sonar';
+--------------------------+------------+------------+-------------+--------------+-----------------+-------------+--------+
| TABLE_NAME | ROW_FORMAT | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | MAX_DATA_LENGTH | DATA_FREE | ENGINE |
+--------------------------+------------+------------+-------------+--------------+-----------------+-------------+--------+
| active_rule_parameters | Dynamic | 138 | 16384 | 16384 | 0 | 0 | InnoDB |
| active_rules | Dynamic | 4385 | 294912 | 147456 | 0 | 0 | InnoDB |
| analysis_properties | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| ce_activity | Dynamic | 74861 | 16793600 | 17334272 | 0 | 4194304 | InnoDB |
| ce_queue | Dynamic | 0 | 16384 | 32768 | 0 | 0 | InnoDB |
| ce_scanner_context | Dynamic | 17122 | 649117696 | 0 | 0 | 1286602752 | InnoDB |
| ce_task_characteristics | Dynamic | 228069 | 36290560 | 17383424 | 0 | 5242880 | InnoDB |
| ce_task_input | Dynamic | 0 | 344064 | 0 | 0 | 113246208 | InnoDB |
| default_qprofiles | Dynamic | 14 | 16384 | 16384 | 0 | 0 | InnoDB |
| duplications_index | Dynamic | 0 | 16384 | 32768 | 0 | 0 | InnoDB |
| es_queue | Dynamic | 0 | 16384 | 16384 | 0 | 5242880 | InnoDB |
| events | Dynamic | 2882 | 3342336 | 2457600 | 0 | 5242880 | InnoDB |
| file_sources | Dynamic | 168412 | 8828141568 | 281624576 | 0 | 33689698304 | InnoDB |
| group_roles | Dynamic | 7000 | 507904 | 2146304 | 0 | 6291456 | InnoDB |
| groups | Dynamic | 48 | 16384 | 0 | 0 | 0 | InnoDB |
| groups_users | Dynamic | 1060 | 81920 | 163840 | 0 | 0 | InnoDB |
| internal_properties | Dynamic | 3 | 16384 | 0 | 0 | 0 | InnoDB |
| issue_changes | Dynamic | 762484 | 104284160 | 64372736 | 0 | 6291456 | InnoDB |
| issues | Dynamic | 184354 | 200441856 | 164954112 | 0 | 35651584 | InnoDB |
| loaded_templates | Dynamic | 2 | 16384 | 16384 | 0 | 0 | InnoDB |
| manual_measures | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| metrics | Dynamic | 169 | 65536 | 16384 | 0 | 0 | InnoDB |
| notifications | Dynamic | 0 | 16384 | 0 | 0 | 6291456 | InnoDB |
| org_qprofiles | Dynamic | 34 | 16384 | 32768 | 0 | 0 | InnoDB |
| organization_members | Dynamic | 393 | 49152 | 0 | 0 | 0 | InnoDB |
| organizations | Dynamic | 1 | 16384 | 16384 | 0 | 0 | InnoDB |
| perm_templates_groups | Dynamic | 4 | 16384 | 0 | 0 | 0 | InnoDB |
| perm_templates_users | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| perm_tpl_characteristics | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| permission_templates | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| plugins | Dynamic | 23 | 16384 | 16384 | 0 | 0 | InnoDB |
| project_branches | Dynamic | 1673 | 229376 | 114688 | 0 | 0 | InnoDB |
| project_links | Dynamic | 26829 | 4734976 | 0 | 0 | 4194304 | InnoDB |
| project_measures | Dynamic | 14545516 | 7061143552 | 10892787712 | 0 | 36237737984 | InnoDB |
| project_qprofiles | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| projects | Dynamic | 398678 | 1495941120 | 1684144128 | 0 | 2999975936 | InnoDB |
| properties | Dynamic | 125 | 16384 | 16384 | 0 | 0 | InnoDB |
| qprofile_changes | Dynamic | 5049 | 1589248 | 425984 | 0 | 4194304 | InnoDB |
| qprofile_edit_groups | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| qprofile_edit_users | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| quality_gate_conditions | Dynamic | 12 | 16384 | 0 | 0 | 0 | InnoDB |
| quality_gates | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| rule_repositories | Dynamic | 37 | 16384 | 0 | 0 | 0 | InnoDB |
| rules | Dynamic | 3185 | 3686400 | 196608 | 0 | 4194304 | InnoDB |
| rules_metadata | Dynamic | 5 | 16384 | 0 | 0 | 0 | InnoDB |
| rules_parameters | Dynamic | 328 | 65536 | 16384 | 0 | 0 | InnoDB |
| rules_profiles | Dynamic | 33 | 16384 | 16384 | 0 | 0 | InnoDB |
| schema_migrations | Dynamic | 237 | 16384 | 0 | 0 | 0 | InnoDB |
| snapshots | Dynamic | 4715 | 1589248 | 327680 | 0 | 2097152 | InnoDB |
| user_roles | Dynamic | 6338 | 507904 | 311296 | 0 | 0 | InnoDB |
| user_tokens | Dynamic | 34 | 16384 | 32768 | 0 | 0 | InnoDB |
| users | Dynamic | 438 | 114688 | 32768 | 0 | 0 | InnoDB |
| webhook_deliveries | Dynamic | 0 | 16384 | 32768 | 0 | 0 | InnoDB |
+--------------------------+------------+------------+-------------+--------------+-----------------+-------------+--------+
53 rows in set (0.00 sec)
数据库清理 DATA_FREE，回收表空间，SQL 语句及输出如下：
mysql> ALTER TABLE file_sources ENGINE=InnoDB;
Query OK, 0 rows affected (2 min 41.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE issue_changes ENGINE=InnoDB;
Query OK, 0 rows affected (4.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE ce_scanner_context ENGINE=InnoDB;
Query OK, 0 rows affected (14.82 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE project_measures ENGINE=InnoDB;
Query OK, 0 rows affected (3 min 55.74 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE projects ENGINE=InnoDB;
Query OK, 0 rows affected (25.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
清理 DATA_FREE 之后，再次查看表相关的元数据信息：
mysql> SELECT TABLE_NAME,ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='sonar';
+--------------------------+------------+------------+-------------+--------------+-----------------+-----------+--------+
| TABLE_NAME | ROW_FORMAT | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | MAX_DATA_LENGTH | DATA_FREE | ENGINE |
+--------------------------+------------+------------+-------------+--------------+-----------------+-----------+--------+
| active_rule_parameters | Dynamic | 138 | 16384 | 16384 | 0 | 0 | InnoDB |
| active_rules | Dynamic | 4385 | 294912 | 147456 | 0 | 0 | InnoDB |
| analysis_properties | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| ce_activity | Dynamic | 74971 | 16793600 | 17334272 | 0 | 4194304 | InnoDB |
| ce_queue | Dynamic | 0 | 16384 | 32768 | 0 | 0 | InnoDB |
| ce_scanner_context | Dynamic | 17451 | 659079168 | 0 | 0 | 6291456 | InnoDB |
| ce_task_characteristics | Dynamic | 228289 | 36290560 | 17383424 | 0 | 5242880 | InnoDB |
| ce_task_input | Dynamic | 0 | 1589248 | 0 | 0 | 113246208 | InnoDB |
| default_qprofiles | Dynamic | 14 | 16384 | 16384 | 0 | 0 | InnoDB |
| duplications_index | Dynamic | 0 | 16384 | 32768 | 0 | 0 | InnoDB |
| es_queue | Dynamic | 0 | 16384 | 16384 | 0 | 5242880 | InnoDB |
| events | Dynamic | 3300 | 3342336 | 2457600 | 0 | 5242880 | InnoDB |
| file_sources | Dynamic | 122205 | 3011493888 | 22626304 | 0 | 5242880 | InnoDB |
| group_roles | Dynamic | 7000 | 507904 | 2146304 | 0 | 6291456 | InnoDB |
| groups | Dynamic | 48 | 16384 | 0 | 0 | 0 | InnoDB |
| groups_users | Dynamic | 1060 | 81920 | 163840 | 0 | 0 | InnoDB |
| internal_properties | Dynamic | 3 | 16384 | 0 | 0 | 0 | InnoDB |
| issue_changes | Dynamic | 731182 | 101318656 | 43089920 | 0 | 6291456 | InnoDB |
| issues | Dynamic | 184366 | 200441856 | 164954112 | 0 | 35651584 | InnoDB |
| loaded_templates | Dynamic | 2 | 16384 | 16384 | 0 | 0 | InnoDB |
| manual_measures | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| metrics | Dynamic | 169 | 65536 | 16384 | 0 | 0 | InnoDB |
| notifications | Dynamic | 0 | 16384 | 0 | 0 | 6291456 | InnoDB |
| org_qprofiles | Dynamic | 34 | 16384 | 32768 | 0 | 0 | InnoDB |
| organization_members | Dynamic | 393 | 49152 | 0 | 0 | 0 | InnoDB |
| organizations | Dynamic | 1 | 16384 | 16384 | 0 | 0 | InnoDB |
| perm_templates_groups | Dynamic | 4 | 16384 | 0 | 0 | 0 | InnoDB |
| perm_templates_users | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| perm_tpl_characteristics | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| permission_templates | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| plugins | Dynamic | 23 | 16384 | 16384 | 0 | 0 | InnoDB |
| project_branches | Dynamic | 1686 | 229376 | 114688 | 0 | 0 | InnoDB |
| project_links | Dynamic | 26866 | 4734976 | 0 | 0 | 4194304 | InnoDB |
| project_measures | Dynamic | 10381786 | 2282733568 | 1306165248 | 0 | 51380224 | InnoDB |
| project_qprofiles | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| projects | Dynamic | 374448 | 314392576 | 132235264 | 0 | 4194304 | InnoDB |
| properties | Dynamic | 125 | 16384 | 16384 | 0 | 0 | InnoDB |
| qprofile_changes | Dynamic | 5049 | 1589248 | 425984 | 0 | 4194304 | InnoDB |
| qprofile_edit_groups | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| qprofile_edit_users | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| quality_gate_conditions | Dynamic | 12 | 16384 | 0 | 0 | 0 | InnoDB |
| quality_gates | Dynamic | 0 | 16384 | 0 | 0 | 0 | InnoDB |
| rule_repositories | Dynamic | 37 | 16384 | 0 | 0 | 0 | InnoDB |
| rules | Dynamic | 3185 | 3686400 | 196608 | 0 | 4194304 | InnoDB |
| rules_metadata | Dynamic | 5 | 16384 | 0 | 0 | 0 | InnoDB |
| rules_parameters | Dynamic | 328 | 65536 | 16384 | 0 | 0 | InnoDB |
| rules_profiles | Dynamic | 33 | 16384 | 16384 | 0 | 0 | InnoDB |
| schema_migrations | Dynamic | 237 | 16384 | 0 | 0 | 0 | InnoDB |
| snapshots | Dynamic | 4737 | 1589248 | 327680 | 0 | 4194304 | InnoDB |
| user_roles | Dynamic | 6338 | 507904 | 311296 | 0 | 0 | InnoDB |
| user_tokens | Dynamic | 34 | 16384 | 32768 | 0 | 0 | InnoDB |
| users | Dynamic | 438 | 114688 | 32768 | 0 | 0 | InnoDB |
| webhook_deliveries | Dynamic | 0 | 16384 | 32768 | 0 | 0 | InnoDB |
+--------------------------+------------+------------+-------------+--------------+-----------------+-----------+--------+
53 rows in set (0.06 sec)
从上面可以看出，相关表（如：project_measures，projects，file_sources）的 DATA_FREE 的大小有很大幅度下降。
然后，查看数据库所占磁盘空间，也有大幅度下降：100G —> 9G 。
至此，SonarQube 的数据清理已取得很好的成果，以此为记。
