Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.0.14, 10.0.17
-
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.