Archive for 24th December 2010

Rare MyISAM failing to delete MYD file for temporary tables.

I manage a few systems that every hundred million disk temporary table deletions or so one of them will fail. MySQL will delete the MYI file but leave the MYD behind. It’s very strange. There isn’t an error in the error log but subsequent queries that try to use the same temporary table name will error because the MYD file still exists. The queries fail with an error like ERROR 1 (HY000): Can’t create/write to file ‘/tmp/#sql_25d1_0.MYD’. Fortunately the client error gives the temporary table name so it’s easy to clean up by deleted the MYD file. While I still don’t know what the root cause is but I was able to patch MySQL to fix the issue.

The old mi_delete_table function which is responsible for deleting only the MYI and MYD file would delete the MYI file and leave the MYD file behind. This function would try to delete the MYI file, get an error back from the filesystem and not attempt to delete the corresponding MYD file. The strange part is that the file is actually deleted. The error is captured in mi_delete_table but is lost by the functions calling it. I’ve tried to patch some parts of the temporary table code to try to track down the actual filesystem error but haven’t seen any errors returned. I don’t think I’ve correctly patched all the temporary table code to capture the error. This hasn’t been a priority because the patch clears up the symptom of the issue which is the MYD file being left behind.

In my new mi_delete_table function I try to delete both the MYD and MYI file before returning an error. This has fixed the issue of MYD files being left behind but I still don’t know the root cause of why they weren’t being deleted in the first place. I’m not sure about filing a bug for this because even though I think my mi_delete_table is better than the stock one I can’t really reproduce the issue.

Anyway here is the patch if anyone is interested.

Second draft of the per session row and index stats patch

I’ve taken the part of the tivo patch that includes table and index statistics and broken it out into it’s own patch. This patch includes the ability to do show [session | global ] table_statistics and the same for index_statistics. In this version the row stats are also logged in the slow query log. To log per query stats I had to track them separately from the per session stats. Because the tracking was already done for the slow query log I’ve modified the command to allow uses to access row stats for the previous query separate from the sum for the current session. The flush commands also act similarly.

Along with changing the slow query log format I’ve also change it to log the timestamp with every query. This made it easier to do automated parsing of the slow query log.

The queries now support three different modes. I’ve detailed how the show commands work in a previous post. This patch has helped developers diagnose numerous query problems that weren’t obvious by the row estimates in the explain output. I highly recommend it for users who run more complicated queries in mysql and have been having a hard time diagnosing why they are slow. This has proved particularly adept at finding misbehaving sub queries and bad join optimization which doesn’t show up in explain output. As I find example queries I will try to post them here.

The patch.