Menu
▾
▴
Re: [Bacula-users] Solution for "The table 'File' is full" with mysql 4.1
|
From: George R. K. <ge...@ne...> - 2006-03-02 21:50:15
|
OK....so what am I missing here, I'm not a MySQL DBA so have no clue what I'd need to do to fix this one.... George >Mmh, for me it works. Have a look: > >Before: > >MyISAM file: /mnt/File.MYI >Record format: Packed >Character set: latin1 (8) >File-version: 1 >Creation time: 2005-10-25 14:44:09 >Recover time: 2006-02-15 11:23:43 >Status: changed >Auto increment key: 1 Last value: 48478197 >Data records: 41446968 Deleted blocks: 32 >Datafile parts: 41535171 Deleted data: 3456 >Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4 >Datafile length: 4294967248 Keyfile length: 1268716544 >Max datafile length: 4294967294 Max keyfile length: 4398046510079 >Recordlength: 43 > >table description: >Key Start Len Index Type Rec/key Root >Blocksize >1 1 4 unique unsigned long 1 1024 >1024 >2 17 4 multip. unsigned long 23 323361792 >1024 > 13 4 unsigned long 6 > 3 9 4 multip. unsigned long 30453 808448000 > 1024 > > >Run: >mysql> alter table File max_rows=200000000000; >Query OK, 41446968 rows affected (28 min 47.36 sec) >Records: 41446968 Duplicates: 0 Warnings: 0 > > >After: > >brussel: /var/lib/mysql/bacula 121# myisamchk -dv File.MYI > >MyISAM file: File.MYI >Record format: Packed >Character set: latin1 (8) >File-version: 1 >Creation time: 2006-03-02 18:17:02 >Recover time: 2006-03-02 18:45:49 >Status: checked,analyzed >Auto increment key: 1 Last value: 48478197 >Data records: 41446968 Deleted blocks: 0 >Datafile parts: 41446968 Deleted data: 0 >Datafile pointer (bytes): 6 Keyfile pointer (bytes): 4 >Datafile length: 4290117612 Keyfile length: 1469418496 >Max datafile length: 281474976710654 Max keyfile length: 4398046510079 >Recordlength: 43 > >table description: >Key Start Len Index Type Rec/key Root >Blocksize >1 1 4 unique unsigned long 1 224365568 >1024 >2 17 4 multip. unsigned long 23 1047458816 >1024 > 13 4 unsigned long 6 > 3 9 4 multip. unsigned long 30476 1469417472 > 1024 > > > > >On Do, 02 Mär 2006, "George R. Kasica" <ge...@ne...> wrote: >> Volker: >> >> I tried the sequence below and I don't see a change in my tables at >> all: >> >> BEFORE: >> ======= >> # myisamchk -dv File >> >> MyISAM file: File >> Record format: Packed >> Character set: latin1 (8) >> File-version: 1 >> Creation time: 2006-01-26 13:31:19 >> Status: open,changed >> Auto increment key: 1 Last value: 3412187 >> Data records: 3164900 Deleted blocks: 176853 >> Datafile parts: 3342897 Deleted data: 18692352 >> Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4 >> Datafile length: 344524708 Keyfile length: 124920832 >> Max datafile length: 2147483646 Max keyfile length: 2147483646 >> Recordlength: 42 >> >> table description: >> Key Start Len Index Type Rec/key Root >> Blocksize >> 1 1 4 unique unsigned long 1 23406592 >> 1024 >> 2 9 4 multip. unsigned long 0 23013376 >> 1024 >> 3 9 4 multip. unsigned long 0 4072448 >> 1024 >> 13 4 unsigned long 0 >> 17 4 unsigned long 0 >> >> >> >> mysql> ALTER TABLE File MAX_ROWS=1000000000 AVG_ROW_LENGTH=102; >> Query OK, 3164900 rows affected (8 min 6.42 sec) >> Records: 3164900 Duplicates: 0 Warnings: 0 >> >> >> AFTER: >> ====== >> # myisamchk -dv File >> >> MyISAM file: File >> Record format: Packed >> Character set: latin1 (8) >> File-version: 1 >> Creation time: 2006-03-02 11:20:33 >> Recover time: 2006-03-02 11:28:38 >> Status: checked,analyzed >> Auto increment key: 1 Last value: 3412187 >> Data records: 3164900 Deleted blocks: 0 >> Datafile parts: 3164900 Deleted data: 0 >> Datafile pointer (bytes): 5 Keyfile pointer (bytes): 4 >> Datafile length: 325084916 Keyfile length: 118755328 >> Max datafile length: 2147483646 Max keyfile length: 2147483646 >> Recordlength: 42 >> >> table description: >> Key Start Len Index Type Rec/key Root >> Blocksize >> 1 1 4 unique unsigned long 1 4426752 >> 1024 >> 2 9 4 multip. unsigned long 49452 63589376 >> 1024 >> 3 9 4 multip. unsigned long 49452 118754304 >> 1024 >> 13 4 unsigned long 16 >> 17 4 unsigned long 1 >> >> >> >On Thu, 2 Mar 2006 17:46:22 +0100, you wrote: >> >> >Hi, >> > >> >this for the archives: I got error messages saying "The table 'File' is >> >full". The table was 4 GB in size. It was on a ext3 filesystem. >> >Switching to xfs didn't help. >> >The solution is: >> >http://dev.mysql.com/doc/refman/5.0/en/full-table.html >> > >> >Saying: >> >You are using a MyISAM table and the space required for the table >> >exceeds what is allowed by the internal pointer size. If you don't >> >specify the MAX_ROWS table option when you create a table, MySQL uses >> >the myisam_data_pointer_size system variable. From MySQL 5.0.6 on, the >> >default value is 6 bytes, which is enough to allow 256TB of data. Before >> >MySQL 5.0.6, the default value is 4 bytes, which is enough to allow only >> >4GB of data. See Section 5.2.2, ?Server System Variables?. >> > >> > >> >You can check the maximum data/index sizes by using this statement: >> > >> >SHOW TABLE STATUS FROM database LIKE 'tbl_name'; >> > >> > >> > You also can use myisamchk -dv /path/to/table-index-file. >> > >> > If the pointer size is too small, you can fix the problem by using >> > ALTER TABLE: >> > >> > ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn; >> > >> > 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. >> > >> >Interesting: http://jeremy.zawodny.com/blog/archives/000796.html >> ===[George R. Kasica]=== +1 262 677 0766 >> President +1 206 374 6482 FAX >> Netwrx Consulting Inc. Jackson, WI USA >> http://www.netwrx1.com >> ge...@ne... >> ICQ #12862186 >> >> |