student
Russian Federation
employee
Mozhaisky Military Aerospace Academy (Department of Mathematical and Software Engineering, Professor)
employee
Russian Federation
VAK Russia 2.9.8
UDC 004.65
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. Available at: http://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html (accessed: May 13, 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. Khomonenko A. D., Tsygankov V. M., Maltsev M. G. Bazy dannykh: uchebnik dlya vuzov [Databases: A Textbook for Universities]. Saint Petersburg, Korona-Vek Publishing House, 2009, 736 p. (In Russian)
4. Zaitsev P. MySQL File System Fragmentation Benchmarks, Percona Blog. Published online at March 22, 2008. Available at: http://www.percona.com/blog/mysql-file-system-fragmentation-benchmarks (accessed: May 13, 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. 12, 25 p. DOI:https://doi.org/10.1145/2815622
6. Malyutin A. G., Lavrukhin A. A., Okishev A. S. Arkhitekturnye aspekty realizatsii korporativnoy informatsionnoy sistemy monitoringa i ucheta resursov [Architectural Aspects of the Implementation of the Corporate Information System for Monitoring and Resources Accounting], Izvestiya Transsiba [Journal of Transsib Railway Studies], 2017, no. 4 (32), pp. 130–141. (In Russian)
7. Pavlov D. V. Relyatsionnaya raspredelennaya sistema upravleniya bazami dannykh s avtomaticheskoy masshtabiruemostyu [Distributed Relational Database Management System with Automatic Scalability], Vestnik Ufimskogo gosudarstvennogo aviatsionnogo tekhnicheskogo Universiteta [Vestnik of Ufa State Aviation Technical University], 2012, Vol. 16, no. 3 (48), pp. 143–152. (In Russian)
8. Malygin D. S. Problemy proizvoditelnosti relyatsionnykh baz dannykh v raspredelennykh arkhitekturakh i strategii ikh resheniya [Performance Issues of Relational Databases in Distributed Architectures and Strategies for Resolution], Sovremennye naukoemkie tekhnologii [Modern High Technologies], 2024, no. 10, pp. 61–71. DOI:https://doi.org/10.17513/snt.40173 (In Russian)
9. Kharchenko P. A. Sovershenstvovanie sistemy i tekhnologii ekspluatatsii podvizhnogo sostava na osnove analiza informatsii sredstv registratsii parametrov dvizheniya, diagnostiki i monitoringa tekhnicheskogo sostoyaniya lokomotiva [Improvement of the System and Technology of Rolling Stock Operation Based on the Analysis of Information from Motion Parameter Registration, Diagnostics and Monitoring of the Technical Condition of the Locomotive], Vestnik Rostovskogo Gosudarstvennogo Universiteta Putej Soobshcheniya, 2025, no. 1 (97), pp. 201–210. DOI:https://doi.org/10.46973/0201– 727X_2025_1_201 (In Russian) DOI: https://doi.org/10.46973/0201-
10. Bezfamilnyi D. G. Kontseptualnye podkhody k optimizatsii zhiznennogo tsikla razrabotki programmnogo obespecheniya [Conceptual Approaches to Optimizing the Software Development Lifecycle], Sovremennye innovatsii, sistemy i tekhnologii [Modern Innovations, Systems and Technologies], 2026, vol. 6, no. 2, pp. 1027–1034. DOI:https://doi.org/10.47813/27822818-2026-6-2-1027-1034 (In Russian)
11. Nichter D. Nastroyka proizvoditelnosti MySQL. Sekrety i priemy [Efficient MySQL Performance: Best Practices and Techniques]. Saint Petersburg, BHV-Peterburg Publishing House, 2023, 340 p. (In Russian)
