good mysql config

MySQL & MariaDB Other MySQL® Questions
1

Hello

i looking for the best mysql config for my apache-mysql servers.
Im currently using these settings:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /mysqltmp
language = /usr/share/mysql/english
skip-external-locking
old_passwords = 1
max_connections = 200
max_user_connections = 10000
max_connect_errors = 99999999
wait_timeout = 60
interactive_timeout = 300
skip-name-resolve
skip-innodb_checksums

* Fine Tuning

max_allowed_packet = 16M
thread_stack = 200K
thread_cache_size = 8
join_buffer_size = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
sort_buffer_size = 2M
myisam_sort_buffer_size = 128M
key_buffer = 384M
myisam-recover = BACKUP
#thread_concurrency = 10
query_cache_limit = 1M
query_cache_size = 32M
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
#Replication
report-host = dw2
server-id = 51
master-host = db1-priv
master-user = XXX
master-password = XXX
master-connect-retry = 30
replicate-do-db = replica-db
relay-log = /var/run/mysqld/mysqld-relay-bin
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 512M
sync_binlog = 0
expire_logs_days = 10
max_binlog_size = 100M

* InnoDB

innodb_buffer_pool_size = 2G
innodb_flush_log_at_trx_commit = 0
innodb_thread_concurrency = 8
innodb_file_per_table = 1
innodb_status_file = 0
innodb_io_capacity = 1000
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_flush_method = O_DIRECT
innodb_adaptive_checkpoint = 1
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M

This server is replicate a database. The db mixed myisam and innodb and one memory table.

HW:
HP DL360G5 / 1processor (Intel(R) Xeon(R) CPU E5310 @ 1.60GHz) with 8GB ram,
2x146Gb SAS HD in raid1+0, with bbwc. Debian linux @64bit /lenny.
mysqld Ver 5.1.34-xtradb5.
innodb status and show status:
[URL]http://pastebin.com/fe72a2a2[/URL]

Can u help me for best performance?

2

Hi there,

Its a kind of hard to tell you if anything wrong as long as we don’t know what you are using your mysql for.

Is there any problem you facing with? Slow queries, too high load or anything at all?

Your config looks good. I think you can turn off log-queries-not-using-indexes as long as you are using memory tables.

Max connections looks a little bit low, but depends on your app.

You don’t need thread-stack to configure from your config except if you really really know why is that.
Leave it to mysql to find the right value. Rearly wrong and in your environment, it won’t be wrong.

Istvan