MySQL Forums :: Performance :: Configuration review requested


Advanced Search

Configuration review requested
Posted by: Jens Meier ()
Date: April 13, 2016 11:55AM

Hi all,

I have been assigned to move our mysql database from our small server (called old server) to our new bigger server (called new server).
The old server has 24 GB RAM, 4 Cores and the MySQL data is stored on a single ssd (without raid).

We wanted to get more security, so we bought a new server with this specifications:

4x 256GB Samsung 850 Pro SSD
1x LSI/3ware 9750-4i SAS/SATA 4-Port HW-Raidcontroller (Storsave set to performance and WriteCache enabled)
1x Intel Xeon E5-26030v4 CPU
1x Supermicro X10DRW-i Mainboard
4x 16 GB Samsung DDR4-2133 ECC-REG
RAID 10


Both servers are running with Windows 2012 R2 and the MySQL database runs on Hyper-V-Guest (Windows 2012 R2 too and Remotedesktop). The VM is not dedicated to the database. The little .NET erp application is running on the VM too. There is nothing else on the host and the VM, not antivirus, no office, just nothing.

On the old server, everything what we do in the erp app is incredibly fast (there are 30 databases). On the new server the erp app isn't fast as it is on the old server. It's much slower. The windows system (like windows explorer) on the new server (host and VM) is incredibly fast too, but not the erp app.


I tried everything to find out why the erp app is much slower on the new server:

- Turned on the write cache in the raid controller settings
- Set Storsave-strategy to "Performance"
- Disabled IPV6 in the vm and set skip-name-resolve=1
- Disabled NUMA in the Hyper-V-Config
- Installed new Raid Drivers
- Virtual Harddisks are fixed (not dynamic)
- The new server has 40 GB RAM
- The VM on the new server has 6 CPU-Cores


Copying large files (2G) from on folder to another is very fast (2 - 4 seconds).

Could you please take a look on my settings and tell me what I can do better? I have no more ideas what I can do.


[client]
no-beep

# pipe
# socket=mysql
port=3306


[mysql]
default-character-set=utf8

[mysqld]
#skip-name-resolve=1
bind-address=localhost
wait_timeout=86400
port=3306
datadir="D:/MySQL/data\"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="TERM1.log"
slow-query-log=1
slow_query_log_file="TERM1-slow.log"
long_query_time=1
expire_logs_days=14
log-error="TERM1.err"
max_connections=1000
query_cache_size=0
table_open_cache=2000
tmp_table_size=125M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=250M
key_buffer_size=10M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=18M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=16M
innodb_buffer_pool_size=8G
innodb_log_file_size=1G
innodb_thread_concurrency=6
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=600
innodb_stats_on_metadata=1
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
query_cache_type=0
sort_buffer_size=4M
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
innodb_log_files_in_group=4
innodb_read_io_threads=16
innodb_write_io_threads=16
performance_schema=off


Thank you very much and sorry for my bad English, I'm from Germany.


Best regards

Jens

Options: ReplyQuote


Subject Views Written By Posted
Configuration review requested 942 Jens Meier 04/13/2016 11:55AM
Re: Configuration review requested 371 Rick James 04/14/2016 01:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.