MySQL 5.1.49 - Optimal settings for memory usage

[Marc Mertes]

Hello community!
I would like to get some feedback/tips around my MySQL system setup.
Here should be a lot of experienced user - also with huge Databases.
May I can get some good hints to use the best results for my hardware.

My system:
debian 6.0.3 x86_64 (squeeze)
64 GB RAM
2x 8 Core AMD Opteron 6134
HD: 1TB Raid 1 on 3ware 9650SE-8LPML

-----------------------------------------
my.cnf:
# generic configuration options
large-pages
port = 3306
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /data/mysql
tmpdir = /tmp
#tmpdir = /dev/shm
connect_timeout = 600
character-set-server = utf8
collation-server = utf8_bin
language = /usr/share/mysql/english
log-warnings = 3
back_log = 50

max_connections = 100
max_connect_errors = 10
table_open_cache = 16384
max_allowed_packet = 512M
binlog_cache_size = 128M
max_heap_table_size = 1024M
sort_buffer_size = 1024M
join_buffer_size = 8M
thread_cache_size = 64
thread_concurrency = 16
query_cache_size = 64M
query_cache_limit = 4M
ft_min_word_len = 4
default-storage-engine = INNODB
thread_stack = 256K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 4G
log-bin=mysql-bin
binlog_format=row
sync_binlog=1
log_slave_updates
log-error=mysql-bin.err
slow_query_log=mysql-slow.log
long_query_time = 10

#*** MyISAM Specific options
key_buffer_size = 512M
read_buffer_size = 8M
read_rnd_buffer_size = 1024M
bulk_insert_buffer_size = 128M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 4
myisam_recover

# *** INNODB Specific options ***
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_size = 15G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_per_table
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 80
innodb_lock_wait_timeout = 120

[mysqldump]
quick
quote-names
max_allowed_packet = 1024M

[myisamchk]
key_buffer_size = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M
------------------------------------------------
/etc/security/limits.conf
* - nofile -1
mysql - memlock 67108864
@mysql - memlock 67108864
root - memlock 67108864

------------------------------------------------
/etc/sysctl.d/mysql.conf
# Huge page tables reservation for mysql
vm.nr_hugepages = 13300
vm.hugetlb_shm_group = 107
kernel.shmmax = 27917287424
kernel.shmall = 6815744
------------------------------------------------

I´d read a lot of HowTos and threads, but I´m still a bit unsure how to use best the available memory.
The databases are all INNODB (at least the big ones), are around 50 GB and have round about 60 million entries (and growing fast).

I hope someone has some good hints - my goal is to keep as much as possible in memory, to reduce disk-io.
Thanks and regards, Marc



Edited 2 time(s). Last edit at 01/10/2012 05:32AM by Marc Mertes.