Some common issues I run into.
1. Disk space usage – usually a result of binary logs hogging up space. Usually alleviated by setting expire_logs_day=n but in some cases the server sees so much traffic it can fill up before n days is reached. Hence the usual method of “mysql> purge master logs to ‘log-name.xxx’;”
2. Replication issues: seconds behind master too high, duplicate key errors, local data infile associated errors
3. Users need more accounts and don’t have grant options to create new ones. Trivial but menial labor. Meh.
4. Connection limits – someone decides not to use connection pooling or persistent connections, therefore we see some stale connections just hanging out and using up the max_connections variable. Please use connection pooling / persistence.
5. Latency – usually comes into play with MySQL Cluster setups where the management node misses heartbeats from the data/storage nodes. Solution: faster interconnects and vlans that separate traffic – or bonded NICs to aggregate bandwidth. Easier said than done depending on who runs the network.
6. RAM: please get as much ram as you can afford, then you can complain to your DBA about performance.
7. Using MyISAM when you should be using InnoDB. Sure your application may not use transactions but in many cases, almost all, InnoDB will perform better – especially if you have lots of RAM. Crash recovery? Yes, InnoDB is better at that as well.
8. Backup processes taking too long. I need to have alternate scripts that make use of mysql-parallel-dump. End of story.
