MySQL - Slow InnoDB Write Speeds on Windows 10

Question

I have Windows 10 bootcamped. I've installed the latest version of MySQL community server. I've also installed WordPress onto IIS. The MacBook has 16Gb of memory. Here is my config file

[client]
no-beep

# pipe
# socket=0.0
port=3306

[mysql]

default-character-set=utf8

[mysqld]
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 5.6/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="DESKTOP-F1OO1LC.log"
slow-query-log=1
slow_query_log_file="DESKTOP-F1OO1LC-slow.log"
long_query_time=10
log-error="DESKTOP-F1OO1LC.err"
server-id=1
max_connections=151
query_cache_size=0
table_open_cache=2000
tmp_table_size=90M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=171M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_additional_mem_pool_size=13M
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size=7M
innodb_buffer_pool_size = 4G
innodb_log_file_size=48M
innodb_thread_concurrency=17
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
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=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

Is there anything obvious I'm doing wrong or do you need more information? I've tried following various guides but getting lost with all the options avilable.

Currently looking at a maximum of 20 writes per second!

Thanks

Answer 1

RECOMMENDATION #1

You have innodb_thread_concurrency at 17. It really needs to be zero(0). Why ?

Rather than rehash stuff I wrote in the past, please read the 7 posts I wrote about innodb_thread_concurrency being the best choice at 0.

RECOMMENDATION #2

Using a small log buffer can be a bottleneck when writing to the redo logs.

Please read my 3-yr-old post MySQL transaction size - how big is too big? why it should be bigger

I recommend innodb_log_buffer_size being set to 128M

RECOMMENDATION #3

The redo log needs to larger as well. You have

You have innodb_log_file_size at the default size for MySQL 5.6 : 48M

I recommend setting innodb_log_file_size = 1G.

RECOMMENDATION #4

You have flush_time set to 0. IMHO For any Windows OS, that's a big NO-NO.

The default is 1800 for Windows. I don't trust MySQL flushing to disk every 30 min in Windows.

I recommend flush_time to 300 (every five(5) minutes)

RECOMMENDATION #5

You should increase the IO threads

The defaults are

innodb_read_io_threads = 4
innodb_write_io_threads = 4

Percona Server's defaults are

innodb_read_io_threads = 8
innodb_write_io_threads = 8

You should start with 8 for both innodb_read_io_threads and innodb_write_io_threads.

GIVE IT A TRY !!!

I have so much more to recommend in terms of per-connection buffers : How costly is opening and closing of a DB connection?

Answer 2

Gather up multiple queries into a single BEGIN ... COMMIT transaction. This will cut down on the overhead and disk hits.

Don't do this blindly -- a million queries in a single transaction has other problems. And error checks need to be handled differently; think ROLLBACK.