List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:May 23 2006 2:55am
Subject:Re: Need guidance with perfomance improvement...
View as plain text  
Hi, Eugene!

Sorry, my last post was sent too soon.


I suspect that you have run into locking issues with your table, which I 
am guessing is a MyISAM table.  MyISAM uses 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.

http://dev.mysql.com/doc/refman/5.0/en/table-locking.html

It appears you have about 10 threads or processes running against this 
table, which is enough to introduce performance problems in your case.

Some suggestions:

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.

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.

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.

Hope this helps, Eugene.

Dan




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