UPDATE query causes CPU drain

[Tim Fullerton]

I am new to MySQL, and I have an issue.

I saw this thread on Google,
http://groups.google.com/groups?hl=e...TF-8&threadm=8
g1276%242mog%241%40FreeBSD.csie.NCTU.edu.tw&rnum=1 0&prev=/groups%3Fhl%3Den%2
6lr%3Dlang_en%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DMySQL%2BUPDATE%2Bslow, that
describes my problem exactly.

I am updating a table in Java via JDBC, and it throws MySQL into an infinite
loop that consumes 100% CPU time.

MySQL SERVER: 4.0.17-nt

The query:
UPDATE prices
SET 1_mo=1.1, 3_mo=1.2, 6_mo=1.3, 1_yr=1.4, 5_yr=1.5, 10_yr=5.3
WHERE dms_ticker='GE' AND date='2003-12-31';

I do repeat this thousands of times via a for loop, where each of the
inserted values is individually calculated.

the prices table looks like:
mysql> show columns from prices;
+-----------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| dms_ticker | varchar(5) | YES | | NULL | |
| dms_security_id | int(11) | YES | | NULL | |
| date | datetime | YES | | NULL | |
| open | double | YES | | NULL | |
| close | double | YES | | NULL | |
| adj_close | double | YES | | NULL | |
| high | double | YES | | NULL | |
| low | double | YES | | NULL | |
| volume | double | YES | | NULL | |
| 1_mo | double | YES | | NULL | |
| 3_mo | double | YES | | NULL | |
| 6_mo | double | YES | | NULL | |
| 1_yr | double | YES | | NULL | |
| 5_yr | double | YES | | NULL | |
| 10_yr | double | YES | | NULL | |
| score | int(11) | YES | | NULL | |
+-----------------+------------+------+-----+---------+----------------+

Is this a known issue in 4.0.17? Is there a better way for me to accomplish
this?

Thanks in advance,

Tim

[Aggro]

Tim Fullerton wrote:

I do repeat this thousands of times via a for loop, where each of the
inserted values is individually calculated.

This will be very slow. Are you sure your MySQL hangs? It could be just
doing it's job.

Can you try to do the same with few hundred inserts, then perhaps a
thousand, etc. Find out how the execution time increases and see if you
can find any critical points when execution time seems to increase to
infinite.

You should also check how long does it take for MySQL to commit one
update command. If one update takes for example 2 seconds, then updating
5000 rows would take about 2,7 hours.

[Aggro]

Tim Fullerton wrote:

I do repeat this thousands of times via a for loop, where each of the
inserted values is individually calculated.

This will be very slow. Are you sure your MySQL hangs? It could be just
doing it's job.

Can you try to do the same with few hundred inserts, then perhaps a
thousand, etc. Find out how the execution time increases and see if you
can find any critical points when execution time seems to increase to
infinite.

You should also check how long does it take for MySQL to commit one
update command. If one update takes for example 2 seconds, then updating
5000 rows would take about 2,7 hours.

[Aggro]

Tim Fullerton wrote:

I do repeat this thousands of times via a for loop, where each of the
inserted values is individually calculated.

This will be very slow. Are you sure your MySQL hangs? It could be just
doing it's job.

Can you try to do the same with few hundred inserts, then perhaps a
thousand, etc. Find out how the execution time increases and see if you
can find any critical points when execution time seems to increase to
infinite.

You should also check how long does it take for MySQL to commit one
update command. If one update takes for example 2 seconds, then updating
5000 rows would take about 2,7 hours.