This post is partially a reply to a request to share my MyISAM/InnoDB/Oracle comparison testing with you guys. In addition, I have a question about locking mechanisms in InnoDB which I'll ask at the end of the post.
I've been comparison testing MyISAM, InnoDB, and Oracle for the past two weeks or so. We're looking to replace MyISAM with InnoDB to eliminate table locking problems we're experiencing on databases with high levels of activity (dozens of queries a second). Currently we migrate these high-activity databases to Oracle, where they perform well and very reliably. In addition, if the performance and reliability is there we may replace Oracle with InnoDB too. My initial tests have been done on a VA Linux 2230, 2gb RAM, and a single disk drive. The single drive puts InnoDB and Oracle at a disadvantage to MyISAM, and it's evident from the tests. Updating a 600,000 row table took about 16 minutes in MyISAM, 28 minutes in InnoDB, and 32 minutes in Oracle. Inserting 1000 rows into a 5.6 million row table showed MyISAM doing 1750 inserts/second, InnoDB doing 72 inserts/second, and Oracle 45 inserts/second. I've also done some lock testing, and of course that's where InnoDB and Oracle shine. Running several long-running selects and doing inserts at the same time took MyISAM minutes to finish thanks to table-level locking while InnoDB and Oracle finished everything up in a matter of seconds. This is what we're really interested in as far as InnoDB is concerned. If InnoDB performs as well as or better than Oracle in our testing (and it's as stable and scalable as Oracle) we'll be happy since Oracle performs very well in our production environment, which is a VA Linux/EMC Symmetrix environment. I'm currently putting together another VA Linux 2230 which will have eight disks configured for RAID 1+0. I want to see what the performance increase we'll see with InnoDB by spreading I/O out on the four mirrored drives. Other than the drives the 2230 is identical to the first one I've been testing on. SO, my question about InnoDB locking mechanisms: there is a blurb in the InnoDB manual that states, "The lock table in InnoDB is stored so space-efficiently that lock escalation is not needed: typically several users are allowed to lock every row in the database, or any random subset of the rows, without InnoDB running out of memory." This statement disturbs me a bit. In the very near future we'll be running databases with potentially several hundred queries per second, and if InnoDB will be running out of memory because of the high locking requirements it encounters we could have a real problem. And this sort of begs the question: is there a way to tune InnoDB's use of lock/latch memory and mechanisms? I'm familiar with latch tuning in Oracle and what can cause latch contention. I'm familiar with the various buffers that can be set in the my.cnf file for InnoDB, but I haven't found any specific recommendations or instructions for lock/latch tuning with InnoDB. Anybody know of such an animal? Thanks much, --Walt Weaver Bozeman, Montana --------------------------------------------------------------------- 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
