Mysql 效能調優 一 1
Mysql 效能調優 一 1
基本的tools工具檢視mysql庫資訊
http://dev.mysql.com 開發網站
商業網站
1. 檢視狀態
mysql> show status like '%connect%';
mysql> show global status like '%connect%'; 全域性狀態
mysql> show session status like '%connect%'; 會話狀態
mysql> show status like '%connect%';
+-----------------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------------+-------+
| Aborted_connects | 0 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 3 |
| Max_used_connections | 1 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 1 |
+-----------------------------------------------+-------+
14 rows in set (0.01 sec)
mysql>
Aborted_connects 異常連線數， 過高，資料庫可能被攻擊
2. oracle ：schema = mysql : database
oracle : 一個賬號就是一個schema
3. information_schema 和 performance_schema 可以檢視很多效能資訊
mysql> show databases;
+---------------------+
| Database |
+---------------------+
| information_schema |
| centreon_status |
| #mysql50#lost+found |
| mysql |
| performance_schema |
| test |
+---------------------+
6 rows in set (0.00 sec)
4. 當前連線資訊
show processlist;
mysql> show processlist \G;
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost
db: information_schema
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 2. row ***************************
Id: 3
User: root
Host: localhost
db: NULL
Command: Sleep
Time: 4
State:
Info: NULL
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> kill 3; （殺會話）
Query OK, 0 rows affected (0.00 sec)
會話已殺，並重新連線：
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***
+---------------------+
| Database |
+---------------------+
| information_schema |
| centreon_status |
| #mysql50#lost+found |
| mysql |
| performance_schema |
| test |
+---------------------+
6 rows in set (0.02 sec)
mysql>
5. 檢視當前使用的資料引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql>
預設使用的是： InnoDB DEFAULT
6. 檢視引數資訊
mysql> show variables like '%buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| 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 | 4294967296 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 536870912 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
22 rows in set (0.00 sec)
mysql>
等等
7. 自帶提交
mysql> show variables like '%commit%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| autocommit | ON |
| binlog_order_commits | ON |
| innodb_api_bk_commit_interval | 5 |
| innodb_commit_concurrency | 0 |
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
5 rows in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
mysql>
8. 停啟服務
正常關閉：mysqladmin -uroot -p123456 shutdown
正常啟動：mysqld --console
[root@test1 ~]# mysqladmin -uroot -p123456 shutdown
Warning: Using a password on the command line interface can be insecure.
[root@test1 ~]# mysqld --console
2014-12-16 16:59:59 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-12-16 16:59:59 25121 [Note] Plugin 'FEDERATED' is disabled.
2014-12-16 16:59:59 25121 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-12-16 16:59:59 25121 [Note] InnoDB: The InnoDB memory heap is disabled
2014-12-16 16:59:59 25121 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-12-16 16:59:59 25121 [Note] InnoDB: Memory barrier is not used
2014-12-16 16:59:59 25121 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-12-16 16:59:59 25121 [Note] InnoDB: Using Linux native AIO
2014-12-16 16:59:59 25121 [Note] InnoDB: Using CPU crc32 instructions
2014-12-16 17:00:00 25121 [Note] InnoDB: Initializing buffer pool, size = 4.0G
2014-12-16 17:00:03 25121 [Note] InnoDB: Completed initialization of buffer pool
2014-12-16 17:00:06 25121 [Note] InnoDB: Highest supported file format is Barracuda.
2014-12-16 17:00:08 25121 [Note] InnoDB: 128 rollback segment(s) are active.
2014-12-16 17:00:08 25121 [Note] InnoDB: Waiting for purge to start
2014-12-16 17:00:08 25121 [Note] InnoDB: 5.6.22 started; log sequence number 1626017
2014-12-16 17:00:09 25121 [Note] Server hostname (bind-address): '*'; port: 3306
2014-12-16 17:00:09 25121 [Note] IPv6 is available.
2014-12-16 17:00:09 25121 [Note] - '::' resolves to '::';
2014-12-16 17:00:09 25121 [Note] Server socket created on IP: '::'.
2014-12-16 17:00:11 25121 [Note] Event Scheduler: Loaded 0 events
2014-12-16 17:00:11 25121 [Note] mysqld: ready for connections.
Version: '5.6.22' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
基本的tools工具檢視mysql庫資訊
http://dev.mysql.com 開發網站
商業網站
1. 檢視狀態
mysql> show status like '%connect%';
mysql> show global status like '%connect%'; 全域性狀態
mysql> show session status like '%connect%'; 會話狀態
mysql> show status like '%connect%';
+-----------------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------------+-------+
| Aborted_connects | 0 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 3 |
| Max_used_connections | 1 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 1 |
+-----------------------------------------------+-------+
14 rows in set (0.01 sec)
mysql>
Aborted_connects 異常連線數， 過高，資料庫可能被攻擊
2. oracle ：schema = mysql : database
oracle : 一個賬號就是一個schema
3. information_schema 和 performance_schema 可以檢視很多效能資訊
mysql> show databases;
+---------------------+
| Database |
+---------------------+
| information_schema |
| centreon_status |
| #mysql50#lost+found |
| mysql |
| performance_schema |
| test |
+---------------------+
6 rows in set (0.00 sec)
4. 當前連線資訊
show processlist;
mysql> show processlist \G;
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost
db: information_schema
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 2. row ***************************
Id: 3
User: root
Host: localhost
db: NULL
Command: Sleep
Time: 4
State:
Info: NULL
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> kill 3; （殺會話）
Query OK, 0 rows affected (0.00 sec)
會話已殺，並重新連線：
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***
+---------------------+
| Database |
+---------------------+
| information_schema |
| centreon_status |
| #mysql50#lost+found |
| mysql |
| performance_schema |
| test |
+---------------------+
6 rows in set (0.02 sec)
mysql>
5. 檢視當前使用的資料引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql>
預設使用的是： InnoDB DEFAULT
6. 檢視引數資訊
mysql> show variables like '%buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| 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 | 4294967296 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 536870912 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
22 rows in set (0.00 sec)
mysql>
等等
7. 自帶提交
mysql> show variables like '%commit%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| autocommit | ON |
| binlog_order_commits | ON |
| innodb_api_bk_commit_interval | 5 |
| innodb_commit_concurrency | 0 |
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
5 rows in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
mysql>
8. 停啟服務
正常關閉：mysqladmin -uroot -p123456 shutdown
正常啟動：mysqld --console
[root@test1 ~]# mysqladmin -uroot -p123456 shutdown
Warning: Using a password on the command line interface can be insecure.
[root@test1 ~]# mysqld --console
2014-12-16 16:59:59 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-12-16 16:59:59 25121 [Note] Plugin 'FEDERATED' is disabled.
2014-12-16 16:59:59 25121 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-12-16 16:59:59 25121 [Note] InnoDB: The InnoDB memory heap is disabled
2014-12-16 16:59:59 25121 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-12-16 16:59:59 25121 [Note] InnoDB: Memory barrier is not used
2014-12-16 16:59:59 25121 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-12-16 16:59:59 25121 [Note] InnoDB: Using Linux native AIO
2014-12-16 16:59:59 25121 [Note] InnoDB: Using CPU crc32 instructions
2014-12-16 17:00:00 25121 [Note] InnoDB: Initializing buffer pool, size = 4.0G
2014-12-16 17:00:03 25121 [Note] InnoDB: Completed initialization of buffer pool
2014-12-16 17:00:06 25121 [Note] InnoDB: Highest supported file format is Barracuda.
2014-12-16 17:00:08 25121 [Note] InnoDB: 128 rollback segment(s) are active.
2014-12-16 17:00:08 25121 [Note] InnoDB: Waiting for purge to start
2014-12-16 17:00:08 25121 [Note] InnoDB: 5.6.22 started; log sequence number 1626017
2014-12-16 17:00:09 25121 [Note] Server hostname (bind-address): '*'; port: 3306
2014-12-16 17:00:09 25121 [Note] IPv6 is available.
2014-12-16 17:00:09 25121 [Note] - '::' resolves to '::';
2014-12-16 17:00:09 25121 [Note] Server socket created on IP: '::'.
2014-12-16 17:00:11 25121 [Note] Event Scheduler: Loaded 0 events
2014-12-16 17:00:11 25121 [Note] mysqld: ready for connections.
Version: '5.6.22' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29500582/viewspace-1379430/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Mysql 效能調優 二 1MySql
- Mysql 效能調優 一 2MySql
- Mysql 效能調優 一 3MySql
- MySQL 效能調優技巧MySql
- Mysql 效能調優 二 2MySql
- MySQL效能調優"經驗"MySql
- MySQL入門--效能調優概述MySql
- mysql 效能調優五種方式MySql
- MySQL效能診斷與調優MySql
- 全棧必備——MySQL效能調優全棧MySql
- MySQL 效能調優的10個方法MySql
- Mysql效能優化一MySql優化
- MySQL調優效能監控之show profileMySql
- MySQL調優效能監控之performance schemaMySqlORM
- mysql效能的檢查和調優方法MySql
- MySQL效能調優my.cnf詳解MySql
- Spark學習——效能調優（一）Spark
- MySql（七）：MySQL效能調優——鎖定機制與鎖優化分析MySql優化
- 技術更新！10個MySQL效能調優技巧MySql
- Spark 效能調優--資源調優Spark
- Spark 效能調優--Shuffle調優 SortShuffleManagerSpark
- 【效能調優】效能測試、分析與調優基礎
- ElasticSearch效能調優Elasticsearch
- Nginx 效能調優Nginx
- iOS效能調優iOS
- php效能調優PHP
- Java效能調優Java
- Spark效能調優Spark
- oracle效能調優Oracle
- MySQL調優MySql
- 一些可以預見的Oracle應用程式效能調優 （1）Oracle
- MySQL效能分析和優化-part1MySql優化
- 【MySQL】效能優化之 index merge (1)MySql優化Index
- 效能調優概述，這是一篇最通俗易懂的效能調優總結
- 【MySQL】效能優化之 order by (一)MySql優化
- （1）Linux效能調優之Linux程式管理Linux
- 效能調優學習之硬體調優
- 效能調優實戰