Mysql 性能调优 一  1

---

基本的tools工具查看mysql库信息

http://dev.mysql.com   开发网站
http://www.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)