Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7764

Optimizer bug:error query plan

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 10.0.14, 10.0.17
    • N/A
    • Optimizer
    • centos6.4

    Description

      I suffered this bug,when I run the benchmark test using mariadb 10.0.14.

      use case:

      Test tool:sysbench0.5
      OS:centos6.4
      DB version mariadb10.0.14
      Data:8 tables and each 25000000rows

      Some my.cnf configuration:

      innodb_file_per_table
      innodb_buffer_pool_size=63G
      innodb_log_file_size= 1G
      innodb_flush_method=O_DIRECT
      innodb_flush_log_at_trx_commit = 1
      sync_binlog = 1
      log-bin
      binlog-format=mixed

      Test command:

      sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --num-threads=512 --db-driver=mysql --mysql-db=sbtest --max-requests=0 --oltp-table-size=25000000 --mysql-table-engine=innodb --mysql-port=55945 --mysql-user=us_frank --mysql-password=123456 --mysql-socket=/var/lib/mysql/mariadb.sock --oltp-tables-count=8 run

      Other:no preheat data,num-threads more than 512

      During the benchmark test, I ran a huge IO-cost query such as select count(*) from sbtest1;.A few time later , show processlist returned following slow queries

      +------+----------------+-----------+--------+---------+------+--------------+----------------------------------------------------------------------+----------+
      | Id   | User           | Host      | db     | Command | Time | State        | Info                                                                 | Progress |
      +------+----------------+-----------+--------+---------+------+--------------+----------------------------------------------------------------------+----------+
      | 5021 | us_frank | localhost | sbtest | Query   | 1430 | Sending data | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN 12522878 AND 12522878+99 |    0.000 |
      | 5044 | us_frank | localhost | sbtest | Query   | 1428 | Sending data | SELECT SUM(K) FROM sbtest3 WHERE id BETWEEN 12407570 AND 12407570+99 |    0.000 |
      | 5376 | us_frank | localhost | sbtest | Query   | 1430 | Sending data | SELECT SUM(K) FROM sbtest7 WHERE id BETWEEN 12545547 AND 12545547+99 |    0.000 |
      +------+----------------+-----------+--------+---------+------+--------------+----------------------------------------------------------------------+----------+

      I ran the explain immediately.

      explain SELECT SUM(K) FROM sbtest7 WHERE id BETWEEN 12545547 AND 12545547+99;
      +------+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
      | id   | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
      +------+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
      |    1 | SIMPLE      | sbtest7 | range | PRIMARY       | PRIMARY | 4       | NULL |   99 | Using where |
      +------+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
      1 row in set (0.00 sec)

      That is right!

      But slow log showed as following .

      That is wrong!Full table scan!

      It seems that mariadb will choose wrong query plan when server is in the condition with high io load.

      Attachments

        Activity

          People

            axel Axel Schwenke
            ahahazhang frank.zhang
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.