Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

MySQL Optimisation , its hangs , very very slow performance

Discussion in 'Workarounds and Optimization' started by cnsweb, Jan 5, 2014.

  1. cnsweb

    cnsweb Registered

    Joined:
    Jan 5, 2014
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    My server is of 24GB Ram , 8 Core Processor

    MY.CNF details please suggest best optimisation for this


    Code:
    [mysqld]
    innodb_file_per_table=1
    bind-address=127.0.0.1
    
    
    open_files_limit=12424
    [mysqld]
    local-infile=0
    datadir=/var/lib/mysql
    skip-locking
    #skip-innodb
    #skip-networking
    skip-name-resolve
    skip-host-cache
    safe-show-database
    innodb_buffer_pool_size = 1G
    #innodb_flush_method= O_DIRECT
    innodb_additional_mem_pool_size= 1G
    innodb_log_file_size= 5M
    innodb_thread_concurrency= 8
    innodb_flush_log_at_trx_commit= 0
    innodb_log_buffer_size= 1M
    innodb_file_per_table =1
    query_cache_limit=1G
    query_cache_size=1024M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_user_connections=2000
    max_connections=3000
    interactive_timeout=360
    wait_timeout=360
    connect_timeout=360
    thread_cache_size=128
    table_cache=2000
    key_buffer=3072M ## 128MB for every 1GB of RAM
    join_buffer=1G
    max_connect_errors=360
    max_allowed_packet=1G
    
    table_cache=4096
    record_buffer=1G
    
    sort_buffer_size=64M ## 1MB for every 1GB of RAM
    read_buffer_size=64M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=128M  ## 1MB for every 1GB of RAM
    thread_concurrency=8 ## Number of CPUs x 2
    myisam_sort_buffer_size=64M
    server-id=1
    collation-server=latin1_general_ci
    socket=/var/lib/mysql/mysql.sock
    [mysql.server]
    user=mysql
     
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
     
    [mysqldump]
    quick
    max_allowed_packet=1G
     
    [mysql]
    no-auto-rehash
    #safe-updates
     
    [isamchk]
    key_buffer=5G
    sort_buffer=5G
    read_buffer=5G
    write_buffer=5G
     
    [myisamchk]
    key_buffer=5G
    sort_buffer=5G
    read_buffer=5G
    write_buffer=5G
     
    [mysqlhotcopy]
    interactive-timeout
     
  2. cPanelPeter

    cPanelPeter Technical Analyst III
    Staff Member

    Joined:
    Sep 23, 2013
    Messages:
    575
    Likes Received:
    20
    Trophy Points:
    143
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello,

    Have you tried running mysqltuner.pl
    yet?

    If not, you should run that first and it will make the appropriate recommendations for you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice