Not the answer you need?
Register and ask your own question!

/tmp No space left on device

GajendraGajendra ContributorCurrent User Role Patron

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

Tagged:

Answers

  • RohitRohit Current User Role Contributor

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

  • GajendraGajendra Contributor Current User Role Patron

    I have enough on tmpdir

    [[email protected]~]# df -h /tmp/

    Filesystem        Size Used Avail Use% Mounted on

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

  • RohitRohit Current User Role Contributor

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

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.