MySQL常见问题分析及定位

1、MySQL管理基础

2、MySQL连接常见问题

3、字符集与大小写敏感问题

4、存储空间问题

5、MySQL性能问题跟踪与定位

MySQL管理基础

1. 配置及状态信息查看

显示系统变量值
    show variables;

查看系统状态值
    show [session|global] status;

查看innodb状态
    show innodb status\G;

查看mysql查询线程
    show processlist;

查看mysql错误日志
    /var/lib/mysql/[hostname].err;

2. 配置修改

修改内存配置
    SET [global|session] variable =  value, variable = value, …;

修改配置文件
    vi /etc/my.cnf

[root@oracledev etc]# more my.cnf
# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password    = your_password
default-character-set=utf8
port        = 3306
socket        = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
skip-name-resolve
character_set_server=utf8
tmp_table_size=64M
max_heap_table_size=64M
lower_case_table_names=1
port        = 3306
socket        = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id    = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
#
# binary logging format - mixed recommended
#binlog_format=mixed

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
[root@oracledev etc]# 

2、MySQL连接常见问题

MySQL连接常见问题

ERROR 1040 (HY000): Too many connections

连接过多的两个相关系统变量

Max_connections
    最大连接数
Max_user_connections
    用户最大连接数

3、字符集与大小写敏感问题

1. MySQL字符集支持两个方面

字符集(Character set)
排序方式(Collation)
2. MySQL字符集控制的四个级别

Server
Database
Table
Column

3. 服务器级字符集控制

字符集(Character set)
character-set-server        (5.5版本以前使用default-character-set)

排序方式(Collation)
collation_server=utf8_bin 

4. 数据库级字符集控制

create database db  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
5. 表级字符集控制

create table utf ( id int ) default charset=utf8 default collate=utf8_bin;

6. 列级字符集控制

 CREATE TABLE Table1(column1 VARCHAR(5) CHARACTER SET utf8 COLLATE utf8_bin);

7. 表名大小写相关的系统变量值

lower-case-table-names

0: 大小写敏感。默认值

1:表名在硬盘上以小写保存，名称对大小写不敏感

4、存储空间问题

1. ibdata文件巨大的问题

原因
Innodb默认将内部数据字典，undo日志，更新缓存，Innodb存储引擎的数据和索引存放在ibdata文件中，此文件只会扩展，不自动收缩感

解决方案
1.将数据库备份导出
2.修改my.cnf，设置innodb_file_per_table参数
3.关闭MySQL服务并将ibdata, ib_logfile*删除
4.重启MySQL服务，将导入第1步备份的数据

2. mysql-bin文件过多

原因
MySQL将对数据库更新的SQL语句会进行日志记录，存储在mysql-bin.xxxx文件中，默认每个文件大小为1.1G，默认一直保存，事务频繁的数据库会产生大量的mysql-bin日志文件。

解决方案
expire_logs_days = 10    (设置在[mysqld]节点下，非之前安装文档中提到的配置文件最后)
删除历史日志文件
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

3. 数据量巨大

原因
原规划磁盘空间不足以容纳现有数据量。

迁移方案
1. mkdir /data
2. service mysql stop
3. mv /var/lib/mysql /data
3. ln -s /data/mysql  /var/lib/mysql (需要关闭系统SELinux,否则导致权限问题无法启动MySQL)
4. service mysql start

5、MySQL性能问题跟踪与定位

1. MySQL的四个配置文件模板

my-small.cnf
内存 <= 64M,只运行MySQL
my-medium.cnf
内存 32M-64M，只运行MySQL；或者内存128M，运行MySQL和其它应用程序
my-large.cnf
内存 512M，主要用于运行MySQL
my-huge.cnf
内存1G-2G，主要用于运行MySQL
my-innodb-heavy-4G.cnf
内存4G，少量的连接，大量的查询

通用情况

每秒查询率QPS: Queries增量/time
    QPS=Queries / seconds
    间隔时间参考: 10s, 1h, 24h
    Show status like ‘Queries ’:已经发送给服务器的查询个数(不单是select操作)

每秒事务量TPS
    TPS=Com_delete/Com_insert/Com_update/Com_delete等

事务TPS:
    TPS=(Com_rollback +  Com_commit,) / seconds

读写比率：
    ( select + qcache_hits) / ( insert + update + delete + replace )
    QCACHE_hits:查询缓存被访问的次数。

表缓存：opened_tables增量/time
    时间间隔建议为1h或24h
    Opened_tables: 已经打开的表的数量。如果opened_tables较大，table_cache值可能太小。
    高峰期查看：variable:table_cache – status: open_tables

线程缓存:threads_created增量/time
    Threads_created:创建用来处理连接的线程数。如果Threads_created较大，可能要增加thread_cache_size值。
    高峰期查看：variable:thread_cache_size – status:threads_cached

连接数: connections
    Variable:max_connections- status: max_used_connections
    Variable:max_connections – status: threads_connected

查询缓存命中率
    Qcache_hits / (Com_select + QCache_hits)
    Qcache_hits:查询缓存命中的次数

查询缓存删除速率
    Qcache_lowmem_prunes / time
    Qcache_lowmem_prunes: 由于内存较少从缓存删除的查询数量。
    增大查询缓存值query_cache_size, 以减少lowmem, 增加缓存命中率

MyISAM

Key缓存命中率
    1 – (key_reads / key_read_requests)
    Key_reads从硬盘读取键的数据块的次数
    Key_read_requests从缓存读键的数据块的请求
    命中率低调大key_buffer_size

MyISAM连接锁比率
    Table_locks_waited / table_locks_immediate
    <1%佳，1%需引起注意，>3%性能问题
    Table_locks_waited: 不能立即获得的表的锁的次数
    Table_locks_immediate:立即获得的表的锁的次数

Innodb

Cache命中率
     ( innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
    Innodb_buffer_pool_reads:不能满足InnoDB必须单页读取的缓冲池中的逻辑读数据。
    Innodb_buffer_pool_read_requests:InnoDB已经完成的逻辑读请求数。
     该值应小， 否则调大变量innodb_buffer_pool_size

Innodb缓冲池
    Innodb_buffer_pool_wait_free:向Innodb缓冲池写时，如果需要读或创建页，并且没有干净的页可用，则需等待页面清空的次数。
    该值应小，否则需调大缓冲池innodb_buffer_pool_size大小。

日志
    Innodb_log_waits:因为日志缓冲区太小，必须等待清空的时间
    此值尽量小，否则需要调大innodb_log_file_size

MySQL线程运行情况

show full processlist;

在来自SHOW PROCESSLIST的输出中常见的一些状态：

　　· Checking table

　　线程正在执行(自动)表格检查。

　　· Closing tables

　　意味着线程正在刷新更改后的表数据，并正在关闭使用过的表。这应该是一个快速的操作。如果不快，则您应该验证您的磁盘没有充满，并且磁盘没有被超负荷使用。

　　· Connect Out

　　连接到主服务器上的从属服务器。

　　· Copying to tmp table on disk

　　临时结果集合大于tmp_table_size。线程把临时表从存储器内部格式改变为磁盘模式，以节约存储器。

　　· Creating tmp table

　　线程正在创建一个临时表，以保持部分结果。

　　· deleting from main table

　　服务器正在执行多表删除的第一部分，只从第一个表中删除。

　　· deleting from reference tables

　　服务器正在执行多表删除的第二部分，从其它表中删除匹配的行。

　　· Flushing tables

　　线程正在执行FLUSH TABLES，并正在等待所有线程，以关闭表。

　　· FULLTEXT initialization

　　服务器正在准备执行一个自然语言全文本搜索。

　　· Killed

　　有人已经向线程发送了一个KILL命令。在下一次检查终止标记时，应放弃。该标记在MySQL的每个大循环中都检查，但是在有些情况下，线程终止只需要较短的时间。如果该线程被其它线程锁定，则只要其它线程接触锁定，终止操作就会生效。

　　· Locked

　　该查询被其它查询锁定。

　　· Sending data

　　线程正在为SELECT语句处理行，同时正在向客户端发送数据。

　　· Sorting for group

　　线程正在进行分类，以满足GROUP BY要求。

　　· Sorting for order

　　线程正在进行分类，以满足ORDER BY要求。

　　· Opening tables

　　线程正在试图打开一个表。这应该是非常快的过程，除非打开操作受到阻止。例如，一个ALTER TABLE或一个LOCK TABLE语句可以阻止打开一个表，直到语句完成为止。

　　· Removing duplicates

　　查询正在使用SELECT DISTINCT。使用时，在早期阶段，MySQL不能优化不同的操作。因此，MySQL要求一个额外的阶段，以便在把结果发送给客户端之前取消所有的复制行。

　　· Reopen table

　　线程得到一个表锁定，但是在得到锁定后被通知带下方的表结构已更改了。它已经释放了锁定，关闭了表，并试图重新打开它。

　　· Repair by sorting

　　修复代码正在使用一个分类来创建索引。

　　· Repair with keycache

　　修复代码正在通过关键缓存一个接一个地使用创建关键字。这比通过分类修复要慢很多。

　　· Searching rows for update

　　线程正在进行第一阶段，以在更新之前，查找所有匹配的行。如果UPDATE正在更改用于查找相关行的索引，则必须这么做。

　　· Sleeping

　　线程正在等待客户端，以向它发送一个新语句。

　　· System lock

　　线程正在等待得到一个用于表的外部系统锁定。如果您没有正在使用多个正在访问同一个表的mysqld服务器，则您可以使用--skip-external-locking选项禁用系统锁定。

　　· Upgrading lock

　　INSERT DELAYED管理程序正在试图得到一个表锁定，以插入行。

　　· Updating

　　线程正在搜索行，并正在更新这些行。

　　· User Lock

　　线程正在等待GET_LOCK()。

　　· Waiting for tables

　　线程得到一个通知，表的底层结构已经改变，需要重新打开表以得到新的结构。但是，为了能重新打开表，必须等待，直到所有其它的线程已经关闭了正在被质询的表。

　　如果其它线程已经对正在被质询的表使用了FLUSH TABLES或以下语句之一：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE或OPTIMIZE TABLE;则会出现通知。

　　· waiting for handler insert

　　INSERT DELAYED管理程序已经处理了所有处于等待状态的插入，并正在等待新插入。

　　多数状态对应于非常快的操作。如果一个线程在这些状态下停留了数秒，则可能是有问题，需要进行调查。

　　有一些其它的状态，在前面的清单中没有提及，但是其中有很多状态对于查找服务器中的程序错误是有用的。

　　从这里可以看到是不是有表锁死了，是不是有些语句执行了很久，甚至可以通过kill id 语句灭了捣乱的连接。