MySql Filesize Limit Options

Question

We have a database that has hit the 4GB limit in a table (I believe this is a filesize limit of the server we have it on ~ Linux). Its a MySql MyISAM DB.

I am wondering what our options are at this point, for example, is there a way to compress the table, or increase the filesize somehow, or any other ideas? At this point, I am trying to determine my best course of action.

Thanks -

Answer 1

you have a couple options:

1) switch to ARCHIVE tables. they are INSERT only. you can add rows, but you can never UPDATE, and you can never DELETE, only INSERT and SELECT. and they are compressed.

2) switch to innodb tables, but first check your inno configuration. see http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html

3) if you're using mysql 5.1, implement partitioning.

also see this page from the mysql manual: http://dev.mysql.com/doc/refman/5.1/en/full-table.html

Answer 2

Here is what I did to fix the issue (in case anyone else has this same issue).

I issued the following command (note: table name is 'module'):

ALTER TABLE module MAX_ROWS=1000000000 AVG_ROW_LENGTH=50

This seems to have fixed the issue. As far as performance goes, so far, it seems fine (it is well indexed), but, only time will tell I suppose if this is a longterm fix, or just a short-term solution.

Thanks.