Nirjonadda

Well-Known Member
May 8, 2013
736
27
78
cPanel Access Level
Root Administrator
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 <[email protected]>
>>  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
 

24x7ss

Well-Known Member
Sep 30, 2014
272
17
68
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.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,908
2,216
463
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.
 

Nirjonadda

Well-Known Member
May 8, 2013
736
27
78
cPanel Access Level
Root Administrator
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 <[email protected]>
>>  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
 

Nirjonadda

Well-Known Member
May 8, 2013
736
27
78
cPanel Access Level
Root Administrator
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