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