/tmp No space left on device

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hi Team,

I am getting error after executing below query which ran daily in cron

Error : mktemp: failed to create file via template /tmp/pmp-check-mysql-status.XXXXXX: No space left on device

version: mysql 5.7

1> delete from tableA WHERE status = ‘used’ ;

2> delete from tableB WHERE expires < unix_timestamp(now()) ;

3 >delete from TABLEC WHERE expires < unix_timestamp(now());

4> delete from TABLED WHERE expires < unix_timestamp(now());

above query fetch around 1lac record

i collected data and index stats for above tables

Table_schema | TABLE_NAME | Data_Length(MB) | Index_Length(MB) | Data_Free(MB) |

±-------------±-------------------------------±----------------±-----------------±--------------+

| DATABASE_AB | tableA | 156007 | 1270 | 8663 |

| DATABASE_AB | tableB | 3885 | 0 | 565 |

| DATABASE_AB | TABLEC | 19518 | 7441 | 7 |

| DATABASE_AB | TABLED | 9780 | 4777 | 6 |

so how can i avoid above error

do i need to increase tmp_table_size value ? ,the current value is 16MB

or any other setting i have to change

Please help me on this

2

Please change tmpdir to some other mount point where you have enough space. This will help you for bulk queries.

3

I have enough on tmpdir

[root@servername~]# df -h /tmp/

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/VG1-LV_root 25G 3.9G 21G 16% /

4

Then no need to change tmpdir. Just monitor query when you are getting space issue on /tmp. Seems issue with query then.