Re: MyISAM/InnoDB/Oracle comparison, and a InnoDB table locking question
On Wed, Dec 26, 2001 at 04:23:46PM -0600, Philip Molter wrote: > Are there guides out there for configuring these things? What is > a "big enough" log file? Honestly, on a lot of stuff, I'm just > guessing, but it takes a lot of time to fiddle with values, clean > out the database, and then shove in 1 million rows to see the > results. I haven't found any really good guidelines but I'm told if you pay for support then the developpers would be more than glad to work out some optimal values for your data set. -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - 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
RE: MyISAM/InnoDB/Oracle comparison, and a InnoDB table locking question
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
Re: MyISAM/InnoDB/Oracle comparison, and a InnoDB table locking question
Philip, >On Thu, Dec 27, 2001 at 12:18:38AM +0200, Heikki Tuuri wrote: >: 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? > >Are there guides out there for configuring these things? What is >a "big enough" log file? Honestly, on a lot of stuff, I'm just >guessing, but it takes a lot of time to fiddle with values, clean >out the database, and then shove in 1 million rows to see the >results. yes, the manual at http://www.innodb.com/ibman.html contains instructions and tuning tips. I have just introduced a new sample my.cnf which will make setting the values easier: . [mysqld] # You can write your other MySQL server options here # ... # innodb_data_home_dir = c:\ibdata #Data files must be able to hold your data and indexes innodb_data_file_path = ibdata1:2000M;ibdata2:2000M #Set buffer pool size to 50 - 80 % of your computer's memory set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M innodb_log_group_home_dir = c:\iblogs innodb_log_arch_dir = c:\iblogs #.._arch_dir must be the same as .._log_group_home_dir innodb_log_archive=0 set-variable = innodb_log_files_in_group=3 #Set the log file size to about 15 % of the buffer pool size set-variable = innodb_log_file_size=10M set-variable = innodb_log_buffer_size=8M #Set ..flush_log_at_trx_commit to 0 if you can afford losing #a few last transactions innodb_flush_log_at_trx_commit=1 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 . >* Philip Molter >* Texas.net Internet >* http://www.texas.net/ >* [EMAIL PROTECTED] Regards, Heikki 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
Re: MyISAM/InnoDB/Oracle comparison, and a InnoDB table locking question
On Thu, Dec 27, 2001 at 12:18:38AM +0200, Heikki Tuuri wrote: : 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? Are there guides out there for configuring these things? What is a "big enough" log file? Honestly, on a lot of stuff, I'm just guessing, but it takes a lot of time to fiddle with values, clean out the database, and then shove in 1 million rows to see the results. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - 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
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
