I have a problem which MYSQL performance insert on difference machine same ram and cpu.
I'm run MYSQL on VM Window 2k3 4GB of Ram, 4 CPUs build on VMWare Workstaion 12 run on my PC vs VM Window 2k3 4GB of Ram, 4 CPUs build on ESXi 6.5 on Server. Of course all hardware of Server is better than my PC.
MYSQL on my PC executes 3000 queries insert in 1.5s but on Server executes 3000 queries insert in 48s. what is the problem?
2 VM have same my.ini config:
[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"
character-set-server=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=500
query_cache_size=0
table_cache=256
tmp_table_size=18M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=35M
key_buffer_size=25M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=47M
innodb_log_file_size=24M
innodb_thread_concurrency=18
When i config innodb_flush_log_at_trx_commit to 0 or 2 Server run very fast in 0.5s but i want to know what is the difference when innodb_flush_log_at_trx_commit is 1 on my PC executes in 1.5s but Server executs in 48s?
3000/48 is about a spinning drive's speed (Rule of Thumb: 10ms per I/O). So that sounds like innodb_flush_log_at_trx_commit=1.
3000/1.5 sounds better than even SSD, so I am guessing there is a RAID controller with Battery Backed Write cache, which would make most writes almost instantaneous. And having the set of 1 would be no problem. Or perhaps the drive has a write cache that is enabled.