List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:February 27 2009 10:25pm
Subject:Re: MyISAM large tables and indexes managing problems
View as plain text  
Great Brent, helps a lot!
it is very good to know your experience.
I will speak to developers and try to see if there is the opportunity to 
apply the 'Divide et Impera' principle!
I am sorry to say MySQL it is a little out of control when dealing with 
huge tables, it is the first time I had to kill MySQL deamon a couple of 
times.

Thanks again Brent

Claudio



Brent Baisley wrote:
> I've used a similar setup and hit up to 400 million with 5-7 million
> records being added and deleted per day. Processing the table like you
> mention gave me the exact same result. The query was just too big for
> MySQL to handle. If you can break down your query into multiple
> smaller queries, it will run much quicker. I went from 7-8 hours down
> to 10 minutes. I broke my query into increments based on date/time and
> merged the results.
> I also switched to using MERGE tables so I could create a much narrow
> set of tables to query on (i.e. current_month). Of course, this means
> splitting your table into separate tables based on a certain criteria.
> Basically, divide and conquer.
>
> Hope that helps.
>
> Brent Baisley
>
> On Fri, Feb 27, 2009 at 4:42 PM, Claudio Nanni <claudio.nanni@stripped>
> wrote:
>   
>> 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