Mysql – Need help in increasing maximum table size

myisamMySQLsize;table

Two of the tables vb_post, vb_profilevisitor getting corrupted again and again.

The hosting company suggested the following:

Looks like the problem is that the .MYD table was converted to a .TMD
table as per the below article:

http://www.raskas.be/blog/2008/04/08…and-tmd-files/

".TMD basically a temparory file of .MYD. it gets converted into .TMD
if the data size of the table has exceeded the actual size of the
data. in that case you can alter the table and increase the MAX size
of the table."

We renamed the table back to the .MYD extension, but please check on
the MAX size of the the table.

May i know how to do it.
I want to change for two tables,

vb_post  
vb_profilevisitor

This is my present values in database:

vb_profilevisitor: present Rows: 11995 Avg_row_length: 15
Max_data_length: 64424509439 vb_post: present Rows: 1325242
Avg_row_length: 1514 Max_data_length: 281474976710655

Can I alter it and increase the maximum table size with the following.

ALTER TABLE `vb_profilevisitor` MAX_ROWS=98281474976710655 AVG_ROW_LENGTH=1000000000;
ALTER TABLE `vb_post` MAX_ROWS=98281474976710655 AVG_ROW_LENGTH=1000000000;

Need quick replies.

Best Answer

Sounds like MISSION: IMPOSSIBLE. Just kidding. I just cringe when I see MyISAM.

I can tell you what the MySQL Documentation says. Please note what it says:

You are using a MyISAM table and the space required for the table exceeds what is permitted by the internal pointer size. MyISAM permits data and index files to grow up to 256TB by default, but this limit can be changed up to the maximum permissible size of 65,536TB (2567 – 1 bytes).

With that in mind, you would have to change myisam_data_pointer_size to 7

First, add this to my.cnf under the [mysqld] group header

myisam_data_pointer_size=7

Then, run this in mysql

SET GLOBAL myisam_data_pointer_size = 7;
SET myisam_data_pointer_size = 7;

This will allow your current session and new connections to have the extended capacity and permit you to safely run those ALTER TABLE statements.

Give it a Try !!!

UPDATE 2014-12-03 00:51 EST

I was just thinking. The MyISAM tables vb_profilevisitor and vb_post may not like the ALTER TABLE because they were created when myisam_data_pointer_size was 6.

To change it manually, do the following

CREATE TABLE vb_post_new LIKE vb_post;
ALTER TABLE  vb_post_new MAX_ROWS=98281474976710655 AVG_ROW_LENGTH=1000000000;
CREATE TABLE vb_profilevisitor_new LIKE vb_profilevisitor;
ALTER TABLE  MAX_ROWS=98281474976710655 AVG_ROW_LENGTH=1000000000;

Check the stats on vb_profilevisitor_new and vb_post_new

SELECT table_name,max_data_length,avg_row_length
FROM information_schema.tables
WHERE table_name IN ('vb_post_new','vb_profilevisitor_new');

If the max_data_length and avg_row_length changed, then copy the table data and swap new tables in

INSERT INTO vb_profilevisitor_new SELECT * FROM vb_profilevisitor;
RENAME TABLE
    vb_profilevisitor TO vb_profilevisitor_zap,
    vb_profilevisitor_new TO vb_profilevisitor
;
DROP vb_profilevisitor_zap;
INSERT INTO vb_post_new SELECT * FROM vb_post;
RENAME TABLE
    vb_post TO vb_post_zap,
    vb_post_new TO vb_post
;
DROP vb_profilevisitor_zap;
Related Question