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 and have json_valid check. json data type seems to be quite similar to varchar(3024).
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 Answered by Anel Husakovic in this comment.
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?
