Description:
While running OLTP_RW workload with very small dataset (1k rows) for mysql-5.5-innodb tree we have observed drop in performance between revisions 3186 and 3262.
# 5.5.7-rc-r3186
# 5.5.8-r3262
#
# Test: OLTP_RW:throughput, TPS
#
# Server 1 - 5.5.7-rc-1k
# Server 2 - 5.5.7-rc-10k
# Server 3 - 5.5.7-rc-100k
# Server 4 - 5.5.8-1k
# Server 5 - 5.5.8-10k
# Server 6 - 5.5.8-100k
#
# Server 1 Server 2 Server 3
# Thread INNODB INNODB INNODB
32 2104.65 2384.02 2483.25
# Thread Server 4 Server 5 Server 6
32 INNODB INNODB INNODB
1821.62 2275.09 2464.00
Further analysis showed that regression were introduced in rev.3219 in fix for
Bug#53046 dict_update_statistics_low can still be run concurrently on same table.
How to repeat:
Run OLTP_RW test with 1k rows
Suggested fix:
Below is the patch from Vasil that fixes observed drop:
--- storage/innobase/handler/ha_innodb.cc revid:vasil.dimov@oracle.com-20101205192108-geoyflq34lqt506q
+++ storage/innobase/handler/ha_innodb.cc 2010-12-07 13:59:29 +0000
@@ -8227,13 +8227,15 @@ ha_innobase::info_low(
stats.create_time = (ulong) stat_info.ctime;
}
}
if (flag & HA_STATUS_VARIABLE) {
- dict_table_stats_lock(ib_table, RW_S_LATCH);
+ if (!(flag & HA_STATUS_NO_LOCK)) {
+ dict_table_stats_lock(ib_table, RW_S_LATCH);
+ }
n_rows = ib_table->stat_n_rows;
/* Because we do not protect stat_n_rows by any mutex in a
delete, it is theoretically possible that the value can be
smaller than zero! TODO: fix this race.
@@ -8278,13 +8280,15 @@ ha_innobase::info_low(
ib_table->stat_clustered_index_size)
* UNIV_PAGE_SIZE;
stats.index_file_length = ((ulonglong)
ib_table->stat_sum_of_other_index_sizes)
* UNIV_PAGE_SIZE;
- dict_table_stats_unlock(ib_table, RW_S_LATCH);
+ if (!(flag & HA_STATUS_NO_LOCK)) {
+ dict_table_stats_unlock(ib_table, RW_S_LATCH);
+ }
/* Since fsp_get_available_space_in_free_extents() is
acquiring latches inside InnoDB, we do not call it if we
are asked by MySQL to avoid locking. Another reason to
avoid the call is that it uses quite a lot of CPU.
See Bug#38185. */
@@ -8351,13 +8355,15 @@ ha_innobase::info_low(
"is different from the number of "
"indexes %u defined in the MySQL ",
ib_table->name, num_innodb_index,
table->s->keys);
}
- dict_table_stats_lock(ib_table, RW_S_LATCH);
+ if (!(flag & HA_STATUS_NO_LOCK)) {
+ dict_table_stats_lock(ib_table, RW_S_LATCH);
+ }
for (i = 0; i < table->s->keys; i++) {
ulong j;
/* We could get index quickly through internal
index mapping with the index translation table.
The identity of index (match up index name with
@@ -8415,13 +8421,15 @@ ha_innobase::info_low(
table->key_info[i].rec_per_key[j] =
rec_per_key >= ~(ulong) 0 ? ~(ulong) 0 :
(ulong) rec_per_key;
}
}
- dict_table_stats_unlock(ib_table, RW_S_LATCH);
+ if (!(flag & HA_STATUS_NO_LOCK)) {
+ dict_table_stats_unlock(ib_table, RW_S_LATCH);
+ }
}
if (srv_force_recovery >= SRV_FORCE_NO_IBUF_MERGE) {
goto func_exit;
}
Description: While running OLTP_RW workload with very small dataset (1k rows) for mysql-5.5-innodb tree we have observed drop in performance between revisions 3186 and 3262. # 5.5.7-rc-r3186 # 5.5.8-r3262 # # Test: OLTP_RW:throughput, TPS # # Server 1 - 5.5.7-rc-1k # Server 2 - 5.5.7-rc-10k # Server 3 - 5.5.7-rc-100k # Server 4 - 5.5.8-1k # Server 5 - 5.5.8-10k # Server 6 - 5.5.8-100k # # Server 1 Server 2 Server 3 # Thread INNODB INNODB INNODB 32 2104.65 2384.02 2483.25 # Thread Server 4 Server 5 Server 6 32 INNODB INNODB INNODB 1821.62 2275.09 2464.00 Further analysis showed that regression were introduced in rev.3219 in fix for Bug#53046 dict_update_statistics_low can still be run concurrently on same table. How to repeat: Run OLTP_RW test with 1k rows Suggested fix: Below is the patch from Vasil that fixes observed drop: --- storage/innobase/handler/ha_innodb.cc revid:vasil.dimov@oracle.com-20101205192108-geoyflq34lqt506q +++ storage/innobase/handler/ha_innodb.cc 2010-12-07 13:59:29 +0000 @@ -8227,13 +8227,15 @@ ha_innobase::info_low( stats.create_time = (ulong) stat_info.ctime; } } if (flag & HA_STATUS_VARIABLE) { - dict_table_stats_lock(ib_table, RW_S_LATCH); + if (!(flag & HA_STATUS_NO_LOCK)) { + dict_table_stats_lock(ib_table, RW_S_LATCH); + } n_rows = ib_table->stat_n_rows; /* Because we do not protect stat_n_rows by any mutex in a delete, it is theoretically possible that the value can be smaller than zero! TODO: fix this race. @@ -8278,13 +8280,15 @@ ha_innobase::info_low( ib_table->stat_clustered_index_size) * UNIV_PAGE_SIZE; stats.index_file_length = ((ulonglong) ib_table->stat_sum_of_other_index_sizes) * UNIV_PAGE_SIZE; - dict_table_stats_unlock(ib_table, RW_S_LATCH); + if (!(flag & HA_STATUS_NO_LOCK)) { + dict_table_stats_unlock(ib_table, RW_S_LATCH); + } /* Since fsp_get_available_space_in_free_extents() is acquiring latches inside InnoDB, we do not call it if we are asked by MySQL to avoid locking. Another reason to avoid the call is that it uses quite a lot of CPU. See Bug#38185. */ @@ -8351,13 +8355,15 @@ ha_innobase::info_low( "is different from the number of " "indexes %u defined in the MySQL ", ib_table->name, num_innodb_index, table->s->keys); } - dict_table_stats_lock(ib_table, RW_S_LATCH); + if (!(flag & HA_STATUS_NO_LOCK)) { + dict_table_stats_lock(ib_table, RW_S_LATCH); + } for (i = 0; i < table->s->keys; i++) { ulong j; /* We could get index quickly through internal index mapping with the index translation table. The identity of index (match up index name with @@ -8415,13 +8421,15 @@ ha_innobase::info_low( table->key_info[i].rec_per_key[j] = rec_per_key >= ~(ulong) 0 ? ~(ulong) 0 : (ulong) rec_per_key; } } - dict_table_stats_unlock(ib_table, RW_S_LATCH); + if (!(flag & HA_STATUS_NO_LOCK)) { + dict_table_stats_unlock(ib_table, RW_S_LATCH); + } } if (srv_force_recovery >= SRV_FORCE_NO_IBUF_MERGE) { goto func_exit; }