Alex,
why you do not look with SHOW INNODB STATUS if there are dangling
transactions which could still see the delete-marked rows? Purge cannot
remove them then.
The main InnoDB thread tries to run a full purge even when the server is
active. This snippet is from srv0srv.c of 4.0.14:
"
/* We run a full purge every 10 seconds, even if the server
were active */
n_pages_purged = 1;
last_flush_time = time(NULL);
while (n_pages_purged) {
if (srv_fast_shutdown && srv_shutdown_state > 0) {
goto background_loop;
}
srv_main_thread_op_info = (char*)"purging";
n_pages_purged = trx_purge();
current_time = time(NULL);
if (difftime(current_time, last_flush_time) > 1) {
srv_main_thread_op_info = (char*) "flushing log";
log_write_up_to(ut_dulint_max, LOG_WAIT_ONE_GROUP,
TRUE);
last_flush_time = current_time;
}
}
"
Regards,
Heikki
----- Original Message -----
From: <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, July 03, 2003 12:10 AM
Subject: Re: Unexpected empty table performance problem with MySQL and
InnoDB
> 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]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
