MySQL常见问题分析及定位

柔光的暖阳◎ 2022-08-13 14:44 223阅读 0赞

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;

![20141101083746364][]

在来自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 语句灭了捣乱的连接。

[20141101083746364]: /images/20220810/abbb44babf1143c785e53514682137df.png