高性能MySQL读书笔记

zyh3033

　　http://hi.baidu.com/thinkinginlamp/blog/item/d6daa61e9f9acc11413417cc.html
高性能MySQL读书笔记：找出谁持有锁
周末重读了一遍《高性能MySQL》，发现有些知识点看过便忘了，没有实际动手操作一遍就是记不牢，所

以今天动手操作了一下“找出谁持有锁”，并把实验步骤记录下来，有兴趣的网友可以参照一二。

问题的背景：在实际使用MySQL时，如果访问量比较大，那么很可能会出现大量Locked状态的进程，但是

却不能方便的识别是哪条SQL引起的问题，很多人遇到此类问题时，多半是通过PhpMyAdmin查询可疑SQL

，然后KILL掉，但问题是可疑SQL可能会很多，这样逐一尝试太过笨拙，有的人一怒之下很可能会重启

MySQL，但如此治标不治本的方法肯定更不可取。

开始实验，在test数据库先建立一个测试表foo（注意：是MyISAM表类型），添加若干数据：

CREATE TABLE IF NOT EXISTS `foo` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`str` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

INSERT INTO `foo` (`id`, `str`) VALUES
(1, 'a'),
(2, 'b');

打开一个MySQL命令行终端：

mysql> USE test;
mysql> SELECT SLEEP(12345) FROM foo;

再打开一个MySQL命令行终端：

mysql> USE test;
mysql> UPDATE foo SET str='bar';

此时执行SHOW PROCESSLIST，可以看到已经出现Locked现象了：

10  User sleep  SELECT sleep(12345) FROM foo
20  Locked      UPDATE foo SET str = 'bar'

当然，我们知道是SLEEP堵塞了UPDATE，但如果不是这个实验，面对同样的情况，比如说几百个SQL查询

同时映入眼帘，我们如何来判断呢？此时没人能打包票，只能瞎蒙了，经验有时候很重要，但我们还需要

明确的命令，在这里就是：

mysqladmin debug

说明：debug 是告诉服务器向错误日志写入调试信息。


注意：如何你没有设定“.my.cnf”配置文件的话，可能需要输入用户名和密码参数

命令执行后，不会有任何明确的输出，不要着急，有价值的东西此时已经被保存到了错误日志里：

mysql> SHOW VARIABLES LIKE 'log_error';

找到错误日志的具体路径后，打开，查看日志的最后部分：

10  test.foo    Locked - read       Low priority read lock
20  test.foo    Waiting - write     High priority write lock

如此，我们就能看到id是10的SQL堵塞了id是20的SQL，至于具体的SQL，到SHOW PROCESSLIST里对照

一下就能看到了。
----------------------------------------------------------
mysql> create table if not exists foo(
    -> id int(10) unsigned not null  auto_increment,
    -> str varchar(100) not null,
    -> primary key (id))
    -> engine=myisam;
Query OK, 0 rows affected (0.09 sec)

insert into foo values (1,"b");


mysql> show processlist;
+----+------+----------------+-------+---------+------+--------------+----------
--------------------+
| Id | User | Host           | db    | Command | Time | State        | Info
                    |
+----+------+----------------+-------+---------+------+--------------+----------
--------------------+
| 26 | root | localhost:3332 | zhang | Query   |  146 | Sending data | select sl
eep(12345) from foo |
| 28 | root | localhost:3371 | zhang | Query   |   26 | Locked       | update fo
o set str='c'       |
| 29 | root | localhost:3378 | NULL  | Query   |    0 | NULL         | show proc
esslist             |
+----+------+----------------+-------+---------+------+--------------+----------
--------------------+
3 rows in set (0.02 sec)

mysql>


把现在错误信息写进错误日志中去：
C:\Documents and Settings\Administrator>mysqladmin debug
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'ODBC'@'localhost' (using password: NO)'

C:\Documents and Settings\Administrator>mysqladmin debug -uroot -p123456

C:\Documents and Settings\Administrator>

--------------------------------------------------------------------

查看二进制日志：

C:\Documents and Settings\Administrator>mysqlbinlog 飞think-bin.000097
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
mysqlbinlog: File '飞think-bin.000097' not found (Errcode: 2)
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

C:\Documents and Settings\Administrator>


如何开启mysql的日志？
a)修改mysql的配置文件my.ini:
[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306

#mysql日志位置
log-error="E:/PHP/mysql/mysql5_install/MySQL Server 5.0/log/error.log" 
log="E:/PHP/mysql/mysql5_install/MySQL Server 5.0/log/mysql.log" 
long_query_time=2 
log-slow-queries= "E:/PHP/mysql/mysql5_install/MySQL Server 5.0/log/slowquery.log"
#Path to installation directory. All paths are usually resolved relative to this.
basedir="E:/PHP/mysql/mysql5_install/MySQL Server 5.0/"

#Path to the database root
datadir="E:/PHP/mysql/mysql5_install/MySQL Server 5.0/Data/"

--------------------------------------------
MYSQL启用日志，和查看日志
mysql有以下几种日志：
   错误日志：     -log-err
   查询日志：     -log
   慢查询日志:   -log-slow-queries
   更新日志:     -log-update
   二进制日志： -log-bin


是否启用了日志
mysql>show variables like 'log_%';

怎样知道当前的日志
mysql> show master status;

顯示二進制日志數目
mysql> show master logs;

看二进制日志文件用mysqlbinlog
shell>mysqlbinlog mail-bin.000001
或者shell>mysqlbinlog mail-bin.000001 | tail

在配置文件中指定log的輸出位置.
Windows：Windows 的配置文件为 my.ini，一般在 MySQL 的安装目录下或者 c:\Windows 下。
Linux：Linux 的配置文件为 my.cnf ，一般在 /etc 下。

在linux下：
Sql代码

   1. # 在[mysqld] 中輸入 
   2. #log 
   3. log-error=/usr/local/mysql/log/error.log 
   4. log=/usr/local/mysql/log/mysql.log 
   5. long_query_time=2 
   6. log-slow-queries= /usr/local/mysql/log/slowquery.log 

# 在[mysqld] 中輸入
#log
log-error=/usr/local/mysql/log/error.log
log=/usr/local/mysql/log/mysql.log
long_query_time=2
log-slow-queries= /usr/local/mysql/log/slowquery.log



windows下:
Sql代码

   1. # 在[mysqld] 中輸入 
   2. #log 
   3. log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log" 
   4. log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log" 
   5. long_query_time=2 
   6. log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log" 

# 在[mysqld] 中輸入
#log
log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log"
log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log"
long_query_time=2
log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log"



开启慢查询
long_query_time =2  --是指执行超过多久的sql会被log下来，这里是2秒
log-slow-queries= /usr/local/mysql/log/slowquery.log  --将查询返回较慢的语句进行记录

log-queries-not-using-indexes = nouseindex.log  --就是字面意思，log下来没有使用索引的query

log=mylog.log  --对所有执行语句进行记录

------------------------------
【关闭mysql的日志】
windows下只需要修改mysql的配置文件my.ini，把关于日志的选项注释掉，然后重启
mysql，即可关闭mysql的日志；
如：
#
[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306

#mysql日志位置
#log-error="E:/PHP/mysql/mysql5_install/MySQL Server 5.0/log/error.log" 
#log="E:/PHP/mysql/mysql5_install/MySQL Server 5.0/log/mysql.log" 
#long_query_time=2 
#log-slow-queries= "E:/PHP/mysql/mysql5_install/MySQL Server 5.0/log/slowquery.log"
#Path to installation directory. All paths are usually resolved relative to this.
basedir="E:/PHP/mysql/mysql5_install/MySQL Server 5.0/"

查询mysql的日志情况是否关闭：

mysql> show variables like 'log_%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_trust_function_creators | OFF   |
| log_error                       | .\    |
| log_queries_not_using_indexes   | OFF   |
| log_slave_updates               | OFF   |
| log_slow_queries                | OFF   |
| log_warnings                    | 1     |
+---------------------------------+-------+
7 rows in set (0.00 sec)

mysql>

-----------------------------
查看mysql的进程和端口号是否启动?

C:\Documents and Settings\Administrator>find /?
在文件中搜索字符串。

FIND [/V] [/C] [/N] [/I] [/OFF[LINE]] "string" [[drive:][path]filename[ ...]]

  /V        显示所有未包含指定字符串的行。
  /C        仅显示包含字符串的行数。
  /N        显示行号。
  /I        搜索字符串时忽略大小写。
  /OFF[LINE] 不要跳过具有脱机属性集的文件。
  "string"  指定要搜索的文字串，
  [drive:][path]filename
            指定要搜索的文件。

如果没有指定路径，FIND 将搜索键入的或者由另一命令产生的文字。

C:\Documents and Settings\Administrator>netstat -ano | find "3306"
  TCP    127.0.0.1:3306         127.0.0.1:3332         FIN_WAIT_2      1180
  TCP    127.0.0.1:3306         127.0.0.1:3371         FIN_WAIT_2      1180
  TCP    127.0.0.1:3332         127.0.0.1:3306         CLOSE_WAIT      6844
  TCP    127.0.0.1:3371         127.0.0.1:3306         CLOSE_WAIT      3596

C:\Documents and Settings\Administrator>



C:\Documents and Settings\Administrator>tasklist | find "mysql";
FIND: 参数格式不正确
C:\Documents and Settings\Administrator>tasklist | find "mysql"
mysql.exe                   6844 Console                 0      2,676 K

上面的find 也查查找字符串的，跟linux上面的grep 作用一样；

---------------------------------------
===============关于存储引警的================
mysql> show engines;
+------------+---------+------------------------------------------
--------+
| Engine     | Support | Comment
        |
+------------+---------+------------------------------------------
--------+
| MyISAM     | YES     | Default engine as of MySQL 3.23 with grea
        |
| MEMORY     | YES     | Hash based, stored in memory, useful for
es      |
| InnoDB     | DEFAULT | Supports transactions, row-level locking,
eys     |
| BerkeleyDB | NO      | Supports transactions and page-level lock
        |
| BLACKHOLE  | NO      | /dev/null storage engine (anything you wr
ppears) |
| EXAMPLE    | NO      | Example storage engine
        |
| ARCHIVE    | YES     | Archive storage engine
        |
| CSV        | NO      | CSV storage engine
        |
| ndbcluster | NO      | Clustered, fault-tolerant, memory-based t
        |
| FEDERATED  | NO      | Federated MySQL storage engine
        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables
        |
| ISAM       | NO      | Obsolete storage engine
        |
+------------+---------+------------------------------------------
--------+
12 rows in set (0.00 sec)

mysql> show variables like '%engin%'
    -> ;
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| engine_condition_pushdown | OFF    |
| have_blackhole_engine     | NO     |
| have_example_engine       | NO     |
| have_federated_engine     | NO     |
| have_merge_engine         | YES    |
| storage_engine            | InnoDB |
+---------------------------+--------+
6 rows in set (0.00 sec)

mysql> show create table foo;
+-------+---------------------------------------------------------
------------------------------------------------------------------
---------------------------+
| Table | Create Table

                           |
+-------+---------------------------------------------------------
------------------------------------------------------------------
---------------------------+
| foo   | CREATE TABLE `foo` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `str` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------
------------------------------------------------------------------
---------------------------+
1 row in set (0.00 sec)

------------------------------
一般情况下，mysql会默认提供多种存储引擎,你可以通过下面的查看:

看你的mysql现在已提供什么存储引擎:
mysql> show engines;

看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';

你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
mysql> show create table 表名;
------------------------------------------