Re: MYI-Status ...

[Sergei Golubchik]

Hi!

On Nov 21, Hagen H?pfner wrote:
> Dear List,
> 
> it's time to ask my next long question regarding the myi-structure. From
> the interals documentation I learned that the status information
> consists (beside others) of the parts that I try to interpret in the
> following. Please let  me know, wether my interpretion is correct or
> correct it.
> 
> Thanx a lot, Hagen
> 
> Name                         Size Dump 
> ----                         ---- ----
> ---------------------------------------------------------------------
> state->open_count             2   00 01
> *** I assume, that this value shows how often the MYI-File is opened
> by paralle processes. *** 

Copying from mi_locking.c

/*
  DOCUMENTATION

  state.open_count in the .MYI file is used the following way:
  - For the first change of the .MYI file in this process open_count is
    incremented by mi_mark_file_change(). (We have a write lock on the file
    when this happens)
  - In mi_close() it's decremented by _mi_decrement_open_count() if it
    was incremented in the same process.

  This mean that if we are the only process using the file, the open_count
  tells us if the MYISAM file wasn't properly closed. (This is true if
  my_disable_locking is set).
*/

> ---------------------------------------------------------------------
> state->changed                1   39
> *** I assume that this is only a flag but do not understand why it is
> 39 here. Furthermore, If I reboot my machine, the value stays 39 but
> the description says that it should be reseted. So I'am a little bit
> confused ;-) ***

In myisamdef.h:

        /* bits in state.changed */

#define STATE_CHANGED             1
#define STATE_CRASHED             2
#define STATE_CRASHED_ON_REPAIR   4
#define STATE_NOT_ANALYZED        8
#define STATE_NOT_OPTIMIZED_KEYS 16
#define STATE_NOT_SORTED_PAGES   32

> ---------------------------------------------------------------------
> state->sortkey                1   FF                      
> *** Is this a relict ??? It is not used but available. ***

Looks like it's not.
There are few parts of MyISAM that nobody besides Monty knows.
They at least 6-7 years old (but probably more) and never used in MySQL,
but only in pre-MySQL ISAM interface.
I guess this is one of those :)

> ---------------------------------------------------------------------
> state->state.records          8   00 00 00 00 00 00 00 02 
> *** Thats easy to understand, but does this mean, that MyISAM allows
> "only" 18446744073709551615 (FF FF FF FF FF FF FF FF) tuples per
> table? ***

Yes.

> ---------------------------------------------------------------------
> state->state.del              8   00 00 00 00 00 00 00 01 
> *** Thats the number of tuples marked deleted. ***

Yes

> ---------------------------------------------------------------------
> state->split                  8   00 00 00 00 00 00 00 03 
> *** How many chunks are available due to the deletion of rows. But
> what is a chunk? *** 

Looks like it's total number of chunks, not only chunks in the deleted
chain.

A chunk (also called block) is a part of the MYD file, in dynamic table
format (mi_dynrec.c). It has a chunk header see the end of mi_dynrec.c
A row is added to the table in one chunk. When it's deleted, the chunk
is moved to deleted chain. A new row could be written later in this
chunk, if it'll be too long, the row will be split and occupy more than
one chunk. That's why number of chunks may be more than number of rows,
but it can never be less.

> ---------------------------------------------------------------------
> state->dellink                8   00 00 00 00 00 00 00 07 
> *** Link to the a row marked deleted in the myd-file ***

To a row (in static row format) or a chunk (in dynamic row format),
that is to a unit of allocation. And this unit contains a link to the
next "marked deleted" unit, and so on.

> ---------------------------------------------------------------------
> state->state.key_file_length  8   00 00 00 00 00 00 0c 00 2048
> *** size of .MYI file --> the maximal size of an index file is
> 18446744073709551615 Bytes ***

I think so. E.g. there's a line in mi_repair()

  if (my_chsize(share->kfile,info->state->key_file_length,0,MYF(0)))

> ---------------------------------------------------------------------
> state->state.data_file_length 8   00 00 00 00 00 00 00 15
> *** size of .MYD file --> the maximal size of an data file is
> 18446744073709551615 Bytes ***

Yes.

> ---------------------------------------------------------------------
> state->state.empty            8   00 00 00 00 00 00 00 00
> *** I have got no idea what this value means ? ***

As far as I understand it's the total free space (in bytes) in deleted
chain.

> ---------------------------------------------------------------------
> state->state.key_empty        8   00 00 00 00 00 00 00 00
> *** I have got no idea what this value means ? ***

Not used, as far as I can grep :)

> ---------------------------------------------------------------------
> state->auto_increment         8   00 00 00 00 00 00 00 00
> *** Current value of the autoincrement column --> only one
> auto_increment column allowed ***

Right.

> ---------------------------------------------------------------------
> state->checksum               8   00 00 00 00 00 00 00 00
> *** Checksum for what??? ***

For the table. See CHECKSUM clause in the CREATE TABLE statement.

> ---------------------------------------------------------------------
> state->process                4   00 00 09 E6             
> *** PID of the process that modified the table for the last time ***

Yes

> ---------------------------------------------------------------------
> state->unique                 4   00 00 00 0B
> *** I assume, that this value is the number of unique entries in the
> index. But, if I have a combined key (S2,S3) ... has the combination
> be unique in order to get counted??? In other world, what does the
> 0x0B mean here??? *

No, it's almost the same as state->process, in mi_open.c:

  info.this_unique= (ulong) info.dfile; /* Uniq number in process */
  ...
  share->this_process=(ulong) getpid();

So, if one process is opening the table many times, 'unique' is used to
distinguish between these "times".

> ---------------------------------------------------------------------
> state->status                 4   00 00 00 00
> *** I do not have any idea ;-( ***

Unused as far as I can see

> ---------------------------------------------------------------------
> state->update_count           4   00 00 00 04 
> *** Number of write locks to the table. What happens in case of an
> overflow? E.g. if I Insert 0x01 00 00 00 00 rows, each with a separate
> insert statement? ***

It's not a number of write locks. This is a a copy of info->this_loop
(you probably noticed already that a bunch of info->this_xxx fields are
copied into state->xxx). And info->this_loop is incremented every time a
write lock is taken or released. Also, info->this_loop is per-open-table
value, if a table is opened twice, there will be two different info's.

state->update_count used to detect if the table was changed:

  if (share->state.process != share->last_process ||
      share->state.unique  != info->last_unique ||
      share->state.update_count != info->last_loop)

So, if the table was modified in a different thread (or even in the same
thread, but another open instance of this table), or in another process
it'll be catched by share->state.process or share->state.unique.
Thus share->state.update_count is only used to find whether the table
was modified via exactly this 'info' structure, this thread, this
process.

And, really, I don't understand why it's 4 bytes value, looks like one
bit would be enough :)

> ---------------------------------------------------------------------      
> state->key_root               8   00 00 00 00 00 00 04 00 
> *** offset in myi-file where I1 key starts. But, what is the reference
> point for this offset??? Furthermore, in the base part of the index
> you have got a field base->keystart which contails the same value. Are
> both values the same? ***

it's an offset in the file.
Not necessarily the same. Naturally for the first index, the first
allocated page will be at keystart. But later this page may be split and
the root won't necessarily be at keystart anymore.

> ---------------------------------------------------------------------
>                                   00 00 00 00 00 00 08 00
> *** offset in myi-file where I2 key starts. But, what is the reference
> point for this offset ... (2048 Bytes after what)? ***

after beginning of file.

> ---------------------------------------------------------------------                
>                                                                                           
>       
> state->key_del                8   FF FF FF FF FF FF FF FF
> *** Link to a place in the myi-file where a key was stored that was
> indexing a now deleted tuple, Am I right? ***

Not exactly. It's the same as in data file. The link to the first
'deleted' (that is free) keypage. This keypages has the link to the next
free keypage and so on.

All keypages in the list have the same size. So if MYI file contains
indexes with different page sizes, there will be more than one
state->key_del. It's actually an array, just as state->key_root. But not
for all indexes, but for all possible key page sizes.

> ---------------------------------------------------------------------
> state->sec_index_changed      4   00 00 00 00
> *** not used ... was it used in previous versions or will it be used
> in upcoming versions? ***

I guess you're right.

> ---------------------------------------------------------------------
> state->sec_index_used         4   00 00 00 00
> *** not used ... was it used in previous versions or will it be used
> in upcoming versions? ***

Same here.

> ---------------------------------------------------------------------
> state->version                4   3F 3F EB F7
> *** this is called to be the timestamp of the creation of the index
> file, isn't it? ***

Yes, looks like it is.

> ---------------------------------------------------------------------
> state->key_map                8   00 00 00 03             
> *** "what keys are in use" --> but, what does this mean? Furthermore,
> I assume, that the lenght in the documentation (8 bytes) is wrong
> here!***

If a bit is not set for some particular key it will not be updated on
write/delete/update, it won't be used on search.

8 bytes is correct, see in mi_open.c:

  state->key_map     = mi_uint8korr(ptr);       ptr +=8;
...
  mi_int8store(ptr,state->key_map);           ptr +=8;

> ---------------------------------------------------------------------
> state->create_time            8   00 00 00 00 3F 3F EB F7 
> *** timestamp of the creation of the database. But, why do you use
> here 8 byte and for the state->version only 4??? ***

I assume you mean "of the table".
Why ? Because 'version' is not used anywhere, as far as I can see, so
nobody cares.

'create_time' one can see at least in SHOW TABLE STATUS

> ---------------------------------------------------------------------
> state->recover_time           8   00 00 00 00 00 00 00 00 
> *** timestamp of last recover ***
> ---------------------------------------------------------------------
> state->check_time             8   00 00 00 00 3F 3F EB F7 "time of last check"
> *** timestamp of last check ***
> ---------------------------------------------------------------------
> state->rec_per_key_rows       8   00 00 00 00 00 00 00 00
> *** I do not have any idea ;-( ***

Neither do I. It's not used anywhere.

> ---------------------------------------------------------------------
> state->rec_per_key_parts      4   00 00 00 00             (key_parts = 3, so
>                                   00 00 00 00              rec_per_key_parts
>                                   00 00 00 00              occurs 3 times)

Ok, this is an array, with number of elements equal to the total number
of keyparts of all indexes. For the table

  CREATE TABLE t (..., INDEX i1 (a, b, c), INDEX i2 (c, d), INDEX i3 (b))

it will have 6 elements, and the values will be

  rec_per_key_parts[0]=SELECT COUNT(*)/COUNT(DISTINCT a) FROM t
  rec_per_key_parts[1]=SELECT COUNT(*)/COUNT(DISTINCT a,b) FROM t
  rec_per_key_parts[2]=SELECT COUNT(*)/COUNT(DISTINCT a,b,c) FROM t
  rec_per_key_parts[3]=SELECT COUNT(*)/COUNT(DISTINCT c) FROM t
  rec_per_key_parts[4]=SELECT COUNT(*)/COUNT(DISTINCT c,d) FROM t
  rec_per_key_parts[5]=SELECT COUNT(*)/COUNT(DISTINCT b) FROM t

and the meaning is, assuming you fixed the values of keyparts, how many
records it will, in average, find:

  SELECT ... WHERE a=const1 and b=const2

(this is for rec_per_key_parts[1])
NULL's complicate the above formula somewhat, see the comment for
update_key_parts() for details.

Regards,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Kerpen, Germany
       <___/  www.mysql.com