List:General Discussion« Previous MessageNext Message »
From:mos Date:October 25 2010 4:25am
Subject:Re: WTA Increasing InnoDB Speed
View as plain text  
At 06:12 AM 10/24/2010, you wrote:
>Regardless of that, it would be nice to know what the parameters are that
>cause this slowdown - some people may be stuck with the default version -
>companies with a support contract come to mind.

You didn't say whether the slowdown occurs when
1) adding new rows to the table, or
2) when querying the table with a Select statement.

If the problem is #1, then I suspect it is caused by having to maintain a 
larger and larger index as more rows are added to the table. It always 
takes longer to add a row to a table with 150 million rows than a table 
with 150 rows. If you drop all indexes to the table you'll probably find 
adding rows to a large table will be quite fast.  The only way to increase 
performance is to maintain only the minimum # of indexes necessary.

If the problem is #2, you could try and optimize the Innodb table with an 
Optimize command (which really executes and Alter table behind the scenes 
for Innodb). I don't know if this will help much because Innodb will 
balance the btree better than MyISAM tables. However if you are deleting a 
lot of rows from the table, then you can run Optimize to remove the deleted 
rows. Optimizing an Innodb table can take quite a while because it needs to 
rebuild the table. You can also try "myisamchk --analyze" to gather 
statistics on the index.

Mike



>On Sat, Oct 23, 2010 at 10:46 AM, Krishna Chandra Prajapati <
>prajapatikc@stripped> wrote:
>
> > Hi Willy,
> >
> > Try percona server. It gives better performance than mysql.
> >
> > Krishna
> >
> > On Sat, Oct 23, 2010 at 3:37 AM, Willy Mularto <sangprabv@stripped>
> > wrote:
> >
> > > Dear List,
> > > I have MySQL 5.14 installed on Dell R710 32GB RAM 600GB SAS HDD with
> > Ubuntu
> > > 10.04 64 Bit. I deploy InnoDB as my default engine. The server is a high
> > > load server. On a fresh install and empty table it can insert around 5
> > > millions new records per day average. But when the table getting fat the
> > > performance starting to drop around 5% per day. It forces me to re create
> > an
> > > empty table each month. So how to keep my server has a stable
> > performance?
> > > Many thanks for the tips.
> > >
> > >
> > >
> > >
> > > sangprabv
> > > sangprabv@stripped
> > > http://www.petitiononline.com/froyo/
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > > http://lists.mysql.com/mysql?unsub=1
> > >
> > >
> >
>
>
>
>--
>Bier met grenadyn
>Is als mosterd by den wyn
>Sy die't drinkt, is eene kwezel
>Hy die't drinkt, is ras een ezel

Thread
WTA Increasing InnoDB SpeedWilly Mularto23 Oct
  • Re: WTA Increasing InnoDB SpeedKrishna Chandra Prajapati23 Oct
    • Re: WTA Increasing InnoDB SpeedJohan De Meersman24 Oct
      • Re: WTA Increasing InnoDB Speedmos25 Oct
        • Re: WTA Increasing InnoDB SpeedJohan De Meersman25 Oct