mysql performance tuning on 64 bit hardy

[WaVeR]

Hi Soon Siang,

Le lundi 14 septembre 2009 à 23:45 +0800, ahsiangsiang a écrit :

> what is the best my.cnf value to configure for best performance?
> 

In my case 8 db servers with 64GB of memory each, the config looks like
(I can't tell you it's the best but works fine for me):


[mysqld1]
socket          = /var/lib/mysql/mysql.1.sock
pid-file        = /var/lib/mysql/mysql.1.pid
datadir         = /xxx/xxx/databases
tmpdir          = /xxx/xxx/tmp
mysqld          = /usr/bin/mysqld_safe
bind-address    = 192.168.xx.xx
wait_timeout    = 30
interactive_timeout=30
skip-locking
skip-name-resolve
skip-log-bin
log-bin         = /xxx/xxx/binlogs/binlog
binlog-do-db    = mysql
expire_logs_days        = 10
max_binlog_size         = 100M
query_cache_size = 1024M
query_cache_limit = 20M
table_cache=4096
sort_buffer_size=128M
read_buffer_size=128M
key_buffer_size=512M
max_connections=3000
set-variable    = net_buffer_length=2M
myisam_sort_buffer_size=128M
set-variable    = max_allowed_packet=32M
set-variable    = thread_stack=128K
set-variable    = thread_cache=8
set-variable    = thread_cache_size=10
max_allowed_packet = 32M
innodb_flush_method=O_DIRECT
innodb_data_home_dir = /xxx/xxx/innodb
innodb_data_file_path = ibdata1:10M:autoextend:max:200G
innodb_log_group_home_dir = /xxx/xxx/iblogs
innodb_log_arch_dir = /xxx/xxx/iblogs.bak
set-variable = innodb_buffer_pool_size=25G
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_log_file_size=250M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit = 1


Regards,


---
Hassan