maria-discuss team mailing list archive

[Said Ramirez <sramirez@xxxxxxxxxxxx>]

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