Shakeel, this may be a known problem. Another user running on Solaris reported low CPU and disk i/o in connection with queries taking a long time. The InnoDB Monitor output he sent suggests Solaris has problems scheduling a large number of threads.
I assume the problem is thread thrashing. I have already written modifications to upcoming 3.23.44 which should cure the problem. The 3.23.44 source should be ready on Monday. A French user who is mentioned on the page http://www.innodb.com/userstories.html runs an average workload of 800 inserts/updates per second on a 4-way Solaris box. Thus in normal conditions your load should not be excessive at all. But please send me the InnoDB Monitor output you have. It contains very valuable information about what causes the thrashing. You should also run the Unix 'top' when the thrashing occurs, and send the output of 'top' to me. Regards, Heikki Innobase Oy >We seem to be seeing some sort of strange problem when delete's are >combined wth inserts. Here's the situation. Our database is essentially >a cache db so we constantly clear the cache, about once per hour. We have >a column in the table whoose name is expire. We calculate the expire time >in the application, and then insert that timestamp into this column. When >we go do delete our statement is as follows: > >DELETE FROM cache_default where expire < now() limit 100; > >This bascially delete's all the expired entries from the table. We pause >between the 100 deletes since with Myisam tables we lock the table, so >this gives it a chance to process other requests. However, >we see some serious issues when it comes to inserts, and then after awhile >of this, we'll see some serious issues with selects as well. Our inserts >always insert a record where the expire time is in the future, so it >should not conflict with the delete which should be affecting rows that >are in the past. > >It seems as though we get lots of inserts that take a substantial amount >of time to do their job. The strange thing is that even inserts that do >not affect the table that is being deleted are drastically slowed down. >Most of the time while this is happening the selects still go through >very quickly, however, if the problem persists for awhile, even selects >will hang around. I've tried to do an innodb_monitor during the most >recent occurance of this, but it wasn't dumping the information, and I had >to kill the mysql base so that our site wouldn't go down. However, I do >have a previous monitor output if anyone thinks that would be useful. > >We're not doing that many queries/sec, maybe in the order of about 80 or >90/sec. I've seen on the Innodb home page that there is a company doing >1200/sec so we shouldn't even be coming close to that. We've got an E-450 >running solaris 8 with mysql using innodb tables. We've applied the latest >8_Recommended patch cluster to this machine.The machine has 4x400Mhz CPU's >and 2GB of ram. When running an iostat on the drive with the datafiles on >it, I don't see excessive usage on the drive, so it doesn't seem to be a >disk I/O problem.Here is the pertinent information from the my.cnf file. >innodb_data_file_path = > ibdata1:4000M;ibdata2:4000M;ibdata3:4000M;ibdata4:4000M;\ > ibdata5:4000M;ibdata6:4000M;ibdata7:4000M;ibdata8:4000M >innodb_flush_log_at_trx_commit=0 >set-variable =innodb_buffer_pool_size=512M >set-variable = innodb_log_buffer_size=8M >innodb_log_archive=0 >set-variable = innodb_additional_mem_pool_size=128M >set-variable =innodb_file_io_threads=4 >set-variable = innodb_lock_wait_timeout=50 > >In the time it took for me to make this post, it happened again. This >time I was able to capture a good amount of the innodb_monitor before I >had to kill the server. > >If anyone has any ideas as to what is going on, I would appreciate that. I >would hate to have to go back to using the myisam tables. The data >caching and row level locking provides great performance boosts for >us....when it's working of course. > >--shak --------------------------------------------------------------------- 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
