InnoDB: running out of AUTO_INCREMENT values

Most InnoDB primary keys are built on integer columns with the AUTO_INCREMENT option (which is a very good practice for reasons that are outside of the purpose of this article). But we have to monitor that we are not going to run out of AUTO_INCREMENT value. If this happens, we will get errors like this:

ERROR 167 (22003): Out of range value for column 'a' at row 1

Obviously, when creating tables, we should use a type that is sufficiently big, and make it UNSIGNED to avoid wasting half of its space. But there are also some details about AUTO_INCREMENT that we should remember.

First, the values to monitor are not MAX(id), but they are the AUTO_INCREMENT column in information_schema TABLES. This column shows the next autoincr value.

A used value is lost. It doesn’t matter if you delete a row: its autoincr value will not be reused. This is why we should monitor the next values from information_schema: it is possible that we used all autoincr values, but MAX(id) is much lower.

Values are lost even for transactions that fail or are rolled back. This is the reason why MAX(id) is generally higher, sometimes much higher, than the next AUTO_INCREMENT value. This can happen especially if we have periodical bulk inserts inserting many rows in one transaction. If such operation fails, it will waste autoincr values. If it fails often, we could run out of values.

Easy trick

An easy trick could be use a command like this:

ALTER TABLE table_name AUTO_INCREMENT = 1000;

But this only works if all values in the table are lower than the specified AUTO_INCREMENT. This can be useful if a bulk insert failed and no new row was still added, but typically this only postpones a bit the problem.

Or again, we could change all id’s to fill all the holes, and then set AUTO_INCREMENT. This is possible if, for example, the server is not used by night, or during the week end. But in other cases, it is not a viable solution. Modifying a primary key is not a cheap operation for InnoDB, and most likely, we will have to do this with many rows. Also, we would need to lock the table to avoid that new rows (with high values) are inserted in the process.

So, what can we do?

If we have a write-heavy workload, lowering the autoincr values is not trivial, because new rows are being inserted any time. And changing an id for many rows can be a long operation. If we do it with one transaction to make things faster, it’s locking. But well, we can do something similar to what pt-online-schema-change does to alter a table without locks. Let’s see the procedure step by step.

For our example, we will assume the following trivial table:

CREATE TABLE employee
(
    id INT UNSIGNED AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
);

1)

First, let’s create a table with the same structure. The id’s in the new table will start from 1. And let’s add a column, which is a reference to the original table’s id.

CREATE TABLE tmp_employee LIKE employee;
ALTER TABLE tmp_employee ADD COLUMN orig_id INT UNSIGNED NULL;
ALTER TABLE tmp_employee ADD UNIQUE INDEX idx_orig_id (orig_id);

2)

Now, we need to add a trigger to the original table, to add the new rows in the new table:

DELIMITER ||

CREATE TRIGGER employee_ai
    AFTER INSERT
    ON employee
    FOR EACH ROW
BEGIN
    INSERT INTO tmp_employee (old_id, name) VALUES (NEW.id, NEW.name);
END ||

DELIMITER ;

Note that original table’s id and the new table’s old_id columns are kept in sync.
This is just an example. In the real world, I would also create triggers for DELETE and UPDATE. This triggers need a reference to the new table, that’s why old_id exists and is indexed.

What if the table already has triggers? Fortunately MySQL 5.7 and MariaDB 10.2 support multiple triggers per timing/event. With older versions, just modify them and add the queries you need.

3)

Copy rows from the original table to the new table. If the new table already has some rows (added by our INSERT trigger) we won’t try to copy it.

INSERT INTO tmp_employee (old_id, name)
    SELECT id, name
        FROM employee
        WHERE
            (SELECT COUNT(*) FROM tmp_employee) = 0
            OR id < (SELECT MIN(old_id) FROM tmp_employee);

We are still populating the old_id column for DELETE and UPDATE triggers.

We didn’t mention the new table’s primary key, so the values are automatically generated.

4)

Until now, I assume we didn’t cause any damage. But the next step is more dangerous. Before proceeding, verify that we didn’t do anything wrong and our transactions didn’t fail.

5)

If everything’s ok, let’s switch the tables:

RENAME TABLE
    employee TO old_employee,
    tmp_employee TO employee;

This operation is atomic, so no query is expected to fail.

6)

Drop the old table and the old_id column.

Enjoy!

How much memory should we assign to MariaDB?

How much memory should we assign to MariaDB?

Before answering, I’ll show you a procedure I use to get the amount of memory I’m using. See the comments to make it work on Oracle MySQL.

CREATE PROCEDURE _.show_buffers()
	CONTAINS SQL
	COMMENT 'Show information about buffers size'
BEGIN
	SET @memory_per_thread := (
			  @@global.read_buffer_size
			+ @@global.read_rnd_buffer_size
			+ @@global.sort_buffer_size
			+ @@global.thread_stack
			+ @@global.join_buffer_size
			+ @@global.binlog_cache_size
		);
	SET @global_memory := (
			  @@global.innodb_buffer_pool_size
			+ @@global.innodb_additional_mem_pool_size
			+ @@global.innodb_log_buffer_size
			+ (SELECT SUM(FULL_SIZE) FROM information_schema.KEY_CACHES) -- MySQL: @@global.key_buffer_size
			+ @@global.aria_pagecache_buffer_size -- only MariaDB
			+ @@global.query_cache_size
		);
	SELECT
		  @global_memory AS global_memory
		, @memory_per_thread AS memory_per_thread
		, @@global.max_connections AS max_connections
		, @memory_per_thread * @@global.max_connections AS max_session_memory
		, CAST((@global_memory + @memory_per_thread * @@global.max_connections) AS UNSIGNED) AS max_total_memory
		;
	SET @memory_per_thread := NULL;
	SET @global_memory := NULL;
END;


Results on my dev machine:

MariaDB [(none)]> CALL _.show_buffers() \G
*************************** 1. row ***************************
     global_memory: 419430400
 memory_per_thread: 2949120
   max_connections: 151
max_session_memory: 445317120
  max_total_memory: 864747520
1 row in set (0.00 sec)


So, what do these columns mean?

  • global_memory: Memory used by global buffers.
  • memory_per_thread: Memory used by each thread (session buffers).
  • max_connections: Max allowed concurrent connectins (max_connections variable).
  • max_session_memory: Memory used by each thread, multiplied by the max allowed concurrent connections.
  • max_total_memory: Total memory occupied by MariaDB with the maximum of concurrent connections.

Let’s see which settings affect these values.

global_memory = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + aria_pagecache_buffer_size + query_cache_size.

memory_per_thread = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size.

If you use non-common storage engines, you may want to add more settings to these formulas. For example, if you use TokuDB, you should add tokudb_cache_size.

So, basically, my advice is: Tune these values so that max_total_memory reaches 80% your memory. Start by setting query_cache_size and unused storage engines caches to 0, then tune the buffer pool. Usually you don’t want to touch session buffers.

Why 80%? Well, an higher percentage could be ok, but I consider 80% safer for the general case. Because:

  • The system needs memory, too.
  • InnoDB buffer pool slightly exceeds the limit you set.
  • Some operations (like building a big index or repairing a non-transactional table) require memory.
  • Some connections could resize their session buffers.
  • There are more buffers, that we didn’t consider here.
  • You don’t want MariaDB/MySQL to swap. But if you turn off the swap and you run out of memory, you could experience various failures, or even crashes.

MariaDB/MySQL: Performances of COUNT()

Versione italiana

How fast is COUNT() execution? Well, it depends from the Storage Engine.

Try to create an Aria or MyISAM table, INSERT some data, and execute an EXPLAIN similar to the following:

MariaDB [(none)]> EXPLAIN SELECT COUNT(*) FROM test.t1;
+------+-------------+-------+------+---------------+------+--------+------+------+------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+------+-------------+-------+------+---------------+------+--------+------+------+------------------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+------+-------------+-------+------+---------------+------+--------+------+------+------------------------------+
1 row in set (0.10 sec)

Optimized away in this case means that there is no need to access the table data, because the number of rows is stored in table’s metadata. So, the results are always retrieved instantly.

For COUNT(col_name), the table is not optimized away – unless the column is NOT NULL and you are not using DISTINCT, but that syntax is equivalent to COUNT(*).

InnoDB cannot optimize away the tables from these queries, because it has a more complex way to handle data. But of course, it can use an index to get the number of values in a column.

The Storage Engines which optimize away the COUNT() operations are Aria, MyISAM, MEMORY, and the ugly thing called MRG_MyISAM. The Storage Engine which have to access the tables are InnoDB, XtraDB, Archive and CSV. (I only made a quick test on MariaDB 5.5 default engines)

Is this a problem for you? Before switching to Aria/MyISAM, consider an alternative: keeping the row count in a very small MEMORY table without changing the original table’s engine.

Enjoy!