[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-internals
Subject:    Re: MYI-Status ...
From:       Sergei Golubchik <serg () mysql ! com>
Date:       2005-12-19 10:22:13
Message-ID: 20051219102213.GA83628 () serg ! mylan
[Download RAW message or body]

Hi!

On Dec 11, Hagen H?pfner wrote:
> Dear all,
> 
> again it took a lot of time before I had the chance to go through all
> the answers ... First thanx to Sergei for doing an exelent job ;-) But,
> some more question came up:
> 
> >>---------------------------------------------------------------------
> >>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
> ...
> 
> Does this mean the following: If we have two processes that try to
> write to the file, then the first one increments open_count and the
> second process gets blocked until process one closes the file, which
> decreases the open_count? 

No, I don't see it. As far as I can see it's used in two places:

  1. mi_open refuses to open a table (considering it corrupted) if
     open_count > 0 and --skip-external-lock is in effect.
  2. CHECK/REPAIR/myisamchk print a warning if open_count>0

> >>---------------------------------------------------------------------
> >>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.
> >
> Perhaps a stupid question but ... Why should one turn of an index ???

To speed up bulk changes - deletes or inserts. As repair-by-sort is
about hundred times faster than inserting row by row (depending on the
number of indexes, though), it may make a lot of sense to disable
indexes before inserting lots of data.

Just don't forget to enable and rebuild indexes afterwards :)

> However, next question: How can I turn of an index without manually
> modifying the key_map.

ALTER TABLE ... ENABLE/DISABLE KEYS (only affects non-unique indexes)
myisamchk -k=#

MySQL does it automatically for insert many rows/insert...select/load
into the empty table, and for alter table (when data are copied to
temporary table). "Does automatically" means it disables non-unique
indexes before copying the data, and enables/rebuilds afterwards.

> And the third question to this answer ... Does this mean, that MyISAM
> supports "only" 64 (8 Bytes ? 8 Bits) indexes per Table?

This used to be the case. But we changed it in 5.0 (see patches for the
Bug#10932) to support up to 255 indexes. It's a compile-time option, and
if MyISAM was built to support more than 64 keys, then highest bit in a
key_map disables/enables all keys above 63rd, only the first 63 keys can
be enabled.disabled individually.

> >>---------------------------------------------------------------------
> >>state->recover_time           8   00 00 00 00 00 00 00 00 
> >>*** timestamp of last recover ***
> >>
> I greped for "recover_time" but was unable to find a tool that
> modifies this value. Is it really used?

Apparently not :)
May be in UNIREG...
 
> >>---------------------------------------------------------------------
> >>state->check_time             8   00 00 00 00 3F 3F EB F7 "time of last check"
> >>*** timestamp of last check ***
> >>
> mysqlcheck updates this value but myisamchk does not ... correct?

What do you mean ? mysqlcheck just calls CHECK TABLE, it does not use
myisam directly.

Ok, you mean that check_time is modified in mi_check.c (that is used in
CHECK TABLE) but not in myisamchk.c ? But myisamchk.c also calls
mi_check.c functions, so it should update check_time too.

> >>---------------------------------------------------------------------
> >>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
> >
> ... again, I tried out this ... might it be true, that
> rec_per_key_parts-entries are created but not calculated for keyparts
> coming from fulltext-indexes?

They are calculated - as I do remember a couple of bugs in these
calculations that were fixed :)

But these values are not used by MySQL.

Regards,
Sergei

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

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/internals?unsub=mysql-internals@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]