As we all know, MYSQL's slow droptable operations know, Ext3 is not the most effective file system. for example, deleting files is very slow (it is really a painful process, isn't it ?), Resulting in a large number of random I/O. However, sometimes it affects MySQL performance better than you think. So when and why?
When you run
DROP TABLE
There are several things to do: write lock on the table so that it will not be used by other threads; the storage engine deletes data files; of course, MySQL will delete the table definition file (. frm file ). This is not all things. there is another thing that needs to be done:
Code:
VOID (pthread_mutex_lock (& LOCK_open ));
Error = mysql_rm_table_part2 (thd, tables, if_exists, drop_temporary, 0, 0 );
Pthread_mutex_unlock (& LOCK_open );
The code for this entire table deletion operation is
LOCK_open
Surrounded by mutex semaphores. This mutex semaphores have been used in many places in MySQL, but mainly when the table is enabled or disabled. This means that when
LOCK_open
When locking, no query statements can be executed because they prevent any access.
This explains when deleting 10 GB files on the ext3 file system is a painful wait. Deleting a 10 GB file will last for a period of time. if this is a MySQL table, the mutex will always exist during this period, and this mutex will delay all queries.
Plain text
Code:
+ ―