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 sangpr...@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=arch...@jab.org
Re: WTA Increasing InnoDB Speed
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 > sangpr...@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=prajapat...@gmail.com > >
Re: WTA Increasing InnoDB Speed
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 < prajapat...@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 > > sangpr...@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=prajapat...@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
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 < prajapat...@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 > > sangpr...@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=prajapat...@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=arch...@jab.org
Re: WTA Increasing InnoDB Speed
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
