5

On one of our mysql databases the size on disk is way larger (28GB) than the actual data (~5GB) so I had a closer look to the files contained in mysql-data

I see the following files

12K     #sql-5254_eaa3.frm
8.9G    #sql-5254_eaa3.ibd
12K     #sql-537f_8d5b.frm
11G     #sql-537f_8d5b.ibd

The above remain even after mysql restart, server reboot etc

Any idea if these are temporary tables that survived e.g. a crash? on a production system is it safe to just remove them or do I need to handle them in a different way?

By the way we do have file_per_table set to true.

thanks in advance for any hints!

the-wabbit
  • 40,737
  • 13
  • 111
  • 174
webgr
  • 213
  • 1
  • 2
  • 7
  • Appears that with mysql's flag set it creates .ibd files (one per table) and basically they can never shrink. See also https://dba.stackexchange.com/questions/142653/mysql-ibd-file-is-too-big – rogerdpack Jun 20 '17 at 17:57

3 Answers3

7

The table files you are seeing are most likely resulting from an ALTER TABLE operation which could not be completed. The relevant portion of the MySQL docs says:

If MySQL crashes in the middle of an ALTER TABLE operation, you may end up with an orphaned temporary table inside the InnoDB tablespace. Using the Table Monitor, you can see listed a table with a name that begins with #sql-. You can perform SQL statements on tables whose name contains the character “#” if you enclose the name within backticks. Thus, you can drop such an orphaned table like any other orphaned table using the method described earlier. To copy or rename a file in the Unix shell, you need to put the file name in double quotation marks if the file name contains “#”.

so I would simply issue a DROP TABLE on them. Note: Do not simply delete the files - otherwise you will get so called orphaned tables yielding database engine warnings like these:

InnoDB: in InnoDB data dictionary has tablespace id N,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
the-wabbit
  • 40,737
  • 13
  • 111
  • 174
  • Thanks this points in the right direction. I took a disk snapshot and run some tests on another mysql instance. Unfortunately doing > DROP TABLE `#sql-537f_8d5b`; returns ERROR 1051 (42S02): Unknown table '#sql-537f_8d5b'. I also tried different combinations like `\#sql-537f_8d5b`, '#sql-53f_8d5b' etc... Indeed when I moved the files I got the orphaned tables warnings. So I havent been successful yet but working on it..... – webgr Mar 06 '12 at 20:55
  • 7
    See http://bugs.mysql.com/bug.php?id=72135 You need to prefix the table with #mysql50#, i.e. DROP TABLE `#mysql50##sql-537f_8d5b` – Tobias M Sep 10 '14 at 05:49
3

i realized this can be temporary files.. the best would be if you could:

  • dump your whole database

mysqldump -uuser -ppass database > file

  • drop your database and re-create it from dump

cat file | mysql -uuser -ppass database

warning from the bottom still applies.

my initial answer:

do those names correspond to the names of your tables? most probably yes, if so - those are just the data files. innodb storage engine [ibd extension suggests you use it] does not shrink the data files ever. the only way to get them smaller is either to:

  • dump each table and re-create it:

mysqldump -uuser -ppass database tablename > file ; cat file | mysql -uuser -ppass database

  • run "optimize table tableName;" in mysql for each of the 'big' tables

warning - both operations will take a lot of time [very much depends on number of indices in the tables, your io subsystem; most probably we're talking about hours of time if not more]; will block reads. do not run this during production hours.

pQd
  • 29,981
  • 6
  • 66
  • 109
  • hi and many thanks for your detailed answer. Actually there are no tables with those names in the DB. Yeah if all else fails I can backup and reimport the database but that would produce some hours of downtime that I would like to avoid if possible. I ll probably end up copying the volume to another mysql instance and delete the files and see what happens. – webgr Feb 28 '12 at 11:16
  • if you have lvm [or other snapshot mechanism] - just take the snapshot of all the files [even with running mysql], restore them on another machine and start experimenting. – pQd Feb 28 '12 at 12:44
1

This can be temporary files e.g. during a long-lasting ALTER command.

  1. Make sure, that it is not still in use by a long-lasting command at the moment. by checking its size over time.

  2. if it doesn't grow, you can get rid of the table safely with the MySql DROP- command (don't just delete the file on the console, which could cause an orphaned table)

rubo77
  • 2,469
  • 4
  • 34
  • 66