Investigating linux server crashes - MySQL

MySQL Configuration

The MySQL configuration file can be found at

/etc/my.cnf

By default it does not create a separate log file for MySQL errors, but you can add a line to separate the error messages out.

log_error = /var/log/mysqld.log

MySQL Tuner

For suggestions on improving your MySQL configuration, install MySQL tuner. Run it after MySQL has been running for at least 24 hours. The more time the better the suggestions.

wget  http://mysqltuner.com/mysqltuner.pl
chmod +x  mysqltuner.pl

Then to run the program, you must be logged in as a user that has privileges to access MySQL. For me, it would not work as my usual user account and I solved the problem by logging in as root and changing to the directory that I wget'ed mysqltuner to.

./mysqltuner.pl

A sample output might be:

-------- Recommendations --------------------------------------------------------------------------- General recommendations: Control warning line(s) into /var/log/mysqld.log file Control error line(s) into /var/log/mysqld.log file Dedicate this server to your database for highest performance. Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Set thread_cache_size to 4 as a starting value Performance shouldn't be activated for MySQL and MariaDB 5.5 and lower version Consider installing Sys schema from https://github.com/mysql/mysql-sys Variables to adjust: query_cache_size (=0) query_cache_type (=0) query_cache_limit (> 1M, or use smaller result sets) tmp_table_size (> 16M) max_heap_table_size (> 16M) thread_cache_size (start at 4) performance_schema = OFF disable PFS innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible.

MySQL Log Files

The two main log files for MySQL will now both be in the following directory.

log-slow-queries=/var/lib/mysql/g9online-vps-slow.log
log_error = /var/log/mysqld.log

The Slow Query Log can be analysed using mysqldumpslow

mysqldumpslow /var/lib/mysql/g9online-vps-slow.log

It summarises the number of times the same query has been logged to the Slow Query log. By default, mysqldumpslow sorts by average query time but you can use the -s option to sort by t or at, query time, l or al, lock time, r or ar, rows sent or c, count.

Another useful options is -t N to display only the first N queries in the output.