St. Petersburg, Russian Federation
employee from 01.01.2004 to 01.01.2025
An experimentally substantiated approach to assessing the impact of data fragmentation on MySQL (InnoDB) performance and selecting a method for its elimination is proposed. Purpose: to study the dependence of read and write operation execution time on the degree of data fragmentation and to determine the most effective defragmentation method. Methods: analysis of InnoDB architecture (B+-trees, page split); creation of an experimental testbed based on MySQL 8.0 in Docker; generation of a test table with 500,000 records; simulation of fragmentation through massive DELETE, UPDATE, and chaotic INSERT operations; load testing using sysbench; measurement of query time, latency, IOPS, and data_free. Results: fragmentation slows down SELECT by 2.5–3.5 times, INSERT and UPDATE by 2–3 times. An increase in data_free from 2 to 38 MB correlates with a 58% drop in TPS. OPTIMIZE TABLE restores performance to 95–98% of the original level. Practical significance: monitoring and scheduled defragmentation techniques are applicable to transport information systems with high data update intensity (tracking, logistics).
data fragmentation, MySQL, InnoDB, performance, page split, OPTIMIZE TABLE, B+-tree, data_free, intelligent transport systems
1. The InnoDB Storage Engine Oracle // MySQL 8.0 Reference Manual. URL: http://dev.mysql.com/doc/refman/8.0/ en/innodb-storage-engine.html (data obrascheniya: 13.05.2026).
2. Schwartz B., Zaitsev P., Tkachenko V. High Performance MySQL: Optimization, Backups, and Replication. Third Edition. Sebastopol (CA): O’Reilly Media, 2012. 826 p.
3. Homonenko A. D., Cygankov V. M., Mal'cev M. G. Bazy dannyh: uchebnik dlya vuzov / pod red. A. D. Homonenko. 6-e izd., dop. SPb.: Korona-Vek, 2009. 736 s.
4. Zaitsev P. MySQL File System Fragmentation Benchmarks // Percona Blog. 22.03.2008. URL: http://www.percona. com/blog/mysql-file-system-fragmentation-benchmarks (data obrascheniya: 13.05.2026).
5. Huang S.-M., Chang L.-P. Exploiting Page Correlations for Write Buffering in Page-Mapping Multichannel SSDs // ACM Transactions on Embedded Computing Systems. 2016. Vol. 15, iss. 1. Art. no. 12. 25 p. DOI:https://doi.org/10.1145/2815622
6. Malyutin A. G., Lavruhin A. A., Okishev A. S. Arhitekturnye aspekty realizacii korporativnoy informacionnoy sistemy monitoringa i ucheta resursov // Izvestiya Transsiba. 2017. № 4 (32). S. 130–141.
7. Pavlov D. V. Relyacionnaya raspredelennaya sistema upravleniya bazami dannyh s avtomaticheskoy masshtabiruemost'yu // Vestnik Ufimskogo gosudarstvennogo aviacionnogo tehnicheskogo universiteta. 2012. T. 16, № 3 (48). S. 143–152.
8. Malygin D. S. Problemy proizvoditel'nosti relyacionnyh baz dannyh v raspredelennyh arhitekturah i strategii ih resheniya // Sovremennye naukoemkie tehnologii. 2024. № 10. S. 61–71. DOI:https://doi.org/10.17513/snt.40173
9. Harchenko P. A. Sovershenstvovanie sistemy i tehnologii ekspluatacii podvizhnogo sostava na osnove analiza informacii sredstv registracii parametrov dvizheniya, diagnostiki i monitoringa tehnicheskogo sostoyaniya lokomotiva // Vestnik Rostovskogo gosudarstvennogo universiteta putey soobscheniya. 2025. № 1 (97). S. 201–210. DOI:https://doi.org/10.46973/0201-727X_2025_1_201
10. Bezfamil'nyy D. G. Konceptual'nye podhody k optimizacii zhiznennogo cikla razrabotki programmnogo obespecheniya // Sovremennye innovacii, sistemy i tehnologii. 2026. T. 6, № 2. S. 1027–1034. DOI:https://doi.org/10.47813/2782- 2818-2026-6-2-1027-1034
11. Nihter D. Nastroyka proizvoditel'nosti MySQL. Sekrety i priemy / per. s angl. SPb.: BHV-Peterburg, 2023. 340 s.
