大家好，请教大家一个mysql5.1 InnoDB大数据表drop table的问题。基本情况如下：1. XP系统，200G存储空间，2G内存，MySql5.1数据库，InnoDB，独立表空间。2. 数据量很大，一张表ibd文件往往能存储到8-10G甚至更多。3. 为了不影响后续存储，会将过期的数据表删除，采用drop table的方法。这时的问题：drop很大的表时消耗的时间太长，会导致给mysql提交其他的存储语句阻塞太久，死锁，最后mysql都启动不起来了。因此想请教大家，像这种比较大的mysql innoDB表，用什么方式特殊处理一下drop table能快一些，尽量对数据库本身的影响小一些。在网上也查到一篇参考文章，是在linux之下采用硬连接。XP之下好像没有类似内容。因此也没法采用。感谢各位！
感谢回复。我看不太懂当时mysql的报错日志。看起来是drop操作阻塞了其他存储操作。最后可能是mysql自身的bug导致数据库自动不起来了，原因应该和这个drop操作有关系。
另外，再问一个blob类型的问题。我向blob字段存储一个字节数组。在进行查询的时候看到的都是乱码文字。怎么操作才能直接看到一个二进制数组的形式呢？就是这样：12 AB 3C 23 00……。是需要在存储还是查询的时候做修改？
130112 20:34:38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 40 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 155135, signal count 154835
Mutex spin waits 0, rounds 2827187, OS waits 91342
RW-shared spins 61579, OS waits 30633; RW-excl spins 21869, OS waits 20835
------------
TRANSACTIONS
------------
Trx id counter 0 53520238
Purge done for trx's n:o < 0 53412935 undo n:o < 0 0
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 53520237, COMMITTED IN MEMORY, OS thread id 892 committing, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
, undo log entries 1
MySQL thread id 303, query id 35844731 localhost 127.0.0.1 root freeing items
insert into 20130112org_data values(444,'20130112203438',484,'20130113054103',383,42600,6,'B2','32','11',517,1,'f7 f7 f7 f7 7a 02 ef f0 00 02 00 15 00 ed 01 20 13 01 12 20 34 40 01 04 10 90 41 3f 85 71 10 81 10 81 00 08 10 81 00 08 10 81 00 08 10 81 00 08 10 81 00 48 10 81 00 48 10 81 00 08 10 81 00 48 10 81 00 48 10 81 00 48 10 81 00 08 10 81 00 08 10 81 00 10 90 10 81 00 10 90 10 81 00 08 10 81 00 08 10 81 00 10 90 10 81 00 08 10 81 00 08 10 81 00 08 10 81 00 48 10 81 00 08 10 81 00 48 10 81 00 08 10 81 00 08 10 81 00 08 10 81 00 08 10 81 00 08 10 81 00 08 10 81 00 08 10 81 00 08 10 81 00 08
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 1; buffer pool: 0
347409 OS file reads, 38503968 OS file writes, 531877 OS fsyncs
1.22 reads/s, 16384 avg bytes/read, 19.50 writes/s, 0.30 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
11420 inserts, 11420 merged recs, 1105 merges
Hash table size 70657, node heap has 1 buffer(s)
26.85 hash searches/s, 11.32 non-hash searches/s
---
LOG
---
Log sequence number 13 303889618
Log flushed up to 13 303787558
Last checkpoint at 13 292969130
1 pending log writes, 0 pending chkp writes
35900299 log i/o's done, 17.45 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 25403674; in additional pool allocated 1825152
Dictionary memory allocated 110520
Buffer pool size 1088
Free buffers 0
Database pages 1087
Modified db pages 716
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 347384, created 2300809, written 2504155
1.22 reads/s, 1.42 creates/s, 1.85 writes/s
Buffer pool hit rate 993 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 1612, state: flushing log
Number of rows inserted 35556564, updated 0, deleted 0, read 56876854
17.62 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
InnoDB: ###### Diagnostic info printed to the standard error stream
InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.
130112 20:34:48 InnoDB: Assertion failure in thread 1604 in file .\srv\srv0srv.c line 2236
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
130112 20:34:48 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.key_buffer_size=10485760
read_buffer_size=65536
max_used_connections=45
max_threads=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 42881 K
bytes of memory
InnoDB: Thread 892 stopped in file .\lock\lock0lock.c line 3588
Hope that's ok; if not, decrease some variables in the equation.thd: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
005B5F19 mysqld.exe!srv_error_monitor_thread()[srv0srv.c:2236]
7C80B713 kernel32.dll!GetModuleFileNameA()
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
InnoDB: Thread 1612 stopped in file .\sync\sync0arr.c line 337
InnoDB: Thread 1600 stopped in file .\os\os0sync.c line 271
130113 11:31:49 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Log scan progressed past the checkpoint lsn 13 294301259
130113 11:31:50 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 13 299544064
130113 11:31:52 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8
1：分批删除数据。比如表有1000W条记录，循环删除，一次删除10W条，全部删除后，再drop table2:过期数据，drop table，好像是说这个表的数据都是过期的了？