[ale] Mysql troubles. Updates are very slow

[Jim Lynch]

You probably caught it.  I ignored that write.  Sort of forgot it was in 
the loop.  I've looked at that code for hours and never saw it.
Thanks,
Jim.

On 12/23/2015 02:47 PM, Jeff Jansen wrote:
> 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 <mailto: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 <mailto:Ale at ale.org>
>     http://mail.ale.org/mailman/listinfo/ale
>     See JOBS, ANNOUNCE and SCHOOLS lists at
>     http://mail.ale.org/mailman/listinfo
>
>
>
>
> _______________________________________________
> 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