MySQL Bugs: #44618: HIGH_PRIORITY/LOW

Bug #44618	HIGH_PRIORITY/LOW_PRIORITY don't work
Submitted:	1 May 2009 22:12	Modified:	23 May 2009 15:11
Reporter:	Chris Bolt	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0.75, 5.1 bzr	OS:	Linux
Assigned to:	Sveta Smirnova	CPU Architecture:	Any

Description:
We have a MyISAM table that gets three types of queries:

- SELECT count(*) to find the number of rows for a user
- SELECT deviationid, ts to get rows when a certain page is viewed
- A long, slow DELETE when a lot of rows need to be deleted

The count(*) is simple and fast and gets run often, and the DELETE is slow but is run in the background. Initially it was blocking all other queries until it finished, so we changed the DELETE to limit to 250 rows at a time and run the query repeatedly. The repeated queries still locked out the SELECTs, so we tried adding LOW_PRIORITY to the DELETE and HIGH_PRIORITY to the SELECT, but even with these options, a SELECT will be locked for 25 seconds but a DELETE will still take priority, even if it's run well after the SELECT. Here is a snapshot of the processlist when this happens: http://dl.getdropbox.com/u/48012/processlist.html

Isn't LOW_PRIORITY/HIGH_PRIORITY supposed to do anything?

How to repeat:
Run a bunch of HIGH_PRIORITY SELECTs in one thread and have another thread run LOW_PRIORITY DELETEs sequentially.

Also, the helper_withdraw iter:44 comment means it's the 44th DELETE query from that connection.

Thank you for the report.

According to http://dev.mysql.com/doc/refman/5.0/en/select.html:

HIGH_PRIORITY gives the SELECT higher priority than a statement that updates a table. You
should use this only for queries that are very fast and must be done at once. A SELECT
HIGH_PRIORITY query that is issued while the table is locked for *reading* runs even if
there is an update statement waiting for the table to be free. 

and to http://dev.mysql.com/doc/refman/5.0/en/insert.html:

If you use the LOW_PRIORITY keyword, execution of the INSERT is delayed until no other
clients are *reading* from the table. This includes other clients that began reading while
existing clients are reading, and while the INSERT LOW_PRIORITY statement is waiting.

In your case INSERT can lock table for *writing* in time when no SELECT was run and following SELECTs have to wait.

But is interesting why time for DELETE is lower than for INSERT. I can not repeat same behavior using stress tests. Please specify if you issue LOCK TABLES and/or run statements in certain order. Also please provide your configuration file.

We do not issue LOCK TABLES (we threw an UNLOCK TABLES in just to make sure) and the DELETEs are run sequentially with nothing in between.

Our config is:

[mysqld]
port                    = 3306
socket                  = /tmp/mysql.sock
set-variable    = long_query_time=2
set-variable    = max_connect_errors=100
set-variable    = max_connections=100
set-variable    = key_buffer=384M
set-variable    = max_allowed_packet=1M
set-variable    = table_cache=1024
set-variable    = join_buffer_size=1M
set-variable    = sort_buffer=1M
set-variable    = record_buffer=2M
set-variable    = myisam_sort_buffer_size=64M
set-variable    = thread_cache=48
set-variable    = thread_concurrency=8
set-variable    = query_cache_size=16M
skip-locking
skip-name-resolve
skip-slave-start
log-slow-queries

Thank you for the feedback.

Verified as described.

To repeat: start mysqld with query_cache=16M, then use following tests for mysql-test-extra:

$cat bug44618.zz 
$tables = {
        rows => [10000],
        engines => ['MyISAM'],
};

$fields = {
        types => ['int', 'varchar'],
        indexes => [undef, 'unique'],
        null => ['not null'],
        sign => ['unsigned'],
};

$data = {
        numbers => ['digit'],
        strings => ['english'],
};

$cat bug44618.yy
query:
         select | select1 | select | select1 | select | select1 | select | select1 | select | select1 | delete ;

select:
          SELECT HIGH_PRIORITY  * from _table where _field=digit;

select1:
          SELECT HIGH_PRIORITY COUNT(*) FROM _table;

delete:
         DELETE LOW_PRIORITY FROM  _table WHERE 1=sleep(10) and _field=232432432423;

$./gentest.pl --dsn=dbi:mysql:host=127.0.0.1:port=3351:user=root:database=bug44618 --gendata=bug44618.zz --grammar=bug44618.yy --threads=20 --queries=10000
# 10:21:33 Starting 
# 10:21:33  ./gentest.pl \ 
...

Then examine output of SHOW PROCESSLIST.

Finally I got:

mysql> show processlist;
+------+------+-----------------+----------+---------+------+------------+------------------------------------------------------------------------------------------------------+
| Id   | User | Host            | db       | Command | Time | State      | Info                                                                                                 |
+------+------+-----------------+----------+---------+------+------------+------------------------------------------------------------------------------------------------------+
| 2577 | root | localhost       | bug44618 | Query   |  739 | Locked     | select * FROM `table10000_myisam_int_autoinc` limit 1                                                | 
...
| 3132 | root | localhost:61703 | bug44618 | Query   |  603 | User sleep | DELETE LOW_PRIORITY FROM `table100_myisam_int_autoinc` WHERE 1=sleep(10) and `varchar_not_null`=2324 | 
| 3133 | root | localhost:61704 | bug44618 | Query   |    2 | Locked     | DELETE LOW_PRIORITY FROM `table100_myisam_int_autoinc` WHERE 1=sleep(10) and `varchar_not_null_uniqu | 
| 3134 | root | localhost:61705 | bug44618 | Query   |  703 | Locked     | SELECT HIGH_PRIORITY * from `table10000_myisam_int_autoinc` where `int_unsigned_not_null_unique`=5   | 
...
| 3145 | root | localhost:61716 | bug44618 | Query   |  102 | Locked     | DELETE LOW_PRIORITY FROM `table100_myisam_int_autoinc` WHERE 1=sleep(10) and `pk`=232432432423       | 
| 3146 | root | localhost:61717 | bug44618 | Query   |  714 | Locked     | SELECT HIGH_PRIORITY * from `table10000_myisam_int_autoinc` where `int_unsigned_not_null`=8          | 
| 3147 | root | localhost       | NULL     | Query   |    0 | NULL       | show processlist                                                                                     | 
+------+------+-----------------+----------+---------+------+------------+------------------------------------------------------------------------------------------------------+
22 rows in set (0.16 sec)

Probably single SELECT without HIGH_PRIORITY required to repeat the problem.

Better *yy script:

$cat  bug44618.yy 
query:
         select | hselect | select | hselect | select | hselect | select | hselect | select | hselect | delete ;

select:
          SELECT  * from _table where _field=digit;

hselect:
          SELECT HIGH_PRIORITY COUNT(*) FROM _table;

delete:
         DELETE LOW_PRIORITY FROM  _table WHERE 1=sleep(10) and _field=232432432423;

For easier checking of error one can use MySQL Proxy with script attached:

$mysql-proxy --proxy-backend-addresses=127.0.0.1:3351 --proxy-lua-script=./bug44618.lua

And in another window: while mysql -h127.0.0.1 -P4040 -e "show processlist" >bug44618.log; do echo 1; done

lua script for MySQL Proxy

Attachment: bug44618.lua (application/octet-stream, text), 1.45 KiB.

High priority/low priority take affect for items in the queue, they do not 'interrupt' queries that are already running as demonstrated below - the High prioirty select that is waiting after the low prioirty delete - is executed before it:

$ ./razno/work/mysql/mysql-5.0/client/mysqladmin process
+----+--------+-----------+-----+---------+------+--------------+-------------------------
-------------------------------+
| Id | User   | Host      | db  | Command | Time | State        | Info                    
                              |
+----+--------+-----------+-----+---------+------+--------------+-------------------------
-------------------------------+
| 1  | sinisa | localhost | bug | Query   | 11   | Sending data | select * from t1 where
id=3 and 1=sleep(90)            |
| 2  | sinisa | localhost | bug | Query   | 4    | Locked       | delete low_priority from
t1 where id=5 and 1=sleep(40) |
| 3  | sinisa | localhost | bug | Sleep   | 139  |              |                         
                              |
| 10 | sinisa | localhost |     | Query   | 0    |              | show processlist        
                              |
+----+--------+-----------+-----+---------+------+--------------+-------------------------
-------------------------------+
bash-3.2$ ./razno/work/mysql/mysql-5.0/client/mysqladmin process
+----+--------+-----------+-----+---------+------+--------------+-------------------------
----------------------------------+
| Id | User   | Host      | db  | Command | Time | State        | Info                    
                                 |
+----+--------+-----------+-----+---------+------+--------------+-------------------------
----------------------------------+
| 1  | sinisa | localhost | bug | Query   | 23   | Sending data | select * from t1 where
id=3 and 1=sleep(90)               |
| 2  | sinisa | localhost | bug | Query   | 16   | Locked       | delete low_priority from
t1 where id=5 and 1=sleep(40)    |
| 3  | sinisa | localhost | bug | Query   | 3    | Sending data | select high_priority *
from t1 where id=6 and 1=sleep(30) |
| 11 | sinisa | localhost |     | Query   | 0    |              | show processlist        
                                 |
+----+--------+-----------+-----+---------+------+--------------+-------------------------
----------------------------------+

bash-3.2$ ./razno/work/mysql/mysql-5.0/client/mysqladmin process
+----+--------+-----------+-----+---------+------+--------------+-------------------------
----------------------------------+
| Id | User   | Host      | db  | Command | Time | State        | Info                    
                                 |
+----+--------+-----------+-----+---------+------+--------------+-------------------------
----------------------------------+
| 1  | sinisa | localhost | bug | Query   | 19   | Sending data | select * from t1 where
id=3 and 1=sleep(90)               |
| 2  | sinisa | localhost | bug | Query   | 16   | Locked       | delete low_priority from
t1 where id=6 and 1=sleep(40)    |
| 3  | sinisa | localhost | bug | Query   | 3    | Sending data | select high_priority *
from t1 where id=6 and 1=sleep(30) |
| 5  | sinisa | localhost |     | Query   | 0    |              | show processlist        
                                 |
+----+--------+-----------+-----+---------+------+--------------+-------------------------
----------------------------------+

bash-3.2$ ./razno/work/mysql/mysql-5.0/client/mysqladmin process
+----+--------+-----------+-----+---------+------+--------------+-------------------------
-------------------------------+
| Id | User   | Host      | db  | Command | Time | State        | Info                    
                              |
+----+--------+-----------+-----+---------+------+--------------+-------------------------
-------------------------------+
| 1  | sinisa | localhost | bug | Query   | 53   | Sending data | select * from t1 where
id=3 and 1=sleep(90)            |
| 2  | sinisa | localhost | bug | Query   | 50   | Locked       | delete low_priority from
t1 where id=6 and 1=sleep(40) |
| 3  | sinisa | localhost | bug | Sleep   | 37   |              |                         
                              |
| 6  | sinisa | localhost |     | Query   | 0    |              | show processlist        
                              |
+----+--------+-----------+-----+---------+------+--------------+-------------------------
-------------------------------+

With the bug I was experiencing, it was happening like this:

- One thread would run a DELETE LOW_PRIORITY
- Another thread would start a SELECT which would be Locked
- First thread would finish, then run a second DELETE LOW_PRIORITY, and that query would run even though the Locked SELECT was started first and has a higher priority

Test case:

create table t1 (a int);
lock tables t1 write;
insert into t1 values (1);

connect (con1,localhost,root,,);
--send delete low_priority from t1;
--sleep 2
connect (con2,localhost,root,,);
--send select * from t1;
--sleep 2
connect (con3,localhost,root,,);
--send select high_priority * from t1;
--sleep 2
connection default;
unlock tables;
connection con1;
--reap
connection con2;
--reap
connection con3;
--reap

If anyone still has doubts about this, swap the statements in con2 and con3 and watch the results.

The test case demonstrates that if a write lock is running and there is a LOW PRIORITY delete waiting, a HIGH PRIORITY select on the top of the queue does have an effect. And this is not documented.

Davi, I don't understand what your test case demonstrates.
TL_WRITE lock of LOCK TABLES blocks everything.
Until it is released all other connections will be block.
Or do you see a bug in the order in which we give locks away once a write lock is released? But your test case doesn't demonstrate that order?

> Or do you see a bug in the order in which we give locks away once a write lock is
> released? But your test case doesn't demonstrate that order?

Yes, I see a inconsistency (or lack of documentation) in the order after a write lock is released.

In the test case the LOCK TABLES is used to simulate a exclusive write lock. Subsequently, a DELETE LOW_PRIORITY, a SELECT and a SELECT HIGH_PRIORITY all wait for the lock to released.

The inconsistency is that if the SELECT HIGH_PRIORITY is on the top of the (read) wait queue, its the one chosen to proceed once the write lock is released. If it's not on the top of the queue (the normal SELECT is), the DELETE LOW_PRIORITY is the one chosen.

So, if the queues are like the following:

write queue -> DELETE LOW_PRIORITY
read queue -> SELECT HIGH_PRIORITY, SELECT

The order will be: SELECT HIGH_PRIORITY, SELECT, DELETE LOW_PRIORITY

But if the queues are like:

write queue -> DELETE LOW_PRIORITY
read queue -> SELECT, SELECT HIGH_PRIORITY

The order will be: DELETE LOW_PRIORITY, SELECT / SELECT HIGH_PRIORITY

Davi,

Please read the manual.

HIGH_PRIORITY in reads are applicable only against updates, not against other SELECTs. Manual states that loud and clear.

Sinisa,

The problem is not the other SELECT. It's there just to demonstrate that the position of the SELECT HIGH_PRIORITY on the read queue affects the order when there is a DELETE LOW_PRIORITY on the write queue.