From: Xuekun Hu
Date: July 15 2009 12:59pm
Subject: Re: why REPEATABLE-READ read more innodb rows than READ-COMMITTED 
	during DBT2 run?
List-Archive: http://lists.mysql.com/internals/37190
Message-Id: <398d69300907150559m44aa539bq31aef68cec3c462a@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

does anyone has ideas? :-)

On Tue, Jul 14, 2009 at 2:02 PM, Xuekun Hu<xuekun.hu@stripped> wrote:
> 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
> =A0 =A0 =A0 =A0Innodb_rows_deleted =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 5.78
> =A0 =A0 =A0 =A0Innodb_rows_inserted =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0134.14
> =A0 =A0 =A0 =A0Innodb_rows_read =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A025693.97
> =A0 =A0 =A0 =A0Innodb_rows_updated =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 253.05
> =A0 =A0 =A0 =A0Queries =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 640.23
> READ-COMMITTED
> =A0 =A0 =A0 =A0Innodb_rows_deleted =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 8.46
> =A0 =A0 =A0 =A0Innodb_rows_inserted =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0134.22
> =A0 =A0 =A0 =A0Innodb_rows_read =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A015699.77
> =A0 =A0 =A0 =A0Innodb_rows_updated =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 252.18
> =A0 =A0 =A0 =A0Queries =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 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
>