I wonder if you use REPLACE instead of UPDATE if this would work around this issue, or at least make it less noticeable.
On Tuesday, July 30, 2002, at 10:27 AM, 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> > > (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 <mysql-unsubscribe- > [EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 --------------------------------------------------------------------- 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
