[ale] Mysql troubles. Updates are very slow

[Jeff Jansen]

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

file_put_contents("/tmp/kids","$kidId $rank\n",FILE_APPEND);

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.)

HTH

Jeff

On Wed, Dec 23, 2015 at 2:00 PM, Jim Lynch <ale_nospam at fayettedigital.com>
wrote:

> Here's a strange one.  I'm running a database update:  The code looks like:
>
>     try {
>         $sth = $dbh->prepare("update  child set class_rank=:rank where
> child_id=:child");
>         foreach($childRanks as $kidId=>$rank) {
>             $sth->bindParam(':rank',$rank,PDO::PARAM_INT);
>             $sth->bindParam(':child',$kidId,PDO::PARAM_INT);
>             $sth->execute();
>     //        prof_flag("update child set rank $kidId");
>             file_put_contents("/tmp/kids","$kidId $rank\n",FILE_APPEND);
>
>         }
> There is an index on child_id.  It takes 19 seconds to execute on 506
> updates.
>
> Number of ranks = 506 Start
>    0.011958
> Start at try
>    0.044773
> Get child data next
>    0.025755
> loop through familys
>    0.005682
> update child set rank
>    19.035966
> Done
> 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.
>
> 506 Start
>    0.000419
> Start at try
>    0.050433
> Get child data next
>    0.048817
> loop through familys
>    0.003815
> update child set rank
>    0.116944
> Done
> 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.
>
> 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.
>
> Version on tower:
> mysqld  Ver 5.5.44-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64
> ((Ubuntu))
> version on VPS:
> mysqld  Ver 5.5.46-0ubuntu0.14.04.2 for debian-linux-gnu on x86_64
> ((Ubuntu))
>
> No special configurations done to either mysql.  Running it as it was
> installed via apt-get.  No tuning.
>
> 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.
>
> Tower is running Mint 17.2 while the VPS is running Ubuntu 14.04.1.
>
> I don't see anything other than updates that are running slow. Selects and
> inserts are OK.
>
> Any ideas?
>
> Thanks,
> Jim.
>
>
>
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://mail.ale.org/mailman/listinfo/ale
> See JOBS, ANNOUNCE and SCHOOLS lists at
> http://mail.ale.org/mailman/listinfo
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ale.org/pipermail/ale/attachments/20151223/4cf95e1d/attachment.html>