Thursday, July 20, 2006

SHOW INNODB STATUS: TRANSACTIONS


------------
TRANSACTIONS
------------
Trx id counter 4 4177811381
Purge done for trx's n:o < 4 4177810974 undo n:o < 0 0
History list length 68
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
MySQL thread id 6, query id 12052143 Has read all relay log; waiting for the slave I/O thread to update it
---TRANSACTION 4 4177811379, ACTIVE 0 sec, process no 1537, OS thread id 418595012843 committing



  • Purge done for trx's n:o - number of transactions to which a purge is done. Means gets rid of old transactions that are not needed anymore. *Look Tuning secion

  • History list length 68 - number of transaction in the undo space

  • Total number of lock structs in row lock hash table - not locked rows but structures which one row may have many of



Tuning


If you see a lot of "waiting in InnoDB queue" then tune innodb_thread_conccurency raise it higher - for my typical workloads, which a fast single statement transactions I allow the innodb kernel to have 32 threads at any given time

If you see a lot of "sleeping before joining InnoDB queue" then tune
innodb_thread_sleep_delay variable. Value is specified in microseconds. Innodb sleeps a thread before it starts to wait for entry into the queue. This is to avoid many threads going into the thread queue to make the most of innodb_thread_conccurency

1 comment:

Frank said...

Hi Dathan,

It was nice to meet you at MySQL Camp.

How many CPUs does the box has on which you have 32 threads?

Just curious.

Frank