Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.1, 5.5.30, 5.3.12
-
Component/s: None
-
Environment:innodb_version 5.5.30-MariaDB-30.1
version 5.5.30-MariaDB-log
version_comment MariaDB Server
version_compile_machine x86_64
version_compile_os Linux
Description
Good day!
I found problem and strange behavior of update
there is table
CREATE TABLE `schedule_test` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `datetime_processed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `datetime_next` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `sign` bigint(20) unsigned NOT NULL DEFAULT '0', `lock` tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `idx_lock_dt_nxt` (`lock`,`datetime_next`) ) ENGINE=InnoDB;
it's a simple task scheduler and table has near 45k rows
Task selecting doing in this way:
update schedule_test as sa set sa.`lock` = 1, sa.sign = 2123123123, sa.`datetime_processed` = now() where sa.`lock` = 0 and sa.`datetime_next` <= now() order by sa.`datetime_next` asc limit 10;
problem is that update doesnt use index `idx_lock_dt_nxt` for selecting and mysql does table fullscan (read all 45k).
I look in innotop count readed rows.
But if rewrite update to select, it works as should - use undex and innodb read from table only 10 rows.
select sa.`lock`, sign, datetime_next from schedule_test as sa where sa.`lock` = 0 and sa.`datetime_next` <= now() order by sa.`datetime_next` asc limit 10;
explain of this:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE sa ref lock lock 1 const 22716 Using index condition; Using where
for me is not understandable nature this behaviour. as i know select and update should use the same execution plan.
As an experiment, i modify index and make it so KEY `idx_lock_dt_nxt` (`datetime_next`)
in this case update begins using it for select rows by date, but i keep in my mind, it works like full scan but little softer, anyway.
because after some time all rows with minimal datetime will be locked (lock=1) and mysql have to scan more and more rows for checking where condition.
i think may be there is an error in choosing good query plan execution for update?
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Another problem: EXPLAIN shows that rows=
{number of rows scanned by range scan}, not
{number of rows scanned before we find LIMIT}(let's call this "rows problem").
For 10.0, I'll fix the query plan problem but not "rows problem". fixing query plan problem is safe, fixing "rows problem" could potentially have some implications.
Pushed a fix into 10.0. #rows in EXPLAIN UPDATE will be addressed separately.
The #rows will be addressed in MDEV-5884.
Test case:
CREATE TABLE `schedule_test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`datetime_processed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`datetime_next` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`sign` bigint(20) unsigned NOT NULL DEFAULT '0',
`lock` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_lock_dt_nxt` (`lock`,`datetime_next`)
) ENGINE=InnoDB;
INSERT INTO schedule_test (datetime_processed,sign,`lock`) VALUES (NOW(),ROUND(RAND()*10000),ROUND(RAND()));
INSERT INTO schedule_test (datetime_processed,sign,`lock`) SELECT NOW(),ROUND(RAND()*10000),ROUND(RAND()) FROM schedule_test;
INSERT INTO schedule_test (datetime_processed,sign,`lock`) SELECT NOW(),ROUND(RAND()*10000),ROUND(RAND()) FROM schedule_test;
INSERT INTO schedule_test (datetime_processed,sign,`lock`) SELECT NOW(),ROUND(RAND()*10000),ROUND(RAND()) FROM schedule_test;
INSERT INTO schedule_test (datetime_processed,sign,`lock`) SELECT NOW(),ROUND(RAND()*10000),ROUND(RAND()) FROM schedule_test;
INSERT INTO schedule_test (datetime_processed,sign,`lock`) SELECT NOW(),ROUND(RAND()*10000),ROUND(RAND()) FROM schedule_test;
INSERT INTO schedule_test (datetime_processed,sign,`lock`) SELECT NOW(),ROUND(RAND()*10000),ROUND(RAND()) FROM schedule_test;
INSERT INTO schedule_test (datetime_processed,sign,`lock`) SELECT NOW(),ROUND(RAND()*10000),ROUND(RAND()) FROM schedule_test;
INSERT INTO schedule_test (datetime_processed,sign,`lock`) SELECT NOW(),ROUND(RAND()*10000),ROUND(RAND()) FROM schedule_test;
INSERT INTO schedule_test (datetime_processed,sign,`lock`) SELECT NOW(),ROUND(RAND()*10000),ROUND(RAND()) FROM schedule_test;
INSERT INTO schedule_test (datetime_processed,sign,`lock`) SELECT NOW(),ROUND(RAND()*10000),ROUND(RAND()) FROM schedule_test;
INSERT INTO schedule_test (datetime_processed,sign,`lock`) SELECT NOW(),ROUND(RAND()*10000),ROUND(RAND()) FROM schedule_test;
INSERT INTO schedule_test (datetime_processed,sign,`lock`) SELECT NOW(),ROUND(RAND()*10000),ROUND(RAND()) FROM schedule_test;
FLUSH STATUS;
select sa.`lock`, sign, datetime_next
from schedule_test as sa
where
sa.`lock` = 0
and sa.`datetime_next` <= now()
order by sa.`datetime_next` asc
limit 10;
SHOW STATUS LIKE 'Handler_read%';
FLUSH STATUS;
update schedule_test as sa
set
sa.`lock` = 1,
sa.sign = 2123123123,
sa.`datetime_processed` = now()
where
sa.`lock` = 0
and sa.`datetime_next` <= now()
order by sa.`datetime_next` asc
limit 10;
SHOW STATUS LIKE 'Handler_read%';
DROP TABLE schedule_test;