At 16:27 +0200 7/30/02, Daniel Brockhaus wrote: >Hi there, > >here's something everyone using variable length records (varchar, >text, blob) should know: > ><sarcasm> >Create a table containing at least one blob: > >> create table db_test ( >> ID int not null, >> vara blob, >> primary key (ID) >> ); > >Insert two records: > >> insert db_test values(1,''); >> insert db_test values(2,''); > >Now, keep updating the blobs, increasing their size by 16 bytes each time: > >> update db_test set vara='AAAAAAAAAAAAAAAA' where ID=1; >> update db_test set vara='BBBBBBBBBBBBBBBB' where ID=2; >> update db_test set vara='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' where ID=1; >> update db_test set vara='BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB' where ID=2; >> update db_test set >>vara='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' where ID=1; >> update db_test set >>vara='BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB' where ID=2; >[...] > >Repeat this a few hundred times, then run myisamcheck -ci. You'll >get (after 1000 iterations): > >Checking MyISAM file: db_test >[...] >- check record links >Records: 2 M.recordlength: 16008 Packed: >0% >Recordspace used: 100% Empty space: 0% >Blocks/Record: 1001.00 >Record blocks: 2002 Delete blocks: 0 >Record data: 32016 Deleted data: 0 >Lost space: 4 Linkdata: 22012 >[...] > >Whoa. Each record has been split into 1000 (one thousand!) blocks. >Reading one of these records would require 1000 reads from your >harddisk. That's about 14 seconds to read a record of 16K length! >(You might get lucky and get better values from the OS's read-ahead >logic and disk cache.) > >Now sit back and marvel at the efficiency of mysql's dynamic record handling. ></sarcasm>
Use OPTIMIZE TABLE periodically to defragment your table. http://www.mysql.com/doc/D/y/Dynamic_format.html http://www.mysql.com/doc/O/P/OPTIMIZE_TABLE.html http://www.mysql.com/doc/O/p/Optimisation.html > >(Tested on mysql 3.23.33 and 3.23.41, known not to be fixed in the >lastest 3.23.X) > >I know this is a worst-case scenario. But still: I was using a table >with blobs of changing size, and this table was getting slower and >slower over time, at a rate of 100% per week. > >You can work around the problem by making sure that your records >change size as seldom as possible. One way to do this is to add >another blob and change its size as needed to keep the total record >length constant. This would fix the problem, but it adds a lot of >overhead to the application, and it is doing something on >application level that the database should do. > >What I'd like to have is an addition to the myisam table handler >(and possibly to the other table handlers too, if those have the >same problem) which allows you to set the minimum block size mysql >will allocate for any record. That is, if you set that value to 512, >every block allocated would be of 512, 1024, 1536, ... bytes length. > >If this was added and you'd use a minimum block size of 512, the >above example would have created 32 blocks for each record. Still >bad, but a hell of a lot of an improvement over the 1000 without it. > >Does anybody know whom I have to write to ask for this to be added? > >I apologize for the lengthy post, the bad english and the sarcasm. > >Regards, > >Daniel Brockhaus > >PS. >sql, query --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
