Hi, list!
I have a little table (about 0.5 milloin records) which is kind of
queue. My perl script takes records from it one by one and does somes
actions.
Here's my table structure:
mysql> desc queue;
+----------------------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default
| Extra |
+----------------------+---------------------+------+-----+-------------------+----------------+
| id | int(10) unsigned | | PRI | NULL
| auto_increment |
| status | tinyint(4) | | MUL | 0
| |
| processor_id | int(10) unsigned | YES | MUL | NULL
| |
... some other data ...
+----------------------+---------------------+------+-----+-------------------+----------------+
This table, of course, has some indecies:
mysql> show indexes from queue;
+--------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name
| Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |
+--------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| queue | 0 | PRIMARY | 1 | id
| A | 522756 | NULL | NULL | | BTREE |
|
| queue | 1 | status | 1 | status
| A | 3 | NULL | NULL | | BTREE |
|
| queue | 1 | processor_id | 1 | processor_id
| A | 522756 | NULL | NULL | YES | BTREE |
|
+--------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
Perl scripts runs a query like 'UPDATE queue SET status=1,
processor_id=XXX WHERE status=0 LIMIT 1'. Whis query shoud (and uses as
we can see in following EXPLAIN result) `status` index and work rather
fast. But.. It becomes slower and slower. I don't understand why.
mysql> explain select * from certificates where status=0;
+----+-------------+--------------+------+---------------+--------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+--------+---------+-------+-------+-------------+
| 1 | SIMPLE | queue | ref | status | status |
1 | const | 52740 | Using where |
+----+-------------+--------------+------+---------------+--------+---------+-------+-------+-------------+
1 row in set (0.00 sec)
Despite row numbers to scan (in EXPLAIN results) I think query should
work fast because of 'LIMIT 1'. And it works. A query like 'SELECT *
FROM queue WHERE status=0 LIMIT 1' works very fast. But in case of
update I see that it's not so fast.
mysql> show processlist;
+------+------+-----------+--------------+---------+------+---------------------------+---------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State
| Info
|
+------+------+-----------+--------------+---------+------+---------------------------+---------------------------------------------------------------------------------------+
| 3257 | root | localhost | certificates | Sleep | 247 |
| NULL
|
| 3261 | root | localhost | certificates | Query | 0 | Locked
| SELECT id, domain FROM queue WHERE status='1' AND
processor_id='10580' LIMIT 1 |
| 3262 | root | localhost | certificates | Query | 1 | Locked
| UPDATE queue SET status='1', processor_id='10584'
WHERE status='0' LIMIT 1 |
| 3263 | root | localhost | certificates | Query | 1 | Locked
| SELECT id, domain FROM queue WHERE status='1' AND
processor_id='10588' LIMIT 1 |
| 3264 | root | localhost | certificates | Query | 1 | Locked
| SELECT id, domain FROM queue WHERE status='1' AND
processor_id='10590' LIMIT 1 |
| 3265 | root | localhost | certificates | Query | 1 | Locked
| UPDATE queue SET status='1', processor_id='10592'
WHERE status='0' LIMIT 1 |
| 3266 | root | localhost | certificates | Query | 1 | Locked
| UPDATE queue SET status='1', processor_id='10604'
WHERE status='0' LIMIT 1 |
| 3267 | root | localhost | certificates | Query | 1 | Locked
| UPDATE queue SET status='1', processor_id='10614'
WHERE status='0' LIMIT 1 |
| 3268 | root | localhost | certificates | Query | 1 | Searching
rows for update | UPDATE queue SET status='1', processor_id='10616'
WHERE status='0' LIMIT 1 |
| 3269 | root | localhost | certificates | Query | 1 | Locked
| UPDATE queue SET status='1', processor_id='10618'
WHERE status='0' LIMIT 1 |
| 3270 | root | localhost | certificates | Query | 1 | Locked
| UPDATE queue SET status='1', processor_id='10620'
WHERE status='0' LIMIT 1 |
| 3271 | root | localhost | certificates | Query | 0 | NULL
| show processlist
|
+------+------+-----------+--------------+---------+------+---------------------------+---------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)
Is it because of index changing after update?
Am I doing something wrong?
Any ideas about how to improve perfomance of this?
Thanks in advance!!
P.S: Size of index is about 10M so it can be loaded into buffer entirely.
mysql> show variables like '%key%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| delay_key_write | ON |
| have_rtree_keys | YES |
| key_buffer_size | 20971520 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_seeks_for_key | 4294967295 |
+--------------------------+------------+
7 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
