Bug #42885 buf_read_ahead_random, buf_read_ahead_linear counters, thread wakeups

• Submitted: 16 Feb 2009 14:52
• Modified: 20 Jun 2010 17:30
• Reporter: Mark Callaghan
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S5 (Performance)
• Version: 5.0,5.1,plugin
• OS: Any
• Assigned to: Satya B
• CPU Architecture: Any
• Tags: ahead, innodb, read

[16 Feb 2009 14:52] Mark Callaghan

Description:
buf_read_ahead_random and buf_read_ahead_linear:

* increment a counter when the end of the function is reached rather than once for each block prefetched. This counter is exported via SHOW STATUS. The value in the counter today is hard to understand. It should count the number of prefetches.

* buf_read_ahead_random() calls os_aio_simulated_wake_handler_threads() whether or not any prefetch requests were done. It does not call os_aio_simulated_put_read_threads_to_sleep() prior to this.

* buf_read_ahead_linear() calls os_aio_simulated_wake_handler_threads() whether or not any prefetch requests were done. It called os_aio_simulated_put_read_threads_to_sleep() prior but that is described as a Windows optimization. Is that code really needed on Linux? The wakeup call is needed after the sleep call, so my question is whether the sleep call is needed.

How to repeat:
read the code

Suggested fix:
Use: srv_read_ahead_seq += count and srv_read_ahead_rnd += count

Remove the call to os_aio_simulated_wake_handler_threads() from buf_read_ahead_random()

[2 Jul 2009 8:36] Sveta Smirnova

Thank you for the report.

Verified as described.

From buf_read_ahead_linear definition:

/* Since Windows XP seems to schedule the i/o handler thread
    very eagerly, and consequently it does not wait for the
    full read batch to be posted, we use special heuristics here */

    os_aio_simulated_put_read_threads_to_sleep();

[23 Aug 2009 19:51] Inaam Rana

A wakeup call is not contingent upon a sleep call. It is needed to wakeup the IO threads whenever we submit some work with OS_AIO_SIMULATED_WAKE_LATER flag set.

True that we are free not to call wakeup if no requests have been submitted. InnoDB is just being a little conservative here which should be OK in terms of performance because the wakeup call is just some extra cycles and it does not wakeup the handler threads unless it finds a pending request.

[11 Sep 2009 23:03] Calvin Sun

The counters are added into the plugin (1.0.5).

[14 Oct 2009 14:39] Bugs System

Pushed into 5.1.41 (revid:[e-mail]-20091014143611-cphb0enjlx6lpat1) (version source revid:[e-mail]-20091008103857-nl1inwpel4n3dk5k) (merge vers: 5.1.40) (pib:13)

[22 Oct 2009 6:36] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091022063126-l0qzirh9xyhp0bpc) (version source revid:[e-mail]-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

[22 Oct 2009 7:09] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091022060553-znkmxm0g0gm6ckvw) (version source revid:[e-mail]-20091019131022-2o2ymjfjjoraq833) (merge vers: 5.5.0-beta) (pib:13)

[2 Nov 2009 23:20] Calvin Sun

Two new status variables are added to the SHOW STATUS output. These global status
variables Innodb_buffer_pool_read_ahead and Innodb_buffer_pool_read_ahead_evicted indicate the number of pages read in as part of read ahead and the number of such pages evicted without ever being accessed respectively. These counters provide global values since the start of the server. Also the status variables Innodb_buffer_pool_read_ahead_rnd and Innodb_buffer_pool_read_ahead_seq have been removed from the SHOW STATUS output.

[5 Nov 2009 18:05] Paul DuBois

Noted in 5.1.41, 5.5.0 changelogs.

For InnoDB Plugin, two new status variables have been added to SHOW
STATUS output. Innodb_buffer_pool_read_ahead and 
Innodb_buffer_pool_read_ahead_evicted indicate the number of pages
read in by the InnoDB read-ahead background thread, and the number of
such pages evicted without ever being accessed, respectively. Also,
the status variables Innodb_buffer_pool_read_ahead_rnd and
Innodb_buffer_pool_read_ahead_seq status variables have been removed.

The built-in version of InnoDB is not affected by these changes.

[18 Dec 2009 10:38] Bugs System

Pushed into 5.1.41-ndb-7.1.0 (revid:[e-mail]-20091218102229-64tk47xonu3dv6r6) (version source revid:[e-mail]-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

[18 Dec 2009 10:54] Bugs System

Pushed into 5.1.41-ndb-6.2.19 (revid:[e-mail]-20091218100224-vtzr0fahhsuhjsmt) (version source revid:[e-mail]-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

[18 Dec 2009 11:09] Bugs System

Pushed into 5.1.41-ndb-6.3.31 (revid:[e-mail]-20091218100616-75d9tek96o6ob6k0) (version source revid:[e-mail]-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

[18 Dec 2009 11:23] Bugs System

Pushed into 5.1.41-ndb-7.0.11 (revid:[e-mail]-20091218101303-ga32mrnr15jsa606) (version source revid:[e-mail]-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

[5 May 2010 15:06] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 17:55] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[28 May 2010 5:54] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:23] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:50] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[30 May 2010 0:02] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[15 Jun 2010 8:21] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:38] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[17 Jun 2010 11:54] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:32] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:20] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)