List:General Discussion« Previous MessageNext Message »
From:Red Hat Linux User Date:April 29 1999 9:27am
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)Red Hat Linux User29 Apr