List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:February 28 2009 2:57pm
Subject:Re: MyISAM large tables and indexes managing problems
View as plain text  
Yes I killed several times the query but now way, the server was continuing
to hog disk space and not even shutdown worked!
Thanks!
Claudio



2009/2/27 Brent Baisley <brenttech@stripped>

> MySQL can handle large tables no problem, it's large queries that it
> has issues with. You couldn't just kill the query instead of killing
> MySQL?
> use show processlist to get the query id, then kill it. You may
> already know that.
>
> Brent
>
> On Fri, Feb 27, 2009 at 5:25 PM, Claudio Nanni <claudio.nanni@stripped>
> wrote:
> > 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