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.

Dedicated server MySQL Optimization (mysqltuner.pl results)

Discussion in 'Workarounds and Optimization' started by ES - George, Jun 27, 2012.

  1. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Hi,

    I've got a dedicated server with 8GB RAM and an Xeon X3440 w/ HT (8 CPUs). I've done a few things to optimize MySQL. Here's the results of the tuning script.


    I can see that the MySQL hasn't been up for the recommended 24 hours yet, however the results will be very similar if not the same when 24 hours has passed.

    Here's my my.cnf file.
    Any suggestions to improve the configuration are appreciated.

    Thank you.
     
  2. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Code:
    query_cache_size=96M
    tmp_table_size = 32M
    max_heap_table_size = 32M
    
    --

    Add This line
    Code:
    key_buffer_size = 160M
    
    --

    rename table_cache to
    table_open_cache

    and change it to
    Code:
    table_open_cache 4096
    
    I would imagine it's at 54K because of accessing phpmyadmin and doing changes there so 4096 should be plenty.

    --

    This seems strange because you didn't post this in your mysql config
    [OK] InnoDB data size / buffer pool: 6.5M/128.0M

    128M is not needed here you only have 6.5M
    Change or add this line.
    Code:
    innodb_buffer_pool_size=8M
    
    ---

    [!!] Temporary tables created on disk: 36% (7K on disk / 20K total)

    This line may be unimportant because you may have many blog or text fields. However if you want you can try to make these higher
    Code:
    tmp_table_size = 32M
    max_heap_table_size = 32M
    
    they both should be the same value. I would go up to 64M if you don't see an improvement in the 36% (lower than 36%) than go back to 32M

    Also update mysql tuner to the lastest 1.2 version

    re-run a new report after 24 hours.
     
    #2 srpurdy, Jun 28, 2012
    Last edited: Jun 28, 2012
  3. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Thanks!

    How do I update the mysqltuner?
     
  4. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You can get the latest at
    http://mysqltuner.pl/mysqltuner.pl

    simply do a wget and put it somewhere safe. than you can run it directly from that folder.

    You would run it with a command like below.
    Code:
    ./folder/full_path_to_script/mysqltuner.pl 
    
    also make sure that the file is owned by root and can be executed.
     
Loading...

Share This Page