List:Internals« Previous MessageNext Message »
From:Xuekun Hu Date:July 14 2009 6:02am
Subject:why REPEATABLE-READ read more innodb rows than READ-COMMITTED during
DBT2 run?
View as plain text  
Hi, Guys

I set transaction isolation level to REPEATABLE-READ and
READ-COMMITTED separately to test DBT2 10 warehouses on 16 cores
system with 32 connections. The dbt2 performance with REPEATABLE-READ
was 9% lower than with READ-COMMITTED. By checking status, I found the
REPEATABLE-READ mode was reading 64% more "innodb_rows_read" than
READ-COMMITTED mode.

DBT2-10WH, Normalized to one NOTPM		
REPEATABLE-READ	
	Innodb_rows_deleted				5.78 		
	Innodb_rows_inserted				134.14 		
	Innodb_rows_read				25693.97
	Innodb_rows_updated				253.05 		
	Queries						640.23 		
READ-COMMITTED	
	Innodb_rows_deleted				8.46 		
	Innodb_rows_inserted				134.22 		
	Innodb_rows_read				15699.77
	Innodb_rows_updated				252.18 		
	Queries						640.45 		

From my understanding, "innodb_rows_read" is counting the number of
rows read from InnoDB tables. Here the "row" should refer to the
physical one, instead of snapshot one. That means if two concurrent
transactions were updating a same row, two record versions were
created. However when another consistent read read this row, the
counter "innodb_rows_read" will be added by 1, not 2 or 3.

So why REPEATABLE-READ read more innodb rows than READ-COMMITTED? Your
comments are appreciated.

Thx, Xuekun
Thread
why REPEATABLE-READ read more innodb rows than READ-COMMITTED during DBT2 run?Xuekun Hu14 Jul
  • Re: why REPEATABLE-READ read more innodb rows than READ-COMMITTED during DBT2 run?Xuekun Hu15 Jul
  • Re: why REPEATABLE-READ read more innodb rows than READ-COMMITTEDduring DBT2 run?Guilhem Bichot29 Jul
    • Re: why REPEATABLE-READ read more innodb rows than READ-COMMITTED during DBT2 run?Xuekun Hu30 Jul