主页

索引

模块索引

搜索页面

配置参数查询优化

mysql> show variables like '%slow%';

+---------------------+----------------------------+

| Variable_name       | Value                      |

+---------------------+----------------------------+

| log_slow_queries    | ON                         |

| slow_launch_time    | 2                          |

| slow_query_log      | ON                         |

| slow_query_log_file | /var/db/mysql/db1-slow.log |

+---------------------+----------------------------+

4 rows in set (0.01 sec)



mysql>  show global status like '%slow%';

+---------------------+-------+

| Variable_name       | Value |

+---------------------+-------+

| Slow_launch_threads | 0     |

| Slow_queries        | 8189  |

+---------------------+-------+

2 rows in set (0.00 sec)



mysql> show variables like 'max_connections';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| max_connections | 1000  |

+-----------------+-------+

1 row in set (0.01 sec)



mysql> show global status like 'max_used_connections';

+----------------------+-------+

| Variable_name        | Value |

+----------------------+-------+

| Max_used_connections | 200   |

+----------------------+-------+

1 row in set (0.00 sec)



mysql> show variables like 'key_buffer_size';

+-----------------+----------+

| Variable_name   | Value    |

+-----------------+----------+

| key_buffer_size | 33554432 |

+-----------------+----------+

1 row in set (0.00 sec)



mysql> show global status like 'key_read%';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Key_read_requests | 3727  |

| Key_reads         | 47    |

+-------------------+-------+

2 rows in set (0.00 sec)



mysql> show global status like 'key_blocks_u%';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Key_blocks_unused | 26745 |

| Key_blocks_used   | 47    |

+-------------------+-------+

2 rows in set (0.00 sec)



mysql> show global status like 'created_tmp%';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 3557  |

| Created_tmp_files       | 740   |

| Created_tmp_tables      | 67587 |

+-------------------------+-------+

3 rows in set (0.00 sec)



mysql> show global status like 'open%tables%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Open_tables   | 776   |

| Opened_tables | 1125  |

+---------------+-------+

2 rows in set (0.01 sec)



mysql> show variables like 'table_open_cache';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| table_open_cache | 2048  |

+------------------+-------+

1 row in set (0.00 sec)



mysql> show global status like 'thread%';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Threads_cached    | 8     |

| Threads_connected | 26    |

| Threads_created   | 16033 |

| Threads_running   | 3     |

+-------------------+-------+

4 rows in set (0.00 sec)



mysql> show variables like 'thread_cache_size';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| thread_cache_size | 8     |

+-------------------+-------+

1 row in set (0.00 sec)



mysql> show global status like 'qcache%';

+-------------------------+-----------+

| Variable_name           | Value     |

+-------------------------+-----------+

| Qcache_free_blocks      | 719       |

| Qcache_free_memory      | 132986888 |

| Qcache_hits             | 70172087  |

| Qcache_inserts          | 20107107  |

| Qcache_lowmem_prunes    | 15130405  |

| Qcache_not_cached       | 435001    |

| Qcache_queries_in_cache | 846       |

| Qcache_total_blocks     | 2439      |

+-------------------------+-----------+

8 rows in set (0.00 sec)



mysql> show variables like 'query_cache%';

+------------------------------+-----------+

| Variable_name                | Value     |

+------------------------------+-----------+

| query_cache_limit            | 2097152   |

| query_cache_min_res_unit     | 4096      |

| query_cache_size             | 134217728 |

| query_cache_type             | ON        |

| query_cache_wlock_invalidate | OFF       |

+------------------------------+-----------+

5 rows in set (0.00 sec)



mysql>

mysql> show global status like 'sort%';

+-------------------+----------+

| Variable_name     | Value    |

+-------------------+----------+

| Sort_merge_passes | 367      |

| Sort_range        | 6229816  |

| Sort_rows         | 22832911 |

| Sort_scan         | 34523    |

+-------------------+----------+

4 rows in set (0.00 sec)



mysql> show global status like 'open_files';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Open_files    | 81    |

+---------------+-------+

1 row in set (0.00 sec)



mysql> show variables like 'open_files_limit';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| open_files_limit | 11095 |

+------------------+-------+

1 row in set (0.01 sec)

mysql> show global status like 'table_locks%';

+-----------------------+----------+

| Variable_name         | Value    |

+-----------------------+----------+

| Table_locks_immediate | 27084296 |

| Table_locks_waited    | 2787     |

+-----------------------+----------+

2 rows in set (0.00 sec)



mysql> show global status like 'handler_read%';

+-----------------------+-------------+

| Variable_name         | Value       |

+-----------------------+-------------+

| Handler_read_first    | 122428      |

| Handler_read_key      | 3266127950  |

| Handler_read_last     | 100         |

| Handler_read_next     | 1208736117  |

| Handler_read_prev     | 42526108    |

| Handler_read_rnd      | 22443206    |

| Handler_read_rnd_next | 15328993696 |

+-----------------------+-------------+

7 rows in set (0.00 sec)



mysql> show global status like 'com_select';

+---------------+----------+

| Variable_name | Value    |

+---------------+----------+

| Com_select    | 20573518 |

+---------------+----------+

1 row in set (0.00 sec)

主页

索引

模块索引

搜索页面