Hi, I wanted to post a follow-up question to the inquiry below. I've done some more research since my last post and now think that the performance problem is related to something other than uncommitted transactions. More specifically, I think the culprit is the lack of timely synchronization (or merging) of the purge list. As I mentioned below, we insert and delete a large number of rows into the DB in a rapid fashion. The newly deleted rows get marked for deletion, but are not physically purged. This seems to be indicated by 'show table status' reporting over 800K rows in the table in question, while in reality the table is empty. Restarting the DB forced the purge list to be either merged on shutdown (with an earlier version of MySQL/InnoDB) or to start being merged when restarted.
We've looked at the source a little and it seems that the scheduling algorithm for the purging tries to initiate the purge process after a period of inactivity (10 sec?). Once started, the process completes, but in our case, it seems that something's preventing it from starting, and so it essentially starves, leaving the purge list full and forcing its scans to answer queries. Does that sound right? If so, can someone shed some light on the scheduling algorithm used to merge the purge list? We originally thought that we just had to make sure there was some idle period for it to get started, but that doesn't seem to be the case. We would make sure that our applications didn't send any queries and wait for about a minute, and the purge process still wouldn't begin. On the other hand, a few seconds after we shut down our application, the process would kick off and after a while correctly sync up the purge list with the database. (We can leave a 'mysql' client console session open, and it doesn't seem to affect the merge operation, as long as we don't send any queries for a while.) We're using the MMMySQL JDBC driver version 2.0 in our application, in case it makes a difference. Can someone give any insights on what could be done to allow MySQL/Innodb to process the purge list in a timely manner? Thanks in advance, Alex Zeltser >"Tom Dangler" <[EMAIL PROTECTED]> wrote: > >>>>>>Is there any way to tell whether MySQL/InnoDB think that there are still >>>>>>uncommitted transactions? >>Yes - show innodb status will give you this information. >> >>-----snipped from show innodb status-------------- >> >> ---TRANSACTION 0 8627445, ACTIVE 6809 sec, OS thread id 503835 >>MySQL thread id 11328, query id 107361 xxx.xxx.xxx.xxx user >>Trx read view will not see trx with id >= 0 8627446, sees < 0 8627446 >> >>------------------------------------------------------------- >> >>Check out http://www.mysql.com/doc/en/Innodb_Monitor.html for more info. Also of >>interest is >>the innodb_lock_monitor which gives some additional information on your transactions >>- described at the above link. >> >>>>> <[EMAIL PROTECTED]> 05/21/03 11:26AM >>> >>Thank you to all who has replied to my original question. It seems that the current >>working theory is that the behavior I've observed is due to transactions not having >>been committed. This could of course be the case, and I will double-check the code, >>but I'm reasonably sure that we commit each individual insert or delete as soon as >>it happens. (We only take advantage of transactions to make certain physically >>separate operations logically atomic.) >> >>Is there any way to tell whether MySQL/InnoDB think that there are still uncommitted >>transactions? Since I still have a server in that state, it would be possible for >>me to get more information about this problem, if I knew what to look for... >> >>If I see in our code that we do, in fact, commit all transactions right away, what >>could lead to them remaining physically uncommited? Furthermore, if it turns out >>that uncommited transactions weren't the cause, can anyone think of anything else >>that may have contributed to the server ending up in that state? >> >>Finally, is there a way to defragment the table 'live'--i.e. without dumping the >>data and recreating the table? >> >>Thank you in advance. Best regards, >> >>Alex >> >> >>__________________________________________________________________ >>Try AOL and get 1045 hours FREE for 45 days! >>http://free.aol.com/tryaolfree/index.adp?375380 >> >>Get AOL Instant Messenger 5.1 free of charge. Download Now! >>http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >> >> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >> >> > >__________________________________________________________________ >Try AOL and get 1045 hours FREE for 45 days! >http://free.aol.com/tryaolfree/index.adp?375380 > >Get AOL Instant Messenger 5.1 free of charge. Download Now! >http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 > > __________________________________________________________________ McAfee VirusScan Online from the Netscape Network. Comprehensive protection for your entire computer. Get your free trial today! http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 Get AOL Instant Messenger 5.1 free of charge. Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promo=380455 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
