MySQL Tutorials - Herong's Tutorial Examples - v4.46, by Herong Yang
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) 2005 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.
herong$ 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 herong$ 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.
herong$ 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
Note that you may get the following error with the above script on the "SET sql_log_bin = off" statement.
1227: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
The solution is to grant the SESSION_VARIABLES_ADMIN privilege for user "herong".
mysql> GRANT SESSION_VARIABLES_ADMIN ON *.* TO herong@'127.0.0.1';
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
Storage Engines in MySQL Server
InnoDB Storage Engine - Primary and Secondary Indexes
►Performance Tuning and Optimization
►Performance of Inserting Integers to MySQL Database
"SHOW PROFILE" - Query Profiling
Impact of Binary Logging on INSERT
Impact of InnoDB Log Buffer on INSERT
Performance Comparison of Inserting Integers vs. Strings
Installing MySQL Server on Linux