The Community Forums

Interact with an entire community of cPanel & WHM users.
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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:
    569
    Likes Received:
    15
    Trophy Points:
    18
    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.
     
Loading...

Share This Page