- 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 <da...@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 sunada...@davidsconsultants.com > > > _______________________________________________ > Linux-il mailing list > Linux-il@cs.huji.ac.il > http://mailman.cs.huji.ac.il/mailman/listinfo/linux-il > >
_______________________________________________ Linux-il mailing list Linux-il@cs.huji.ac.il http://mailman.cs.huji.ac.il/mailman/listinfo/linux-il
