WTA Increasing InnoDB Speed

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@gmail.com
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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: WTA Increasing InnoDB Speed

--001636c5ade725083f049344cbc6
Content-Type: text/plain; charset=ISO-8859-1

Hi Willy,

Try percona server. It gives better performance than mysql.

Krishna

On Sat, Oct 23, 2010 at 3:37 AM, Willy Mularto 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@gmail.com
> 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=prajapatikc@gmail.com
>
>

--001636c5ade725083f049344cbc6--

Re: WTA Increasing InnoDB Speed

--0022152d6c910f948004935af32a
Content-Type: text/plain; charset=ISO-8859-1

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.

On Sat, Oct 23, 2010 at 10:46 AM, Krishna Chandra Prajapati <
prajapatikc@gmail.com> wrote:

> Hi Willy,
>
> Try percona server. It gives better performance than mysql.
>
> Krishna
>
> On Sat, Oct 23, 2010 at 3:37 AM, Willy Mularto
> 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@gmail.com
> > 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=prajapatikc@gmail.com
> >
> >
>



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0022152d6c910f948004935af32a--

Re: WTA Increasing InnoDB Speed

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@gmail.com> wrote:
>
> > Hi Willy,
> >
> > Try percona server. It gives better performance than mysql.
> >
> > Krishna
> >
> > On Sat, Oct 23, 2010 at 3:37 AM, Willy Mularto
> > 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@gmail.com
> > > 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=prajapatikc@gmail.com
> > >
> > >
> >
>
>
>
>--
>Bier met grenadyn
>Is als mosterd by den wyn
>Sy die't drinkt, is eene kwezel
>Hy die't drinkt, is ras een ezel


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: WTA Increasing InnoDB Speed

--005045015678ca9af504936d353e
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Oct 25, 2010 at 6:25 AM, mos wrote:

> 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
>

I didn't because I'm not the one with the problem, Willy is :-)

Thanks for the info, though.



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--005045015678ca9af504936d353e--