Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
    
Do you install 64-bit MySQL? –  alvosu Feb 5 '11 at 7:24
    
It's a 64 bit OS, so I guess, Yes. –  Cyril Gupta Feb 5 '11 at 9:52

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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