Re: MySQl crashes several times per day with a table > 300 Mb

[Davide Iudica]

I don't know if you solved the problem in the meantime, and if so please send me the solution.
We have the same problem on a 4-cpu Solaris with Invision and the ibf_posts growing. Actually the table is at 330Mb and its indexes are about 170Mb but in a few hours indexes become 280Mb and active connections rapidly (few seconds) increase 'til the limit of max_connections and we have to re-start Mysql doing a OPTIMIZE TABLE command returning to the previous size.
As Peter said we see many updates on the table going suddenly very slow (60 seconds and more) causing many select to wait for lock release on the Myisam table.
It seems as the indexes were not balanced or with many holes and becoming very poor in performance.
We use Mysql Administrator for top session monitoring and have max_connections to 1500 (we tried to increase them but the 4-cpu were at the 100% hanging the server). We set also the slow query parameter.
Mysql is 4.1.
Invision doesn't suggest to use Innodb for lack of indexing on text which is very important in a Forum app (infact a solution could be to use a row locking mechanism to free the selects, also if updates are very slow).