MySQL troubleshooting.

First, check the log. /var/log/mysqld.log has anything logged by the MySQL server itself. If that file doesn't exist, check the /etc/my.cnf

The my.cnf file is the file used to configure MySQl. MySQL optimization such as number of connections, how long connections are kept open etc are kept in this file. Editing this file can only be done via command line ssh. '); return false">my.cnf for the location of the file. Look for the "log-error" setting. If there is an error when MySQL is trying to start, it'll show up here.

Common issues:

1) Check disk space before anything else. Yes, it's basic, but it's common. If there are bin-logs in the /var/lib/mysql directory and replication isn't enabled, they can probably be deleted. One note: Point-in-time restoration is also dependent on these files, so removing the logs will mean you can't do a transaction replay.

To flush the logs, log into mysql as admin/root and run

mysql> FLUSH LOGS;

mysql> RESET MASTER;

You can also check the my.cnf file and add an entry for expire_logs_days to cycle the logs to keep them from building up. 7 days should be more than enough.

***NOTE*** Do not do this on a server that is a Replication Master! You will break things.

2) If they are getting "too many connections" error:

Check /etc/my.cnf for the skip-name-resolve option. If it isn't there, add it under the [mysqld] section and restart mysql. This will fix the majority of the errors.

Log into mysql and check the running process list. You'll see something like this.

mysql> show processlist;

+-------+------+-----------+------+---------+------+-------+------------------+

| Id    | User | Host      | db   | Command | Time | State | Info             |

+-------+------+-----------+------+---------+------+-------+------------------+

| 23540 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |

+-------+------+-----------+------+---------+------+-------+------------------+

1 row in set (0.04 sec)

This will tell you where the connections are coming from, and the number of connections via the rows in set.

3) For "slow response" type problems:

Check the slow query log. It should be in /var/lib/mysql. If it doesn't exist, enable it.

To enable slow query log:

edit /etc/my.cnf

Add:

log_slow_queries=/var/lib/mysql/mysqld-slow.log

long_query_time=5

to the file under the [mysqld] portion. This will log any query that takes more than 5 seconds.

Restart mysql to enable the logging.

It may take a while to get anything in the log, but the contents are the first things that need to be debugged. Remember, 80% or more of MySQL optimization is optimizing the queries, not the server.

4) If the server is crashing / running slow...

Check my.cnf for the max_connections. Odds are good that this is set far too high. MySQL connections are not the same as Apache connections, it is certainly not a 1:1 relationship between the two. Most servers are fine with 250.

The problem with setting the max_connections too high is that it will cause MySQL to attempt to reserve far too much memory. The formula is total per-thread buffer usage = (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections.

So, having high settings for those buffers with a max_connections limit that is too high will bring the server to its knees. Our Managed Services config uses:

256k+512k+512k+192k+128k+32k = ~1.5M per connection.

Those four things cover about 90% of the problems that we see with MySQL.