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 Optimization Request - my.cnf Tuning for Quad Core Processor - 6GB Ram

Discussion in 'Workarounds and Optimization' started by mdp, Mar 21, 2014.

  1. mdp

    mdp Member

    Joined:
    Mar 21, 2014
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Good Day!

    Looking forward to getting guidance on optimizing my mysql server to keep the load down and function efficiently.

    Below posted is the information that would help base suggestions. Thanks for your time in advance.

    Server Details:

    Code:
    Dual Xeon 5620 Quad core Processors
    6 GB RAM
    500 GB SATA x 2
    RAID 1 Controller
    Redhat Enterprise 5.1
    Code:
    my.cnf settings
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    group_concat_max_len=10000000
    key_buffer_size = 512M
    thread_cache_size = 25
    query_cache_size = 64M
    query_cache_limit = 2M
    #tmp_table_size = 24M
    #max_heap_table_size = 24M
    skip-innodb
    default-storage-engine = myisam
    table_open_cache  = 500
    #table_definition_cache = 20000
    #open_files_limit = 15000
    #low_priority_updates=1
    #concurrent_insert=ALWAYS
    log-slow-queries=/var/lib/mysql/slow.log

    Code:
    mysqltuner.pl output
    perl mysqltuner.pl
    
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    Please enter your MySQL administrative login: root
    Please enter your MySQL administrative password:
    [OK] Currently running supported MySQL version 5.5.36-log
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED -InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 12G (Tables: 59790)
    [!!] Total fragmented tables: 30
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 16h 16m 25s (1M q [20.720 qps], 78K conn, TX: 2B, RX: 247M)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 592.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 997.8M (16% of installed RAM)
    [OK] Slow queries: 0% (0/1M)
    [OK] Highest usage of available connections: 46% (70/151)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/1.5G
    [OK] Key buffer hit rate: 97.3% (56M cached / 1M reads)
    [!!] Query cache efficiency: 17.3% (148K cached / 855K selects)
    [!!] Query cache prunes per day: 1021830
    [OK] Sorts requiring temporary tables: 0% (197 temp sorts / 167K sorts)
    [!!] Temporary tables created on disk: 39% (135K on disk / 348K total)
    [OK] Thread cache hit rate: 99% (71 created / 78K connections)
    [!!] Table cache hit rate: 0% (500 open / 96K opened)
    [OK] Open file limit used: 85% (998/1K)
    [OK] Table locks acquired immediately: 100% (707K immediate / 707K locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        query_cache_limit (> 2M, or use smaller result sets)
        query_cache_size (> 64M)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        table_cache (> 500)

    "top" Command Output


    "free" command output

     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    16 hours should be sufficient, but ideally you want to let MySQL run for at least 24 hours to get the most accurate results from a tuner.

    Thank you.
     
  3. mdp

    mdp Member

    Joined:
    Mar 21, 2014
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thanks for the heads up. I will post back the results after about 8 hours :)
     
  4. mdp

    mdp Member

    Joined:
    Mar 21, 2014
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    As suggested, below is the latest output after almost 24 hours of mysql running. Request optimization tips please!

    Server Details

    Code:
    Dual Xeon 5620 Quad core Processors
    6 GB RAM
    500 GB SATA x 2
    RAID 1 Controller
    Redhat Enterprise 5.1
    my.conf settings

    Code:
    my.cnf settings
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    group_concat_max_len=10000000
    key_buffer_size = 512M
    thread_cache_size = 25
    query_cache_size = 64M
    query_cache_limit = 2M
    #tmp_table_size = 24M
    #max_heap_table_size = 24M
    skip-innodb
    default-storage-engine = myisam
    table_open_cache  = 500
    #table_definition_cache = 20000
    #open_files_limit = 15000
    #low_priority_updates=1
    #concurrent_insert=ALWAYS
    log-slow-queries=/var/lib/mysql/slow.log
    Code:
    perl mysqltuner.pl
    
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.36-log
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED -InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 15G (Tables: 70038)
    [!!] Total fragmented tables: 43
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 23h 36m 4s (1M q [20.884 qps], 120K conn, TX: 2B, RX: 358M)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 592.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 997.8M (16% of installed RAM)
    [OK] Slow queries: 0% (8/1M)
    [OK] Highest usage of available connections: 46% (70/151)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/2.2G
    [OK] Key buffer hit rate: 97.1% (79M cached / 2M reads)
    [!!] Query cache efficiency: 16.5% (203K cached / 1M selects)
    [!!] Query cache prunes per day: 1027183
    [OK] Sorts requiring temporary tables: 0% (267 temp sorts / 236K sorts)
    [!!] Temporary tables created on disk: 38% (198K on disk / 510K total)
    [OK] Thread cache hit rate: 99% (74 created / 120K connections)
    [!!] Table cache hit rate: 0% (500 open / 438K opened)
    [OK] Open file limit used: 85% (997/1K)
    [OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_cache over 64: [url=http://bit.ly/1mi7c4C]table_cache negative scalability - MySQL Performance Blog[/url]
    Variables to adjust:
        query_cache_limit (> 2M, or use smaller result sets)
        query_cache_size (> 64M)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        table_cache (> 500)
    

    "top" command output

    Code:
     top
    top - 05:54:18 up 603 days,  7:00,  1 user,  load average: 2.56, 3.21, 2.96
    Tasks: 256 total,   1 running, 255 sleeping,   0 stopped,   0 zombie
    Cpu(s):  0.3%us,  0.1%sy,  0.0%ni, 98.6%id,  1.1%wa,  0.0%hi,  0.0%si,  0.0%st
    Mem:   6220724k total,  5497128k used,   723596k free,   180300k buffers
    Swap:  9438176k total,       84k used,  9438092k free,  4141436k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    30382 mysql     15   0 1085m 655m 4516 S 193.4 10.8   1173:23 mysqld

    "free" command output

    Code:
    free
                 total       used       free     shared    buffers     cached
    Mem:       6220724    5644120     576604          0     193020    4274768
    -/+ buffers/cache:    1176332    5044392
    Swap:      9438176         84    9438092
    
     
  5. mdp

    mdp Member

    Joined:
    Mar 21, 2014
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    And here is a latest mysqltuner report

    Code:
    perl mysqltuner.pl
    
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/url]
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.36-log
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED -InnoDB +MRG_MYISAM
    
    
    
    [--] Data in MyISAM tables: 15G (Tables: 70038)
    [OK] Total fragmented tables: 0
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 17h 28m 51s (3M q [23.480 qps], 235K conn, TX: 4B, RX: 691M)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 592.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 997.8M (16% of installed RAM)
    [OK] Slow queries: 0% (17/3M)
    [OK] Highest usage of available connections: 46% (70/151)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/2.1G
    [OK] Key buffer hit rate: 95.9% (127M cached / 5M reads)
    [!!] Query cache efficiency: 14.9% (365K cached / 2M selects)
    [!!] Query cache prunes per day: 1198010
    [OK] Sorts requiring temporary tables: 0% (429 temp sorts / 461K sorts)
    [!!] Temporary tables created on disk: 38% (397K on disk / 1M total)
    [OK] Thread cache hit rate: 99% (101 created / 235K connections)
    [!!] Table cache hit rate: 0% (500 open / 874K opened)
    [!!] Open file limit used: 86% (1K/1K)
    [OK] Table locks acquired immediately: 100% (2M immediate / 2M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_cache over 64: /http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/ table_cache negative scalability - MySQL Performance Blog
    Variables to adjust:
        query_cache_limit (> 2M, or use smaller result sets)
        query_cache_size (> 64M)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        table_cache (> 500)
        open_files_limit (> 1161)
    
     
  6. mdp

    mdp Member

    Joined:
    Mar 21, 2014
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi, Can anyone help me out! Thanks.
     
  7. euro-space

    euro-space Member

    Joined:
    Mar 24, 2014
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Enabling large page support in memory, significantly helped me with the mysql optimization and dropped CPU usage from ~30% to ~10%-15%.

    You can read all memory related stuff at - MySQL :: MySQL 5.0 Reference Manual :: 14.4 The MEMORY (HEAP) Storage Engine and understand how it works here - MySQL :: MySQL 5.5 Reference Manual :: 8.11.4.1 How MySQL Uses Memory .
    After that you can follow these steps - MySQL :: MySQL 5.5 Reference Manual :: 8.11.4.2 Enabling Large Page Support

    Thanks to gelleby for that info at Stackoverflow.
     
  8. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Code:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    myisam_use_mmap=1
    
    group_concat_max_len=10000000
    key_buffer_size = 4G
    thread_cache_size = 50
    query_cache_size = 100M
    query_cache_limit = 2M
    tmp_table_size = 64M
    max_heap_table_size = 64M
    skip-innodb
    default-storage-engine = myisam
    table_open_cache  = 7500
    table_definition_cache = 5000
    open_files_limit = 25000
    #low_priority_updates=1
    #concurrent_insert=ALWAYS
    slow_query_log=1
    slow_query_log_file=/var/lib/mysql/mysql-slow.log
    long_query_time=0.1	
    
    key_buffer_size should be always kept higher than used MyiSAM Indexes size,
    [OK] Key buffer size / total MyISAM indexes: 512.0M/2.1G

    so if all MyISAM indexes are used, higher than 2.1G
     
  9. mdp

    mdp Member

    Joined:
    Mar 21, 2014
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    @euro-space - Thanks, will check it out. But I guess its for innodb, I am using MyIsam

    @thinkbot - Thanks for the inputs. I made the changes and after restarting the server, started getting the following errors:

    Error Number: 5 Out of memory (Needed 1245304 bytes) for many queries/page loads.
    Out of memory (Needed 16391 bytes)

    So I reverted back to the original my.cnf file, but increased the value of key_buffer_size = 4G. The current below is the my.cnf file

    I also recollect that earlier when I had made changes (increased the values of open_files_limit, table_open_cache, table_definition_cache, I got that same error and therefore you will notice that they have been commented.)

    Which variable could be the reason for out of memory error? -- open_files_limit, table_open_cache, table_definition_cache, tmp_table_size, query_cache_size ??
     
Loading...

Share This Page