List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 29 1999 11:51am
Subject:Database corruption (or maybe Index order problem)
View as plain text  
>>>>> "Jean" == Jean Claude LAFFITTE <jc.laffitte@stripped>
> writes:

>> Description:
Jean> this last times, I've noticed that some queries take a long time (about
Jean> 10 sec).
Jean> These queries  where generated from a CGI script, using perl DBI and I
Jean> was used to get the results in less than 1 sec.

Jean> I've run isamchk on all the tables, sorting index, sorting rows.
Jean> I've check for errors with -r and -o options.
 
Jean> After that, the problem remains the same.

Jean> I've done a mysqldump of the database, and than create a new one from
Jean> the dump.
Jean> The queries works fine (less than 1 sec) on the new database.

Jean> I've done isamchk -e *.ISM on each database, and there is no diff

Jean> Here is the description of the tables used by the query :


Jean> #
Jean> # Table structure for table 'valeur_variables'
Jean> #
Jean> CREATE TABLE valeur_variables (
Jean> site mediumint(9) DEFAULT '0' NOT NULL,
Jean> i_num int(11) DEFAULT '0' NOT NULL,
Jean> v_num tinyint(4),
Jean> dt datetime,
Jean> valeur char(30),
Jean> KEY site (site,i_num),
Jean> KEY i_num (i_num)
Jean> );
                            
Jean> #
Jean> # Table structure for table 'instances'
Jean> #
Jean> CREATE TABLE instances (
Jean> site mediumint(9) DEFAULT '0' NOT NULL,
Jean> f_type smallint(6),
Jean> l_num smallint(6) DEFAULT '0' NOT NULL,
Jean> inst_fonc tinyint(4),
Jean> num int(11) DEFAULT '0' NOT NULL,
Jean> KEY site (site,num,l_num),
Jean> UNIQUE site_2 (site,num),
Jean> KEY l_num (l_num)
Jean> );
       
You should remove the KEY 'site';  This will not help in any circumstances!

A new key 'valeur_varibles.dt' may also help a bit!

Jean> Here is the query :

Jean> select dayofmonth(VV.dt), hour(VV.dt), VV.valeur  
Jean> from valeur_variables VV, instances I   
Jean> where I.num = VV.i_num    AND I.f_type= 201   AND VV.v_num  = 0   
Jean> AND VV.dt>=\"1999-3-01 01:00:00\" AND VV.dt<\"1999-4-01
Jean> 01:00:00\"  
Jean> AND VV.site = I.site  AND I.site = 3  ORDER BY VV.dt ASC


Jean> I've done an explain select and a show index from valeur_variables on
Jean> each
Jean> database.

Jean> the old database ( the slow one) gives :

Jean>
> +-------+--------+---------------+--------+---------+--------------+-------+------------+
Jean> | table | type   | possible_keys | key    | key_len | ref          |
Jean> rows  | Extra      |
Jean>
> +-------+--------+---------------+--------+---------+--------------+-------+------------+
Jean> | VV    | ref    | site,i_num    | site   |       3 | ???          |
Jean> 69383 | where used |
Jean> | I     | eq_ref | site,site_2   | site_2 |       7 | ???,VV.i_num |    
Jean> 1 | where used |
Jean>
> +-------+--------+---------------+--------+---------+--------------+-------+------------+ 
>   

How many entries do you have with 'WW.site = 3' ?  Is '69383' about right?

<cut>

Jean> the new database ( the fast one) gives :

Jean>
> +-------+------+---------------+--------+---------+-----------+-------+------------+
Jean> | table | type | possible_keys | key    | key_len | ref       | rows  |
Jean> Extra      |
Jean>
> +-------+------+---------------+--------+---------+-----------+-------+------------+
Jean> | I     | ref  | site,site_2   | site_2 |       3 | ???       |     1 |
Jean> where used |
Jean> | VV    | ref  | site,i_num    | site   |       7 | ???,I.num | 46256 |
Jean> where used |
Jean>
> +-------+------+---------------+--------+---------+-----------+-------+------------+

The big difference is in this case that the table order is different.

You can force the table order in MySQL with 'STRAIGHT_JOIN'

<cut>

Jean> I've done an isamchk -r -q valeur_variables on the new database and
Jean> after
Jean> I get the same result than the old database (and query goes slow)

Jean> Does isamchk -r do any kind of sorting ? 

The problem with your queries are that if you only examine keys, both
join versions should resolve the query in about the same time.

(MySQL uses only key statistics to find out in which order it should
join tables)

isamchk -a (and -r) will update the 'mean' key statistics, which
usually helps to resolve queries faster.

There is however one thing I find strange.

Can you mail me the output of:

explain select straight_join dayofmonth(VV.dt), hour(VV.dt), VV.valeur  
from instances I, valeur_variables VV, 
where I.num = VV.i_num    AND I.f_type= 201   AND VV.v_num  = 0   
      AND VV.dt>=\"1999-3-01 01:00:00\" AND VV.dt<\"1999-4-01
01:00:00\"  
      AND VV.site = I.site  AND I.site = 3  ORDER BY VV.dt ASC

after you have run 'isamchk -r' or 'isamchk -a'

(I would like to check the values in the 'rows' column).

At any rate, you can get the above query faster by adding more keys or using
STRAIGHT_JOIN.

Regards,
Monty
Thread
Database corruption (or maybe Index order problem)Jean Claude LAFFITTE29 Apr
  • Database corruption (or maybe Index order problem)Michael Widenius29 Apr
  • Re: Database corruption (or maybe Index order problem)Jean Claude LAFFITTE29 Apr