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
  • "Crash the whole server" - Does the OS have a swappiness setting? If so, what is it, and how much swap space is allocated. – Rick James Dec 14 '15 at 19:47
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.

  • No. You are not using 11GB. Not even close. That formula (and all others I have seen) are gross overestimates. And that particular one is an underestimate since it fails to allow for multiple tmp tables from all connections. – Rick James Dec 14 '15 at 1:13
  • Of course it's not an accurate formula. In addition to your point, it doesn't allow for multiple join buffers. However, it is useful to illustrate a very rough worst-case scenario. And with a join_buffer_size=50MB it is a viable reason why "When i restart server,It run smooth for period of time. and then crash the whole server." – Derek Downey Dec 14 '15 at 13:38
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.