How to Set up MySQL Server to utilize more memory
- by Cyril Gupta
Hi there,
I have MySQL setup on Windows along with Plesk. The version is 5.0.45 Community.
The databases I have on the server are MyISAM as well as InnoDb, but predominantly innodb.
I had 8G memory on my server, but MySQL isn't going up more than 1.3G and tweaking the settings isn't helping. I tried to increase the memory allocation for innodb_buffer_pool_size, it works if I set it up to 1G, but if I set 2G, or above the server doesn't come back online!
I want mySQL to use at least 5-6 Gigs of the memory I have for performance, but I can't get this to work.
Can anyone please help? My mysql config file is below (there are 2 mysqld sections... when i used MySQL workbench it created another one!)
[MySQLD]
port=3306
basedir=C:\\Program Files (x86)\\Parallels\\Plesk\\Databases\\MySQL
datadir=C:\\Program Files (x86)\\Parallels\\Plesk\\Databases\\MySQL\\Data
default-character-set=latin1
default-storage-engine=INNODB
query_cache_size=128M
table_cache=1024
tmp_table_size=32M
thread_cache=32
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=2M
key_buffer_size=32M
read_buffer_size=16M
read_rnd_buffer_size=2M
sort_buffer_size=8M
innodb_additional_mem_pool_size=24M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=10M
innodb_buffer_pool_size=1G
innodb_log_file_size=10M
innodb_thread_concurrency=8
max_connections=700
key_buffer=48M
max_allowed_packet=5M
sort_buffer=2M
net_buffer_length=4K
old_passwords=1
wait_timeout=20
connect_timeout=60
[client]
port=3306
[mysqld]
query_cache_min_res_unit = 4096
innodb_additional_mem_pool_size = 1048576
innodb_buffer_pool_size = 1G
query_cache_limit = 1048576
key_buffer_size = 8388608
sort_buffer_size = 2097144
query_cache_type = 1
query_cache_size = 312M
log-slow-queries
connect_timeout = 5
wait_timeout = 20
thread_cache_size = 15
read_buffer_size = 131072
table_cache = 64
