List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:February 27 2009 10:32pm
Subject:RE: MyISAM large tables and indexes managing problems
View as plain text  
Have you tried disabling indexes while loading?
Here is what I mean...

CREATE TABLE tb1 (A INT NOT NULL AUTO INCREMENT PRIMARY KEY,B VARCHAR(20),C VARCHAR(10));

Load tb1 with data

Create a new table, tb2, with new structure (indexing B and C columns)
CREATE TABLE tb2 LIKE tb1;
ALTER TABLE tb2 ADD INDEX NDX1 (B);
ALTER TABLE tb2 ADD INDEX NDX2 (C);

Load tb2 with non-unique indexes turned off

ALTER TABLE tb2 DISABLE KEYS;
INSERT INTO tb2 SELECT * FROM tb1;

Only the Primary Key got loaded in tb2
Now build the other two indexes

ALTER TABLE tb2 ENABLE KEYS;

This should build the indexes linearly, loading key entries into the .MYI file of the
MyISAM table.

Give it a try !!!


Rolando A. Edwards
MySQL DBA (CMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM : RolandoLogicWorx
Skype : RolandoLogicWorx
redwards@stripped


-----Original Message-----
From: Claudio Nanni [mailto:claudio.nanni@stripped] 
Sent: Friday, February 27, 2009 4:43 PM
To: mysql@stripped
Subject: MyISAM large tables and indexes managing problems

Hi,
I have one 15GB table with 250 million records and just the primary key,
it is a very simple table but when a report is run (query) it just takes
hours,
and sometimes the application hangs.
I was trying to play a little with indexes and tuning (there is not great
indexes to be done though)
but eveytime I try to alter table for indexes it just hogs the disk space
and takes hours
to try to build indexes in various passages(.TMD) but it is a real pain
since I cannot even kill the mysql process,
and I had to kill the server with table corruption and had to stop/start and
repair table.
Does anybody experience problems in managing a simple MyISAM table with  250
million records and a primary key?
I tried also to duplicate the table, add indexes and insert into it (also
using INNODB for the new table) but it is really
taking ages everytime. And I had to move the 'tmpdir' to the data partition
because it was filling the / 100%.

MySQL is 5.0.x on 64bit RHEL 5 with 16GB RAM and NAS storage.

Any hint on how to manage big tables?

Thanks

Claudio Nanni
Thread
MyISAM large tables and indexes managing problemsClaudio Nanni27 Feb
  • RE: MyISAM large tables and indexes managing problemsRolando Edwards27 Feb
    • Re: MyISAM large tables and indexes managing problemsClaudio Nanni28 Feb
Re: MyISAM large tables and indexes managing problemsClaudio Nanni27 Feb
Re: MyISAM large tables and indexes managing problemsClaudio Nanni28 Feb
  • Re: MyISAM large tables and indexes managing problemsBaron Schwartz1 Mar
    • Re: MyISAM large tables and indexes managing problemsClaudio Nanni1 Mar
      • Re: MyISAM large tables and indexes managing problemsBrent Baisley1 Mar