Performance of Inserting Integers to MySQL Database

Linux Apps Tutorials - Herong's Tutorial Examples

∟Running MySQL Database Server on Linux Systems

∟Performance of Inserting Integers to MySQL Database

This section provides a tutorial example on how to test the performance of inserting integers into MySQL server database using PHP 'mysqli' functions. MySQL server is 100 times slower on my CentOS computer and macOS computer.

If you are working with large data sets, then you need to know the performance of MySQL server. Here are some measurements I collected on MySQL servers running my CentOS 8 and macOS 10 computers.

1. Create a PHP script to measure the speed of inserting integers into database.

<?php
# speed-of-insert-integer.php
# Copyright (c) 2009 HerongYang.com. All Rights Reserved.
#
  $count = 100;
  if (count($argv)>1) $count = $argv[1];

  $binLog = "on";
  if (count($argv)>2) $binLog = $argv[2];

  print("Testing parameters:\n");
  print("   Number of operations: ".$count."\n");
  print("   Binary log = $binLog\n");

  $con = mysqli_connect("127.0.0.1", 'herong', 'TopSecret', "test");

  $sql = "SET sql_log_bin = $binLog";
  $res = mysqli_query($con, $sql);

  $sql = "CREATE TABLE Tests (Quantity INTEGER)";
  $res = mysqli_query($con, $sql);

  $startTime = round(microtime(true)*1000);
  $i = 0;
  while ($i < $count) {
    $sql = "INSERT INTO Tests VALUES ($i)";
    $res = mysqli_query($con, $sql);
    $i++;
  }
  $endTime = round(microtime(true)*1000);
  $exeTime = $endTime - $startTime;
  print("Testing result:\n");
  print("   Execution time in milliseconds = ".$exeTime."\n");
  print("   Milliseconds per operation = ".round($exeTime/$count)."\n");

  $sql = "SELECT COUNT(*) FROM Tests";
  $res = mysqli_query($con, $sql);
  $row = mysqli_fetch_array($res);
  print("   Records inserted = ".$row[0]."\n");

  $sql = "DROP TABLE Tests";
  $res = mysqli_query($con, $sql);
  mysqli_close($con);
?>

2. Establish performance baseline for the MySQL server on my CentOS 8 computer. I see that inserting an integer into the database took about 55 milliseconds. This is too high and needs to be investigated.

centos$ php speed-of-insert-integer.php 100
Testing parameters:
   Number of operations: 100
   Binary log = on
Testing result:
   Execution time in milliseconds = 5295
   Milliseconds per operation = 53
   Records inserted = 100

centos$ php speed-of-insert-integer.php 1000
Testing parameters:
   Number of operations: 1000
   Binary log = on
Testing result:
   Execution time in milliseconds = 59162
   Milliseconds per operation = 59
   Records inserted = 1000

3. Check the impact of binary logging on the MySQL server on my CentOS 8 computer. I see that turning off binary logging does not improve the performance. This is also a surprise to me.

centos$ php speed-of-insert-integer.php 100 off
Testing parameters:
   Number of operations: 100
   Binary log = off
Testing result:
   Execution time in milliseconds = 5586
   Milliseconds per operation = 56
   Records inserted = 100

4. Compare performance with the MySQL server on my macOS 10 computer. I see that my macOS computer is 100 times faster! This is a big surprise to me!

macOS$ php speed-of-insert-integer.php 100
Testing parameters:
   Number of operations: 100
   Binary log = on
Testing result:
   Execution time in milliseconds = 47
   Milliseconds per operation = 0
   Records inserted = 100

macOS$ php speed-of-insert-integer.php 1000
Testing parameters:
   Number of operations: 1000
   Binary log = on
Testing result:
   Execution time in milliseconds = 397
   Milliseconds per operation = 0
   Records inserted = 1000