List:General Discussion« Previous MessageNext Message »
From:Eugene Kosov Date:May 24 2006 8:28am
Subject:Re: Need guidance with perfomance improvement...
View as plain text  
> 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)
>>
>>
>>
>>
>>
>>
> 
Thread
Need guidance with perfomance improvement...Eugene Kosov22 May
  • Re: Need guidance with perfomance improvement...Dan Buettner23 May
  • Re: Need guidance with perfomance improvement...Dan Buettner23 May
    • Re: Need guidance with perfomance improvement...Eugene Kosov24 May