The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 18, 2019, 10:39:14 AM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: MySQL slooowwwwsssss down  (Read 1602 times)
kurdt
Lifer
*****
Offline Offline

Posts: 1153


paha arkkitehti


View Profile
« on: November 27, 2009, 09:05:35 AM »

Here's interesting behavior from MySQL.

Here's my script:
Code:
$count = exe_sql(DATABASE,"SELECT count(id) FROM sites");
$cycle = 10000;
for($i=0;$i<$count[0];$i++) {
$start = time();
echo $i."/".$count[0]."\n";
$urls = exe_sql(DATABASE,"SELECT id,url FROM sites ORDER by id LIMIT ".$i.",".$cycle);
foreach($urls as $url) {
$valid_url = valid_url($url['url']);
if(!$valid_url) {
echo $url['id']." - ".$url['url']." is not valid.\n";
exe_sql(DATABASE,"DELETE FROM sites WHERE id = ".$url['id']);
}
}
$i = $i+$cycle;
$end = time();
$timetook[] = $end-$start;
foreach($timetook as $took) {
$ave += $took;
}
echo "average time: ".$ave/count($timetook)." - ".count($urls)."\n";
}
This is a script that checks that url data is valid in certain table. Now here's the output:
Code:
0/8994994
average time: 0 - 10000
10001/8994994
average time: 0 - 10000
20002/8994994
average time: 0 - 10000
30003/8994994
average time: 0.25 - 10000
40004/8994994
average time: 0.4 - 10000
50005/8994994
average time: 0.666666666667 - 10000
60006/8994994
average time: 0.857142857143 - 10000
70007/8994994
average time: 1.125 - 10000
80008/8994994
average time: 1.44444444444 - 10000
90009/8994994
average time: 1.7 - 10000
100010/8994994
average time: 2 - 10000
110011/8994994
average time: 2.33333333333 - 10000
120012/8994994
average time: 2.69230769231 - 10000
130013/8994994
average time: 3.07142857143 - 10000
140014/8994994
average time: 3.46666666667 - 10000
150015/8994994
average time: 3.875 - 10000
160016/8994994
average time: 4.29411764706 - 10000
170017/8994994
average time: 4.72222222222 - 10000
180018/8994994
average time: 5.21052631579 - 10000
190019/8994994
average time: 5.7 - 10000
200020/8994994
average time: 6.2380952381 - 10000
210021/8994994
average time: 6.77272727273 - 10000
220022/8994994
average time: 7.34782608696 - 10000
230023/8994994
average time: 7.91666666667 - 10000
240024/8994994
...ctrl+c

Any ideas why it's slowing down like that?
Logged

I met god and he had nothing to say to me.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #1 on: November 29, 2009, 03:33:55 PM »

I believe that there are processes that will run in the background to clean up the tables you've deleted from, even though the initial command is complete. As you push on it in rapid fire, you'd be binding up the processor more and more. I'd wager if you put a small sleep between them you'd do better.

You'd also probably do better if you through the whole thing up as a multiquery, because it'd be done really in sequence I think - given that the entire thing would be prepared as a query. I THINK.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!