maria-discuss team mailing list archive

[Paul McCullagh <paul.mccullagh@xxxxxxxxxxxxx>]

Hi Monty,

On May 5, 2010, at 7:02 PM, Michael Widenius wrote:

> > > > > > "Paul" == Paul McCullagh <paul.mccullagh@xxxxxxxxxxxxx> writes:
>
> <cut>
>
> Paul> PBXT can help you out with this. Do the following:
>
> Paul> (1) Load the telpbxt table with a few 1000 rows (or use  
> telpbxt2,
> Paul> since telpbxt already has too many rows in it).
> Paul> (2) In mysql client enter: CHECK TABLE telpbxt2; (note: do not  
> do this
> Paul> on a table with millions of rows. It will take very long to  
> complete).
> Paul> (3) Check the MySQL error log, you should see something like the
> Paul> output below.
>
> Paul> CHECK TABLE: ./test/telpbxt
> Paul> Record buffer size      = 1024
> Paul> Fixed length rec. len.  = 1020
> Paul> Handle data record size = 638
> Paul> Min/max header size     = 14/26
> Paul> Min/avg/max record size = 19/624/1019
> Paul> Avg row len set for tab = not specified
> Paul> Rows fixed length       = NO
> Paul> Maximum fixed size      = 16384
> Paul> ...
>
> Paul> Paste the output in an e-mail, and I will show you what to use  
> for the
> Paul> AVG_ROW_LENGTH.
>
> Would it be hard for PBXT to as part of the CHECK TABLE add:
>
> Optimial create AVG_ROW_LENGTH: ?

Currently CHECK TABLE prints:

Average comp. rec. len. = 200

This is the closest to your suggestion of an "Optimal create  
AVG_ROW_LENGTH", there is at the moment.

But, this value is not necessarily optimal.

Exactly what is optimal depends on a few additional factors, like  
whether you want to optimize for size or caching behavior.

What I can do is add more information to help in this decision making  
process, and output a "Recommended AVG_ROW_LENGTH".

Then I can change OPTIMIZE TABLE to automatically set this value, if  
no explicit AVG_ROW_LENGTH has been set yet.

Best regards,

Paul

--
Paul McCullagh
PrimeBase Technologies
www.primebase.org
www.blobstreaming.org
pbxt.blogspot.com