Re: Row size computation

[Michal Kurgan]

Rick James Wrote:
> The answer is here:
> > ) ENGINE=InnoDB
>
> InnoDB has a lot of overhead --
> * 1-2 bytes per field
> * About 29 bytes per row
> * Minimum of 6%, avg of 40%, padding per block

Yes, thanks. I wasn't aware about that.

One thing that really bothers me is constant increase of row size. After doing OPTIMIZE TABLE (which was surprisingly fast, so i did it even on production server) row size dropped to 75 bytes, but after few days of heavy use (just inserts and updates) it grew to 120 bytes. As far as i can see row size should be constant so i don't understand why this happens (probably because of clustered index). Any clues, some documentation to help understand this process and estimate 'future' row length? Are there any ways to prevent that from happening or limit that with InnoDB engine?

Maybe other engine, it need to support big number of parallel writes (inserts and updates) and occasional metrics scanning whole table or big chunks of table.

> > `lang` char(3) CHARACTER SET utf8 NOT NULL,
> > `country` char(3) CHARACTER SET utf8 NOT NULL,
> Each of these unnecessarily takes 9 bytes (plus
> overhead). CHAR utf8 assumes the worst case, 3
> bytes per 'character'. Assuming those are
> standard codes for language and country, CHARACTER
> SET ascii would suffice, and bring the size down
> to 3 bytes each.

Yes, thanks for hint. I already tried it, but the gain was only 3 bytes on optimized table. Either way i will probably migrate production db.