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.

Optimization of my.cnf

Discussion in 'Workarounds and Optimization' started by igordr, Jul 13, 2012.

  1. igordr

    igordr Member

    Joined:
    Jan 7, 2012
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hello,

    I have problems with mySQL. In some of my sites, i get error at the top of the page:

    Code:
    Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in 
    Warning: session_start() [function.session-start]: open(/tmp/sess_2768563f6f13c08c044c710c554aaa37, O_RDWR) failed: No space left on device (28) in /home/stranifi/public_html/musicvideo.php on line 17
    
    Warning: session_start() [function.session-start]: Cannot send session cookie - headers already sent by (output started at /home/stranifi/public_html/musicvideo.php:17) in /home/stranifi/public_html/musicvideo.php on line 17
    
    Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /home/stranifi/public_html/musicvideo.php:17) in /home/stranifi/public_html/musicvideo.php on line 17
    Mentioned problems appeared suddenly. I didn't do anything.
    My server configuration is:

    HDD 2x 500 GB SATARAM
    6 GB DDR2-RAM
    CPU AMD Athlon 64 X2 3400+, Dual-Core
    Template CentOS 6 - minimal - RAID 1

    I have about 15k unique visitors every day.

    My present my.cnf file looks like this:

    Code:
    [mysqld]
    max_connections=500
    join_buffer_size = 2M
    tmp_table_size = 24M
    max_heap_table_size = 24M
    query_cache_size = 512M
    query_cache_limit = 512M
    key_buffer=256M
    key_buffer_size = 1332M
    thread_cache_size = 16K
    table_cache = 1K
    table_definition_cache = 4K
    open_files_limit = 3K
    table_open_cache = 96
    innodb_buffer_pool_size = 27M
    long_query_time = 5
    slow_query_log
    local-infile=0
    What i should tweak to get error disappear and get mySQL to work again?

    THANKS,
    Igor
     
  2. igordr

    igordr Member

    Joined:
    Jan 7, 2012
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    MySQL tuner results:


    Code:
     >>  MySQLTuner 1.2.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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.63-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 171M (Tables: 751)
    [--] Data in InnoDB tables: 208K (Tables: 13)
    [!!] Total fragmented tables: 51
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 5h 2m 31s (4M q [38.327 qps], 119K conn, TX: 7B, RX: 335M)
    [--] Reads / Writes: 96% / 4%
    [--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 449.2M (8% of installed RAM)
    [OK] Slow queries: 0% (2K/4M)
    [!!] Highest connection usage: 100%  (152/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/51.9M
    [OK] Key buffer hit rate: 99.5% (386M cached / 1M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 2% (14K temp sorts / 497K sorts)
    [!!] Joins performed without indexes: 1156
    [!!] Temporary tables created on disk: 27% (73K on disk / 268K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 215K opened)
    [OK] Open file limit used: 12% (124/1K)
    [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
    [OK] InnoDB data size / buffer pool: 208.0K/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Reduce or eliminate persistent connections to reduce connection usage
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        max_connections (> 151)
        wait_timeout (< 28800)
        interactive_timeout (< 28800)
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 64)
     
  3. voshka

    voshka Active Member

    Joined:
    Apr 4, 2010
    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    6
    First optimize the databases by running this command
    mysqlcheck -u root -r -e --auto-repair --check --optimize --all-databases

    Then review through the recommendation

    Query cache is disabled so enable it
    Highest connection usage: 100% (152/151) so make the connection higher than this amount
     
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    MySQL optimization will be unable to fix MySQL errors occurring on a site, since the error referred to isn't in reference to MySQL failing to run. If MySQL were overloaded and that were causing site errors, that could be helped by optimization. The first error reported would be a different cause entirely.

    Here's a discussion on the error you reported:

    php - mysql_num_rows() expects parameter 1 to be resource, boolean given in - Stack Overflow

    Did you make changes to the code on the site recently or upgrade MySQL? What are the sites running that are showing the error? If it's a specific application, it might be an issue with the MySQL version and the application or with the application itself.
     
  5. igordr

    igordr Member

    Joined:
    Jan 7, 2012
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    My.Cnf now looks like:

    Code:
    [mysqld]
    set-variable = max_connections=500
    query_cache_limit       = 2M
    query_cache_size        = 256M
    table_cache = 400
    thread_cache_size = 286
    log-slow-queries
    safe-show-database
    
    Code:
    >>  MySQLTuner 1.2.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
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.63-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 168M (Tables: 715)
    [!!] Total fragmented tables: 3
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 50m 16s (291K q [44.134 qps], 9K conn, TX: 531M, RX: 25M)
    [--] Reads / Writes: 90% / 10%
    [--] Total buffers: 280.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.6G (29% of installed RAM)
    [OK] Slow queries: 0% (27/291K)
    [OK] Highest usage of available connections: 3% (15/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/51.8M
    [OK] Key buffer hit rate: 99.5% (20M cached / 97K reads)
    [OK] Query cache efficiency: 53.0% (119K cached / 225K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (10 temp sorts / 7K sorts)
    [!!] Joins performed without indexes: 115
    [OK] Temporary tables created on disk: 22% (1K on disk / 7K total)
    [OK] Thread cache hit rate: 99% (15 created / 9K connections)
    [!!] Table cache hit rate: 11% (196 open / 1K opened)
    [OK] Open file limit used: 14% (370/2K)
    [OK] Table locks acquired immediately: 99% (123K immediate / 123K locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        join_buffer_size (> 128.0K, or always use indexes with joins)
        table_cache (> 400)
    May you help me to optimize my.cnf according to my server overview ?

    Thanks
     
  6. Webinx

    Webinx Member

    Joined:
    Sep 20, 2012
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
  7. albatroz

    albatroz Well-Known Member

    Joined:
    Mar 6, 2003
    Messages:
    258
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Virtual Orbis / Peru
    cPanel Access Level:
    Root Administrator
    Hi!,
    What do you think of the mysqltuner.pl results of my server?
    BTW,
    How do I get the MySQL root password for making a database optimization?

    HTML:
    root@pi [/etc]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    
     >>  MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.63-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 306M (Tables: 1995)
    [--] Data in InnoDB tables: 733M (Tables: 1380)
    [--] Data in MEMORY tables: 0B (Tables: 3)
    [!!] Total fragmented tables: 1410
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 7s (157 q [22.429 qps], 14 conn, TX: 466K, RX: 26K)
    [--] Reads / Writes: 94% / 6%
    [--] Total buffers: 42.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (47% of installed RAM)
    [OK] Slow queries: 0% (0/157)
    [OK] Highest usage of available connections: 0% (2/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/63.5M
    [OK] Key buffer hit rate: 98.9% (11K cached / 130 reads)
    [!!] Query cache efficiency: 1.6% (2 cached / 127 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 23 sorts)
    [!!] Temporary tables created on disk: 46% (13 on disk / 28 total)
    [!!] Thread cache is disabled
    [OK] Table cache hit rate: 81% (30 open / 37 opened)
    [OK] Open file limit used: 1% (34/2K)
    [OK] Table locks acquired immediately: 100% (245 immediate / 245 locks)
    [!!] InnoDB data size / buffer pool: 733.8M/8.0M
    
    -------- 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
        Set thread_cache_size to 4 as a starting value
    Variables to adjust:
        query_cache_limit (> 4M, or use smaller result sets)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        innodb_buffer_pool_size (>= 733M)
     
  8. pwhjenny

    pwhjenny Well-Known Member

    Joined:
    Aug 31, 2012
    Messages:
    135
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    You need to check size of the /tmp partition. It appears to be full.
     
Loading...

Share This Page