Mysql 8.0:Innodb index performance loss until server is dead. Performance perfect after index is re-created?












0















Important Update

Spent 15 hours on this problem, rebuilt and changed indexes and the table many times.

Finally I can say that it's NOT a "virtual column" problem, it's an Innodb Indexing issue.

The "query cost" increases by about 10,000 (which is a LOT) every hour of using the index.

When rebuilding the index (drop, create) the query cost is back 1.1.

So based on the analyzer performance gets 50,000 times slower in half a day;)



This happens because of INSERTS and UPDATES on the table, it seems the index is increasingly corrupted somehow, about half of the inserted rows seem not to update inside the index.



Simplified table:



    CREATE TABLE `users`
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`last_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
`d_table_id` int(11) DEFAULT NULL,
`last_code` smallint(6) DEFAULT NULL,
`date_created` datetime NOT NULL,
`date_assigned` datetime DEFAULT NULL,
`date_finished` datetime DEFAULT NULL,
`virt_ready_for_assign` tinyint(1) GENERATED ALWAYS AS ((isnull(`date_assigned`) and isnull(`date_finished`) and (`d_table_id` > 0))) VIRTUAL,
PRIMARY KEY (`id`),
KEY `date_assigned` (`date_assigned`),
KEY `d_table_id` (`d_table_id`),
KEY `d_table_id_date_assigned` (`d_table_id`, `date_assigned`),
KEY `date_assigned_date_finished` (`date_assigned`, `date_finished`),
KEY `virt_ready_for_assign` (`virt_ready_for_assign` DESC)
) ENGINE = InnoDB
AUTO_INCREMENT = 76124573
DEFAULT CHARSET = utf8
COLLATE = utf8_bin


EXPLAIN:



"explain select count(*) from users where virt_ready_for_assign=1"

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users NULL ref virt_ready_for_assign virt_ready_for_assign 2 const 696608 100.00 Using index


The problem: The table has 76 million entries and 20 (!!!) of them have virt_ready_for_assign = 1, 99% have virt_ready_for_assign = 0



Why does it show 696608 ?

This query is being executed a lot and sometimes stalls for seconds, but it should be lightning fast..



The reason for the virtual column was to make this query as fast as possible to dedicate a perfect index to it without wasting disk space (or having to rebuild).

It works perfectly after creating the index and after a day with some million of inserts it gets bad and shows a wrong number in explain.



The actual query used is one of those:



"explain select id from users where virt_ready_for_assign=1 FOR UPDATE"
"explain select id from users where virt_ready_for_assign=1 FOR UPDATE SKIP LOCKED"


P.S. I did try run ANALYZE table, it didn't help. Only removing the index and re-creating it will help. But that's not a solution.



Update: JSON explain:

explain format=json select count(*) from users where virt_ready_for_assign=1



  {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "77786.30"
},
"table": {
"table_name": "users",
"access_type": "ref",
"possible_keys": [
"virt_ready_for_assign"
],
"key": "virt_ready_for_assign",
"used_key_parts": [
"virt_ready_for_assign"
],
"key_length": "2",
"ref": [
"const"
],
"rows_examined_per_scan": 772200,
"rows_produced_per_join": 772200,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "566.30",
"eval_cost": "77220.00",
"prefix_cost": "77786.30",
"data_read_per_join": "1G"
},
"used_columns": [
"d_table_id",
"date_assigned",
"date_finished",
"virt_ready_for_assign"
]
}
}
}


The above explain SQL will yield to count(*) = 0

Not one row currently found



Proof of the bug from my point of view:

I created a second identical index and made queries to the table with both indexes, it reproduceable shows a significant time difference in execution.



mysql> select count(*) from users FORCE INDEX  (virt_ready_for_assign_2) where virt_ready_for_assign=1;
+----------+
| count(*) |
+----------+
| 241 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign) where virt_ready_for_assign=1;
+----------+
| count(*) |
+----------+
| 300 |
+----------+
1 row in set (0.14 sec)

mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign_2) where virt_ready_for_assign=1;
+----------+
| count(*) |
+----------+
| 751 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign) where virt_ready_for_assign=1;
+----------+
| count(*) |
+----------+
| 745 |
+----------+
1 row in set (0.08 sec)

mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign_2) where virt_ready_for_assign=1;
+----------+
| count(*) |
+----------+
| 42 |
+----------+
1 row in set (0.01 sec)









share|improve this question





























    0















    Important Update

    Spent 15 hours on this problem, rebuilt and changed indexes and the table many times.

    Finally I can say that it's NOT a "virtual column" problem, it's an Innodb Indexing issue.

    The "query cost" increases by about 10,000 (which is a LOT) every hour of using the index.

    When rebuilding the index (drop, create) the query cost is back 1.1.

    So based on the analyzer performance gets 50,000 times slower in half a day;)



    This happens because of INSERTS and UPDATES on the table, it seems the index is increasingly corrupted somehow, about half of the inserted rows seem not to update inside the index.



    Simplified table:



        CREATE TABLE `users`
    (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `first_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
    `last_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
    `d_table_id` int(11) DEFAULT NULL,
    `last_code` smallint(6) DEFAULT NULL,
    `date_created` datetime NOT NULL,
    `date_assigned` datetime DEFAULT NULL,
    `date_finished` datetime DEFAULT NULL,
    `virt_ready_for_assign` tinyint(1) GENERATED ALWAYS AS ((isnull(`date_assigned`) and isnull(`date_finished`) and (`d_table_id` > 0))) VIRTUAL,
    PRIMARY KEY (`id`),
    KEY `date_assigned` (`date_assigned`),
    KEY `d_table_id` (`d_table_id`),
    KEY `d_table_id_date_assigned` (`d_table_id`, `date_assigned`),
    KEY `date_assigned_date_finished` (`date_assigned`, `date_finished`),
    KEY `virt_ready_for_assign` (`virt_ready_for_assign` DESC)
    ) ENGINE = InnoDB
    AUTO_INCREMENT = 76124573
    DEFAULT CHARSET = utf8
    COLLATE = utf8_bin


    EXPLAIN:



    "explain select count(*) from users where virt_ready_for_assign=1"

    id select_type table partitions type possible_keys key key_len ref rows filtered Extra
    1 SIMPLE users NULL ref virt_ready_for_assign virt_ready_for_assign 2 const 696608 100.00 Using index


    The problem: The table has 76 million entries and 20 (!!!) of them have virt_ready_for_assign = 1, 99% have virt_ready_for_assign = 0



    Why does it show 696608 ?

    This query is being executed a lot and sometimes stalls for seconds, but it should be lightning fast..



    The reason for the virtual column was to make this query as fast as possible to dedicate a perfect index to it without wasting disk space (or having to rebuild).

    It works perfectly after creating the index and after a day with some million of inserts it gets bad and shows a wrong number in explain.



    The actual query used is one of those:



    "explain select id from users where virt_ready_for_assign=1 FOR UPDATE"
    "explain select id from users where virt_ready_for_assign=1 FOR UPDATE SKIP LOCKED"


    P.S. I did try run ANALYZE table, it didn't help. Only removing the index and re-creating it will help. But that's not a solution.



    Update: JSON explain:

    explain format=json select count(*) from users where virt_ready_for_assign=1



      {
    "query_block": {
    "select_id": 1,
    "cost_info": {
    "query_cost": "77786.30"
    },
    "table": {
    "table_name": "users",
    "access_type": "ref",
    "possible_keys": [
    "virt_ready_for_assign"
    ],
    "key": "virt_ready_for_assign",
    "used_key_parts": [
    "virt_ready_for_assign"
    ],
    "key_length": "2",
    "ref": [
    "const"
    ],
    "rows_examined_per_scan": 772200,
    "rows_produced_per_join": 772200,
    "filtered": "100.00",
    "using_index": true,
    "cost_info": {
    "read_cost": "566.30",
    "eval_cost": "77220.00",
    "prefix_cost": "77786.30",
    "data_read_per_join": "1G"
    },
    "used_columns": [
    "d_table_id",
    "date_assigned",
    "date_finished",
    "virt_ready_for_assign"
    ]
    }
    }
    }


    The above explain SQL will yield to count(*) = 0

    Not one row currently found



    Proof of the bug from my point of view:

    I created a second identical index and made queries to the table with both indexes, it reproduceable shows a significant time difference in execution.



    mysql> select count(*) from users FORCE INDEX  (virt_ready_for_assign_2) where virt_ready_for_assign=1;
    +----------+
    | count(*) |
    +----------+
    | 241 |
    +----------+
    1 row in set (0.02 sec)

    mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign) where virt_ready_for_assign=1;
    +----------+
    | count(*) |
    +----------+
    | 300 |
    +----------+
    1 row in set (0.14 sec)

    mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign_2) where virt_ready_for_assign=1;
    +----------+
    | count(*) |
    +----------+
    | 751 |
    +----------+
    1 row in set (0.01 sec)

    mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign) where virt_ready_for_assign=1;
    +----------+
    | count(*) |
    +----------+
    | 745 |
    +----------+
    1 row in set (0.08 sec)

    mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign_2) where virt_ready_for_assign=1;
    +----------+
    | count(*) |
    +----------+
    | 42 |
    +----------+
    1 row in set (0.01 sec)









    share|improve this question



























      0












      0








      0


      1






      Important Update

      Spent 15 hours on this problem, rebuilt and changed indexes and the table many times.

      Finally I can say that it's NOT a "virtual column" problem, it's an Innodb Indexing issue.

      The "query cost" increases by about 10,000 (which is a LOT) every hour of using the index.

      When rebuilding the index (drop, create) the query cost is back 1.1.

      So based on the analyzer performance gets 50,000 times slower in half a day;)



      This happens because of INSERTS and UPDATES on the table, it seems the index is increasingly corrupted somehow, about half of the inserted rows seem not to update inside the index.



      Simplified table:



          CREATE TABLE `users`
      (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `first_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
      `last_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
      `d_table_id` int(11) DEFAULT NULL,
      `last_code` smallint(6) DEFAULT NULL,
      `date_created` datetime NOT NULL,
      `date_assigned` datetime DEFAULT NULL,
      `date_finished` datetime DEFAULT NULL,
      `virt_ready_for_assign` tinyint(1) GENERATED ALWAYS AS ((isnull(`date_assigned`) and isnull(`date_finished`) and (`d_table_id` > 0))) VIRTUAL,
      PRIMARY KEY (`id`),
      KEY `date_assigned` (`date_assigned`),
      KEY `d_table_id` (`d_table_id`),
      KEY `d_table_id_date_assigned` (`d_table_id`, `date_assigned`),
      KEY `date_assigned_date_finished` (`date_assigned`, `date_finished`),
      KEY `virt_ready_for_assign` (`virt_ready_for_assign` DESC)
      ) ENGINE = InnoDB
      AUTO_INCREMENT = 76124573
      DEFAULT CHARSET = utf8
      COLLATE = utf8_bin


      EXPLAIN:



      "explain select count(*) from users where virt_ready_for_assign=1"

      id select_type table partitions type possible_keys key key_len ref rows filtered Extra
      1 SIMPLE users NULL ref virt_ready_for_assign virt_ready_for_assign 2 const 696608 100.00 Using index


      The problem: The table has 76 million entries and 20 (!!!) of them have virt_ready_for_assign = 1, 99% have virt_ready_for_assign = 0



      Why does it show 696608 ?

      This query is being executed a lot and sometimes stalls for seconds, but it should be lightning fast..



      The reason for the virtual column was to make this query as fast as possible to dedicate a perfect index to it without wasting disk space (or having to rebuild).

      It works perfectly after creating the index and after a day with some million of inserts it gets bad and shows a wrong number in explain.



      The actual query used is one of those:



      "explain select id from users where virt_ready_for_assign=1 FOR UPDATE"
      "explain select id from users where virt_ready_for_assign=1 FOR UPDATE SKIP LOCKED"


      P.S. I did try run ANALYZE table, it didn't help. Only removing the index and re-creating it will help. But that's not a solution.



      Update: JSON explain:

      explain format=json select count(*) from users where virt_ready_for_assign=1



        {
      "query_block": {
      "select_id": 1,
      "cost_info": {
      "query_cost": "77786.30"
      },
      "table": {
      "table_name": "users",
      "access_type": "ref",
      "possible_keys": [
      "virt_ready_for_assign"
      ],
      "key": "virt_ready_for_assign",
      "used_key_parts": [
      "virt_ready_for_assign"
      ],
      "key_length": "2",
      "ref": [
      "const"
      ],
      "rows_examined_per_scan": 772200,
      "rows_produced_per_join": 772200,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
      "read_cost": "566.30",
      "eval_cost": "77220.00",
      "prefix_cost": "77786.30",
      "data_read_per_join": "1G"
      },
      "used_columns": [
      "d_table_id",
      "date_assigned",
      "date_finished",
      "virt_ready_for_assign"
      ]
      }
      }
      }


      The above explain SQL will yield to count(*) = 0

      Not one row currently found



      Proof of the bug from my point of view:

      I created a second identical index and made queries to the table with both indexes, it reproduceable shows a significant time difference in execution.



      mysql> select count(*) from users FORCE INDEX  (virt_ready_for_assign_2) where virt_ready_for_assign=1;
      +----------+
      | count(*) |
      +----------+
      | 241 |
      +----------+
      1 row in set (0.02 sec)

      mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign) where virt_ready_for_assign=1;
      +----------+
      | count(*) |
      +----------+
      | 300 |
      +----------+
      1 row in set (0.14 sec)

      mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign_2) where virt_ready_for_assign=1;
      +----------+
      | count(*) |
      +----------+
      | 751 |
      +----------+
      1 row in set (0.01 sec)

      mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign) where virt_ready_for_assign=1;
      +----------+
      | count(*) |
      +----------+
      | 745 |
      +----------+
      1 row in set (0.08 sec)

      mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign_2) where virt_ready_for_assign=1;
      +----------+
      | count(*) |
      +----------+
      | 42 |
      +----------+
      1 row in set (0.01 sec)









      share|improve this question
















      Important Update

      Spent 15 hours on this problem, rebuilt and changed indexes and the table many times.

      Finally I can say that it's NOT a "virtual column" problem, it's an Innodb Indexing issue.

      The "query cost" increases by about 10,000 (which is a LOT) every hour of using the index.

      When rebuilding the index (drop, create) the query cost is back 1.1.

      So based on the analyzer performance gets 50,000 times slower in half a day;)



      This happens because of INSERTS and UPDATES on the table, it seems the index is increasingly corrupted somehow, about half of the inserted rows seem not to update inside the index.



      Simplified table:



          CREATE TABLE `users`
      (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `first_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
      `last_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
      `d_table_id` int(11) DEFAULT NULL,
      `last_code` smallint(6) DEFAULT NULL,
      `date_created` datetime NOT NULL,
      `date_assigned` datetime DEFAULT NULL,
      `date_finished` datetime DEFAULT NULL,
      `virt_ready_for_assign` tinyint(1) GENERATED ALWAYS AS ((isnull(`date_assigned`) and isnull(`date_finished`) and (`d_table_id` > 0))) VIRTUAL,
      PRIMARY KEY (`id`),
      KEY `date_assigned` (`date_assigned`),
      KEY `d_table_id` (`d_table_id`),
      KEY `d_table_id_date_assigned` (`d_table_id`, `date_assigned`),
      KEY `date_assigned_date_finished` (`date_assigned`, `date_finished`),
      KEY `virt_ready_for_assign` (`virt_ready_for_assign` DESC)
      ) ENGINE = InnoDB
      AUTO_INCREMENT = 76124573
      DEFAULT CHARSET = utf8
      COLLATE = utf8_bin


      EXPLAIN:



      "explain select count(*) from users where virt_ready_for_assign=1"

      id select_type table partitions type possible_keys key key_len ref rows filtered Extra
      1 SIMPLE users NULL ref virt_ready_for_assign virt_ready_for_assign 2 const 696608 100.00 Using index


      The problem: The table has 76 million entries and 20 (!!!) of them have virt_ready_for_assign = 1, 99% have virt_ready_for_assign = 0



      Why does it show 696608 ?

      This query is being executed a lot and sometimes stalls for seconds, but it should be lightning fast..



      The reason for the virtual column was to make this query as fast as possible to dedicate a perfect index to it without wasting disk space (or having to rebuild).

      It works perfectly after creating the index and after a day with some million of inserts it gets bad and shows a wrong number in explain.



      The actual query used is one of those:



      "explain select id from users where virt_ready_for_assign=1 FOR UPDATE"
      "explain select id from users where virt_ready_for_assign=1 FOR UPDATE SKIP LOCKED"


      P.S. I did try run ANALYZE table, it didn't help. Only removing the index and re-creating it will help. But that's not a solution.



      Update: JSON explain:

      explain format=json select count(*) from users where virt_ready_for_assign=1



        {
      "query_block": {
      "select_id": 1,
      "cost_info": {
      "query_cost": "77786.30"
      },
      "table": {
      "table_name": "users",
      "access_type": "ref",
      "possible_keys": [
      "virt_ready_for_assign"
      ],
      "key": "virt_ready_for_assign",
      "used_key_parts": [
      "virt_ready_for_assign"
      ],
      "key_length": "2",
      "ref": [
      "const"
      ],
      "rows_examined_per_scan": 772200,
      "rows_produced_per_join": 772200,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
      "read_cost": "566.30",
      "eval_cost": "77220.00",
      "prefix_cost": "77786.30",
      "data_read_per_join": "1G"
      },
      "used_columns": [
      "d_table_id",
      "date_assigned",
      "date_finished",
      "virt_ready_for_assign"
      ]
      }
      }
      }


      The above explain SQL will yield to count(*) = 0

      Not one row currently found



      Proof of the bug from my point of view:

      I created a second identical index and made queries to the table with both indexes, it reproduceable shows a significant time difference in execution.



      mysql> select count(*) from users FORCE INDEX  (virt_ready_for_assign_2) where virt_ready_for_assign=1;
      +----------+
      | count(*) |
      +----------+
      | 241 |
      +----------+
      1 row in set (0.02 sec)

      mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign) where virt_ready_for_assign=1;
      +----------+
      | count(*) |
      +----------+
      | 300 |
      +----------+
      1 row in set (0.14 sec)

      mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign_2) where virt_ready_for_assign=1;
      +----------+
      | count(*) |
      +----------+
      | 751 |
      +----------+
      1 row in set (0.01 sec)

      mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign) where virt_ready_for_assign=1;
      +----------+
      | count(*) |
      +----------+
      | 745 |
      +----------+
      1 row in set (0.08 sec)

      mysql> select count(*) from users FORCE INDEX (virt_ready_for_assign_2) where virt_ready_for_assign=1;
      +----------+
      | count(*) |
      +----------+
      | 42 |
      +----------+
      1 row in set (0.01 sec)






      mysql innodb mysql-8.0






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 1 min ago







      John

















      asked 13 hours ago









      JohnJohn

      1327




      1327






















          0






          active

          oldest

          votes











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228115%2fmysql-8-0innodb-index-performance-loss-until-server-is-dead-performance-perfec%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          0






          active

          oldest

          votes








          0






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes
















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Database Administrators Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228115%2fmysql-8-0innodb-index-performance-loss-until-server-is-dead-performance-perfec%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Liste der Baudenkmale in Friedland (Mecklenburg)

          Single-Malt-Whisky

          Czorneboh