my.ini optimization on Windows 2008 R2 VPS

Posted by MKphpDev on Server Fault See other posts from Server Fault or by MKphpDev
Published on 2012-10-07T19:33:09Z Indexed on 2012/10/07 21:40 UTC
Read the original article Hit count: 154

I have a vmware VPS running Windows Server 2008 R2 Enterprise that has performance issues with MySQL. Every few minutes, MySQL stall for few seconds then responed to queries. I'm sure that my.ini need to be optimized, but unfortunately, I don't have any idea of my.ini configuration.

What's running on the server:

2 small wordpress blogs, 1 vbulletin forums (approx. 1.2 GB database, and increasing), small database for some sort of plug-ins (no more than 4000 records)

Server Info:

Processor: Intel Xeon X5550 @ 2.67GHz, RAM: 6 GB (memory useage never exceeded 2 GB), MySQL 5.5, PHP 5.3.10, IIS 7

current my.ini:

[mysqld]
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE _USER,NO_ENGINE_SUBSTITUTION"
max_connections=250
myisam_max_sort_file_size=20G
innodb_additional_mem_pool_size=256M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=8M
innodb_buffer_pool_size=512MB
innodb_log_file_size=128M
innodb_thread_concurrency=10
key_buffer_size = 512M
myisam_sort_buffer_size = 8M
join_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 256K
table_cache = 4000
thread_cache_size = 200
wait_timeout = 30
connect_timeout = 10
tmp_table_size = 32M
max_allowed_packet = 1M
max_connect_errors = 10000
query_cache_size = 16M
query_cache_limit = 2M
query_cache_type = 1
query_cache_min_res_unit = 1024
query_prealloc_size = 16384
query_alloc_block_size = 16384
skip-external-locking
read_rnd_buffer_size=1M
max_heap_table_size=16M
thread_concurrency=8

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

any help with that, please?

© Server Fault or respective owner

Related posts about mysql

Related posts about windows-server-2008-r2