mysql> show engine innodb status \G;
 *************************** 1. row ***************************
   Type: InnoDB
   Name:
 Status:
 =====================================
 2015-07-20 16:35:06 7ff4efbd1700 INNODB MONITOR OUTPUT
 =====================================
 Per second averages calculated from the last 5 seconds
 -----------------
 BACKGROUND THREAD
 -----------------
 srv_master_thread loops: 56 srv_active, 0 srv_shutdown, 24558 srv_idle
 srv_master_thread log flush and writes: 24614
 ----------
 SEMAPHORES
 ----------
 OS WAIT ARRAY INFO: reservation count 207
 OS WAIT ARRAY INFO: signal count 204
 Mutex spin waits 107, rounds 3210, OS waits 107
 RW-shared spins 83, rounds 2520, OS waits 84
 RW-excl spins 1, rounds 480, OS waits 16
 Spin rounds per wait: 30.00 mutex, 30.36 RW-shared, 480.00 RW-excl
 ------------
 TRANSACTIONS
 ------------
 Trx id counter 14048
 Purge done for trx's n:o < 14032 undo n:o < 0 state: running but idle
 History list length 277
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 14047, not started
 MySQL thread id 6, OS thread handle 0x7ff4ef908700, query id 489 196.128.1.123 root cleaning up
 ---TRANSACTION 0, not started
 MySQL thread id 3, OS thread handle 0x7ff4ef949700, query id 356 196.128.1.123 root cleaning up
 ---TRANSACTION 0, not started
 MySQL thread id 2, OS thread handle 0x7ff4efb90700, query id 491 196.128.1.123 root cleaning up
 ---TRANSACTION 14045, not started
 MySQL thread id 1, OS thread handle 0x7ff4efbd1700, query id 492 localhost root init
 show engine innodb status
 --------
 FILE I/O
 --------
 I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
 I/O thread 1 state: waiting for completed aio requests (log thread)
 I/O thread 2 state: waiting for completed aio requests (read thread)
 I/O thread 3 state: waiting for completed aio requests (read thread)
 I/O thread 4 state: waiting for completed aio requests (read thread)
 I/O thread 5 state: waiting for completed aio requests (read thread)
 I/O thread 6 state: waiting for completed aio requests (write thread)
 I/O thread 7 state: waiting for completed aio requests (write thread)
 I/O thread 8 state: waiting for completed aio requests (write thread)
 I/O thread 9 state: waiting for completed aio requests (write thread)
 Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
  ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
 Pending flushes (fsync) log: 0; buffer pool: 0
 524 OS file reads, 3253 OS file writes, 548 OS fsyncs
 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
 -------------------------------------
 INSERT BUFFER AND ADAPTIVE HASH INDEX
 -------------------------------------
 Ibuf: size 1, free list len 0, seg size 2, 0 merges
 merged operations:
  insert 0, delete mark 0, delete 0
 discarded operations:
  insert 0, delete mark 0, delete 0
 Hash table size 276671, node heap has 1 buffer(s)
 0.00 hash searches/s, 0.00 non-hash searches/s
 ---
 LOG
 ---
 Log sequence number 370547070
 Log flushed up to   370547070
 Pages flushed up to 370547070
 Last checkpoint at  370547070
 0 pending log writes, 0 pending chkp writes
 185 log i/o's done, 0.00 log i/o's/second
 ----------------------
 BUFFER POOL AND MEMORY
 ----------------------
 Total memory allocated 137363456; in additional pool allocated 0
 Dictionary memory allocated 148788
 Buffer pool size   8191
 Free buffers       6400
 Database pages     1790
 Old database pages 640
 Modified db pages  0
 Pending reads 0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages made young 786, not young 88
 0.00 youngs/s, 0.00 non-youngs/s
 Pages read 428, created 2319, written 2770
 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 No buffer pool page gets since the last printout
 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
 LRU len: 1790, unzip_LRU len: 0
 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 --------------
 ROW OPERATIONS
 --------------
 0 queries inside InnoDB, 0 queries in queue
 0 read views open inside InnoDB
 Main thread process no. 2074, id 140689922205440, state: sleeping
 Number of rows inserted 557065, updated 4, deleted 0, read 3014729
 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
 ----------------------------
 END OF INNODB MONITOR OUTPUT
 ============================

1 row in set (0.00 sec)

ERROR:
 No query specified

mysql> set global hot_cache.key_buffer_size=128*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> set global hot_cache.key_buffer_size=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'key_buffer_s';
Empty set (0.00 sec)

mysql> show variables like 'key_buffer_s%';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)

mysql> set global hot_cache.key_buffer_size=128*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 engine=myisam;
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create index ind_t1 on t1(id);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> cache index t1 in hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+
1 row in set (0.00 sec)

mysql>  show variables like 'read_buffer_s%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.00 sec)

mysql>  show variables like 'read_rnd_buffer_s%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
1 row in set (0.00 sec)

 MyISAM内存优化：

mysql> show variables like 'key_cache_di%';
 +--------------------------+-------+
 | Variable_name            | Value |
 +--------------------------+-------+
 | key_cache_division_limit | 100   |
 +--------------------------+-------+
 1 row in set (0.00 sec)

 mysql> set global key_cache_division_limit=70;
 Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'key_cache_di%';
 +--------------------------+-------+
 | Variable_name            | Value |
 +--------------------------+-------+
 | key_cache_division_limit | 70    |
 +--------------------------+-------+
 1 row in set (0.00 sec)

 mysql> show variables like 'key_cache_age%';
 +-------------------------+-------+
 | Variable_name           | Value |
 +-------------------------+-------+
 | key_cache_age_threshold | 300   |
 +-------------------------+-------+
 1 row in set (0.00 sec)

 InnoDB内存优化：

mysql> show variables like 'innodb_old%';
 +------------------------+-------+
 | Variable_name          | Value |
 +------------------------+-------+
 | innodb_old_blocks_pct  | 37    |
 | innodb_old_blocks_time | 1000  |
 +------------------------+-------+
 2 rows in set (0.00 sec)

1000即使1s

 mysql> show variables like 'innodb_lru_scan%';
 +-----------------------+-------+
 | Variable_name         | Value |
 +-----------------------+-------+
 | innodb_lru_scan_depth | 1024  |
 +-----------------------+-------+
 1 row in set (0.00 sec)

mysql>  show variables like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
8 rows in set (0.00 sec)

mysql> select 134217728/1024/1024 \G;
*************************** 1. row ***************************
134217728/1024/1024: 128.00000000
1 row in set (0.00 sec)

ERROR:
No query specified

[root@dba ~]# mysqladmin -S /var/lib/mysql/mysql.sock ext -p | grep -i innodb_buffer_pool
Enter password:
| Innodb_buffer_pool_dump_status                | not started |
| Innodb_buffer_pool_load_status                | not started |
| Innodb_buffer_pool_pages_data                 | 396         |
| Innodb_buffer_pool_bytes_data                 | 6488064     |
| Innodb_buffer_pool_pages_dirty                | 0           |
| Innodb_buffer_pool_bytes_dirty                | 0           |
| Innodb_buffer_pool_pages_flushed              | 60          |
| Innodb_buffer_pool_pages_free                 | 7796        |
| Innodb_buffer_pool_pages_misc                 | 0           |
| Innodb_buffer_pool_pages_total                | 8192        |
| Innodb_buffer_pool_read_ahead_rnd             | 0           |
| Innodb_buffer_pool_read_ahead                 | 0           |
| Innodb_buffer_pool_read_ahead_evicted         | 0           |
| Innodb_buffer_pool_read_requests              | 7524        |
| Innodb_buffer_pool_reads                      | 393         |
| Innodb_buffer_pool_wait_free                  | 0           |
| Innodb_buffer_pool_write_requests             | 233         |

INNODB缓存池的命中率：

1 row in set (0.00 sec)

mysql> select (1-(393/7524))*100 \G;
*************************** 1. row ***************************
(1-(393/7524))*100: 94.7767
1 row in set (0.00 sec)

ERROR:
No query specified

 mysql> show variables like 'innodb_buffer_pool_i%';
 +------------------------------+-------+
 | Variable_name                | Value |
 +------------------------------+-------+
 | innodb_buffer_pool_instances | 8     |
 +------------------------------+-------+
 1 row in set (0.00 sec)

 mysql> show variables like 'innodb_max_d%';
 +--------------------------------+-------+
 | Variable_name                  | Value |
 +--------------------------------+-------+
 | innodb_max_dirty_pages_pct     | 75    |
 | innodb_max_dirty_pages_pct_lwm | 0     |
 +--------------------------------+-------+
 2 rows in set (0.00 sec)

mysql> show variables like 'innodb_io_c%';
 +------------------------+-------+
 | Variable_name          | Value |
 +------------------------+-------+
 | innodb_io_capacity     | 200   |
 | innodb_io_capacity_max | 2000  |
 +------------------------+-------+
 2 rows in set (0.00 sec)

mysql>  show variables like 'innodb_adaptive_f%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_adaptive_flushing     | ON    |
| innodb_adaptive_flushing_lwm | 10    |
+------------------------------+-------+
2 rows in set (0.00 sec)

mysql> show variables like '%doublewr%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_doublewrite | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'sort_m%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_flush_log_at%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout    | 1     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

mysql> show engine innodb status\G select sleep(60);show engine innodb status\G
Log sequence number 375785900
1 row in set (0.00 sec)

1 row in set (59.99 sec)

Log sequence number 375785900
1 row in set (0.00 sec)

mysql> nopager
PAGER set to stdout
mysql> select round((375785900-375785900)/1024/1024) as MB;
+------+
| MB   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

select * from GLOBAL_STATUS t where t.VARIABLE_NAME='innodb_os_log_written';

mysql> show variables like 'max_conne%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)

mysql> show variables like 'open_file%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 5000  |
+------------------+-------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.23    |
+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'back_l%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log      | 80    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'table_open%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_open_cache           | 2000  |
| table_open_cache_instances | 1     |
+----------------------------+-------+
2 rows in set (0.00 sec)

mysql> show variables like 'thread%';
+--------------------+---------------------------+
| Variable_name      | Value                     |
+--------------------+---------------------------+
| thread_cache_size  | 9                         |
| thread_concurrency | 10                        |
| thread_handling    | one-thread-per-connection |
| thread_stack       | 262144                    |
+--------------------+---------------------------+
4 rows in set (0.00 sec)

mysql> show variables like 'innodb_lock_wait_t%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)