一、mysql出现Copying to tmp table耗时较长:修改配置tmp_table_size
今天在MYSQL中偶然发现查询命令中有很多语名处在Copying to tmp table状态,接口响应也变得很慢。因为业务需要这个SQL也确实比较复杂,同时因为有union操作,也使用到了临时表。
Copying to tmp table on disk The temporary result set was larger than tmp_table_size and the thread is now changing the in memory-based temporary table to a disk based one to save memory.
Copying to tmp table状态即表示如果查询超出了MYSQL中配置的tmp_table_size的限制,tmp_table_size配置是每个线程都要分配的大小。应用中实际起限制作用除了tmp_table_size配置外还有max_heap_table_size(会取两者中的最小值)。如果内存临时表超出了限制,MySQL就会自动地把它转化为基于磁盘的MyISAM表,并用配置中的tmpdir即/tmp来保存查询结果,这无疑会降低响应速度。
但是我的MYSQL本身的配置也不低:tmp_table_size已经配置为1G了.
mysql> show variables like '%tmp%';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 1073741824 |
| tmpdir | /tmp |
+-------------------+------------+
mysql> show variables like 'max_heap_table_size';
+---------------------+------------+
| Variable_name | Value |
+---------------------+------------+
| max_heap_table_size | 1073741824 |
+---------------------+------------+
1 row in set (0.00 sec)
另外也不知道当时为什么,MYSQL报了这一次的问题后,之后也没再出现这样的问题。不确定是不是有什么特殊情况,按说这个配置已经足够大了。等哪天再碰到时再来研究。
#附上动态改变配置tmp_table_size和max_heap_table_size的命令
set global tmp_table_size=1073741824;
set max_heap_table_size=1073741824;
二、启动mysql时导致启动很多MYSQL进程-mysql进程数一直在增加
测试服务器假期之前切断过电源一次,今天登录测试机时发现机器超慢,随便ps看了一下进程,发现mysql的进程非常多,看来是mysql出问题了,统计了一下mysql的进程数,有6000多个了,真要命,批量杀掉这些进程。
pkill -9 mysql #批量杀死所有mysql进程,还是这个方法快捷
因为但这也怪了,之前的mysql运行好好的。看来是加在开机自动启动里的命令有问题。可我的启动命令也看不出什么问题啊,如下:
#开机启动mysql
/opt/modules/mysql57/bin/mysqld_safe --user=mysql &
但现实确实发现我在命令里使用这个命令启动时mysql时导致启动很多MYSQL进程-mysql进程数一直在增加。应该是mysql配置的问题了,导致mysql启动失败然后一直在启动。我的mysql是5.7版本,且安装目录并不是默认目录。配置配置文件时需要进行以下修改:
#安装和初始化数据库
[kermit]# bin/mysql_install_db --user=mysql --basedir=/opt/modules/mysql57 --datadir=/opt/modules/mysql57/data
#拷贝mysql的配置文件以及启动文件
[kermit]# cp -a /opt/modules/mysql57/support-files/my-default.cnf /etc/my.cnf
[kermit]# cp -a /opt/modules/mysql57/support-files/mysql.server /etc/init.d/mysqld
#---------注意-------------
#如果要自定义安装路径,需要修改以下文件
#修改配置文件:/etc/my.cnf、/etc/init.d/mysqld 中的路径
basedir='/opt/modules/mysql57'
datadir='/opt/modules/mysql57/data'
#创建链接
[kermit]mkdir -p /usr/local/mysql/bin
[kermit]ln -s /opt/modules/mysql57/bin/mysqld /usr/local/mysql/bin/mysqld
----------------------
#此时通过mysqld_safe启动
[kermit]# /opt/modules/mysql57/bin/mysqld_safe --user=mysql &
在重启碰到下面的报错,如下:根据提示是需要先清除一些data目录下的ib_logfile和ibdata文件:
2017-05-02T08:56:50.252245Z 0 [Note] InnoDB: Completed initialization of buffer pool 2017-05-02T08:56:50.258495Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2017-05-02T08:56:50.268492Z 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created! 2017-05-02T08:56:50.268653Z 0 [ERROR] InnoDB: redo log file './ib_logfile1' exists. Creating system tablespace with existing redo log files is not recommended. Please delete all redo log files before creating new system tablespace. 2017-05-02T08:56:50.268718Z 0 [ERROR] InnoDB: InnoDB Database creation was aborted with error Generic error. You may need to delete the ibdata1 file before trying to start up again.
如果想通过/etc/init.d/mysqld 进行启动/重启/停止,则需要设置一下pid文件的路径,在my.cnf中添加配置:
pid-file='/tmp/mysql.pid' #加了这项才可以通过/etc/init.d/mysqld进行重启
log-error='/opt/modules/mysql57/data/mysql.err' #日志文件路径最好自定义,注意将属主改成mysql
操作上面的pid-file路径后就可以通过/etc/init.d/mysqld 来管理MSYQl
#查看mysql运行状态的命令
[kermit]#/etc/init.d/mysqld status
#重启mysql操作命令:还有其它stop,start
[kermit]# /etc/init.d/mysqld restart
#最后也可以通过chkconfig加入到开机启动中
[kermit] chkconfig --level 35 mysqld on