469,081 Members | 1,808 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,081 developers. It's quick & easy.

UPDATE query causes CPU drain

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
Jul 19 '05 #1
3 1574
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.
Jul 19 '05 #2
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.
Jul 19 '05 #3
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.
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Joshua Moore-Oliva | last post: by
3 posts views Thread by Greg Strong | last post: by
reply views Thread by Metal2You | last post: by
12 posts views Thread by si_owen | last post: by
2 posts views Thread by eggie5 | last post: by
6 posts views Thread by Nano | last post: by
5 posts views Thread by Chris Cowles | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.