Aha! That may explain why an operation that takes several minutes on a
large, otherwise idle, InnoDB table can take hours when there is other
activity on the table.
> -----Original Message-----
> From: Marko Mäkelä [mailto:marko.makela@stripped]
> Sent: Thursday, August 02, 2007 6:10 AM
> To: Baron Schwartz
> Cc: Internals List
> Subject: Re: Why does a txn say ACTIVE 60 sec when stmt has
> been running240 secs?
>
> On Thu, Aug 02, 2007 at 08:43:35AM -0400, Baron Schwartz wrote:
> > Long statements seem to "restart" the InnoDB transaction
> several times
> > during the time they are running, as though the statement
> is actually
> > several transactions.
> >
> > For example, I just ran an UPDATE that took over 8 minutes
> to complete.
> > Yet SHOW INNODB STATUS never showed an ACTIVE time greater
> than a couple of
> > minutes. (I tried to grab a sample of the processlist and
> InnoDB status,
> > but just then the statement completed).
>
> As far as I can tell, InnoDB initializes trx->start_time = time(NULL)
> in two places: when creating a trx object or when starting a
> transaction.
> The latter case seems to be guarded by the test
> trx->conc_state == TRX_NOT_STARTED.
>
> The field conc_state is initialized to TRX_NOT_STARTED in
> trx_create(),
> trx_commit_off_kernel(), and trx_cleanup_at_db_startup().
>
> I would tend to believe that it is MySQL that is committing the
> transaction for some reason, or the "commit ALTER TABLE every
> 10,000 rows"
> hack in ha_innobase::write_row() is not working properly. Can you set
> a breakpoint on innobase_commit_low or add some debug printout there?
>
> > This isn't just idle curiosity; I'm trying to track down
> something weird
> > happening in replication, where timestamps are different on
> the slave, and
> > I wonder if large transactions have anything to do with it.
> Your thoughts
> > on either problem are appreciated. I'm working on getting
> together enough
> > details for a bug report, but I don't have enough info yet
> to be very
> > specific.
>
> I'm not a replication expert, but as far as I understand, transactions
> are sent to the replication slave upon commit. The upcoming row-based
> replication should be less prone to errors than the old
> statement-based
> replication.
>
> Best regards,
>
> Marko Mäkelä
> Innobase Oy/Oracle Corp.
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:
> http://lists.mysql.com/internals?unsub=1
>
>
