数据库服务器满了 —— 优化数据表 OPTIMIZE TABLE 省出来40%空间，惊！ #277

New Issue

2025-12-27T02:04:08+08:00

bigtran commented

2025-12-27 02:04:08 +08:00

1、查看大文件 find / -type f -size +80M -exec ls -lh {} + | sort -k5 -hr

2、优化数据表 OPTIMIZE TABLE

OPTIMIZE TABLE opm_mw_info_data;
OPTIMIZE TABLE opm_mw_raw_data;

root@iZ8vb8yjo0evdjmag79vs6Z:/var/log/journal# find / -type f -size +80M -exec ls -lh {} + | sort -k5 -hr
find: ‘/proc/15602/task/15602/fdinfo/6’: No such file or directory
find: ‘/proc/15602/fdinfo/5’: No such file or directory
-r--------  1 root     root            128T Dec 27 01:49 /proc/kcore
-rw-r-----  1 mysql    mysql           7.6G Dec 27 01:48 /var/lib/mysql/opm_mw_medical_waste/opm_mw_info_data.ibd
-rw-r-----  1 mysql    mysql           3.4G Dec 26 20:17 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000001c29_0000000000001de7_INDEX_1.ibd
-rw-r-----  1 mysql    mysql           3.3G Dec 27 01:48 /var/lib/mysql/opm_mw_medical_waste/opm_mw_report_log.ibd
-rw-r-----  1 mysql    mysql           2.8G Dec 27 01:46 /var/lib/mysql/opm_mw_medical_waste/opm_mw_raw_data.ibd
-rw-r-----  1 mysql    mysql           2.6G Sep  3 17:30 /var/lib/mysql/opm_mw_medical_waste/opm_mw_info_data_2024.ibd
-rw-r-----  1 mysql    mysql           2.0G Aug 25 14:42 /var/lib/mysql/opm_mw_medical_waste/opm_mw_info_data_2023.ibd
-rw-r-----  1 mysql    mysql           1.8G Mar 25  2025 /var/lib/mysql/opm_mw_medical_waste/opm_mw_info_data_2022.ibd
-rw-r-----  1 mysql    mysql           932M Dec 26 20:17 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000001c29_0000000000001de7_INDEX_5.ibd
-rw-r-----  1 mysql    mysql           812M Dec 26 23:38 /var/lib/mysql/opm_mw_medical_waste/opm_mw_raw_data_copy1.ibd
-rw-r-----  1 mysql    mysql           716M Dec 27 01:48 /var/lib/mysql/ibdata1
-rw-r-----  1 mysql    mysql           524M Mar  1  2025 /var/lib/mysql/opm_mw_medical_waste/opm_mw_info_data_2021.ibd
-rw-r-----  1 mysql    mysql           476M Dec 26 20:42 /var/lib/mysql/opm_mw_medical_waste/opm_mw_data.ibd
-rw-r-----  1 mysql    mysql           416M Dec 26 23:41 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000002b7e_0000000000002ea5_INDEX_1.ibd
-rw-r-----  1 mysql    mysql           398M Dec 27 01:41 /var/lib/mysql/opm_mw_medical_waste/opm_mw_raw_data_all.ibd
-rw-r-----  1 mysql    mysql           332M Dec 27 01:45 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000001c29_DELETED.ibd
-rw-r-----  1 mysql    mysql           228M Dec 27 01:42 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000002b8a_0000000000002ebc_INDEX_1.ibd
-rw-r--r--+ 1 root     systemd-journal 216M Dec  3 03:35 /var/log/journal/31ef12d25fde0293ebe62cb760a7297c.tar.gz
-rw-r-----  1 mysql    mysql           180M Dec 26 20:17 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000001c29_0000000000001de7_INDEX_2.ibd
-rw-r-----  1 mysql    mysql           164M Mar  1  2025 /var/lib/mysql/opm_mw_medical_waste/opm_mw_info_data_2020.ibd
-rw-r-----  1 mysql    mysql           160M Dec 26 22:32 /var/lib/mysql/opm_mw_medical_waste/opm_mw_js_report_log.ibd
-rw-r-----  1 mysql    mysql           136M Dec 26 20:17 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000001c29_0000000000001de7_INDEX_3.ibd
-rw-r--r--  1 root     root            115M Dec  3 03:42 /var/backups/opm_mw_info_data.sql.tar.gz
-rw-r-----  1 mysql    mysql           104M Dec 26 23:41 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000002b7e_0000000000002ea5_INDEX_5.ibd
-rw-r-----  1 www-data adm              89M Dec 26 06:21 /var/log/nginx/error.log.1
-rw-r-----+ 1 root     systemd-journal  88M Dec 14 18:24 /var/log/journal/31ef12d25fde0293ebe62cb760a7297c/system@8c7650944e2c41848057021b401fe7ba-0000000000439d1b-000644f4bd72c630.journal
-rw-r--r--  1 root     root             84M Dec  3 03:45 /var/backups/opm_mw_info_data_2023.sql.tar.gz
-rw-r-----  1 mysql    mysql            84M Feb 26  2025 /var/lib/mysql/opm_mw_medical_waste/opm_mw_route_sign.ibd

# 1、查看大文件 find / -type f -size +80M -exec ls -lh {} + | sort -k5 -hr # 2、优化数据表 OPTIMIZE TABLE OPTIMIZE TABLE opm_mw_info_data; OPTIMIZE TABLE opm_mw_raw_data; ![image.png](/attachments/f9b6fb08-101d-4ab8-be2d-d5f9afb3955e) ![image.png](/attachments/07a4fdef-ddd4-4569-841e-12afd17d15d3) ![image.png](/attachments/d9e69e52-2cff-4f57-89e6-37f50b4de9db) ``` root@iZ8vb8yjo0evdjmag79vs6Z:/var/log/journal# find / -type f -size +80M -exec ls -lh {} + | sort -k5 -hr find: ‘/proc/15602/task/15602/fdinfo/6’: No such file or directory find: ‘/proc/15602/fdinfo/5’: No such file or directory -r-------- 1 root root 128T Dec 27 01:49 /proc/kcore -rw-r----- 1 mysql mysql 7.6G Dec 27 01:48 /var/lib/mysql/opm_mw_medical_waste/opm_mw_info_data.ibd -rw-r----- 1 mysql mysql 3.4G Dec 26 20:17 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000001c29_0000000000001de7_INDEX_1.ibd -rw-r----- 1 mysql mysql 3.3G Dec 27 01:48 /var/lib/mysql/opm_mw_medical_waste/opm_mw_report_log.ibd -rw-r----- 1 mysql mysql 2.8G Dec 27 01:46 /var/lib/mysql/opm_mw_medical_waste/opm_mw_raw_data.ibd -rw-r----- 1 mysql mysql 2.6G Sep 3 17:30 /var/lib/mysql/opm_mw_medical_waste/opm_mw_info_data_2024.ibd -rw-r----- 1 mysql mysql 2.0G Aug 25 14:42 /var/lib/mysql/opm_mw_medical_waste/opm_mw_info_data_2023.ibd -rw-r----- 1 mysql mysql 1.8G Mar 25 2025 /var/lib/mysql/opm_mw_medical_waste/opm_mw_info_data_2022.ibd -rw-r----- 1 mysql mysql 932M Dec 26 20:17 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000001c29_0000000000001de7_INDEX_5.ibd -rw-r----- 1 mysql mysql 812M Dec 26 23:38 /var/lib/mysql/opm_mw_medical_waste/opm_mw_raw_data_copy1.ibd -rw-r----- 1 mysql mysql 716M Dec 27 01:48 /var/lib/mysql/ibdata1 -rw-r----- 1 mysql mysql 524M Mar 1 2025 /var/lib/mysql/opm_mw_medical_waste/opm_mw_info_data_2021.ibd -rw-r----- 1 mysql mysql 476M Dec 26 20:42 /var/lib/mysql/opm_mw_medical_waste/opm_mw_data.ibd -rw-r----- 1 mysql mysql 416M Dec 26 23:41 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000002b7e_0000000000002ea5_INDEX_1.ibd -rw-r----- 1 mysql mysql 398M Dec 27 01:41 /var/lib/mysql/opm_mw_medical_waste/opm_mw_raw_data_all.ibd -rw-r----- 1 mysql mysql 332M Dec 27 01:45 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000001c29_DELETED.ibd -rw-r----- 1 mysql mysql 228M Dec 27 01:42 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000002b8a_0000000000002ebc_INDEX_1.ibd -rw-r--r--+ 1 root systemd-journal 216M Dec 3 03:35 /var/log/journal/31ef12d25fde0293ebe62cb760a7297c.tar.gz -rw-r----- 1 mysql mysql 180M Dec 26 20:17 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000001c29_0000000000001de7_INDEX_2.ibd -rw-r----- 1 mysql mysql 164M Mar 1 2025 /var/lib/mysql/opm_mw_medical_waste/opm_mw_info_data_2020.ibd -rw-r----- 1 mysql mysql 160M Dec 26 22:32 /var/lib/mysql/opm_mw_medical_waste/opm_mw_js_report_log.ibd -rw-r----- 1 mysql mysql 136M Dec 26 20:17 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000001c29_0000000000001de7_INDEX_3.ibd -rw-r--r-- 1 root root 115M Dec 3 03:42 /var/backups/opm_mw_info_data.sql.tar.gz -rw-r----- 1 mysql mysql 104M Dec 26 23:41 /var/lib/mysql/opm_mw_medical_waste/FTS_0000000000002b7e_0000000000002ea5_INDEX_5.ibd -rw-r----- 1 www-data adm 89M Dec 26 06:21 /var/log/nginx/error.log.1 -rw-r-----+ 1 root systemd-journal 88M Dec 14 18:24 /var/log/journal/31ef12d25fde0293ebe62cb760a7297c/system@8c7650944e2c41848057021b401fe7ba-0000000000439d1b-000644f4bd72c630.journal -rw-r--r-- 1 root root 84M Dec 3 03:45 /var/backups/opm_mw_info_data_2023.sql.tar.gz -rw-r----- 1 mysql mysql 84M Feb 26 2025 /var/lib/mysql/opm_mw_medical_waste/opm_mw_route_sign.ibd ```

image.png

23 KiB

image.png

30 KiB

image.png

171 KiB

bigtran commented

2025-12-27 02:23:34 +08:00