On 6/6/06, Eugene Kosov <[EMAIL PROTECTED]> wrote:
Hi, List!
I'm a little bit confused with (IMHO) poor query performance.
I have a table with 1'000'000 records.
Table consists of 2 service fields and a number of data fields. Service
fields are status and processor_id (added for concurrent queue processing).
The question is why are updates so slow?
A query like:
UPDATE queue SET status=1 WHERE status=0 LIMIT 1;
takes about 5 seconds while this
SELECT * FROM queue WHERE status=0 LIMIT 1;
takes 0.01-0.02 second.
As I can see in process list most of the time query is "Searching rows
for update" what's very strange. I thought UPDATE searches rows the
same way SELECT does. Doesn't it?
Actually, seems like it does, because if I remove all fields except for
id and status, same both queries (SELECT & UPDATE) work quite fast.
So, why is my update query so slow? What can I do to make it work faster?
Can I somehow find out what is the bottleneck here? May be I should
increase some buffers or something else? I copied "my-huge" my.cnf
sample from mysql distribution.
I'm looking forward for any help because I'm stuck with this and don't
know what to do.
Thanks in advance to all!
P.S.:
Some table info:
mysql> show table status like 'queue';
+------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+------------------+----------+----------------+-----------------------+
| Name | Engine | Version | Row_format | Rows |
Avg_row_length | Data_length | Max_data_length | Index_length |
Data_free | Auto_increment | Create_time | Update_time |
Check_time | Collation | Checksum | Create_options |
Comment |
+------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+------------------+----------+----------------+-----------------------+
| queue | InnoDB | 9 | Dynamic | 726423
| 159 | 116031488 | NULL | 32555008
| 0 | 1000001 | 2006-06-06 22:01:21 | NULL |
NULL | koi8r_general_ci | NULL | | InnoDB free:
68608 kB |
+------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+------------------+----------+----------------+-----------------------+
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 | 1170633 | NULL | NULL | |
BTREE | |
| queue | 1 | status | 1
| status | A | 18 | NULL | NULL | |
BTREE | |
| queue | 1 | processor_id | 1
| processor_id | A | 18 | NULL | NULL | YES |
BTREE | |
+----------------------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.01 sec)
/etc/my.cnf:
...
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
...
Check http://bugs.mysql.com/bug.php?id=12915
--
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
