MySQL “No space left on device from storage engine”

Jan 10, 2022

Mins to Read

All

We have planned for archiving the data to improve the DB performance and to reclaim the space. We were evaluating Compression in InnoDB and TokuDB. To find out the best compression method. We started benchmarking the compression ratio between InnoDB and TokuDB.

Everything goes well for some time, but after a few hours got an error message that can’t able to insert the data into the TokuDB table due to storage being full. It is so strange and the host has enough free space.

Table structure:-

 
mysql> show create table mydbops.tokudb\G
*************************** 1. row ***************************
       Table: tokudb
Create Table: CREATE TABLE `tokudb` (
  `ID` int DEFAULT NULL,
  `Name` longtext,
  `Image` blob
) ENGINE=TokuDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (2.18 sec)

mysql> show create table mydbops.innodb\G
*************************** 1. row ***************************
       Table: innodb
Create Table: CREATE TABLE `innodb` (
  `ID` int DEFAULT NULL,
  `Name` longtext,
  `Image` blob
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (2.27 sec)

Server version

 
root@centos17:/var/lib/mysql# mysql --version
mysql  Ver 8.0.25-15 for Linux on x86_64 (Percona Server (GPL), Release '15', Revision 'a558ec2')

Error faced:-

 
mysql> INSERT IGNORE INTO mydbops.tokudb (ID,Name,Image) VALUES (583532949, "aut maxime sint dolores quibusdam nam fuga eos hic.", "incidunt qui maxime consectetur nulla delectus.");
ERROR 1030 (HY000): Got error 28 - 'No space left on device' from storage engine;-

Available storage:

 
root@centos17:/var/lib/mysql# df -h /
Filesystem                                     Size  Used Avail Use% Mounted on
/dev/mapper/packer--debian--9--amd64--vg-root   78G   69G  4.4G  95% /

Around 4.4 GB of free space is available. But the inserts are not failing for the table with the InnoDB engine.

For InnoDB

 
mysql> INSERT IGNORE INTO mydbops.innodb (ID,Name,Image) VALUES (583532949, "aut maxime sint dolores quibusdam nam fuga eos hic.", "incidunt qui maxime consectetur nulla delectus.");
Query OK, 1 row affected (0.20 sec)

After around analysis and discussions, it is found that a TokuDB internal variable is blocking the writes.

The “tokudb_fs_reserve_percent” variable will block the writes (Inserts) to the TokuDB table when the server reaches the mentioned percentage of free space. The default value is 5.

TokuDB will check the filesystem for every 5 sec in the background to determine how much percentage of free space is available, if the free space drops below the mentioned value, then the inserts will get blocked.

This kind of behavior is set to avoid the server crash due to disk FULL. Even the disk got filled due to other engines. Then the TokuDB will freeze instead of crash. Until the free space is created/recovered.

It is a static variable, so changing the value requires a MySQL server restart.

 
mysql> show global variables like 'tokudb_fs_reserve_percent';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| tokudb_fs_reserve_percent |     5 |
+---------------------------+-------+
1 row in set (2.62 sec)

Hope we have figured it out, so in the future, if we faced any disk issue reg the TokuDB engine we can have a look at both OS as well as its configuration as well. Happy troubleshooting.

InnoDB

MySQL

No items found.

About the Author

Mydbops

MySQL “No space left on device from storage engine”

About the Author

Topic covered

Continue Reading

TiDB Lightning for High-Speed Data Import

Simplifying Replication with Tagged GTID in MySQL 8.4

MongoDB Shutdown Methods: db.shutdownServer() vs systemctl stop

PostgreSQL 17: Convert Streaming Replica to Logical Replication with pg_createsubscriber

Subscribe Now!