Hosting MySQL’s tips: Error 28 from Storage Engine
If you have any experience with hosting MySQL systems you have probably come across the following error. Here are some tips on how to fix it.
Error 28 is a common error message from your MySQL storage engine. This message indicates that the drive is full, meaning that one or more of the partitions on your server is out of space, and MySQL cannot write to the disk. You need to clear disk space for MySQL to operate. Note that, if you have a dedicated server, you may receive this error even if your control panel still shows free space. (For example, cPanel does not include the database disk space used. It just calculates the disk space of /home/cPanel_username (root directory) for the account.)
Often, the problem can be solved by cleaning up your /tmp directory, which can be overfilled with unnecessary files, like older backups from a scheduled backup script.
Tip 1: First, stop MySQL immediately
On cpanel the /tmp directory is an image created on the / drive so removing files from it won’t help…
Some places worth checking on cpanel is the domlogs if they do not need stats or /home/cpbackuptmp as it is a temporary directory for backups so long as backups are not currently running. Also to check their actual backups and if they are on the same drive that they aren’t keeping too much retention vs available size on their drive, i.e., if they have a 250gb drive and /home & /var/lib/mysql are 80gb keeping a daily & weekly will fill up the drive every time backups run.
Tip 2: Restart MySQL
# /etc/init.d/mysql start
If your /tmp directory is not causing the error, you may also need to look into your /var/log directory and remove or compress the logs.
Additionally, periodically optimizing your tables in MySQLcheck improves database performance. Optimizing is a similar process to defragmentation.
mysqlcheck –all-databases –analyze –optimize -u root -p
Tip 3: Repair all tables in your database
mysqlcheck --all-databases --auto-repair -u root -p
Analyzing, optimizing, and repairing tables can also easily be scheduled as a cron job.
In PHPMyAdmin, table optimization is done by cleaning up table overhead. Overhead is similar to hard drive defragmentation—database tables that are frequently accessed or updated store extra file size, called “Overhead,†that can take up a lot of space over time and requires maintenance. To clear this space, login to PHPMyAdmin, select the applicable database, and you should see a list of all the tables in the database.
In the far right column, note the heading “Overhead.†Check all the tables that have an “Overhead†value listed. At the bottom of the window, you’ll find a drop-down menu. Choose “Optimize Table†from the menu. Overhead will be cleared, and if you click on the database name again, you’ll see all €œOverhead†values should be cleared.
Hope this helps anyone out there looking to solve issues with their hosted Mysql setup.
