MySQL configuration suggestion

[VJ29]

Hi All,

I wonder, if you have a suggestion in terms of MySQL Configuration for my Server because it is consuming a lot of memory; I am running Magento Community 1.8 in a Server with 57 GB RAM memory; sometimes it takes 54.5 GB from Total Memory and it makes that each time we have to restart the mysql service:

This is my database file configuration:

user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
skip-name-resolve

## For Slave DB Replication
server-id=1
#master-host=
#master-user=slave_user
#master-password=secret
#master-connect-retry=60
#replicate-do-db=databases

innodb_thread_concurrency=24
innodb_flush_log_at_trx_commit=2
thread_concurrency=24
thread_cache_size=128
table_cache=1024
query_cache_size=64M
query_cache_limit=20M
join_buffer_size=8M
tmp_table_size=512M
key_buffer_size=64M
innodb_autoextend_increment=512
max_allowed_packet=256M
max_heap_table_size=512M
read_buffer_size=2M
read_rnd_buffer_size=20M
bulk_insert_buffer_size=64M
myisam_sort_buffer_size=128M
myisam_max_sort_file_size=5G
#myisam_max_extra_sort_file_size=10G
myisam_repair_threads=1
net_read_timeout=120
wait_timeout = 200
innodb_buffer_pool_size=21G
query_cache_type=1
table_open_cache=1512
table_definition_cache=1024
max_user_connections=0
max_connections=10000
innodb_buffer_pool_instances=8
#_
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# LOGGING #
log-error = /data/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 20
slow-query-log-file = /data/mysql/mysql-slow.log

It is  important to mention that I have a dedicated server for Database; Could you give some advices in order to get better my perfomance configuration please?

Thanks in advanced!

Regards,

JV

[JLHC]

It looks like the innodb_buffer_pool_size is quite a lot at 21GB and that will eat up a lot of your RAM. 

Is there any reason this is set to such a high number? 

[Mukesh Tiwari]

@VJ29 Are you using dedicated database server or combined webserver with database server?

[MagenX]

innodb_buffer_pool_size=21G
max_connections=10000
innodb_buffer_pool_instances=8

a bit wrong, 

innodb_buffer_pool_size = {data size + 50%} # run mysqltuner.pl to find out
max_connections = {real simultaneous connections + 50%} # default = 150
innodb_buffer_pool_instances = {innodb_buffer_pool_size} # N per Gb

[Stuart M]

If this is a dedicated database server and not running anything else other than the operating system then 54.5GB out of 57GB is good as you are making sure your DB uses all available memory for best performance.

21GB for the buffer pool may be excessive however you will need to tell us your total database size for all databases on this server in order to confirm that. As per the last reply [Datasize + Overhead] is a good calculation for this.