Mysql – How to Set up MySQL Server to utilize more memory

MySQL

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

Best Answer

It's not easy, though. MySQL has many variables that affect memory utilization. A bunch of those are storage-engine dependent, too. Some of the variables are global, some are thread-specific so you will have to multiply them by the number of concurrent connections.

This is a script for calculate Memory utilization given a parameter list : 

#!/usr/bin/env - perl

$K=1024;
$M=$K*$K;

 $num_connections = 20;

$thread_stack = 64*$K;
$read_rnd_buffer_size = 2*$M;
$max_allowed_packet = 1*$M;
$tmp_table_size = 2*$M;
# Set low for non-myisam config, but not lower than 4M since temp tables need it, too.
$myisam_sort_buffer_size=4 *$M;
$read_buffer_size = 2 *$M;
$join_buffer_size = 8 *$M;

$query_cache_size = 128 * $M;
# Set low for non-myisam config
$key_buffer_size = 4 * $M;
$innodb_buffer_pool_size = 64 *$M;
$innodb_additional_mem_pool_size = 2 *$M;
$innodb_log_buffer_size = 4 * $M;

#=============== CALCULATIONS. DO NOT MODIFY

$per_thread_ram = $thread_stack
+ $read_rnd_buffer_size
+ $max_allowed_packet
+ $tmp_table_size
+ $myisam_sort_buffer_size
+ $read_buffer_size
+ $join_buffer_size;

$total_ram = $per_thread_ram * $num_connections
+ $query_cache_size
+ $key_buffer_size
+ $innodb_buffer_pool_size
+ $innodb_additional_mem_pool_size
+ $innodb_log_buffer_size;

print "Total RAM consumed: ".$total_ram/$M." MB\n";

More info at this link.

Related Topic