We have noticed a very strange index growth with one of our tables. The data in the table is currently ~ 42GB and has been growing at a rate of about 1 GB per day for the week. Inserts/Updates on the tables are done with statements like: insert into foo ( ...) on duplicate key update ... We have a job which updates about 1/3 of the table 12 times a day. We running Mariadb version 5.5.27-MariaDB-log We noticed that the index sizes have been growing quite a bit: ** 7 days ago ***

select table_name,index_name,index_total_pages, (index_total_pages * 16*1024)/(1024*1024*1024) index_size_gb from INNODB_INDEX_STATS where table_name='foo'; +--------------------+----------------------+-------------------+---------------+ | table_name | index_name | index_total_pages | index_size_gb | +--------------------+----------------------+-------------------+---------------+ | foo | PRIMARY | 1192456 | 18.1954 | | foo | idx_active_deleted | 5647809 | 86.1787 | | foo | idx_last_update_plus | 5329538 | 81.3223 | | foo | active | 8586719 | 131.0229 | +--------------------+----------------------+-------------------+---------------+ 4 rows in set (0.00 sec)

optimize table db.foo;

+---------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+----------+----------+-------------------------------------------------------------------+ | db.foo | optimize | note | Table does not support optimize, doing recreate + analyze instead | | db.foo | optimize | status | OK | +---------------------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (4 hours 1 min 24.73 sec)

select table_name,index_name,index_total_pages, (index_total_pages * 16*1024)/(1024*1024*1024) index_size_gb from INNODB_INDEX_STATS where table_name='foo'; +--------------------+----------------------+-------------------+---------------+ | table_name | index_name | index_total_pages | index_size_gb | +--------------------+----------------------+-------------------+---------------+ | foo | PRIMARY | 639296 | 9.7549 | | foo | idx_active_deleted | 403520 | 6.1572 | | foo | idx_last_update_plus | 531392 | 8.1084 | | foo | active | 517312 | 7.8936 | +--------------------+----------------------+-------------------+---------------+ 4 rows in set (0.00 sec)

** Today **

select table_name,index_name,index_total_pages, (index_total_pages * 16*1024)/(1024*1024*1024) index_size_gb from INNODB_INDEX_STATS where table_name='foo'; +--------------------+----------------------+-------------------+---------------+ | table_name | index_name | index_total_pages | index_size_gb | +--------------------+----------------------+-------------------+---------------+ | foo | PRIMARY | 1013436 | 15.4638 | | foo | idx_active_deleted | 2478512 | 37.8191 | | foo | idx_last_update_plus | 2459700 | 37.5320 | | foo | active | 3888175 | 59.3288 | +--------------------+----------------------+-------------------+---------------+ 4 rows in set (0.10 sec)

One of the even stranger things is that the slaves of this table do not experience such a noticeable index size growth: [slave] >select table_name,index_name,index_total_pages, (index_total_pages * 16*1024)/(1024*1024*1024) index_size_gb from INNODB_INDEX_STATS where table_name='foo'; +--------------------+----------------------+-------------------+---------------+ | table_name | index_name | index_total_pages | index_size_gb | +--------------------+----------------------+-------------------+---------------+ | foo | PRIMARY | 1010492 | 15.4189 | | foo | idx_active_deleted | 454510 | 6.9353 | | foo | idx_last_update_plus | 543085 | 8.2868 | | foo | active | 583468 | 8.9030 | +--------------------+----------------------+-------------------+---------------+ 4 rows in set (0.01 sec) Our suspicion is that it has to do with how transactions are executing on the master since on the slaves the insert/updates would be executed in a single thread. However we can't tell why this is happening. Is this a known bug we are hitting ? As anyone else seen this behavior and come up with a solution other than having to run optimize table ? Thanks, -Said