InnoDB vs myISAM performance

I've been doing some testing with the logging database.  I
know the 
docs state that it requires InnoDB for transactions;
however, it 
seems if I remove the foreign keys, things work OK in myISAM
as 
well.  So given the loss of foreign keys (which seems OK
with me 
generally since the cleanup scripts take care of that
anyways), I did 
a little testing.  The two databases have the same set of
data and 
have the same load.

Some config variables I'm using (theoretically giving innodb
an edge ram wise):
innodb_buffer_pool_size  = 384M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
query-cache-size = 64M
key_buffer_size = 256M


MYISAM SPEED
mysql> select count(*) from quarantine where not exists
(select 1 
from msgs where mail_id=quarantine.mail_id);
+----------+
| count(*) |
+----------+
|    62080 |
+----------+
1 row in set (8.80 sec)
mysql> delete from quarantine where not exists (select 1
from msgs 
where mail_id=quarantine.mail_id);
Query OK, 62080 rows affected (2 min 48.50 sec)

INNODB SPEED
mysql> select count(*) from quarantine where not exists
(select 1 
from msgs where mail_id=quarantine.mail_id);
+----------+
| count(*) |
+----------+
|    62080 |
+----------+
1 row in set (5 min 39.49 sec)
mysql> delete from quarantine where not exists (select 1
from msgs 
where mail_id=quarantine.mail_id);
Query OK, 62080 rows affected (7 min 58.00 sec)

Similar results with other databases.  So if we remove the
foreign 
key dependency, and given that myISAM is considerably
faster, what 
are the benefits to continuing to use innodb?

- Nate


------------------------------------------------------------
-------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and
a browser.
Download your FREE copy of Splunk now >>  http://get.splunk.com/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

Nate wrote:
> I've been doing some testing with the logging database.
 I know the 
> docs state that it requires InnoDB for transactions;
however, it 
> seems if I remove the foreign keys, things work OK in
myISAM as 
> well.  So given the loss of foreign keys (which seems
OK with me 
> generally since the cleanup scripts take care of that
anyways), I did 
> a little testing.  The two databases have the same set
of data and 
> have the same load.
> 
> Some config variables I'm using (theoretically giving
innodb an edge ram wise):
> innodb_buffer_pool_size  = 384M
> innodb_log_buffer_size = 8M
> innodb_flush_log_at_trx_commit = 0
> query-cache-size = 64M
> key_buffer_size = 256M
> 
> 
> MYISAM SPEED
> mysql> select count(*) from quarantine where not
exists (select 1 
> from msgs where mail_id=quarantine.mail_id);
> +----------+
> | count(*) |
> +----------+
> |    62080 |
> +----------+
> 1 row in set (8.80 sec)
> mysql> delete from quarantine where not exists
(select 1 from msgs 
> where mail_id=quarantine.mail_id);
> Query OK, 62080 rows affected (2 min 48.50 sec)
> 
> INNODB SPEED
> mysql> select count(*) from quarantine where not
exists (select 1 
> from msgs where mail_id=quarantine.mail_id);
> +----------+
> | count(*) |
> +----------+
> |    62080 |
> +----------+
> 1 row in set (5 min 39.49 sec)
> mysql> delete from quarantine where not exists
(select 1 from msgs 
> where mail_id=quarantine.mail_id);
> Query OK, 62080 rows affected (7 min 58.00 sec)
> 
> Similar results with other databases.  So if we remove
the foreign 
> key dependency, and given that myISAM is considerably
faster, what 
> are the benefits to continuing to use innodb?

MyISAM locks tables during certain operations, so if you
search a huge 
MyISAM table while another process is doing many inserts you
will kill 
the insert process waiting for the table to unlock. On a
busy server 
this can be a big problem.  InnoDB has different locking
characteristics 
and can handle simultaneous reads and writes better.


> 
> - Nate
> 
> 
>
------------------------------------------------------------
-------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems? 
Stop.
> Now Search log events and configuration files using
AJAX and a browser.
> Download your FREE copy of Splunk now >>  http://get.splunk.com/
> _______________________________________________
> AMaViS-user mailing list
> AMaViS-userlists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/amavis-user

> AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
> AMaViS-HowTos:http://www.amavis.org/ho
wto/
> 
> 
> 


------------------------------------------------------------
-------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and
a browser.
Download your FREE copy of Splunk now >>  http://get.splunk.com/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/