Simon:
This issue is version specific in its resolution.
Notwisthstanding some fairly large 'real' ones, I have built some
enormous dev mail databases with 'fake' users -- perl scripts pumping
'stuff' for days. I have had varying results depending on the MySQL
version, file system and the drive controllers. (MySQL 5.0.15 on UFS2
with hardware raid can give you some really twisted storage capabilities
at good speed.)
MySQL counts the null integers as bytes if they are used or not say for
example an empty varchar(50) field still uses 50 bytes. The maximum size
of a row in a |MyISAM| table is 65,534 bytes. Each |BLOB| and |TEXT|
column accounts for only five to nine bytes toward this size. You can
delve further at
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
This is what I have done without pain. 4 Gigabytes is actually not that
much is it?
mysql> ALTER TABLE dbmail_messageblks MAX_ROWS=10000000000
AVG_ROW_LENGTH=40000;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE dbmail_physmessage MAX_ROWS=10000000000
AVG_ROW_LENGTH=40000;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE dbmail_headername MAX_ROWS=10000000000
AVG_ROW_LENGTH=40000;
Query OK, 12 rows affected (0.02 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE dbmail_datefield MAX_ROWS=10000000000
AVG_ROW_LENGTH=40000;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
etc...
best...
Mike
Simon wrote:
It looks like we have reached the Max_data_length for the
dbmail_messageblks table, this is currently 4294967295 (which is 4GB
im gussing - which is about right). From the mysql docs, this can be
easliery solved by running:
ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;
Any idea what i would put in the AVG_ROW_LENGTH= here?
From the mysql docs: "You have to specify AVG_ROW_LENGTH only for
tables with BLOB or TEXT columns; in this case, MySQL can't optimize
the space required based only on the number of rows."
show table status from dbmail like 'dbmail_messageblks';
give me an AVG_ROW_LENGTH of 35381, is this what i use here?
Thanks!!!
Simon
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
