1 - while your current select query is reasonably fast, it likely could
be faster. It may be that it's taking 0.9 seconds and feels fast, but
perhaps it could be 0.09 seconds instead - and that's not something a
human can really tell the difference on. It's using an indexed column
(status), but it's not a very good index (no offense). There are only a
few possible values in 500,000 records, 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 using the ID column (primary
key), which should be faster than the status column alone. This may be
enough to alleviate your locking problem; not sure.
My select query works realy fast. It's not even 0.09 sec. It takes less
than 0,01 sec (client shows 0,00), while UPDATE executes 0.33 sec (if I
stop all other processes). I know `status` index here isn't very good,
but I think 'LIMIT 1' should help here to work faster. I can't use here
primary key because of script architecture. Or is there a way of
concurrent queue processing, without some kind of status field?
2 - Switch to an InnoDB table, which should allow concurrent SELECT and
UPDATE commands. This is a fairly big change if you're not used to
InnoDB tables, so read up on this first.
I thought It will be notably slower with InnoDB.
3 - Depending on how you use this table, running OPTIMIZE TABLE
periodically may help performance. Updates and deletes on a MyISAM
table can cause a fragmented table and poor performance. In my last job
I had a table with many thousands of INSERT and subsequent UPDATE
operations every day, and it would regularly become fragmented to the
point where performance was 1/4 of what it should have been. I started
running an OPTIMIZE TABLE command every few hours, took a couple of
seconds each time, and performance stayed good.
Thanks for advice. I think I should I give this a try.. :)
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]
