4

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

  • I've exactly the same issue. Did you find the solution? Rolandos reccomendations did nothing performance wise in my case. – mhughes Jan 18 '16 at 16:26
6

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?

| improve this answer | |
  • (5.6.6 changed the Windows default to 0 for flush_time.) – Rick James Nov 3 '15 at 18:55
  • Slightly risky, but: innodb_doublewrite = OFF. – Rick James Nov 3 '15 at 18:56
  • All suggestions reduced process time 4 times! thank you! – uli Nov 17 '18 at 1:47
  • Google brought me here. I also recommend to double-check the innodb_buffer_pool_size setting - in my installation it was set to 8M, a value way too low. Setting it to 1G remedied my MySQL performance issue. – Abdull Jun 24 '19 at 11:30
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.