Port v5.1 SHOW MASTER LOGS without SUPER privilege

Summary:
Makes SHOW MASTER LOGS work for users without SUPER privilege.

Test Plan: New test included, passes.

Reviewers: chip

Reviewed By: chip
Port v5.1 process_can_disable_bin_log

Summary:
SUPER has been required to do 'set sql_log_bin=...'.
This provides an option that PROCESS is sufficient,
but only when process_can_disable_bin_log is set.

Test Plan: New test included, passes.

Reviewers: chip

Reviewed By: chip
Port v5.1 sql_log_bin changes

Summary:
When sql_log_bin is OFF, don't get S row locks during
insert into T select from S statements on rows of S.

The code already checks for srv_locks_unsafe_for_binlog.

In addition, also check for session level sql_log_bin option.

Test Plan: New test included, passes.

Reviewers: nponnekanti

Reviewed By: nponnekanti
Port replication counter stats to 5.6

Summary:
Adds Rpl_count* for event counts and Rpl_seconds* for event timers
"
show global status like "rpl_count%";
Variable_name Value
Rpl_count_append_block 0
Rpl_count_begin_load_query 0
Rpl_count_create_file 0
Rpl_count_delete_file 0
Rpl_count_delete_rows 0
Rpl_count_exec_load 0
Rpl_count_execute_load_query 0
Rpl_count_format_description 0
Rpl_count_incident 0
Rpl_count_intvar 0
Rpl_count_load 0
Rpl_count_new_load 0
Rpl_count_other 0
Rpl_count_pre_ga_delete_rows 0
Rpl_count_pre_ga_update_rows 0
Rpl_count_pre_ga_write_rows 0
Rpl_count_query 0
Rpl_count_rand 0
Rpl_count_rotate 0
Rpl_count_slave 0
Rpl_count_start_v3 0
Rpl_count_stop 0
Rpl_count_table_map 0
Rpl_count_unknown 0
Rpl_count_update_rows 0
Rpl_count_user_var 0
Rpl_count_write_rows 0
Rpl_count_xid 0
"
show global status like "rpl_seconds%";
Variable_name Value
Rpl_seconds_append_block 0.000000
Rpl_seconds_begin_load_query 0.000000
Rpl_seconds_create_file 0.000000
Rpl_seconds_delete_file 0.000000
Rpl_seconds_delete_rows 0.000000
Rpl_seconds_exec_load 0.000000
Rpl_seconds_execute_load_query 0.000000
Rpl_seconds_format_description 0.000000
Rpl_seconds_incident 0.000000
Rpl_seconds_intvar 0.000000
Rpl_seconds_load 0.000000
Rpl_seconds_new_load 0.000000
Rpl_seconds_other 0.000000
Rpl_seconds_pre_ga_delete_rows 0.000000
Rpl_seconds_pre_ga_update_rows 0.000000
Rpl_seconds_pre_ga_write_rows 0.000000
Rpl_seconds_query 0.000000
Rpl_seconds_rand 0.000000
Rpl_seconds_rotate 0.000000
Rpl_seconds_slave 0.000000
Rpl_seconds_start_v3 0.000000
Rpl_seconds_stop 0.000000
Rpl_seconds_table_map 0.000000
Rpl_seconds_unknown 0.000000
Rpl_seconds_update_rows 0.000000
Rpl_seconds_user_var 0.000000
Rpl_seconds_write_rows 0.000000
Rpl_seconds_xid 0.000000

Test Plan: mysqltest.sh

Reviewers: steaphan

Reviewed By: steaphan
Port Stats: binlog slave offset to 5.6

Summary:
This uses a variation of code from the Google patch to display the
binlog name and offset currently downloaded by a replication slave
SHOW PROCESSLIST;
Id User Host db Command Time State Info
3 root localhost:47190 test Query 1 Writing to net INSERT INTO t1 SELECT * FROM t1
5 root localhost:47194 NULL Binlog Dump 1 NULL slave offset: master-bin.000001 4

Test Plan: mysqltest

Reviewers: steaphan

Reviewed By: steaphan
Port Stats: slow_log_if_rows_examined to 5.6

Summary:
add slow_log_if_rows_examined for slow query log

Test Plan:
mysqltest.sh
New test: slow_log_extra_rows_examined_exceed

Reviewers: steaphan

Reviewed By: steaphan
Port Per-Tablespace Mutex Contention Fix to 5.6

Summary:
Use a separate mutex and hash table for per-tablespace stats.

This is done to reduce mutex contention on fil_system->mutex as that
mutex is briefly locked before every page read and we don't want to make
those operations wait. The new hash is fil_system->stats_hash and it
uses an array of mutexes. Entries are searched by tablespace ID. There
is a small window right after a tablespace is created and right before
it is deleted when there might not be an entry in stats_hash for a valid
tablespace. The hash can be searched via the function
fil_stats_get_by_id.

Test Plan:
Tested with mysqltest.sh and passed in both release and debug modes.

Reviewers: steaphan

Reviewed By: steaphan
Port Fuzzy Checkpoint Limits to 5.6

Summary:
Adds the Innodb my.cnf variable innodb_sync_checkpoint_limit.
This is a percentage and is used to override the sync and async checkpoint
limits. This is ignored when set to 0. Those limits determine when dirty
pages are flushed to enforce fuzzy checkpoint constraints. Making this
greater than 80 might reduce the dirty page flush rate. Assume that max_size
is the sum of the transaction log file sizes, then the current code sets the
sync checkpoint limit to 0.9 * 0.9 * (15/16) of max_size (about 75% of it)
and the async limit to 0.9 * 0.9 * (7/8) of max size (about 70% of it).
When this is set to a non-zero value, then the sync limit is set to
(sync_checkpoint_limit/100) * 0.95 of max_size and the async limit is set to
(sync_checkpoint_limit/100) * 0.90 of max_size. The impact of this is that
you get the performance benefits of a larger transaction log file without
making it larger.
"
Below, "age" is Innodb_lsn_current - Innodb_lsn_oldest
Also adds SHOW STATUS counters:
Innodb_preflush_async_limit - limit at which async page flushes are done for fuzzy checkpoint
Innodb_preflush_sync_limit - limit at which sync page flushes are done for fuzzy checkpoint
Innodb_preflush_async_margin - age minus preflush_async_limit
Innodb_preflush_sync_margin - age minus preflush_sync_limit
Innodb_checkpoint_lsn - LSN of last checkpoint
Innodb_lsn_current - current LSN
Innodb_lsn_current_minus_oldest - current LSN minus oldest LSN for which there
is a dirty page in the buffer pool
Innodb_lsn_current_minus_last_checkpoint
Innodb_lsn_oldest - min of the oldest modification LSN from all dirty pages
Innodb_purged_pages - number of pages processed by trx_purge

Test Plan: mysqltest.sh

Reviewers: steaphan

Reviewed By: steaphan
Port Stats: Log -vs- Double-Write to 5.6

Summary:
Adds separate counters for log and doublewrite IO.

Add separate performance counters for log and doublewrite writes as these
have different characteristics then page IO.

This adds new SHOW GLOBAL STATUS counters
Innodb_data_log_write_requests 530
Innodb_data_log_write_bytes 9830912
Innodb_data_log_write_svc_seconds 0.010076
Innodb_data_double_write_requests 19
Innodb_data_double_write_bytes 8568832
Innodb_data_double_write_svc_seconds 0.314883

This adds 2 lines to the FILE IO section of SHOW INNODB STATUS
Log writes: 535 requests, 0 old, 18386.06 bytes/r, svc: 0.01 secs, 0.02
msecs/r, 3.31 max msecs, wait: 0.01 secs 0.02 msecs/r, 3.31 max msecs
Doublewrite buffer writes: 20 requests, 0 old, 421888.00 bytes/r, svc: 0.01
secs, 0.30 msecs/r, 0.81 max msecs, wait: 0.01 secs 0.30 msecs/r, 0.81 max msecs

Test Plan: mysqltest.sh

Reviewers: steaphan

Reviewed By: steaphan
Port Ignore innodb_thread_concurrency for Replication Thread to 5.6

Summary:
Ignore innodb_thread_concurrency for the SQL replication thread.
Set trx->always_enter_innodb for the SQL replication thread in the
Innodb handler. Ignore the thread queue in srv_conc_enter_innodb when
it is set. Give it more tickets to avoid more of the expensive checks
in srv_conc_enter_innodb

Test Plan: mysqltest.sh

Reviewers: steaphan

Reviewed By: steaphan
Port Fix Locality of trx_ts to 5.6

Summary:
Previously, trx_t's were allocated at random heap locations.
This causes performance issues when iterating over all open transactions
(such as when creating a new transaction's list of visible
transactions). This change allocates larger blocks of trx_t's and
tracks the next usable trx_t with a singly linked list. As blocks are
filled, new blocks are allocated. This results in a 50% improvement in
performance in the 2000 transaction test case.

Test Plan: All unit tests pass

Reviewers: chip, steaphan

Reviewed By: chip
Port Stats: Slow IOs to 5.6

Summary:
Uses "slow_ios" in place of "old_ios" in SHOW STATUS, SHOW INNODB STATUS
and IS tables because the results are for slow IOs as defined here.

* slow IOs are requests for which the service time is too long
  (innodb_aio_slow_usecs)

* old IOs are requests that wait too long to be serviced
  (innodb_aio_old_usecs). This is only possible for async reads and
  async writes.

Adds the my.cnf variable innodb_aio_slow_usecs. File reads, writes and
fsyncs that take longer than this are counted as "slow_ios".

Changes the default value for innodb_aio_old_usecs from 2 seconds to 0.5
seconds.

Adds SHOW STATUS counters:
* data_fsync_max_seconds - slowest fsync for InnoDB
* data_fsync_slow - number of slow fsyncs

These count the number of operations that are slow:
* data_async_read_slow_ios
* data_sync_read_slow_ios
* data_async_write_slow_ios
* data_sync_write_slow_ios
* data_log_write_slow_ios
* data_double_write_slow_ios

These count the number of operations that wait too long in request
queues for background IO threads:
* data_async_read_old_ios
* data_async_write_old_ios

Test Plan: mysqltest.sh

Reviewers: steaphan

Reviewed By: steaphan
Port Optimizations for os_aio_thread_buffer to 5.6

Summary:
Make the number of slots in the IO array is a function of
innodb_io_capacity. Use os_aio_thread_buffer and os_aio_thread_buffer_size to
reduce malloc/free calls. Increment the slot pointer rather than calling
os_aio_array_get_nth_slots. Use one pass instead of 2 on the request array to
determine the oldest and lowest request. Use one pass instead of nested loop
on the array to determine the requests to be merged.

Test Plan: mysqltest.sh

Reviewers: steaphan, jtolmer

Reviewed By: jtolmer
Port per page type table stats, and fix native_aio data stats

Summary:
Read/write per page stats for tablespaces are collected from os_aio,
global per page stats are collected in a bit different place
(buf_page_io_complete) but these numbers still should be comparable.

Native aio stats are only collected to update the IO_READ_XXX,
IO_WRITTEN_XXX in table_stats; os_async_read_perf, os_async_write_perf
used in innodb status dump.

Test Plan:
added test: show_table_stats_per_page_type
added test: innodb.innodb_aio_stats

Reviewers: akotulski, rongrong

Reviewed By: akotulski, rongrong