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.

Optimizing MySQL parameters

Discussion in 'Workarounds and Optimization' started by Shalabh, Aug 4, 2013.

  1. Shalabh

    Shalabh Registered

    Joined:
    Jul 30, 2013
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    I have purchased a VPS with 2.43GHz CPU, 2GB RAM, 80GB Disk Space with a band width of 1500 GB per month. MySQL version is version: 5.1.70-cll.

    I want to know whether my current MySQL parameters good enough for my website. I have developed an online marketplace for translators and their clients. Translators and clients create their profiles on website. Clients post translation jobs and translators bid on these jobs. Recently, while creating demo profiles for testing, some of MySQL tables got crashed which I got repaired from my hosting company. My hosting company also has asked me to optimize the MySQL parameters to avoid table crash.

    current my.cnf file

    [mysqld]
    Port = *port number
    socket = /var/lib/mysql/mysql.sock
    skip-locking
    key_buffer_size = 384M
    max_allowed_packet = 1M
    table_open_cache = 512
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size = 32M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8

    1. Please suggest what parameters should I change/add to my.cnf file
    2. Will it be enough to avoid table crash in future?
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Add this for automatic repair, in case of MyISAM table crash

    myisam_sort_buffer_size = 256M
    myisam_recover = BACKUP,FORCE

    and lower those:
    sort_buffer_size = 256K
    read_buffer_size = 128K
    read_rnd_buffer_size = 2M


    thread_concurrency = 8 - this can be removed, no use

    and add slow log

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1

    and few basics:
    max_connections = 100
    wait_timeout = 60
    connect_timeout = 2
    myisam_use_mmap=1

    if you dont use innodb database engine, put also after [mysqld]
    skip-innodb

    this will save you some RAM
     
  3. Shalabh

    Shalabh Registered

    Joined:
    Jul 30, 2013
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Can I add table_cache (> 64)? Is it of any use?
     
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    its the same as table_open_cache, just different name of variable for compatibility
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  6. Shalabh

    Shalabh Registered

    Joined:
    Jul 30, 2013
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    After optimizing MySQL parameters, my CPU usage is showing between 6.5%-8%, earlier it was only .03%.
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    where do you read that ?
    do you have munin plugin installed in WHM ? can you post your load/cpu/memory/mysql/apache graphs ?
     
Loading...

Share This Page