<div dir="ltr"><div><div><div>Just to be clear, is your benchmark only profiling the MySQL calls or the disk access as well? The problem could be disk access with the<br><br> file_put_contents("/tmp/kids","$kidId $rank\n",FILE_APPEND);<br><br></div>line. 19 seconds is still A LONG TIME to open, write, and close a file 500+ times, but I would comment that line out and run the code again and see what happens. If it is a disk problem, try fopen() before the foreach() loop, fwrite() in the loop, and fclose() after the loop. See how that compares. (Make sure you fclose() on any exceptions in that case.)<br><br></div>HTH<br><br></div>Jeff<br><div><div><div><div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Dec 23, 2015 at 2:00 PM, Jim Lynch <span dir="ltr"><<a href="mailto:ale_nospam@fayettedigital.com" target="_blank">ale_nospam@fayettedigital.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Here's a strange one. I'm running a database update: The code looks like:<br>
<br>
try {<br>
$sth = $dbh->prepare("update child set class_rank=:rank where child_id=:child");<br>
foreach($childRanks as $kidId=>$rank) {<br>
$sth->bindParam(':rank',$rank,PDO::PARAM_INT);<br>
$sth->bindParam(':child',$kidId,PDO::PARAM_INT);<br>
$sth->execute();<br>
// prof_flag("update child set rank $kidId");<br>
file_put_contents("/tmp/kids","$kidId $rank\n",FILE_APPEND);<br>
<br>
}<br>
There is an index on child_id. It takes 19 seconds to execute on 506 updates.<br>
<br>
Number of ranks = 506 Start<br>
0.011958<br>
Start at try<br>
0.044773<br>
Get child data next<br>
0.025755<br>
loop through familys<br>
0.005682<br>
update child set rank<br>
19.035966<br>
Done<br>
That's output from a custom profiling app. time php xxx.php gives similar results without the profiling. It seems like a lot, so I've run it on another machine, a VPS in the cloud.<br>
<br>
506 Start<br>
0.000419<br>
Start at try<br>
0.050433<br>
Get child data next<br>
0.048817<br>
loop through familys<br>
0.003815<br>
update child set rank<br>
0.116944<br>
Done<br>
This is a mysql database. The slow one is on a 4 processor AMD machine with a single 1 TB Seagate SATA drive. (3 mb/s). Nothing else is running except apache, chrome and mysql. Memory usage is 3GB out of 4GB. Cpu usage is around 5% average on each of the 4 processors. Very little swap is used. CPU bogomips 6443. Running on a ext4 FS. Smartctl says nothing is amiss. No errors in logs.<br>
<br>
The VPS shows a 2 processor Intel running 6800 bogomips. Can't tell anything about I/O. Main memory is 512 MB with 100 MB used. Most of swap is also used.<br>
<br>
Version on tower:<br>
mysqld Ver 5.5.44-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))<br>
version on VPS:<br>
mysqld Ver 5.5.46-0ubuntu0.14.04.2 for debian-linux-gnu on x86_64 ((Ubuntu))<br>
<br>
No special configurations done to either mysql. Running it as it was installed via apt-get. No tuning.<br>
<br>
I verified that both runs actually did update the table. The indexes are identical. I exported the tables on the tower and imported them into the VPS. I verified they were the same.<br>
<br>
Tower is running Mint 17.2 while the VPS is running Ubuntu 14.04.1.<br>
<br>
I don't see anything other than updates that are running slow. Selects and inserts are OK.<br>
<br>
Any ideas?<br>
<br>
Thanks,<br>
Jim.<br>
<br>
<br>
<br>
_______________________________________________<br>
Ale mailing list<br>
<a href="mailto:Ale@ale.org" target="_blank">Ale@ale.org</a><br>
<a href="http://mail.ale.org/mailman/listinfo/ale" rel="noreferrer" target="_blank">http://mail.ale.org/mailman/listinfo/ale</a><br>
See JOBS, ANNOUNCE and SCHOOLS lists at<br>
<a href="http://mail.ale.org/mailman/listinfo" rel="noreferrer" target="_blank">http://mail.ale.org/mailman/listinfo</a><br>
</blockquote></div><br></div></div></div></div></div></div>