I hate the checking permissions issue.
You may have to disable key checks before the DROP DATABASE
SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;
UPDATE 2013-04-15 18:04 EDT
I just noticed you have innodb_file_per_table OFF. What gives ?
- You currently have all the InnoDB data and the corresponding index sitting in a single file.
- Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
- Internal Fragmentation of ibdata1
- Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
- See Pictorial Representation of ibdata1 to see everything that goes into ibdata1
Recommendation : Remove all Data and Index Pages from ibdata1
This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.
You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.
UPDATE 2013-04-22 08:10 EDT
Three suggestions
SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.
SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.
SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.
Right now, you are in a very fortunate position. I noticed you have big-tables
defined. This is preventing you from experiencing "Table is Full" errors. Why is this good?
Whenever you get "Repair With Keycache" as a status, you have no free space to do file sorting. Making sort_buffer_size bigger isn't necessarily the answer since temp tables become disk files immediately.
You have two options
OPTION #1 : Increase Diskspace for datadir
The data volume where /var/lib/mysql
(or whatever datadir
is) resides may not have enough room to house a materialized temp table on disk. I would suggest increasing the disk volume's size to, at least, twice its size.
DRAWBACK : A one-time maintenance to move the database to the bigger disk.
OPTION #2 : Separate Disk for Temp Tables
Perhaps having a separate disk volume who sole purpose in life is to house temp tables should be set up. Try this
- Step 01 : Install a disk with the same size as the home of
datadir
- Step 02 :
mkdir /tmptables
- Step 03 : Mount the new disk volume to the folder
/tmptables
- Step 04 :
chown mysql:mysql /tmptables
- Step 05 : Add this to
my.cnf
- Step 06 :
service mysql restart
Once you make the disk and add tmpdir
, you should have more elbow room.
DRAWBACK : Transferring of the temp tables contents from /tmptables
back to the home of datadir
for certain SQL commands (such as DDL).
UPDATE 2013-05-21 00:10 EDT
You simply don't have enough memory. All the cores in the world cannot help MyISAM.
SUGGESTION #1 : Shorten your keys
I can tell from the keys that you are trying to retrieve all data from the indexes and avoid touching the table. All well and good IF IT WEREN'T FOR THE BILLIONS OF ROWS.
Here is something worth considering: Shorten the keys for each index
CREATE TABLE `research_storage1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`word1` mediumint(8) unsigned NOT NULL,
`word2` mediumint(8) unsigned NOT NULL,
`origyear` smallint(5) unsigned NOT NULL,
`cat` tinyint(3) unsigned NOT NULL,
`pibn` int(10) unsigned NOT NULL,
`page` smallint(5) unsigned NOT NULL,
`pos` smallint(5) unsigned NOT NULL,
`num` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `pibnpage` (`pibn`,`page`),
KEY `word21` (`word2`,`word1`),
KEY `cat1` (`cat`,`word1`),
KEY `year1` (`origyear`,`word1`),
KEY `catyear1` (`cat`,`origyear`),
KEY `pibn` (`pibn`,`word1`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin
DATA DIRECTORY='/storage/researchdb/'
INDEX DIRECTORY='/storage/researchdb/';
SUGGESTION #2 : Stop using ALTER TABLE...ENABLE KEYS;
You should try to get the MyISAM imported out into a new table without using ENABLE KEYS
.
CREATE TABLE `research_storagenew` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`word1` mediumint(8) unsigned NOT NULL,
`word2` mediumint(8) unsigned NOT NULL,
`origyear` smallint(5) unsigned NOT NULL,
`cat` tinyint(3) unsigned NOT NULL,
`pibn` int(10) unsigned NOT NULL,
`page` smallint(5) unsigned NOT NULL,
`pos` smallint(5) unsigned NOT NULL,
`num` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `pibnpage` (`pibn`,`page`,`word2`,`word1`),
KEY `word21pibn` (`word2`,`word1`,`pibn`,`num`),
KEY `word12num` (`word1`,`word2`,`num`),
KEY `cat1` (`cat`,`word1`),
KEY `year1` (`origyear`,`word1`),
KEY `catyear1` (`cat`,`origyear`,`word1`),
KEY `pibn` (`pibn`,`word1`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin
DATA DIRECTORY='/storage/researchdb/'
INDEX DIRECTORY='/storage/researchdb/';
INSERT INTO `research_storagenew` SELECT * FROM `research_storage1`;
DROP TABLE `research_storage1`;
ALTER TABLE `research_storagenew` RENAME `research_storage1`;
SUMMARY
Look at the table definition again. There is 18 bytes for an index entry on just the pibnpage
index. That's 18G per billion rows. Same goes with word21pibn
. You just don't have enough room. It is imperative to try one of my newest suggestion to bypass the need to sort all these keys.
UPDATE 2013-05-22 12:15 EDT
YoU asked
About shortening the keys though: what kind of performance hit will this result in? Are we talking twice as long, 10x as long, 1000x as long?
I cannot say for sure what running time impact there will. However, I can say this: There may be some additional disk I/O because the indexes will no longer contain the needed column info. Queries will not have to turn to the .MYD
file to retrieve additional column information. Please keep in mind that MyISAM is suitable for heavy-read queries.
The only tuning I can further recommend if there is supposed to be INSERTs and DELETEs in the middle of the day during heavy-read periods would be to enable concurrent INSERTs.
[mysqld]
concurrent_insert=1
This will allow INSERTs into MyISAM without cross checking for free blocks within the table. This may make the table grow a little faster.
As far as maintenance goes, you must use SUGGESTION #2
as not rely so much on ALTER TABLE ... ENABLE KEYS;
on such bloated table. Perhaps you should think of moving /storage/researchdb/
to SSD.
Best Answer
If you are using innodb tables, try to raise
innodb_buffer_pool_size
as much as you can. You must balance the ram from mysql and other tools you use for development. The idea is to don't have a swapping machine and to use memory resource as best as possible.