Last week, a colleague faced an interesting situation: he lost control of an auto-increment column of utmost importance, which led to the interruption of a critical online service. Well, it is the DBA’s job to keep data tightly under control, even if it sometimes behaves like a wild horse, trying to sidestep Murphy’s Laws, Moore’s Laws, and many others.
Making columns larger than they actually need to be is a huge waste of resources. Especially in MySQL, where there is a wide variety of data types.
Smaller columns take up less space on disk, in memory, and, most importantly, generate less I/O. In fact, minimizing I/O is our biggest and most legitimate concern when it comes to the topic of “data types.”
Everyone has an “id” column to call their own. At least if they minimally use one of the normal forms. Without over-theorizing: every table should have a column that uniquely identifies a row (id). In other words, it’s a column that guarantees maximum uniqueness for a row, making it a prime candidate for the primary key, with a unique and unmistakable value! One way MySQL provides this is through the use of AUTO_INCREMENT and PRIMARY KEY.
Here’s a simple example of creating a table with an auto-incrementable “id” column:
sqlCopiarEditarCREATE TABLE clientes (
cliente_id smallint NOT NULL AUTO_INCREMENT PRIMARY KEY,
cliente_nome varchar(30) NOT NULL
) ENGINE=InnoDB;
So, automatically, every time we insert a new row (a new record) into the clientes table, MySQL will create a new sequential number for the customer. To do this, simply set the cliente_id to NULL or don’t provide a value in the INSERT command.
The Problem!
For those who didn’t skip school :-), you know the cliente table will never have more than 32,767 customers, because that’s the maximum value supported by a smallint column (signed, ufff, I wrote that…).
What happens when we try to insert the 32,768th record? Error, that’s what happens. We’ve exceeded the column’s maximum capacity (range). In a 24/7 online application, this would be catastrophic. It would cause an outage for a specific service and/or application.
The “Home-Cooked” Solution
It’s simple—just make the column bigint unsigned, and now the table can hold 18,446,744,073,709,551,615 records!!! Pretty nice, huh? Nooooo! Now, instead of using a column optimized to just 2 bytes, we’d be using an 8-byte column, and for what? To avoid the DBA from knowing his database, resulting in high I/O?
This solution might work for most people, but it’s not right!
The Rational Solution
As I always say: “The success or failure of an application starts to be determined in the requirements gathering phase, by defining entities and properties, and then, necessarily, through database modeling.” Uncle has said this many times!
Control, monitor, and manage your database. Create columns that are as rational as possible, and, especially in the case of auto-increment columns, manage the growth of tables and their evolution accordingly.
Okay, MySQL doesn’t provide a “pain-free” way to do this. You ask, Uncle does.
With that in mind, I created a simple, fast, but efficient application: alemaxai.
alemaxai will scan an entire MySQL instance, look for all tables with auto-increment columns, check the data type of those columns, and, based on a safety margin defined by the user, display (or send by email) a list of tables that have auto-increment columns with values exceeding the safety margin.
The usage is very simple:
- Create a configuration file with the
.alemaxaiextension (I’ve included a sample file for download). This file contains: host, port, user, password for connection, and email to send messages. - Run alemaxai by specifying the path to the configuration file.
Example usage:
shell> /opt/mysql/alemaxai jobs # (note: jobs is the jobs.alemaxai file located in /opt/mysql)
Output:
+-----------------------------------------+
| alemaxai v1.0 – by Alexandre Almeida (alepilot) |
+-----------------------------------------+
Browsing for tables with misbehavior…
---------------------------------------------------
Databases Tables Max ID Data Type
---------------------------------------------------
cphulkd ale 125 tinyint
cphulkd ale1 126 tinyint
Visit my blog: https://www.alexandremalmeida.com.br
Simple as that!
Download the program, use it liberally, and control your auto-increment columns. Avoid making columns larger than they really need to be, but keep them under control.
Visit our Blog
Learn more about databases
Learn about monitoring with advanced tools
Have questions about our services? Visit our FAQ
Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!
Discover the History of HTI Tecnologia
