2

Snapshot of High Load average

Mysql is used by only one application which only fire query periodically. When i restart server,It run smooth for period of time. and then crash the whole server.

My my.cnf file

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_file_per_table=1
set-variable = max_connections=200
log-slow-queries=/var/lib/mysql/slow.log
safe-show-database
query_cache_size=128M
join_buffer_size=50M
tmp_table_size=128M
max_heap_table_size=128M
table_cache=10K
long_query_time =10
open_files_limit=50000
table_open_cache=20000
table_cache=20000
query_cache_limit=134217728
innodb_buffer_pool_size=900M

[mysqld_safe]

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Md Haidar Ali Khan
  • 6,523
  • 9
  • 40
  • 62
Brijesh Mistry
  • 21
  • 1

2 Answers2

2

From your screenshot, it seems you are running on a machine just under 4GB of RAM. You also have ~4GB of SWAP space that MySQL could use if necessary. A rough calculation of how much memory your settings will allow is ~11.5GB.

From mysqlcalculator.com: mysqlcalculator.com estimate

Due to the complex nature of memory allocation, this is an estimate of a worst-case scenario only.

Out of the box, your join_buffer_size is extremely large. This is a per-session thread that can be allocated multiple times in a query:

For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.

I recommend setting this back to the default (in 5.6, that is 256kb). If you find many queries that are starting to show 'join_buffer' in the explain plan, then you should examine them for better index usage. If that fails, you might consider increasing join_buffer_size at the SESSION level for specific queries.

Derek Downey
  • 23,568
  • 11
  • 79
  • 104
1

table_open_cache=20000 table_cache=20000

Rarely does anyone need more than a few hundred for table_open_cache. table_cache is no longer used.

High CPU (or load average) is almost always caused by lack of indexes or poorly written queries. Turn on the slowlog and set long_query_time = 1; then see what shows up in the slowlog.

Or, do SHOW FULL PROCESSLIST; to see what is currently running. Then let's discuss them. Please provide SHOW CREATE TABLE.

Rick James
  • 80,479
  • 5
  • 52
  • 119