Hi! ----- Original Message ----- From: ""Orr, Steve"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, September 26, 2002 9:56 PM Subject: RE: MyISAM or InnoDB
> <PROVOCATIVE RANT> > > MySQL doesn't provide "native" support for transactions but is dependent on > the Berkeley or InnoDB table types. > > Does MySQL really provide TRUE row level locking? That's the claim but is it Does Oracle really provide TRUE row level locking? A Microsoft pamphlet said that if you set the transaction isolation level SERIALIZABLE in Oracle, then Oracle uses an optimistic validation method which is page level. I have not checked from the latest Oracle manual if this is still true. Or you must use table level manual locks. Does MS SQL Server really provide TRUE row level locking? If you set many locks, then it escalates row lock first to pages, and eventually to tables. > valid? The InnoDB web site seems to be more accurate by explaining that > locking is a next-key value locking mechanism that is indexed based. Here's > what the InnoDB manual says, "InnoDB does the row level locking so that when > it searches or scans an index of a table, it sets shared or exclusive locks > on the index records in encounters. Thus the row level locks are more > precisely called index record locks." > > I developed some of my own benchmark tests which intentionally stress > transactions in an artificial manner. The results were that MySQL with > InnoDB didn't even come close to the performance of Oracle. Maybe my tests > weren't very realistic and maybe a real world application won't encounter > locking problems but I wouldn't just assume that InnoDB will scale like > Oracle or meet the needs of your application. You've got to test it! Since a big customer requested this, MySQL/InnoDB-4.0.5 will feature the following READ COMMITTED isolation level which more closely imitates Oracle: <cut> > > > > > > > > > > READ COMMITTED > > > > > > > > > > which will switch off next-key locks from > > > > > > > > > > 1) SELECT ... FOR UPDATE; > > > > > 2) SELECT ... LOCK IN SHARE MODE; > > > > > 3) any UPDATE or DELETE which touches only a single row > > through a > > > > unique > > > > > index. > > > > > 4) Furthermore, a plain consistent read SELECT will > > read the latest > > > > > snapshot > > > > > like in Oracle. > > > > > > > > > > It is NOT possible to switch off next-key locks from a > > range DELETE or > > > > > UPDATE, because phantom rows would break MySQL replication and > > > > recovery. <cut> > "Open-source database providers readily admit that their databases are not > yet geared toward powering high-end enterprise systems, such as ERP and CRM > applications." From an Infoworld article about open-source databases > entitled "Finding an opening..." > http://www.infoworld.com/articles/pl/xml/02/09/23/020923pldatabses.xml > > One more thing to rant on... Regarding benchmark test... > http://www.mysql.com/doc/en/MySQL-PostgreSQL_bench_marks.html > Quoting from this page: "We know of two benchmark tests that claim that > PostgreSQL performs better than MySQL Server. These both where multi-user > tests, a test that we here at MySQL AB haven't had time to write and include > in the benchmark suite, mainly because it's a big task to do this in a > manner that is fair to all databases." > > It's "funny" how MySQL AB just hasn't had time to write a multi-user > benchmark. Based on the age of the link they haven't had time for a long > time now. Yet they say benchmarking is important? Give me a break! We have run pgbench and compared it to similar mysqlbench. At 5 users MyISAM-4.0.2 was 1.3 times as fast as Pg-7.2.1, and InnoDB was 5 times as fast. At 100 users MyISAM was 2 times as fast as Pg and InnoDB was 5 times as fast. I have been hoping some independent party would run these tests and publish the numbers. > What we desperately need are some TPC benchmarks for the open-source > databases. Benchmarks which are independenttly audited and verified. eWEEK ran a multiuser benchmark on DB2, SQL Server, Oracle, Sybase, MySQL/InnoDB: http://www.eweek.com/article2/0,3959,293,00.asp There are few independent benchmarks on databases because running a big benchmark requires time, and consequently money. Furthermore, the licenses of Oracle and several other databases forbid publication of benchmarks without a permission from the vendor. > </PROVOCATIVE RANT> Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query > -----Original Message----- > From: Josh Trutwin [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 25, 2002 10:03 AM > To: [EMAIL PROTECTED] > Subject: Re: MyISAM or InnoDB > > > > > We are moving a databse over from Oracle to MySQL 4.0.3 and I was > > wondering if I should use MyISAM or InnoDb. The databse currently has > > 800-900 updates and inserts done to it in a day along with about 1000 > > selects. Would I run in to any table locking problems with MyISAM? > > If you want to support transactions and/or foriegn key constraints then > you have to go InnoDB. As for locking problems, I would guess not, but > I'll leave that to more experienced list members. > > Josh > > > Thanks in advance. > > > --------------------------------------------------------------------- > 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
