Investigation of Data Fragmentation Impact on Read/Write Performance and Methods for Its Elimination in MySQL (InnoDB)

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).