[AMaViS-user] 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-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/...fo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/