|
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 Suna [email protected] |
_______________________________________________ Linux-il mailing list [email protected] http://mailman.cs.huji.ac.il/mailman/listinfo/linux-il
