List:General Discussion« Previous MessageNext Message »
From:Janusz Krzysztofik Date:November 24 2003 12:57pm
Subject:Big difference in MyISAM and InnoDB SELECT speed
View as plain text  
Hello,

I am trying to optimize MySQL (3.23.49 from Debian stable) setup for
ASPseek application. I decided to try InnoDB in order to be able
to update tables while performing time consuming selects.
After converting all tables to InnoDB I noticed a big difference
in processing speed of one of the SELECT queries performed by the application.

My configuration file /etc/my.cnf is based on my-huge.cnf example.

Table structure:
create table urlword(url_id integer auto_increment primary key,
        site_id integer not null,
        tree_id integer not null,
        deleted tinyint DEFAULT 0 NOT NULL,
        url varchar(128) not null,
        next_index_time INT NOT NULL,
        status int(11) DEFAULT '0' NOT NULL,
        crc char(32) DEFAULT '' NOT NULL,
        last_modified varchar(32) DEFAULT '' NOT NULL,
        etag varchar(48) DEFAULT '' NOT NULL,
        last_index_time INT NOT NULL,
        referrer int(11) DEFAULT '0' NOT NULL,
        tag int(11) DEFAULT '0' NOT NULL,
        hops int(11) DEFAULT '0' NOT NULL,
        redir integer,
        origin integer,
        unique index(url),
        index(next_index_time),
        index(hops,next_index_time),
        index crc (origin, crc(8)));

Query:
select url_id from urlword where deleted=0 and status=200 and origin=1

MyISAM:
Table status:
| Name    | Type   | Row_format | Rows     | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time         |
Update_time         | Check_time | Create_options     | Comment |
+---------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+---------+
| urlword | MyISAM | Dynamic    | 46648925 |            143 |  6714978360 |  
1099511627775 |   4052629504 |         0 |       46648929 | 2003-11-21 14:13:28 |
2003-11-22 04:01:16 | NULL       | max_rows=100000000 |         |

Sample disk usage (iostat -x -d /dev/scsi/... 10):
Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz  
await  svctm  %util
/dev/scsi/host1/bus4/target0/lun0/disc
           5510.20   0.20 361.10  0.40 46970.40    4.80 23485.20     2.40   129.95   
16.41   45.39  27.11  98.00

Processing time:
Query OK, 14274315 rows affected (4 min 54.88 sec)

InnoDB:
Table status:
| Name    | Type   | Row_format | Rows     | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time |
Check_time | Create_options     | Comment                 |
+---------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+--------------------+-------------------------+
| urlword | InnoDB | Dynamic    | 44477984 |            228 | 10150215680 |           
NULL |  13322158080 |         0 |       46648929 | NULL        | NULL        | NULL      
| max_rows=100000000 | InnoDB free: 6715392 kB |

Sample disk usage (iostat -x -d /dev/scsi/... 10):
Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz  
await  svctm  %util
/dev/scsi/host1/bus4/target0/lun0/disc
           2179.10   0.00 94.90  0.00 4548.00    0.00  2274.00     0.00    47.92     9.96 
104.85 104.00  98.70

Processing time:
Query OK, 14274315 rows affected (1 day 8 hours 46 min 46.70 sec)

Could someone please explain me what can be the reason of this difference
and if there is a way to optimize InnoDB to perform better?

Thanks,
Janusz
Thread
Big difference in MyISAM and InnoDB SELECT speedJanusz Krzysztofik24 Nov
RE: Big difference in MyISAM and InnoDB SELECT speedMechain Marc24 Nov
  • Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Janusz Krzysztofik24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)gerald_clark24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Martijn Tonies24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Janusz Krzysztofik24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Martijn Tonies24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Janusz Krzysztofik27 Nov
RE: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Mechain Marc24 Nov