Maciek,
At 12:20 PM 11/9/01 +0100, you wrote:
>> please show us the CREATE TABLE statements and what EXPLIN SELECT ... says
>> about both table types.
>
>CREATE TABLE `stats_InnoDB` (
> `id` int(10) unsigned NOT NULL auto_increment,
> `ident` char(100) NOT NULL default '',
> `class` int(2) unsigned NOT NULL default '0',
> `status` int(1) unsigned NOT NULL default '0',
> `last` int(6) unsigned NOT NULL default '0',
> `created` int(6) unsigned NOT NULL default '0',
> `counter` int(3) unsigned NOT NULL default '0',
> `stat_val_1` int(1) unsigned NOT NULL default '0',
> `stat_val_2` int(1) unsigned NOT NULL default '0',
> `stat_val_3` int(1) unsigned NOT NULL default '0',
>....
> `stat_val_85` int(1) unsigned NOT NULL default '0',
> PRIMARY KEY (`id`),
> UNIQUE KEY `ident` (`ident`,`class`),
> KEY `status` (`class`,`status`),
> KEY `last` (`last`)
>) TYPE=InnoDB
>
>The MyISAM type table is exactly the same.
>
>
>MyISAM
>======
>
>EXPLAIN SELECT COUNT(*) FROM stats_MyISAM WHERE ( stats_val_20 = '1' ) AND
>( stats_val_35 = '1' OR stats_val_37 = '1' OR stats_val_38 = '1' OR
>stats_val_42 = '1' OR stats_val_43 = '1' OR stats_val_44 = '1' OR
>stats_val_45 = '1' OR stats_val_47 = '1' OR stats_val_48 = '1' OR
>stats_val_50 = '1' OR stats_val_51 = '1' OR stats_val_52 = '1' ) AND NOT (
>stats_val_51 = '1' )
>
>+--------------+------+---------------+------+---------+------+---------+---
>---------+
>| table | type | possible_keys | key | key_len | ref | rows |
>Extra |
>+--------------+------+---------------+------+---------+------+---------+---
>---------+
>| stats_MyISAM | ALL | NULL | NULL | NULL | NULL | 1197337 |
>where used |
>+--------------+------+---------------+------+---------+------+---------+---
>---------+
>
>+----------+
>| COUNT(*) |
>+----------+
>| 316055 |
>+----------+
>1 row in set (31.24 sec)
>
>INNODB
>======
>
>EXPLAIN SELECT COUNT(*) FROM stats_InnoDB WHERE ( stats_val_20 = '1' ) AND
>( stats_val_35 = '1' OR stats_val_37 = '1' OR stats_val_38 = '1' OR
>stats_val_42 = '1' OR stats_val_43 = '1' OR stats_val_44 = '1' OR
>stats_val_45 = '1' OR stats_val_47 = '1' OR stats_val_48 = '1' OR
>stats_val_50 = '1' OR stats_val_51 = '1' OR stats_val_52 = '1' ) AND NOT (
>stats_val_51 = '1' )
>
>+--------------+------+---------------+------+---------+------+---------+---
>---------+
>| table | type | possible_keys | key | key_len | ref | rows |
>Extra |
>+--------------+------+---------------+------+---------+------+---------+---
>---------+
>| stats_InnoDB | ALL | NULL | NULL | NULL | NULL | 1197337 |
>where used |
>+--------------+------+---------------+------+---------+------+---------+---
>---------+
>
>+----------+
>| COUNT(*) |
>+----------+
>| 316055 |
>+----------+
>1 row in set (4 min 25.65 sec)
>
>> What is the CPU usage % given by 'top' when you run the queries?
>
>MyISAM type table - the CPU usage grows each time 'top' refreshes (up to
>25-30% when the query ends)
>InnoDB type table - for a short time usage goes up, then falls down to about
>10%
ok, this may explain it. It is a disk-bound operation. Does the time change
if you run the query twice?
Have you inserted the rows in the order of the primary key? If yes, then it
should be sequential disk read, which runs 3 - 10 MB / s.
Strange that it takes so long: 246 seconds to read a table of 786 MB. Could
there be some other disk i/o present at the same time? Swapping of virtual
memory? Does it run faster if you make InnoDB buffer pool smaller, say set
it to 100 MB, or if you set the buffer pool very big, say 800 MB?
Since you are running MySQL/InnoDB-3.23.44, you could try running
innodb_monitor during the operation. It reports a lot of statistics about
file i/o. For example, we see how many OS file reads correspond to how many
buffer pool page reads.
>> How big is the table physically in bytes?
>
>stats_MyISAM.MYD - 463369419 bytes
>stats_InnoDB - 786432000 bytes ('Data length' from SHOW TABLE STATUS)
>
>More info that can be helpful:
>1 * Pentium III Xeon 600Mhz
>1GB RAM
>U2SCSI drives
>
>FreeBSD 4.4-STABLE
>MySQL 3.23.44
Regards,
Heikki
http://www.innodb.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
