GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

good mysql config

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • good mysql config

    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:
    http://pastebin.com/fe72a2a2

    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

    Comment

    Working...
    X