Sounds good. Thanks for the info, Heikki. --Walt
-----Original Message----- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 23, 2002 11:13 AM To: [EMAIL PROTECTED] Subject: Re: Benchmarking MyISAM, InnoDB, and Oracle: a problem with InnoDB Walt, this is probably a performance bug in Linux. I have seen the same phenomenon on our 2-way Xeon, Linux-2.4.4 when there are just big SELECT queries running concurrently. My hypothesis is that the semaphore reservations I added to 3.23.44 cause some strange phenomenon inside the Linux kernel. Funny, but the problem did not appear when there were also inserts present. It seems to be some kind of a resonance problem. I will change .48 so that there are less semaphore operations in it. That will probably fix the problem, at least it it fixed a similar problem in some other concurrent SELECTs. You could try running MySQL-Max-3.23.43 to check if the strange phenomenon happens in it too. Regards, Heikki .................. I'm finally finishing up my benchmarking, comparing MyISAM, Oracle, and InnoDB, and am having a problem with InnoDB. The final testing was load testing; we set up a number of perl scripts to simulate massive inserts, subquery inserts, selects, and updates to three tables. A master perl script runs all of the other scripts that connect and perform the activity on the tables. The benchmarking is being done on a VA Linux 2230 with 2gb of memory and two disk drives (about 30gb each) available. We're running Linux kernel 2.4.17, MySQL 3.23.47, Oracle 8.1.7.2. Doing inserts only, we can load 95,000 rows into one table in about 1 minute on MyISAM and around 2 minutes for both InnoDB and Oracle. When we run everything -- inserts, selects, updates -- at once, Oracle finishes the test in about 12 minutes, MyISAM finishes in about 26 minutes, but InnoDB just keeps running, and running, and running. In one test we let InnoDB run for over 24 hours and it was still going. MySQL doesn't hang; the daemons are cranking like crazy the whole time. There winds up being 19 MySQL processes that keep running. I'm thinking it's a configuration issue, but I'm not sure what I'm doing wrong. It seems to be the selects that are taking so long. Doing a show processlist, each of the processes look like this: | Id | User | Host | db | Command | Time | State| Info | 12 | oracle | localhost | test_innodb | Query | 817 | Copying to tmp table | SELECT q.key_val, q.timestamp_val, q.range_val, q.rand_val,d.text_val FROM bench_data d, bench | And output from part of the InnoDB Monitor looks like this: MySQL thread id 12, query id 137166 localhost oracle Copying to tmp table SELECT q.key_val, q.timestamp_val, q.range_val, q.rand_val, d.text_val FROM bench_data d, bench Trx read view will not see trx with id >= 0 618839, sees < 0 618839 I've copied the my.cnf file; it's at the end of the email. At this point I'm not sure what's wrong. We're hoping to migrate from MyISAM to InnoDB, but our production environment is web-hosting based one that can get very busy, and a combination of inserts, updates, and selects is common. If anyone has any ideas about configuring InnoDB for this benchmarking I'm all ears. I'm also more than happy to provide output from InnoDB Monitor, the perl scripts, etc. Thanks,-- 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 --------------------------------------------------------------------- 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
