Details

    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?

      Attachments

        Issue Links

          Activity

            chernomorets Sergey Chernomorets added a comment - - edited

            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:
            

            chernomorets Sergey Chernomorets added a comment - - edited 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:

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

            chernomorets Sergey Chernomorets added a comment - We altered table vacsubscriptions_stat (alter table force) to compact it some days ago, but table still grows.

            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.

            elenst Elena Stepanova added a comment - 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.

            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?

            chernomorets Sergey Chernomorets added a comment - 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?

            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)
            

            chernomorets Sergey Chernomorets added a comment - 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)

            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.

            marko Marko Mäkelä added a comment - 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.

            People

              marko Marko Mäkelä
              chernomorets Sergey Chernomorets
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.