Hi, Eugene!
I suspect that you have run into locking issues with your table, which I
am guessing is a MyISAM table. MyISAM provides only table-level locking
- that is, only one write operation can occur at any time on the table.
Other operations are blocked until their turn comes.
It appears you have about 10 threads or processes running against this
table, which is enough to introduce performance problems.
Some suggestions:
1 - while your current select query is reasonably fast, it likely could
be faster. It's using an indexed column (status), but it's not a very
good index (no offense). There are only a few possible values, so the
index isn't terribly helpful in locating matching records. If possible,
you could re-write your perl scripts to do selects and updates against
the ID column (primary key), which should be much faster. This may be
enough to alleviate your locking problem; not sure.
2 -
Eugene Kosov wrote:
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]
