<div dir="ltr"><div><div><div><div><div>- 1st, I suggest to activate some monitor which save historic data. <br></div>You can install 'atop' or config some SaaS monitor.<br></div>- How many RAM do you have? Is it dedicated MySQL server or you run webserver+MySQL?<br></div>- I suggest to activate slow query log as well.<br></div>Regards,<br></div>vitaly<br></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Apr 25, 2018 at 10:12 PM, David Suna <span dir="ltr"><<a href="mailto:david@davidsconsultants.com" target="_blank">david@davidsconsultants.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div style="direction:ltr" text="#000000" bgcolor="#FFFFFF">
I am working on a VPS. Earlier today there was a load spike that
made the server unresponsive for a period of time (around 10-15
minutes). Both ssh and web access were not responsive. After a while
the problem just stopped and the server started responding again.<br>
<br>
How can I go about diagnosing what caused the problem? I looked at
our application log around that time and I do not see anything out
of the ordinary. When I was finally able to get back into the server
and I ran top, the process that was using the most CPU was mysqld. I
don't know if that was the process that was causing the load spike
at the time.<br>
<br>
I saw several references to MySQLTuner so I downloaded and ran it.
It gave some suggestions but I am not a DBA so I don't understand
most of the suggestions and I don't want to make any changes without
understanding what is involved. Below are the suggestions MySQLTuner
came up with. If anyone can explain some of the suggestions or point
me in the direction of a good resource that would explain them I
would appreciate it.<br>
<br>
General recommendations:<br>
Configure your accounts with ip or subnets only, then update
your configuration with skip-name-resolve=1<br>
Adjust your join queries to always utilize indexes<br>
When making adjustments, make tmp_table_size/max_heap_table_<wbr>size
equal<br>
Reduce your SELECT DISTINCT queries which have no LIMIT clause<br>
Read this before changing innodb_log_file_size and/or
innodb_log_files_in_group: <a class="m_3384173808723610967moz-txt-link-freetext" href="http://bit.ly/2wgkDvS" target="_blank">http://bit.ly/2wgkDvS</a><br>
Variables to adjust:<br>
query_cache_size (=0)<br>
query_cache_type (=0)<br>
query_cache_limit (> 1M, or use smaller result sets)<br>
join_buffer_size (> 256.0K, or always use indexes with joins)<br>
tmp_table_size (> 16M)<br>
max_heap_table_size (> 16M)<br>
innodb_buffer_pool_size (>= 908M) if possible.<br>
innodb_log_file_size should be (=16M) if possible, so InnoDB
total log files size equals to 25% of buffer pool size.<br>
<br>
Thanks,<span class="HOEnZb"><font color="#888888"><br>
<pre class="m_3384173808723610967moz-signature" cols="72">--
David Suna
<a class="m_3384173808723610967moz-txt-link-abbreviated" href="mailto:david@davidsconsultants.com" target="_blank">david@davidsconsultants.com</a></pre>
</font></span></div>
<br>______________________________<wbr>_________________<br>
Linux-il mailing list<br>
<a href="mailto:Linux-il@cs.huji.ac.il">Linux-il@cs.huji.ac.il</a><br>
<a href="http://mailman.cs.huji.ac.il/mailman/listinfo/linux-il" rel="noreferrer" target="_blank">http://mailman.cs.huji.ac.il/<wbr>mailman/listinfo/linux-il</a><br>
<br></blockquote></div><br></div>