GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Percona Mysql my.cnf review

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

  • Percona Mysql my.cnf review



    Can you please review my config file for any suggestions.

    Quad core Xeon
    8GB of ram

    Main drive is 512 raid 1

    Mysql drive is ssd

    [mysqldump]
    quick
    [mysql]
    no-auto-rehash
    [myisamchk]
    key_buffer=256M
    key_buffer_size=32M
    write_buffer=2M
    read_buffer=2M
    sort_buffer_size=256M
    [client]
    socket="/mysql/mysql.sock"
    [mysqld]
    # INNODB #
    innodb_flush_method = O_DIRECT
    innodb_log_files_in_group = 2
    #innodb_log_file_size = 256M
    innodb_flush_log_at_trx_commit = 1
    innodb_file_per_table = 1
    innodb_buffer_pool_size = 4G

    wait_timeout = 600 seconds

    # CACHES AND LIMITS #
    tmp_table_size = 32M
    max_heap_table_size = 32M
    query_cache_type = 0
    query_cache_size = 32M
    max_connections = 100
    thread_cache_size = 50
    open_files_limit = 65535
    table_definition_cache = 4096
    table_open_cache = 10240

    # LOGGING #
    log_error = /mysql/mysql-error.log
    log_queries_not_using_indexes = 1
    slow_query_log = 1
    slow_query_log_file = /mysql/mysql-slow.log

    loose_handlersocket_port = 9998
    # the port number to bind to for read requests
    loose_handlersocket_port_wr = 9999
    # the port number to bind to for write requests
    loose_handlersocket_threads = 16
    # the number of worker threads for read requests
    loose_handlersocket_threads_wr = 1
    # the number of worker threads for write requests
    # to allow handlersocket to accept many concurrent
    # connections, make open_files_limit as large as
    # possible.

    #join_buffer_size=12M
    #thread_concurrency=4
    #query_cache_limit=256M
    #connect_timeout=1000
    #key_buffer=384M
    #table_cache=4096
    #max_user_connections=50
    #local-infile=0
    #server-id=14
    #wait_timeout=1000
    #read_rnd_buffer_size=16M
    #read_buffer_size=2M
    #myisam_sort_buffer_size=64M
    #max_allowed_packet=32M
    #socket="/mysql/mysql.sock"
    #skip-external-locking
    #sort_buffer_size=2M
    #datadir="/mysql"
    #[mysqlhotcopy]
    #interactive-timeout





  • #2
    Why this is commented out:
    #innodb_log_file_size = 256M

    If you use InnoDB tables, then using default size if far from being optimal. If you are on 5.5.x+ then you should use large innodb logs, like 512M+

    Also innodb_buffer_pool_size = 4G so 50% of you current total memory - whether it is OK depends on if the machine is MySQL dedicated and if you use MyISAM and how much data is distributed among InnoDB and MyISAM tables.

    Comment

    Working...
    X