Hello,

I was wondering why the partition is almost full, since the databases
aren't very big. Then I noticed these files in the database directory:

-rw-rw---- 1 mysql mysql 4310560 Dec 24 10:13 #sql_10d4_0.MYD
-rw-rw---- 1 mysql mysql 1024 Dec 24 10:13 #sql_10d4_0.MYI
-rw-rw---- 1 mysql mysql 17182880 Jan 10 01:28 #sql_10e0_0.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 10 01:28 #sql_10e0_0.MYI
-rw-rw---- 1 mysql mysql 20853120 Mar 17 10:55 #sql_10e6_0.MYD
-rw-rw---- 1 mysql mysql 1024 Mar 17 10:55 #sql_10e6_0.MYI
-rw-rw---- 1 mysql mysql 19048320 Feb 20 07:35 #sql_10ef_0.MYD
-rw-rw---- 1 mysql mysql 1024 Feb 20 07:35 #sql_10ef_0.MYI
-rw-rw---- 1 mysql mysql 25702240 Jan 10 03:23 #sql_10fa_0.MYD
-rw-rw---- 1 mysql mysql 1024 Jan 10 03:23 #sql_10fa_0.MYI
-rw-rw---- 1 mysql mysql 8159360 Mar 17 11:17 #sql_1111_0.MYD
-rw-rw---- 1 mysql mysql 1024 Mar 17 11:17 #sql_1111_0.MYI
-rw-rw---- 1 mysql mysql 7724800 Mar 8 18:11 #sql_1119_0.MYD
-rw-rw---- 1 mysql mysql 1024 Mar 8 18:11 #sql_1119_0.MYI
-rw-rw---- 1 mysql mysql 17927360 Feb 6 04:40 #sql_1125_0.MYD
-rw-rw---- 1 mysql mysql 1024 Feb 6 04:40 #sql_1125_0.MYI
-rw-rw---- 1 mysql mysql 11600160 Mar 3 12:59 #sql_1126_0.MYD
-rw-rw---- 1 mysql mysql 1024 Mar 3 12:59 #sql_1126_0.MYI
-rw-rw---- 1 mysql mysql 0 Mar 3 12:59 #sql_1126_1.MYD
-rw-rw---- 1 mysql mysql 1024 Mar 3 12:59 #sql_1126_1.MYI
-rw-rw---- 1 mysql mysql 25076800 Dec 24 10:28 #sql_1129_0.MYD
-rw-rw---- 1 mysql mysql 1024 Dec 24 10:28 #sql_1129_0.MYI
-rw-rw---- 1 mysql mysql 27626080 Mar 8 18:19 #sql_1135_0.MYD
-rw-rw---- 1 mysql mysql 1024 Mar 8 18:19 #sql_1135_0.MYI

There a really dozends of it, currently about 5000 files using 45 GB (!)
of the partition. I thought these might be files from temporary tables,
but then I restartet mysql so it should remove them, but they didn't
disappear.

What are these files from and how can I get rid of it? Can I simply
delete them?

And how can I be sure that they don't appear again?

Regards
Marten

Marten, in my experience, these are most often temporary files leftover from
an incomplete operation.

They might be from a long-running query that was canceled, or from a table
repair operation that errored out or was interrupted.

In my experience it is safe to delete them, provided you take care to not
delete one that is in use (i.e. mysql is actively writing to it). The
modification dates should let you watch for that.

Hope this s.

-Dan


On Sun, Mar 30, 2008 at 10:57 AM, Marten Lehmann <lehmann@cnm.de> wrote:

> Hello,
>
> I was wondering why the partition is almost full, since the databases
> aren't very big. Then I noticed these files in the database directory:
>
> -rw-rw---- 1 mysql mysql 4310560 Dec 24 10:13 #sql_10d4_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Dec 24 10:13 #sql_10d4_0.MYI
> -rw-rw---- 1 mysql mysql 17182880 Jan 10 01:28 #sql_10e0_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Jan 10 01:28 #sql_10e0_0.MYI
> -rw-rw---- 1 mysql mysql 20853120 Mar 17 10:55 #sql_10e6_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Mar 17 10:55 #sql_10e6_0.MYI
> -rw-rw---- 1 mysql mysql 19048320 Feb 20 07:35 #sql_10ef_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Feb 20 07:35 #sql_10ef_0.MYI
> -rw-rw---- 1 mysql mysql 25702240 Jan 10 03:23 #sql_10fa_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Jan 10 03:23 #sql_10fa_0.MYI
> -rw-rw---- 1 mysql mysql 8159360 Mar 17 11:17 #sql_1111_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Mar 17 11:17 #sql_1111_0.MYI
> -rw-rw---- 1 mysql mysql 7724800 Mar 8 18:11 #sql_1119_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Mar 8 18:11 #sql_1119_0.MYI
> -rw-rw---- 1 mysql mysql 17927360 Feb 6 04:40 #sql_1125_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Feb 6 04:40 #sql_1125_0.MYI
> -rw-rw---- 1 mysql mysql 11600160 Mar 3 12:59 #sql_1126_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Mar 3 12:59 #sql_1126_0.MYI
> -rw-rw---- 1 mysql mysql 0 Mar 3 12:59 #sql_1126_1.MYD
> -rw-rw---- 1 mysql mysql 1024 Mar 3 12:59 #sql_1126_1.MYI
> -rw-rw---- 1 mysql mysql 25076800 Dec 24 10:28 #sql_1129_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Dec 24 10:28 #sql_1129_0.MYI
> -rw-rw---- 1 mysql mysql 27626080 Mar 8 18:19 #sql_1135_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Mar 8 18:19 #sql_1135_0.MYI
>
> There a really dozends of it, currently about 5000 files using 45 GB (!)
> of the partition. I thought these might be files from temporary tables,
> but then I restartet mysql so it should remove them, but they didn't
> disappear.
>
> What are these files from and how can I get rid of it? Can I simply
> delete them?
>
> And how can I be sure that they don't appear again?
>
> Regards
> Marten
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>

Marten, in my experience, these are most often temporary files leftover from
an incomplete operation.

They might be from a long-running query that was canceled, or from a table
repair operation that errored out or was interrupted.

In my experience it is safe to delete them, provided you take care to not
delete one that is in use (i.e. mysql is actively writing to it). The
modification dates should let you watch for that.

Hope this s.

-Dan


On Sun, Mar 30, 2008 at 10:57 AM, Marten Lehmann <lehmann@cnm.de> wrote:

> Hello,
>
> I was wondering why the partition is almost full, since the databases
> aren't very big. Then I noticed these files in the database directory:
>
> -rw-rw---- 1 mysql mysql 4310560 Dec 24 10:13 #sql_10d4_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Dec 24 10:13 #sql_10d4_0.MYI
> -rw-rw---- 1 mysql mysql 17182880 Jan 10 01:28 #sql_10e0_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Jan 10 01:28 #sql_10e0_0.MYI
> -rw-rw---- 1 mysql mysql 20853120 Mar 17 10:55 #sql_10e6_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Mar 17 10:55 #sql_10e6_0.MYI
> -rw-rw---- 1 mysql mysql 19048320 Feb 20 07:35 #sql_10ef_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Feb 20 07:35 #sql_10ef_0.MYI
> -rw-rw---- 1 mysql mysql 25702240 Jan 10 03:23 #sql_10fa_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Jan 10 03:23 #sql_10fa_0.MYI
> -rw-rw---- 1 mysql mysql 8159360 Mar 17 11:17 #sql_1111_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Mar 17 11:17 #sql_1111_0.MYI
> -rw-rw---- 1 mysql mysql 7724800 Mar 8 18:11 #sql_1119_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Mar 8 18:11 #sql_1119_0.MYI
> -rw-rw---- 1 mysql mysql 17927360 Feb 6 04:40 #sql_1125_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Feb 6 04:40 #sql_1125_0.MYI
> -rw-rw---- 1 mysql mysql 11600160 Mar 3 12:59 #sql_1126_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Mar 3 12:59 #sql_1126_0.MYI
> -rw-rw---- 1 mysql mysql 0 Mar 3 12:59 #sql_1126_1.MYD
> -rw-rw---- 1 mysql mysql 1024 Mar 3 12:59 #sql_1126_1.MYI
> -rw-rw---- 1 mysql mysql 25076800 Dec 24 10:28 #sql_1129_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Dec 24 10:28 #sql_1129_0.MYI
> -rw-rw---- 1 mysql mysql 27626080 Mar 8 18:19 #sql_1135_0.MYD
> -rw-rw---- 1 mysql mysql 1024 Mar 8 18:19 #sql_1135_0.MYI
>
> There a really dozends of it, currently about 5000 files using 45 GB (!)
> of the partition. I thought these might be files from temporary tables,
> but then I restartet mysql so it should remove them, but they didn't
> disappear.
>
> What are these files from and how can I get rid of it? Can I simply
> delete them?
>
> And how can I be sure that they don't appear again?
>
> Regards
> Marten
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>

Hello,

> Marten, in my experience, these are most often temporary files leftover
> from an incomplete operation.
>
> They might be from a long-running query that was canceled, or from a
> table repair operation that errored out or was interrupted.
>
> In my experience it is safe to delete them, provided you take care to
> not delete one that is in use (i.e. mysql is actively writing to it).
> The modification dates should let you watch for that.

thanks. Mysql 5 seems a bit buggy then, because I have never seen this
behavior in mysql 4.1. And from the logs I couldn't see that mysql
crashed or restartet. In fact, our mysql 5 server is mostly idling
around (in contrary to our mysql 4.1 servers).

Kind regards
Marten