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.
No comments:
Post a Comment