List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:February 28 2009 2:56pm
Subject:Re: MyISAM large tables and indexes managing problems
View as plain text  
Hi Rolando,
I am going to give it a try, but the thing is that the creation of index
with MyISAM table causes
a re-copy of the table (using temporary table) and so it is the same thing,
and seens to take a lot of time.
I stopped it after 10 hours or so.
I think is the way mysql manages the creation of indexes.

Anyway thank you and I will update you!

Claudio


2009/2/27 Rolando Edwards <redwards@stripped>

> 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