Slow edit on big MyISAM table - i need a solution please

Hello

I have a forum with 1.400.000 posts and increase daily by 5.000 new posts and i want a solution as when i edit any of these is very slow as the table is big.

I was thinking to convert the table to Innodb but i am not sure if this is the best solution for the future also....

The read of the table is very fast and the problem is only the edit.

The server is powerfull with 16gb ram and 2x quad xeon.

Thank you

 

There are many possibilities like converting MyISAM to InnoDB (this will not work if your table has FULLTEXT index in it), partition the table if you have MySQL 5.1.

 

Yes i am using V5.1.54.

Can you give me more details please?

Thank you

 

Initially I recommend changing MyISAM to InnoDB. Do you have FULLTEXT index in the table? If you don't know what I mean then run the following command in MySQL and paste the output here.

SHOW CREATE TABLE table_name;

Code (markup):

To know more about partitioning visit http://dev.mysql.com/doc/refman/5.1/en/partitioning.html.

 

Are you using any ready available forum software? Or you have a custom one?
Generally all forums have full text index for search options and hence converting MyISAM table into InnoDB won't be a good idea, unless of course you are planning to move search from mysql to Sphinx.

Do share more info about the forum to help you better.

 

Yes it is invision forum v2.3.x and yes i use already sphinx

 

If you already using index then you can drop FULLTEXT index from the existing table (if exists) and convert MyISAM to InnoDB. InnoDB is resource intensive as compared to MyISAM but you have already mentioned that you have a dedicated server. This should not be a problem. Before this make sure InnoDB is enabled or not by running the following query

SHOW ENGINES;

Code (markup):

What do you say mastermunj?

 

MyISAM table gets table level lock when insert / update is happening. Invision board is programmed well and won't slow down with just 1.4 million records.

I would recommend you to first discuss this problem on Invision board support forum as you will find webmasters there who would have faced similar problem earlier and resolved it successfully.

 

http://blogs.oracle.com/mysql/2011/01/comparing_innodb_to_myisam_performance.html

Here's an article from yesterday about scalability of Myisam vs InnoDB. Myisam basically has none...

 

Did you consult invision forum?

InnoDB performance in MySQL 5.5 is much better than in MySQL 5.1. But it is still preferred if you have MySQL 5.1.

 

Yes digital point about 10 seconds.