在linux 环境下 地址为:/etc/my.cnf,windows下为/bin/my.ini
要热改动的话,set global XXXXXXXX=1;
热更新 也可以用以下命令 service mysql reload 或者 service mysqld reload
1. mysql慢查询Slow Log和未使用索引(Not Using Indexes)查询配置和使用
mysql的“慢查询”指的是超过了允许的最大查询时间(long_query_time
)的sql语句,而“未使用索引”查询顾名思义就是查询语句没有使用到索引的sql语句。
慢查询配置和使用
在msyqld的启动配置文件或命令行参数中增加以下参数
#slow query
log-slow-queries = =/var/mysql/logs/slow.log
#log_queries_not_using_indexes=ON
log_slow_queries=slow-log
long_query_time=2
log-long-format
long_query_time参数表示的是慢查询的度量时间,单位是秒,最小是1,缺省值是10,凡是执行时间超过long_query_time的sql语句都会记录到慢查询日志中。
--log-slow-queries[=
的file_name参数可选,缺省值是file_name
]
,如果指定了file_name参数的话,mysql就会把慢查询的日志记录到file_name所设定的文件中,如果file_name提供的是一个相对路径,mysql会把日志记录到mysql的data目录中host_name
-slow.log
log_slow_queries=slow-log 这样配置的话,直接在目录/var/lib/mysql/ 下生成 slow-log 文件
在mysql的启动配置文件或命令行参数中增加--log-queries-not-using-indexes
参数就可以打印出未使用索引查询语句了,不受long_query_time的设置。
看来我误用了 slow_launch_time,还以为新版本把这个替换了long_query_time
slow_launch_time 如果创建线程的时间超过该秒数,服务器增加Slow_launch_threads状态变量。
要设置关闭的话,
log-long-format 向每个日志项目添加其他信息(类似用户名和时间戳)
把上述参数打开,运行一段时间,就可以关掉了,省得影响生产环境。
接下来就是分析了,我这里的文件名字叫host-slow.log。
先mysqldumpslow –help以下,俺主要用的是
-s ORDER what to sort by (t, at, l, al, r, ar etc), ‘at’ is default
-t NUM just show the top n queries
-g PATTERN grep: only consider stmts that include this string
-s,是order的顺序,说明写的不够详细,俺用下来,包括看了代码,主要有
c,t,l,r和ac,at,al,ar,分别是按照 query次数,时间,lock的时间和返回的记录数来排序,前面加了a的时倒叙
-t,是top n的意思,即为返回前面多少条的数据
-g, 后边可以写一个正则匹配模式,大小写不敏感的
mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log
上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。
mysqldumpslow -t 10 -s t -g “left join” host-slow.log
这个是按照时间返回前10条里面含有左连接的sql语句。
用了这个工具就可以查询出来那些sql语句是性能的瓶颈,进行优化,比如加索引,该应用的实现方式等。
使用案例
执行以下命令
select sleep(1); select sleep(3); select * from t1;
日志内容如下
D:\MySQL\MySQL Server 5.1\bin\mysqld, Version: 5.1.31-community-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
# Time: 090625 12:58:09
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 3.000077 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1245905889;
select sleep(3);
# Time: 090625 12:58:42
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.046876 Lock_time: 0.031251 Rows_sent: 0 Rows_examined: 0
SET timestamp=1245905922;
select * from t1;
特别要注意的是,你需要有对=/var/mysql/logs/slow.log写的权限,否则log是写不进去的,笔者就一开始遇到了这个问题。
3.skip-name-resolve
mysql接收到连接请求后,获得的是客户端的ip,为了更好的匹配mysql.user里的权限记录(某些是用hostname定义的)。
如果mysql服务器设置了dns服务器,并且客户端ip在dns上并没有相应的hostname,那么这个过程很慢,导致连接等待。
添加skip-name-resolve以后就跳过着一个过程了。
4.共享表空间和独占表空间
innodb_file_per_table = 1
innodb这种引擎,与MYISAM引擎的区别很大。特别是它的数据存储格式等。
对于innodb的数据结构,首先要解决两个概念性的问题: 共享表空间以及独占表空间。
什么是共享表空间和独占表空间
共享表空间以及独占表空间都是针对数据的存储方式而言的。
共享表空间: 某一个数据库的所有的表数据,索引文件全部放在一个文件中,默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1 初始化为10M。
独占表空间: 每一个表都将会生成以独立的文件方式来进行存储,每一个表都有一个.frm表描述文件,还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容,默认情况下它的存储位置也是在表的位置之中。
两者之间的优缺点
共享表空间:
优点:
可以放表空间分成多个文件存放到各个磁盘上(表空间文件大小不受表大小的限制,如一个表可以分布在不同步的文件上)。数据和文件放在一起方便管理。
缺点:
所有的数据和索引存放到一个文件中以为着将有一个很常大的文件,虽然可以把一个大文件分成多个小文件,但是多个表及索引在表空间中混合存储,这样对于一个表做了大量删除操作后表空间中将会有大量的空隙,特别是对于统计分析,日值系统这类应用最不适合用共享表空间。
独立表空间:
在配置文件(my.cnf)中设置: innodb_file_per_table
优点:
1. 每个表都有自已独立的表空间。
2. 每个表的数据和索引都会存在自已的表空间中。
3. 可以实现单表在不同的数据库中移动。
4. 空间可以回收(除drop table操作处,表空不能自已回收)
a) Drop table操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
b) 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
c) 对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。
缺点:
单表增加过大,如超过100个G。
相比较之下,使用独占表空间的效率以及性能会更高一点。
共享表空间以及独占表空间之间的转化
innodb_file_per_table 通过这个参数来实现的转化,如果为ON说明所使用的是独占表空间【默认情况下,所使用的表空间为共享表空间】
mysql> show variables like '%innodb_file_per_table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ 1 row in set (0.00 sec)
innodb_file_per_table值来进行修改即可,但是对于之前使用过的共享表空间则不会影响,除非手动的去进行修改或者是
innodb_file_per_table=1 为使用独占表空间
innodb_file_per_table=0 为使用共享表空间
遇到的问题:
在my.cnf加以下属性时
skip-character-set-client-handshake
init-connect='SET NAMES utf8'
default-character-set=utf8
有时候会遇到如下异常:
etc/init.d/mysqld start
Starting MySQL...The server quit without updating PID file (/db/mysql/XBDZ-TJ01-QATest01.pid)
解决办法就是不加这些东西上去。
发现log-bin文件很大,这些文件用来数据恢复,很占用空间,解决办法注释掉一下几行:
#log-bin=mysql-bin
#binlog_format=mixed
以下是my.cnf配置样例:
[root@WEBGAME-KX01-DBServer mysql]# cat /etc/my.cnf
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# 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
port = 3306
socket = /db/mysql/mysql.sock
#default-character-set=utf8
# Here follows entries for some specific programs
# The MySQL server
[mysqld] port = 3306 datadir = /db/mysql/ socket = /db/mysql/mysql.sock skip-locking skip-name-resolve key_buffer = 1024M max_allowed_packet = 1M max_heap_table_size=256M tmp_table_size=256M sort_buffer_size = 8M join_buffer_size=8M net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 8M bulk_insert_buffer_size=64M myisam_sort_buffer_size = 64M skip-character-set-client-handshake init-connect='SET NAMES utf8' default-character-set=utf8 #innodb_force_recovery = 6 #log=general-log log-slow-queries=slow-log long_query_time=1 #query cache config query_cache_size = 128M query_cache_type = 1 thread_cache_size = 64 thread_concurrency=32 #max_connnection config current values=800 add by Allen max_connections = 1200 table_open_cache=1024 #back_log config add by kevin sun back_log=500 #record_buffer add by kevin sun record_buffer=16M #bootstrap init_file=/mysqlinit/bootstrap._sql #owner table space innodb_file_per_table
# 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 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 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 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using InnoDB tables innodb_status_file = 0 innodb_data_home_dir = /db/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /db/mysql/ #innodb_log_arch_dir = /db/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 = 3G innodb_additional_mem_pool_size = 16M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 256M innodb_log_buffer_size = 32M innodb_flush_log_at_trx_commit = 2 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 [isamchk] key_buffer = 512M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 512M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout