MySQL Forums
Forum List  »  Newbie

Re: MYSQL manual using MANY frequently changing Varchar(255)
Posted by: Rick James
Date: December 28, 2010 04:11PM

This applies to MyISAM only.

Sounds like your data size is about 6GB if dynamic, 15GB if Fixed? How much RAM do you have?

Here's what is going on.

"Fixed" tables (only CHAR, INT, etc)...
UPDATE will replace the row right where it is. This is possible because the 'new' row will be exactly the same length as the old one.

"Dynamic" tables (those that include VARCHAR, etc)...
UPDATE may make the row longer. This is likely to lead to splitting the record -- some bytes here, a link, and some bytes somewhere else. A subsequent SELECT on that table will probably have to hit two spots on disk to reconstruct the record. If the table is so big that the data cannot be cached, this means a disk hit, which is on the order of 10ms.

DELETE, UPDATE, and REPLACE on Dynamic rows will usually leave holes in the table. These holes will be used by subsequent INSERTs and UPDATEs, thereby breaking even INSERTs into multiple pieces sometimes.

It will take a long time (months?) before you will notice this fragmentation. One solution is to run OPTIMIZE TABLE, but this takes an amount of time proportional to the size of the table (or worse).

So, which should you do? Based on your numbers, it sounds like the Fixed version may be 2x-3x bigger than the Dynamic version. This, in itself, would lead to more disk I/O, thereby possibly being slower.

Bottom line: It depends. I always advise letting the rows be Dynamic unless you can make a compelling argument for Fixed. (You have not convinced me yet.)

Are your accesses 'random'? If you have you have less than 4GB of RAM, you are pretty much I/O bound either way. If you have 32GB of RAM, you are probably CPU bound.

If you can afford an hour's "downtime" once a month, I would recommend Dynamic with a monthly OPTIMIZE.

There are other speed tricks that can be played.
* Do you hit all 20 CHAR columns frequently? Let's discuss vertical partitioning.
* What kind of text is it? Let's discuss compression (to save I/O).

SHOW CREATE TABLE

Options: ReplyQuote


Subject
Written By
Posted
Re: MYSQL manual using MANY frequently changing Varchar(255)
December 28, 2010 04:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.