Thanks for the reply, Heikki. That the MyISAM would fit in OS file cache didn't occur to me. That would certainly help explain the big discrepancy. And yes, we had autocommit turned on for InnoDB, not for Oracle. The InnoDB buffer pool size was 350mb, not big enough to hold the whole table. The Oracle SGA size was 75mb. On the new machine (with the RAID 1+0 disks) I'll be increasing the InnoDB buffer pool size and the Oracle SGA to be large enough for the table to fit. Thanks for the tips.
And, thanks for the info on the "innodb_flush_log_at_trx_commit=0" parm. Sounds similar to Oracle's log_checkpoint_interval parm. I'll add that to the my.cnf file. The redo logs are 30mb for both InnoDB and Oracle (three each). Oracle was cycling through a single log about every 2 minutes or so. Not sure how to check that on InnoDB. With what I've learned about InnoDB during my initial testing I think I'll be able to set up a better "real world" environment on the new machine, once it's ready. I'm currently "RTFM'ing" the InnoDB manual to better familiarize myself with the startup parms. Thanks again, -- Walt Weaver Bozeman, Montana -----Original Message----- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 26, 2001 3:19 PM To: [EMAIL PROTECTED] Subject: Re: MyISAM/InnoDB/Oracle comparison, and a InnoDB table locking question Walt, thank you for the test! >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. The MyISAM table obviously fit in the OS file cache, otherwise 1750 inserts per second would not be possible. Did the table fit in the buffer pool of InnoDB or the SGA of Oracle? Did you commit each insert individually in InnoDB and Oracle? Setting innodb_flush_log_at_trx_commit=0 in my.cnf will speed up individual inserts if you can afford losing a few of the last transactions in a crash. Did you configure the log files big enough for InnoDB and Oracle? >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. If you run queries where only a (non-locking) SELECT is used, then InnoDB and Oracle as multiversioned databases can do without setting locks altogether. So no lock table memory is consumed in SELECTs. Updates and deletes consume memory in the lock table of InnoDB, but inserts do not. Thus running out of memory really should not happen in real-world applications of InnoDB. If you look at the user stories at http://www.innodb.com/userstories.html you notice that over 1000 queries per second is attainable if the working set of your application fits in the buffer pool. >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? The only latch tuning in InnoDB is through the parameter innodb_thread_concurrency in my.cnf. Latch contention can cause slow thread scheduling in the OS, and lowering or raising its value from the default of 8 can help. In Oracle you may have to tune, for example, the number of rollback segments to reduce latch contention. In InnoDB rollback segment implementation is different from Oracle, and no such tuning is needed. You can use innodb_monitor to look at the number of spin waits and thread suspensions that happen at latches. In InnoDB the high-contention latches are the buffer pool latch and the DB kernel latch. I studied extensively methods to reduce the buffer pool latch contention. But I decided to keep a single latch, because when running InnoDB in a multiprocessor Xeon computer the memory bus gets saturated before any latch contention. >Thanks much, >--Walt Weaver > Bozeman, Montana Regards, Heikki Tuuri Innobase Oy 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 --------------------------------------------------------------------- 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
