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 Tips Requested

Discussion in 'Workarounds and Optimization' started by Nirjonadda, Apr 28, 2015.

  1. Nirjonadda

    Nirjonadda Well-Known Member

    Joined:
    May 8, 2013
    Messages:
    151
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Website Owner
    Please can you let me know, How to optimize my MySQL query to use less mysql connection? what is good parameter to set for optimize?

    This is my default my.cnf settings.

    Code:
    [mysqld]
    local-infile=0
    
    max_connections=800
    max_user_connections = 1000
    datadir="/var/lib/mysql"
    socket          = "/var/lib/mysql/mysql.sock"
    
    key_buffer = 256M
    table_cache = 28560
    open_files_limit=50000
    join_buffer_size = 2M
    read_buffer_size = 2M
    sort_buffer_size = 6M
    tmp_table_size=128M
    read_rnd_buffer_size=4M
    max_heap_table_size=64M
    myisam_sort_buffer_size=64M
    thread_cache_size=8
    thread_cache=512
    query_cache_limit=12M
    query_cache_size=256M
    thread_concurrency = 8
    wait_timeout=1000
    connect_timeout=1000
    interactive_timeout=1000
    #log-slow-queries = /var/log/mysqlslowqueries.log
    max_allowed_packet=268435456
    
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    [isamchk]
    key_buffer =64M
    sort_buffer_size = 64M
    read_buffer = 16M
    write_buffer = 16M
    
    [myisamchk]
    key_buffer = 64M
    sort_buffer_size = 64M
    read_buffer = 16M
    write_buffer = 16M
    
    [mysqlhotcopy]
    interactive-timeout
    [mysqld_safe]
    
    log-error="/var/log/mysqld.log"
    Code:
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    
    >>  MySQLTuner 1.4.0 mod - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
    >>  Run with '--help' for additional options and output filtering
    [OK] Logged in using credentials passed on the command line
    [OK] Currently running supported MySQL version 5.5.42-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 4G (Tables: 937)
    [--] Data in InnoDB tables: 637M (Tables: 42)
    [--] Data in MEMORY tables: 3M (Tables: 6)
    [!!] Total fragmented tables: 36
    
    -------- Security Recommendations  -------------------------------------------
    ERROR 1142 (42000) at line 1: SELECT command denied to user 'nadda'@'localhost' for table 'user'
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 8h 24m 57s (8M q [43.851 qps], 733K conn, TX: 50B, RX: 1B)
    [--] Reads / Writes: 78% / 22%
    [--] Total buffers: 720.0M global + 14.2M per thread (800 max threads)
    [!!] Maximum possible memory usage: 11.8G (154% of installed RAM)
    [OK] Slow queries: 0% (41/8M)
    [OK] Highest usage of available connections: 6% (50/800)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/2.9G
    [OK] Key buffer hit rate: 99.6% (643M cached / 2M reads)
    [OK] Query cache efficiency: 61.8% (3M cached / 5M selects)
    [OK] Query cache prunes per day: 86
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 189K sorts)
    [!!] Joins performed without indexes: 2355 (see join_buffer_size note below)
    [!!] Temporary tables created on disk: 62% (70K on disk / 112K total)
    [OK] Thread cache hit rate: 99% (50 created / 733K connections)
    [OK] Table cache hit rate: 37% (1K open / 5K opened)
    [OK] Open file limit used: 4% (2K/57K)
    [OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
    [!!] InnoDB  buffer pool / data size: 128.0M/637.3M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability.
        See notes on accuracy of this recommendation below
        Enable the slow query log to troubleshoot bad queries.
        Do not forget to disable slow query logging after troubleshooting
       - For MySQL 5.0 http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
       - For MySQL 5.1 http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
       - For MySQL 5.5 http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
       - For MySQL 5.6 http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
        Adjust your join queries to always utilize indexes. Please note this
        calculation is made by adding Select_full_join + Select_range_check
        status values and triggered when the total >250
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
      *** MySQL maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
      *** Please note this recommendation is not entirely accurate.
          The formula used to calculate max memory usage assumes all queries utilise
          all memory buffers simultaneously. When in reality it is very rare for a
          query to engage & utilise all memory buffers simultaneously.
    
          Formula also assumes all predefined max_connections are reached.
          You could have set max_connections = 1000 and in a whole year
          of usage never hit beyond 50 max_used_connections. So your real MySQL
          memory usage is only 1/20th of theorectical max memory usage reported.
    
          So real max memory usage will never reach this peak.
          So do not be too concerned with this warning.
    
          It is better to monitor your real MySQL max_used_connection and MySQL
          memory usage over time and adjust accordingly.
    
          You can use tools such as Cacti, Munin or mysqlmymonlite.sh at mysqlmymon.com to
          monitor your MySQL memory usage over time.
    
          MySQL performs optimally when its required amount of memory is met.
          Reducing and starving MySQL memory allocation to adhere to this
          artificial max memory warning - of which in reality will never be reached,
          will only reduce MySQL performance in many cases ***
    
        join_buffer_size (> 2.0M, or always use indexes with joins)
        tmp_table_size (> 128M, increase tmp_table_size)
        max_heap_table_size (> 64M, increase max_heap_table_size)
        innodb_buffer_pool_size (>= 637M)
    
    
    Report Complete:
    Tue Apr 28 13:18:26 EDT 2015
     
  2. 24x7ss

    24x7ss Well-Known Member

    Joined:
    Sep 30, 2014
    Messages:
    271
    Likes Received:
    16
    Trophy Points:
    18
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello,

    I can see you are using too many connections for mysql and users which can lead mysql to use more memory on your server. Also, I can see your query_cache_limit is set to 12M where query_cache_size is set to 256M. Please note that limit will overrule size always.

    Further to this, if you use mysql_close function in your scripts, you won't have to use too many connections. Enabling slow query log with threshold like 10 would be helpful to gain the the queries that are taking too long to execute.
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,834
    Likes Received:
    672
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello,

    You may also want to use a command such as "mysqladmin processlist" to get a better idea of which databases are the most active.

    Thank you.
     
  4. Nirjonadda

    Nirjonadda Well-Known Member

    Joined:
    May 8, 2013
    Messages:
    151
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Website Owner
    This is my default my.cnf settings.

    Code:
    [mysqld]
    local-infile=0
    
    max_connections=800
    max_user_connections = 1000
    datadir="/var/lib/mysql"
    socket          = "/var/lib/mysql/mysql.sock"
    
    key_buffer = 256M
    table_cache = 28560
    open_files_limit=50000
    join_buffer_size = 2M
    read_buffer_size = 2M
    sort_buffer_size = 6M
    tmp_table_size=128M
    read_rnd_buffer_size=4M
    max_heap_table_size=64M
    myisam_sort_buffer_size=64M
    thread_cache_size=8
    thread_cache=512
    query_cache_limit=12M
    query_cache_size=256M
    thread_concurrency = 8
    wait_timeout=1000
    connect_timeout=1000
    interactive_timeout=1000
    #log-slow-queries = /var/log/mysqlslowqueries.log
    max_allowed_packet=268435456
    
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    [isamchk]
    key_buffer =64M
    sort_buffer_size = 64M
    read_buffer = 16M
    write_buffer = 16M
    
    [myisamchk]
    key_buffer = 64M
    sort_buffer_size = 64M
    read_buffer = 16M
    write_buffer = 16M
    
    [mysqlhotcopy]
    interactive-timeout
    [mysqld_safe]
    
    log-error="/var/log/mysqld.log"
    This is my output I get when I run the ./mysqltuner.pl or ./mysqlmymonlite.sh mysqltuner. Please let me know. How fixing on this issue?

    Code:
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    
    >>  MySQLTuner 1.4.0 mod - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
    >>  Run with '--help' for additional options and output filtering
    [OK] Logged in using credentials passed on the command line
    [OK] Currently running supported MySQL version 5.5.42-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 4G (Tables: 937)
    [--] Data in InnoDB tables: 637M (Tables: 42)
    [--] Data in MEMORY tables: 3M (Tables: 6)
    [!!] Total fragmented tables: 36
    
    -------- Security Recommendations  -------------------------------------------
    ERROR 1142 (42000) at line 1: SELECT command denied to user 'nadda'@'localhost' for table 'user'
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 8h 24m 57s (8M q [43.851 qps], 733K conn, TX: 50B, RX: 1B)
    [--] Reads / Writes: 78% / 22%
    [--] Total buffers: 720.0M global + 14.2M per thread (800 max threads)
    [!!] Maximum possible memory usage: 11.8G (154% of installed RAM)
    [OK] Slow queries: 0% (41/8M)
    [OK] Highest usage of available connections: 6% (50/800)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/2.9G
    [OK] Key buffer hit rate: 99.6% (643M cached / 2M reads)
    [OK] Query cache efficiency: 61.8% (3M cached / 5M selects)
    [OK] Query cache prunes per day: 86
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 189K sorts)
    [!!] Joins performed without indexes: 2355 (see join_buffer_size note below)
    [!!] Temporary tables created on disk: 62% (70K on disk / 112K total)
    [OK] Thread cache hit rate: 99% (50 created / 733K connections)
    [OK] Table cache hit rate: 37% (1K open / 5K opened)
    [OK] Open file limit used: 4% (2K/57K)
    [OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
    [!!] InnoDB  buffer pool / data size: 128.0M/637.3M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability.
        See notes on accuracy of this recommendation below
        Enable the slow query log to troubleshoot bad queries.
        Do not forget to disable slow query logging after troubleshooting
       - For MySQL 5.0 http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
       - For MySQL 5.1 http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
       - For MySQL 5.5 http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
       - For MySQL 5.6 http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
        Adjust your join queries to always utilize indexes. Please note this
        calculation is made by adding Select_full_join + Select_range_check
        status values and triggered when the total >250
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
      *** MySQL maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
      *** Please note this recommendation is not entirely accurate.
          The formula used to calculate max memory usage assumes all queries utilise
          all memory buffers simultaneously. When in reality it is very rare for a
          query to engage & utilise all memory buffers simultaneously.
    
          Formula also assumes all predefined max_connections are reached.
          You could have set max_connections = 1000 and in a whole year
          of usage never hit beyond 50 max_used_connections. So your real MySQL
          memory usage is only 1/20th of theorectical max memory usage reported.
    
          So real max memory usage will never reach this peak.
          So do not be too concerned with this warning.
    
          It is better to monitor your real MySQL max_used_connection and MySQL
          memory usage over time and adjust accordingly.
    
          You can use tools such as Cacti, Munin or mysqlmymonlite.sh at mysqlmymon.com to
          monitor your MySQL memory usage over time.
    
          MySQL performs optimally when its required amount of memory is met.
          Reducing and starving MySQL memory allocation to adhere to this
          artificial max memory warning - of which in reality will never be reached,
          will only reduce MySQL performance in many cases ***
    
        join_buffer_size (> 2.0M, or always use indexes with joins)
        tmp_table_size (> 128M, increase tmp_table_size)
        max_heap_table_size (> 64M, increase max_heap_table_size)
        innodb_buffer_pool_size (>= 637M)
    
    
    Report Complete:
    Tue Apr 28 13:18:26 EDT 2015
     
  5. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,471
    Likes Received:
    199
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    New thread merged into existing thread.
     
  6. Nirjonadda

    Nirjonadda Well-Known Member

    Joined:
    May 8, 2013
    Messages:
    151
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    Website Owner
    Does this ok to set : query_cache_limit is set to 256M and query_cache_size is set to 256M ? Please give me any Suggestions, Thanks
     
Loading...

Share This Page