Blog Developers

MySQL Tips

Looking after your MySQL database is important as it's usually the bottleneck for most applications. Here are some tips to keep your database server running smoothly.

Be Mindful

1. Before establishing a connection to your mysql server, be sure that you actually need to make the connection. Most static sites will connect to the database and never use the connection for anything.
2. Before you write, make sure what you are writing has value. I wrote an analytics module one time and didn't check for crawlers (google, etc), and in-turn our tables were full of one-time records that provided no value. Session tables can fall under this as well.
3. While it can be tempting, do not store file data in mysql. You can make a file table that points to files on the server or in a cdn, but having the file data stored in mysql is a waste of resources (and bandwidth if your mysql server isn't local to your web server).
4. Make sure you have a dedicated mysql user for the web served with limited permissions. I recommend only allowing SELECT, INSERT, and UPDATE. Create an active/deleted flag on the tables then have a scheduled event perform the DELETE.
5. This isn't mysql related directly, but if you have a high-volume site and are tracking things like analytics/pageviews, writing to a file is significantly faster, then have a cron parse your file and write into the database.

Proper Table Structure

When creating your tables, you should always have at least the following 4 columns. An id column, which should be first, the primary key, and an auto_increment field. At the end of your columns you should have an active or deleted flag, a created datetime/timestamp, and a timestamp column that is set to ON UPDATE CURRENT_TIMESTAMP. This will help keep your tables organized and consistant.

You will also need to make sure that you have your foreign keys set up to maintain data integrity.

Check your logs

There are two logs that you want to watch when managing your mysql database. On ubuntu (and probably others) these are located in /var/logs/mysql/.

The first log you want to check is your error.log file. This will report any errors that are triggered by scheduled events, errors triggered by your website/application will be handled by those programs and not reported in this log.

The second log file you will want to watch is your slow query log. Any queries that take longer than 30 seconds (configurable in your my.cnf file) will be reported here regardless of where they originated. The most common ones you will see here are for DELETEs of large table and poorly indexed SELECTs. There isn't usually much you can do for the DELETEs as they require full table locks, but you can review your SELECTs and make sure they have the proper indices.

How much data is too much?

I've seen quite a lot of databases that are set up and over time the data within them becomes massive. You should have a plan in place to trim down/archive your data periodically.

There are two areas that you can trim down your data; stored data and indices. Here is a query that will show you the size for both and the total.

SELECT 
    `TABLE_SCHEMA`,
    `TABLE_NAME`,
    FORMAT(SUM(`DATA_LENGTH`) / 1024 / 1024, 1) AS `Data in Megabytes`,
    FORMAT(SUM(`INDEX_LENGTH`) / 1024 / 1024,1) AS `Index in Megabytes `,
    FORMAT(SUM(`DATA_LENGTH` + `INDEX_LENGTH`) / 1024 / 1024, 1) AS `Total Megabytes`
FROM
    `INFORMATION_SCHEMA`.`TABLES`
WHERE
    `TABLE_SCHEMA ` = 'my_schema'
GROUP BY
    `TABLE_SCHEMA`, `TABLE_NAME`
ORDER BY
    SUM((`DATA_LENGTH` + `INDEX_LENGTH`) / 1024 / 1024) DESC

Once you have run that query, it will be quite obvious which tables you need to address. Some tables do have legal restrictions on what can/can't be deleted until a specific time has passed. Other tables, like sessions, can be purged more frequently.

Cleaning up your tables should be run through scheduled events if possible for the following reasons:

• No need to make a mysql connection (those are limited)
• Sometimes deletes can take a while, especially with foreign keys cascading

Cleaning up Indices

For newer versions of mysql, you can check the unused index table.

SELECT
    *
FROM
    `sys`.`schema_unused_indexes`
WHERE
    `object_schema ` = 'my_schema'

Before you blindly delete all every index that is returned by this query, be aware that it will list all the indices that haven't been used since the mysql server restarted. You should review these carefully as ones that look like they may not be used are sometimes used on reports or crons that do not run very often.

If you have any tips you want to see here, shoot me an email at blake@blakenichols.com and I'll give them a try!