[MDEV-16003] too large INDEX_LENGTH Created: 2018-04-24  Updated: 2018-06-05  Resolved: 2018-06-05

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 10.1.30
Fix Version/s: 5.5.59, 10.0.34, 10.2.12, 10.3.4, 10.1.31

Type: Bug Priority: Major
Reporter: Sergey Chernomorets Assignee: Marko Mäkelä
Resolution: Duplicate Votes: 0
Labels: None
Environment:

centos 7

Issue Links:
Duplicate
duplicates MDEV-14799 After UPDATE of indexed columns, old ... Closed

 Description   

We have a table with integer fields, it has too large index space. When I copy data to new table the new table significantly less then origin. Data is append mostly, no update.

 CREATE TABLE `mobile_app_user_log` (
 
  `id_soul` int(10) unsigned NOT NULL DEFAULT '0',
 
  `id_user` int(10) unsigned NOT NULL DEFAULT '0',
 
  `id_app` int(10) unsigned NOT NULL DEFAULT '0',
 
  `id_device` int(10) unsigned NOT NULL DEFAULT '0',
 
  `id_device_model` int(10) unsigned NOT NULL DEFAULT '0',
 
  `id_version` int(10) unsigned NOT NULL DEFAULT '0',
 
  `id_code_version` int(10) unsigned NOT NULL DEFAULT '0',
 
  `id_os` int(10) unsigned NOT NULL DEFAULT '0',
 
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 
  `date_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  PRIMARY KEY (`id_user`,`id_soul`,`id_app`,`id_device`,`id_device_model`,`id_version`,`id_code_version`,`id_os`),
 
  KEY `id_user` (`id_user`) USING BTREE,
 
  KEY `date_update` (`date_update`) USING BTREE,
 
  KEY `date_update_cover` (`id_user`,`date_update`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 


> create table tmp.ss like api.mobile_app_user_log;
 
 
 
> insert into  ss select * from api.mobile_app_user_log;
 
Query OK, 9295572 rows affected (5 min 40.75 sec)
 
Records: 9295572  Duplicates: 0  Warnings: 0
 
 
 
> select * from information_schema.tables  where table_name in ('mobile_app_user_log', 'ss')\G;
 
*************************** 1. row ***************************
 
  TABLE_CATALOG: def
 
   TABLE_SCHEMA: api
 
     TABLE_NAME: mobile_app_user_log
 
     TABLE_TYPE: BASE TABLE
 
         ENGINE: InnoDB
 
        VERSION: 10
 
     ROW_FORMAT: Compact
 
     TABLE_ROWS: 8260584
 
 AVG_ROW_LENGTH: 124
 
    DATA_LENGTH: 1030750208
 
MAX_DATA_LENGTH: 0
 
   INDEX_LENGTH: 51393871872
 
      DATA_FREE: 5242880
 
 AUTO_INCREMENT: NULL
 
    CREATE_TIME: 2017-12-22 00:04:39
 
    UPDATE_TIME: NULL
 
     CHECK_TIME: NULL
 
TABLE_COLLATION: utf8_general_ci
 
       CHECKSUM: NULL
 
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
*************************** 2. row ***************************
 
  TABLE_CATALOG: def
 
   TABLE_SCHEMA: tmp
 
     TABLE_NAME: ss
 
     TABLE_TYPE: BASE TABLE
 
         ENGINE: InnoDB
 
        VERSION: 10
 
     ROW_FORMAT: Compact
 
     TABLE_ROWS: 9030468
 
 AVG_ROW_LENGTH: 64
 
    DATA_LENGTH: 580911104
 
MAX_DATA_LENGTH: 0
 
   INDEX_LENGTH: 1501560832
 
      DATA_FREE: 6291456
 
 AUTO_INCREMENT: NULL
 
    CREATE_TIME: 2018-04-24 15:44:16
 
    UPDATE_TIME: NULL
 
     CHECK_TIME: NULL
 
TABLE_COLLATION: utf8_general_ci
 
       CHECKSUM: NULL
 
 CREATE_OPTIONS: 
  TABLE_COMMENT:


# ls -l api/mobile_app_user_log.* tmp/ss.*
 
-rw-rw---- 1 mysql mysql        8944 Apr 24  2017 api/mobile_app_user_log.frm
 
-rw-rw---- 1 mysql mysql 53208940544 Apr 24 15:54 api/mobile_app_user_log.ibd
 
-rw-rw---- 1 mysql mysql        2682 Apr 24 15:44 tmp/ss.frm
 
-rw-rw---- 1 mysql mysql  2139095040 Apr 24 15:50 tmp/ss.ibd

How is it possible? Pages fragmentation?

 Comments   
Comment by Sergey Chernomorets [ 2018-04-24 ]

Another table:

CREATE TABLE `vacsubscriptions_stat` (
 
  `id` int(10) unsigned NOT NULL DEFAULT '0',
 
  `count_vacs` int(10) unsigned NOT NULL DEFAULT '0',
 
  `count_messages` mediumint(8) unsigned NOT NULL DEFAULT '0',
 
  `date_last_message` int(10) unsigned NOT NULL DEFAULT '0',
 
  `count_views` int(10) unsigned NOT NULL DEFAULT '0',
 
  `date_last_view` int(10) unsigned NOT NULL DEFAULT '0',
 
  `count_clicks` mediumint(8) unsigned NOT NULL DEFAULT '0',
 
  `date_last_click` int(10) unsigned NOT NULL DEFAULT '0',
 
  `count_searches` mediumint(8) unsigned NOT NULL DEFAULT '0',
 
  `date_last_search` int(10) unsigned NOT NULL DEFAULT '0',
 
  `date_processed` int(10) unsigned NOT NULL DEFAULT '0',
 
  PRIMARY KEY (`id`),
 
  KEY `date_processed` (`date_processed`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
 
 
 
> create table tmp.ss like region.vacsubscriptions_stat;
 
> insert into tmp.ss select * from region.vacsubscriptions_stat;
 
Query OK, 22522628 rows affected (2 min 18.36 sec)
 
Records: 22522628  Duplicates: 0  Warnings: 0

In new table index space 10 times lesser:

> select * from information_schema.tables  where table_name in ('vacsubscriptions_stat','ss')\G;
 
*************************** 1. row ***************************
 
  TABLE_CATALOG: def
 
   TABLE_SCHEMA: region
 
     TABLE_NAME: vacsubscriptions_stat
 
     TABLE_TYPE: BASE TABLE
 
         ENGINE: InnoDB
 
        VERSION: 10
 
     ROW_FORMAT: Dynamic
 
     TABLE_ROWS: 21864463
 
 AVG_ROW_LENGTH: 66
 
    DATA_LENGTH: 1451229184
 
MAX_DATA_LENGTH: 0
 
   INDEX_LENGTH: 4725915648
 
      DATA_FREE: 7340032
 
 AUTO_INCREMENT: NULL
 
    CREATE_TIME: 2018-04-14 22:32:31
 
    UPDATE_TIME: NULL
 
     CHECK_TIME: NULL
 
TABLE_COLLATION: utf8_general_ci
 
       CHECKSUM: NULL
 
 CREATE_OPTIONS: row_format=DYNAMIC
 
  TABLE_COMMENT: 
*************************** 2. row ***************************
 
  TABLE_CATALOG: def
 
   TABLE_SCHEMA: tmp
 
     TABLE_NAME: ss
 
     TABLE_TYPE: BASE TABLE
 
         ENGINE: InnoDB
 
        VERSION: 10
 
     ROW_FORMAT: Dynamic
 
     TABLE_ROWS: 22434550
 
 AVG_ROW_LENGTH: 64
 
    DATA_LENGTH: 1449132032
 
MAX_DATA_LENGTH: 0
 
   INDEX_LENGTH: 485457920
 
      DATA_FREE: 7340032
 
 AUTO_INCREMENT: NULL
 
    CREATE_TIME: 2018-04-24 16:08:30
 
    UPDATE_TIME: NULL
 
     CHECK_TIME: NULL
 
TABLE_COLLATION: utf8_general_ci
 
       CHECKSUM: NULL
 
 CREATE_OPTIONS: row_format=DYNAMIC
 
  TABLE_COMMENT:

Comment by Sergey Chernomorets [ 2018-04-24 ]

We altered table vacsubscriptions_stat (alter table force) to compact it some days ago, but table still grows.

Comment by Elena Stepanova [ 2018-05-31 ]

What exactly bothers you, INDEX_LENGTH or the size of the ibd file on disk?
INDEX_LENGTH might go down eventually, e.g. I've seen FLUSH TABLES; ANALYZE TABLE <table name> help with that.
The ibd file doesn't shrink unless you rebuild the table.

Comment by Sergey Chernomorets [ 2018-05-31 ]

INDEX_LENGTH grows very quickly, I think it very strange. And INDEX_LENGTH reduced more than 10x after copying all data into new table - why indexes consume so many space?

Comment by Sergey Chernomorets [ 2018-05-31 ]

I tried flush and analyze - no effect:

MariaDB [(none)]> select * from information_schema.tables  where table_name in ('vacsubscriptions_stat')\G
 
*************************** 1. row ***************************
 
  TABLE_CATALOG: def
 
   TABLE_SCHEMA: region
 
     TABLE_NAME: vacsubscriptions_stat
 
     TABLE_TYPE: BASE TABLE
 
         ENGINE: InnoDB
 
        VERSION: 10
 
     ROW_FORMAT: Dynamic
 
     TABLE_ROWS: 22085971
 
 AVG_ROW_LENGTH: 66
 
    DATA_LENGTH: 1475346432
 
MAX_DATA_LENGTH: 0
 
   INDEX_LENGTH: 21882716160
 
      DATA_FREE: 6291456
 
 AUTO_INCREMENT: NULL
 
    CREATE_TIME: 2018-04-14 22:32:31
 
    UPDATE_TIME: NULL
 
     CHECK_TIME: NULL
 
TABLE_COLLATION: utf8_general_ci
 
       CHECKSUM: NULL
 
 CREATE_OPTIONS: row_format=DYNAMIC
 
  TABLE_COMMENT: 
1 row in set (0.01 sec)
 
 
 
MariaDB [(none)]> flush tables;
 
Query OK, 0 rows affected (0.19 sec)
 
 
 
MariaDB [(none)]> select * from information_schema.tables  where table_name in ('vacsubscriptions_stat')\G
 
*************************** 1. row ***************************
 
  TABLE_CATALOG: def
 
   TABLE_SCHEMA: region
 
     TABLE_NAME: vacsubscriptions_stat
 
     TABLE_TYPE: BASE TABLE
 
         ENGINE: InnoDB
 
        VERSION: 10
 
     ROW_FORMAT: Dynamic
 
     TABLE_ROWS: 22085967
 
 AVG_ROW_LENGTH: 66
 
    DATA_LENGTH: 1475346432
 
MAX_DATA_LENGTH: 0
 
   INDEX_LENGTH: 21882716160
 
      DATA_FREE: 6291456
 
 AUTO_INCREMENT: NULL
 
    CREATE_TIME: 2018-04-14 22:32:31
 
    UPDATE_TIME: NULL
 
     CHECK_TIME: NULL
 
TABLE_COLLATION: utf8_general_ci
 
       CHECKSUM: NULL
 
 CREATE_OPTIONS: row_format=DYNAMIC
 
  TABLE_COMMENT: 
1 row in set (0.00 sec)

Comment by Marko Mäkelä [ 2018-06-05 ]

This should be a duplicate of MDEV-14799, which was fixed in MariaDB 10.1.31. In the 10.1 series, this bug (introduced by MDEV-14051) is present in 10.1.29 and 10.1.30.

FLUSH TABLES has no effect inside InnoDB. FLUSH TABLE region.vacsubscriptions_stat FOR EXPORT could have some effect.

I assume that the DATA_LENGTH and INDEX_LENGTH are reported in bytes. For the primary key index (clustered index), DATA_LENGTH/TABLE_ROWS is 66.8 bytes, which looks reasonable, because the record payload size should be about 4*11+6+7+5 bytes (6+7 bytes for the hidden fields DB_TRX_ID,DB_ROLL_PTR and 5 bytes for the record header). 62 bytes per record is not much less than the average of 66.8 bytes per row, if we consider the page header and footer that are more than 100 bytes of the default innodb_page_size=16384 bytes.

I see that there is only one secondary index on the table. Its record size should be 5+4+4=13 bytes (including the fixed-size header). In InnoDB, the multi-versioning of secondary indexes basically is copy-on-write. Maybe the reason for the growth is that the indexed column is being updated frequently? INDEX_LENGTH/TABLE_ROWS/13 = 76.2. Could each record have an average of more than 70 updates of the date_processed column?

Entries in the secondary indexes would be removed by a background process called purge. Due to MDEV-14051, the purge did not work.

You would not have to rebuild the whole table; DROP INDEX followed by CREATE INDEX should also work to reduce the index size. But it would not shrink the data file.

Generated at Thu Feb 08 08:25:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.