Description:
I performs an update operation repeatedly 1000 times .
I executed this script on MySQL 8.0.39 and MySQL 5.7.44 ,MySQL 8 takes twice as long as MySQL 5.7 to complete .
They are used the same server and the same configuration file .(Only one version of MySQL will run at a time)
-- configuration
-- MySQL 5.7
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir=/usr/local/software/mysql-5.7.44-linux-glibc2.12-x86_64
datadir=/dbdata/my57/data
log-error=/dbdata/my57/err.log
-- MySQL 8.0
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir=/usr/local/software/mysql-8.0.39-linux-glibc2.12-x86_64
datadir=/dbdata/my80/data
log-error=/dbdata/my80/err.log
-- 5.7 run three times
# python t3.py
Test standard SQL ...
standar SQL total time : 1.265814 seconds
# python t3.py
Test standard SQL ...
standar SQL total time : 1.266818 seconds
# python t3.py
Test standard SQL ...
standar SQL total time : 1.291490 seconds
-- 8.0 run three times
# python t3.py
Test standard SQL ...
standar SQL total time : 2.709134 seconds
# python t3.py
Test standard SQL ...
standar SQL total time : 2.687441 seconds
# python t3.py
Test standard SQL ...
standar SQL total time : 2.695148 seconds
How to repeat:
-- 1 .create test table
create database if not exists test ;
use test ;
drop table if exists sequence;
create table sequence (
seq_name varchar(100) not null
,current_value int not null
,increment int not null default 1
,primary key (seq_name)
) engine = innodb ;
insert into sequence values('genid' ,0 ,1 );
-- 2. run test python script to get total time
import pymysql
import time
if __name__ == "__main__":
connection = pymysql.connect(
unix_socket='/tmp/mysql.sock',
user='user_name',
password='pwd',
database='test',
)
cursor = connection.cursor()
print("Test standard SQL ...")
sql_cmd_1 = "update sequence set current_value = current_value + increment where seq_name = 'genid'"
#sql_cmd_2 = "select current_value from sequence where seq_name = 'genid'"
start_time = time.time()
for i in range(1000):
cursor.execute(sql_cmd_1)
#cursor.execute(sql_cmd_2)
connection.commit()
end_time = time.time()
elapsed_time = end_time - start_time
print(f"standar SQL total time : {elapsed_time:.6f} seconds \n")
cursor.close()
connection.close()
Description: I performs an update operation repeatedly 1000 times . I executed this script on MySQL 8.0.39 and MySQL 5.7.44 ,MySQL 8 takes twice as long as MySQL 5.7 to complete . They are used the same server and the same configuration file .(Only one version of MySQL will run at a time) -- configuration -- MySQL 5.7 [mysqld] port = 3306 socket = /tmp/mysql.sock basedir=/usr/local/software/mysql-5.7.44-linux-glibc2.12-x86_64 datadir=/dbdata/my57/data log-error=/dbdata/my57/err.log -- MySQL 8.0 [mysqld] port = 3306 socket = /tmp/mysql.sock basedir=/usr/local/software/mysql-8.0.39-linux-glibc2.12-x86_64 datadir=/dbdata/my80/data log-error=/dbdata/my80/err.log -- 5.7 run three times # python t3.py Test standard SQL ... standar SQL total time : 1.265814 seconds # python t3.py Test standard SQL ... standar SQL total time : 1.266818 seconds # python t3.py Test standard SQL ... standar SQL total time : 1.291490 seconds -- 8.0 run three times # python t3.py Test standard SQL ... standar SQL total time : 2.709134 seconds # python t3.py Test standard SQL ... standar SQL total time : 2.687441 seconds # python t3.py Test standard SQL ... standar SQL total time : 2.695148 seconds How to repeat: -- 1 .create test table create database if not exists test ; use test ; drop table if exists sequence; create table sequence ( seq_name varchar(100) not null ,current_value int not null ,increment int not null default 1 ,primary key (seq_name) ) engine = innodb ; insert into sequence values('genid' ,0 ,1 ); -- 2. run test python script to get total time import pymysql import time if __name__ == "__main__": connection = pymysql.connect( unix_socket='/tmp/mysql.sock', user='user_name', password='pwd', database='test', ) cursor = connection.cursor() print("Test standard SQL ...") sql_cmd_1 = "update sequence set current_value = current_value + increment where seq_name = 'genid'" #sql_cmd_2 = "select current_value from sequence where seq_name = 'genid'" start_time = time.time() for i in range(1000): cursor.execute(sql_cmd_1) #cursor.execute(sql_cmd_2) connection.commit() end_time = time.time() elapsed_time = end_time - start_time print(f"standar SQL total time : {elapsed_time:.6f} seconds \n") cursor.close() connection.close()