Temporary file behavior… (and how lsof save my life)

I would like to share this story based on a true event about the temporary files behavior in MySQL.

MONyog reports this error to my already full mailbox several times a day :


 

1 – Catch the query (if you can) !

 

I don’t have access to the client logs but I would like to know which query is involved in this error.

Let me explain how I can retrieve informations about this query with MONyog and a very simple shell loop :

  •  Enable the query sniffer in MONyog (based on processlist) : Edit server -> Advanced settings -> Sniffer settings

 

  • Monitor your MySQL TMPDIR directory with this simple shell loop :
  • [ Use this command to retrieve the MySQL temporary directory : show variables like 'tmpdir'; ]
  • [ In this case : tmpdir=/database/tmp ]
while [ 1 = 1 ]; do { date;ls -artlh /database/tmp ; df -h /database/tmp;
  lsof | grep mysql | grep /database/tmp;sleep 1; }; done
 
With this loop, I can follow in real time the informations about the files created in the MySQL temporary directory and it will be very useful to find how exactly the error happens (see below).

With the MONyog query sniffer, I found the query involved by comparing the two emails sent by MONyog (Error message above and the emails about long queries).
The explain plan of this query was as follow :

 

And finaly, the real error was :
ERROR 3 (HY000): Error writing file '/database/tmp/MYakhJC5' (Errcode: 28)
 Error (Code 3): Error writing file '/database/tmp/MYakhJC5' (Errcode: 28)
 Error (Code 1028): Sort aborted
# perror 28
 OS error code  28:  No space left on device

 

2 – No space left on device, really ?!

 

Let see the filesystem definition for /database/tmp with the df command :
And here is a list view of the files in the directory when the error occured :
 total 2,3G
 drwxrwx--- 11 mysql mysql 4,0K 16 févr. 10:37 ..
 -rw-rw----  1 mysql mysql    6  6 avril 15:39 mysql.pid
 srwxrwxrwx  1 mysql mysql    0  6 avril 15:39 mysql.sock
 -rw-rw----  1 mysql mysql 1,0K 12 avril 15:41 #sql_7237_0.MYI
 -rw-rw----  1 mysql mysql 2,3G 12 avril 15:42 #sql_7237_0.MYD
 -rw-rw----  1 mysql mysql 1,0K 12 avril 15:42 #sql_7237_5.MYI
 -rw-rw----  1 mysql mysql    0 12 avril 15:42 #sql_7237_5.MYD
 drwxrwx---  2 mysql mysql 4,0K 12 avril 15:43 .
The /database/tmp filesystem has a size of 6,5Go and there was a total of 2,3Go of files when the error occured !

 

3 - “Was passiert ?!”

 

The lsof command was my very good friend to discover what happened.
Let see the trace of my tiny loop when the error occured (lsof part only) :

 

 

I can see a 2,3Go temporary file but there are two other temporary files which continue to grow until the end of the world :-(
And these two (deleted) files appears to be the cause of my issue.

 

4 – What next ?

 

The killer question : why did you have only 6.5 GB for your temporary space ?
Answer : Why not :-)

You have to consider this behavior to set your temporary space properly, specially if this space is a ramdisk filesystem.

Tschüs !

PS : I let the experts explains why these two additional files are created (hint : look at the Exta column)

Why a new memory engine may change everything ?

I’m sure you are aware that the last Percona server release includes a new improved MEMORY storage engine for MySQL.
This new engine is based on Dynamic Row Format and offers some of great features, specialy for VARCHAR, VARBINARY, TEXT and BLOB fields in MEMORY tables.

But because this new MEMORY engine by Percona has some limitations and because Percona server hasn’t used it for its internal temporary tables yet, I would like to talk about what can be the real benefits to have a brand new MEMORY engine based on Dynamic row format specialy for internal memory tables.

Just remember or discover how MySQL uses internal memory tables

And the MEMORY storage engine characteristics and its limitations

So, the memory storage engine transforms all varchar fields in char fields for internal temporary tables or for user created memory tables. 

1. Let me explain what is the problem with a simple exemple :

I’ve created an InnoDB table (without index) with two varchar fields (50 & 100) :

mysql> show table status like 'test_memory5'\G
*************************** 1. row ***************************
           Name: test_memory5
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 621089
 Avg_row_length: 66
    Data_length: 41484288
Max_data_length: 0
   Index_length: 0
      Data_free: 5242880
 Auto_increment: NULL
    Create_time: 2011-09-21 13:10:17
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:

The size of this table is about 48Mb (and more than 600.000 rows) :

-rw-rw---- 1 mysql mysql  48M 2011-09-21 13:11 test_memory5.ibd

Now, I’m creating a new memory table with exactly the same structure and I’m setting paramters for memory tables like this :

  • set tmp_table_size=50*1024*1024;
  • set max_heap_table_size=50*1024*1024;

That means I can create a 50Mb max memory table.
Thus, let me insert my 600.000 rows in this table :

mysql> insert into test_memory6 select * from test_memory5;
ERROR 1114 (HY000): The table 'test_memory6' is full

My 50Mb memory table can’t  contain the 48Mb of the InnoDB table !
Let’s try with 80Mb :

mysql> set tmp_table_size=80*1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> set max_heap_table_size=80*1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test_memory6 select * from test_memory5;
ERROR 1114 (HY000): The table 'test_memory6' is full

And this error occurs until the memory tables can have a 110Mb maximum size !

Why ? Because the two varchar fields of the InnoDB table are converted in char fields with the memory storage engine.
Let’s see this example from MySQL documentation :

Value CHAR(4) Storage Required VARCHAR(4) Storage Required
'' '    ' 4 bytes '' 1 byte
'ab' 'ab  ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

By the way, this is a very good reason to take care of your varchar fields.

Conclusion : A memory table can be really bigger than an InnoDB table

2. Let  me explain why a new memory engine may change everything :

Changing the rules for memory tables may change everything for, at least, two reasons :

  • VARCHARVARBINARYTEXT and BLOB fields will be supported by this new engine for user created memory tables (Percona server can do it with restrictions)
  • Internal memory tables could be more efficient with Dynamic Row Format
The real benefits will come with internal memory tables, how often do you see that when you “explain” your queries :
Extra: Using where; Using temporary; Using filesort

Thus, for each query using a temporary table, MySQL could use less of memory (RAM) !
I don’t know what would be the real benefit but I am convinced that it can be really significant.

I look forward to see more benchmark about that with last percona server release and I hope that Percona server or MariaDB will support dynamic row format for internal memory tables soon.

Please, let us know if you have already tested this new Percona Memory engine.