List:General Discussion« Previous MessageNext Message »
From:Jean Claude LAFFITTE Date:April 29 1999 9:54am
Subject:Database corruption (or maybe Index order problem)
View as plain text  
>Description:

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

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

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

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

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


#
# Table structure for table 'valeur_variables'
#
CREATE TABLE valeur_variables (
  site mediumint(9) DEFAULT '0' NOT NULL,
  i_num int(11) DEFAULT '0' NOT NULL,
  v_num tinyint(4),
  dt datetime,
  valeur char(30),
  KEY site (site,i_num),
  KEY i_num (i_num)
);
                            
#
# Table structure for table 'instances'
#
CREATE TABLE instances (
  site mediumint(9) DEFAULT '0' NOT NULL,
  f_type smallint(6),
  l_num smallint(6) DEFAULT '0' NOT NULL,
  inst_fonc tinyint(4),
  num int(11) DEFAULT '0' NOT NULL,
  KEY site (site,num,l_num),
  UNIQUE site_2 (site,num),
  KEY l_num (l_num)
);
       
Here is the query :

select dayofmonth(VV.dt), hour(VV.dt), VV.valeur  
from valeur_variables VV, instances I   
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


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

the old database ( the slow one) gives :

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

+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+
| Table            | Non_unique | Key_name | Seq_in_index | Column_name
| Collation | Cardinality | Sub_part |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+
| valeur_variables |          1 | site     |            1 | site       
| A         |          34 |     NULL |
| valeur_variables |          1 | site     |            2 | i_num      
| A         |          34 |     NULL |
| valeur_variables |          1 | i_num    |            1 | i_num      
| A         |          25 |     NULL |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+


the new database ( the fast one) gives :

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

+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+
| Table            | Non_unique | Key_name | Seq_in_index | Column_name
| Collation | Cardinality | Sub_part |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+
| valeur_variables |          1 | site     |            1 | site       
| A         |        NULL |     NULL |
| valeur_variables |          1 | site     |            2 | i_num      
| A         |        NULL |     NULL |
| valeur_variables |          1 | i_num    |            1 | i_num      
| A         |        NULL |     NULL |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+

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

Does isamchk -r do any kind of sorting ? 

>How-To-Repeat:
        
>Fix:
        

>Submitter-Id:  <submitter ID>
>Originator:    Red Hat Linux User
>Organization:
 
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis:       Database corruption (or maybe Index order problem)
>Severity:      
>Priority:      
>Category:      mysql
>Class:         
>Release:       mysql-3.22.21 (Source distribution)
>Server: /usr/bin/mysqladmin  Ver 7.11 Distrib 3.22.21, for pc-linux-gnu on i686
TCX Datakonsult AB, by Monty

Server version          3.22.21
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 2 hours 52 min 57 sec

Threads: 1  Questions: 185  Slow queries: 2  Opens: 90  Flush tables:
12  Open tables: 2
>Environment:
        
System: Linux yeti2.SOBEL 2.0.35 #5 Wed Sep 16 18:36:26 MEST 1998 i686
unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.7.2.3/specs
gcc version 2.7.2.3
Compilation info: CC='egcs'  CFLAGS='-O6 -fomit-frame-pointer
-mpentium'  CXX='egcs'  CXXFLAGS='-O6
-fomit-frame-pointer                  -felide-constructors
-fno-exceptions -fno-rtti -mpentium'  LDFLAGS=''
Configure command: ./configure  --enable-shared --enable-assembler
--disable-shared --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --with-mysqld-user=mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/
--exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc
--datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info
--includedir=/usr/include --mandir=/usr/man
Perl: This is perl, version 5.004_04 built for i386-linux
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