ANALYZE TABLE, Error writing file '/tmp/MYdOeJgz' (Errcode: 28 "No space left on device")
MariaDB [cm]> ANALYZE TABLE X1 PERSISTENT FOR ALL; +---------------------------+---------+----------+----------------------------------------------------------------------------+
Table | Op | Msg_type | Msg_text |
+---------------------------+---------+----------+----------------------------------------------------------------------------+
db1.X1 | analyze | Error | Error writing file '/tmp/MYdOeJgz' (Errcode: 28 "No space left on device") |
db1.X1 | analyze | status | Operation failed |
+---------------------------+---------+----------+----------------------------------------------------------------------------+ 2 rows in set (10 min 20.071 sec)
df -h /tmp Filesystem Size Used Avail Use% Mounted on /dev/... 200G 26G 175G 13% /
I have more than 70 million rows in DB in X1 table. (approximation.)
There is JSON data in the table. Inside one JSON entry/row there is a list. List contains of about 15 entries. Each entry in the JSON list is something like: xxxxxxxxxxx:valyyyyyyyyyyyyyy. (15 entries like this one in one JSON entry or JSON row in the DB table.) So, entries are not so big. But there can be a lot of data.
In this first test I had varchar(3024) data type in the column where I stored json data. Additionally I had json_valid check. json data type seems to be quite similar to varchar(3024) + json_valid check.
The table X1.ibd takes currently 90G of space on disk.
My question would be: how much space does MariaDB need for the ANALYZE operation? 175 GB is not enough in this situation? Or are my settings / commands incorrect.
I have following settings in /etc/my.cnf:
innodb_log_file_size=4G innodb_buffer_pool_size=50G
Version of MariaDB: 10.3.9
Side note, I tried to change tmpdir (in /etc/my.cnf) to separate mount point, which has bigger disk. I did not succeed with it yet. MariaDB start up fails to Permission denied error. (This side note is a topic for an other question, if I do not manage so solve it.)
Answer
Hi, did you manage to solve this?
You created table as innodb and you have enough space (so your configuration in `/etc/my.cnf` related to innodb have nothing to do with this), but creating the temporary files (maybe?) fails to write a table for engine-independent table statistics (EITS) that happens to be a MyISAM table (according to the error you are receiving `/tmp/MYdOeJgz`).
What is your `/dev/...` ? Are you sure `/tmp/` is on the right place and did you manage to mount it on the right place? Can you please provide the option file you are using?