Server load spike debugging

[linux.il]

- 1st, I suggest to activate some monitor which save historic data.
You can install 'atop' or config some SaaS monitor.
- How many RAM do you have? Is it dedicated MySQL server or you run
webserver+MySQL?
- I suggest to activate slow query log as well.
Regards,
vitaly

On Wed, Apr 25, 2018 at 10:12 PM, David Suna <david at davidsconsultants.com>
wrote:

> 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.
>
> 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.
>
> 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.
>
> General recommendations:
>     Configure your accounts with ip or subnets only, then update your
> configuration with skip-name-resolve=1
>     Adjust your join queries to always utilize indexes
>     When making adjustments, make tmp_table_size/max_heap_table_size equal
>     Reduce your SELECT DISTINCT queries which have no LIMIT clause
>     Read this before changing innodb_log_file_size and/or
> innodb_log_files_in_group: http://bit.ly/2wgkDvS
> Variables to adjust:
>     query_cache_size (=0)
>     query_cache_type (=0)
>     query_cache_limit (> 1M, or use smaller result sets)
>     join_buffer_size (> 256.0K, or always use indexes with joins)
>     tmp_table_size (> 16M)
>     max_heap_table_size (> 16M)
>     innodb_buffer_pool_size (>= 908M) if possible.
>     innodb_log_file_size should be (=16M) if possible, so InnoDB total log
> files size equals to 25% of buffer pool size.
>
> Thanks,
>
> --
> David Sunadavid at davidsconsultants.com
>
>
> _______________________________________________
> Linux-il mailing list
> Linux-il at cs.huji.ac.il
> http://mailman.cs.huji.ac.il/mailman/listinfo/linux-il
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.cs.huji.ac.il/pipermail/linux-il/attachments/20180425/5e7d5d07/attachment.html>