[ale] Mysql troubles. Updates are very slow

[Alex Carver]

There's also another small speedup that you can do.  Double quotes in
PHP are parsed looking for variable replacements.  Single quotes are
not.  String concatenation is faster than parsing so change your write
statement from:

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

(or similar if you use fwrite())

to:

file_put_contents('/tmp/kids',$kidId.' '.$rank.PHP_EOL,FILE_APPEND);

(PHP_EOL is the predefined end of line for the platform, you could also
substitute your own constant and just define() it:

define('NL', chr(10));

)


On 2015-12-23 13:01, Jim Lynch wrote:
> 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.