Skip to main content

Optimization Tips Requested

Nirjonadda
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.
[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"

------------------------------------------------- mysqltuner output ------------------------------------------------- >> MySQLTuner 1.4.0 mod - Major Hayden >> 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

Comments

5 comments

Date Votes
  • 24x7ss
    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.
    0
  • cPanelMichael
    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.
    0
  • Nirjonadda
    This is my default my.cnf settings.
    [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?
    ------------------------------------------------- mysqltuner output ------------------------------------------------- >> MySQLTuner 1.4.0 mod - Major Hayden >> 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
    0
  • Infopro
    This is my default my.cnf settings.

    New thread merged into existing thread.
    0
  • Nirjonadda
    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.

    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
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post