MySQL connection 的相关信息
查看mysql连接数的语句
- mysql 中有
Max_connection
,这个值是对应于一个mysql实例。
一个mysql 实例中 ,可以有多个database、movie_log、movie_order、movie_pay 等。 show variables like '%max_connections%';
可以查看当前数据库实例设置的最大连接数。
实际MySQL服务器允许的最大连接数16384。show variables;
可以查看当前数据库实例配置的所有变量,即my.ini 文件中的配置信息。show processlist;
如果是root帐号,你能看到所有用户的当前连接。如果是其它普通帐号,只能看到自己占用的连接。
注:show processlist;
只列出前100条,如果想全列出请使用show full processlist;
show status;
查看当前数据库的状态。show status like '%Threads%';
查看当前数据库线程变量的状态。
结果:
1 | +-------------------+-------+ |
Threads_connected 跟show processlist结果相同,表示当前连接数。准确的来说,Threads_running是代表当前并发数(当前连接正在使用中对数量。)
当前连接数(包括使用中对连接,也包括闲置的连接(sleep状态),也包括阻塞的连接(waiting for ……))
java 中有数据库连接池,连接池中有最小连接数,
因此:mysql 会有最小连接数 个连接 ,可能这些连接都是 sleep 状态的,
show status like '%connect%';
Connections,试图连接到(不管是否成功)MySQL服务器的连接数。其中Max_used_connections表示服务器启动后已经同时使用的连接的最大数量。Threads_connected表示当前的连接数。
关于sleep与lock的问题
- 造成sleep的原因有三个
下面是mysql手册给出的解释:
- 客户端程序在退出之前没有调用
mysql_close()
。 - 客户端sleep的时间在 wait_timeout 或 interactive_timeout 规定的秒内没有发出任何请求到服务器。
- 客户端程序在结束之前向服务器发送了请求还没得到返回结果就结束掉了.
- 客户端程序在退出之前没有调用
解决办法:
避免大量 sleep的连接的方法,数据库连接池设置min_idle设置小一些。
waiting for metadata lock 的原因 ?
- metadata lock 机制是为了保证数据一致性存在的。在有事务的操作时候,需要首先获得metadata lock,然后操作。如果这个时候,又来了一个事务也要ddl操作 同一个表,就会出现 metadata lock。
- 自动提交模式下,单语句就是一个事务,执行完了事务也就结束了。
- preparestatement会获得 metalock,一旦prepare 完毕, metalock 就释放了。
- online DDL应该是指在alter table进行的时候,插入/修改/删除数据的sql语句不会Waiting for table metadata lock. 一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,也会在Opening tables的阶段进入Waiting for table metadata lock的队列
alter table
什么情况下会发生锁?(主要针对于myisam 引擎(锁表))通过show processlist看到TableA上有正在进行的操作(包括读),此时alter table语句无法获取到metadata 独占锁,会进行等待。
通过show processlist看不到TableA上有任何操作,但实际上存在有未提交的事务,可以在information_schema.innodb_trx中查看到。在事务没有完成之前,TableA上的锁不会释放,alter table同样获取不到metadata的独占锁
通过show processlist看不到TableA上有任何操作,在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中,对TableA进行了一个失败的操作(比如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效。从performance_schema.events_statements_current表中可以查到失败的语句
也就是说除了语法错误,其他错误语句获取到的锁在这个事务提交或回滚之前,仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解,因为错误的语句根本不会被记录到二进制日志
总之,alter table的语句是很危险的,在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务,在无人监管的时候运行,最好通过lock_wait_timeout设置好超时时间,避免长时间的metedata锁等待。
MySQL的锁机制
1 | MyISAM和MEMORY存储引擎采用的是表级锁(table-level-locking) |
名词解释
表级:直接锁定整张表,在你锁定期间,其它进程无法对该表进行写操作。如果你是写锁,则其它进程则读也不允许
行级:仅对指定的记录进行加锁,这样其它进程还是可以对同一个表中的其它记录进行操作。
页级:表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录
三种锁的特性归纳
- 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
- 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
- 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
- 三种锁各有各的特点,若仅从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如WEB应用;行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。
知识点
innodb 虽然是行级别锁,但是在执行update 操作时候,如果where 查询条件 不存在索引,一样会扫描整个表,锁住整张表,解决办法就是需要添加索引。这样锁住的就是某些行了。
对于读写
- 对WRITE,MySQL使用的表锁定方法原理如下:如果在表上没有锁,在它上面放一个写锁。否则,把锁定请求放在写锁定队列中。
- 对READ,MySQL使用的锁定方法原理如下:如果在表上没有写锁定,把一个读锁定放在它上面 否则,把锁请求放在读锁定队列中。
InnoDB使用行锁定,BDB使用页锁定。对于这两种存储引擎,都可能存在死锁。这是因为,在SQL语句处理期间,InnoDB自动获得行锁定和BDB获得页锁定,而不是在事务启动时获得。
行锁优缺点
- 行级锁定的优点:
- 当在许多线程中访问不同的行时只存在少量锁定冲突。
- 回滚时只有少量的更改。
- 可以长时间锁定单一的行。
- 行级锁定的缺点:
- 比页级或表级锁定占用更多的内存。
- 当在表的大部分中使用时,比页级或表级锁定速度慢,因为你必须获取更多的锁。
- 如果你在大部分数据上经常进行
GROUP BY
操作或者必须经常扫描整个表,比其它锁定明显慢很多。用高级别锁定,通过支持不同的类型锁定,你也可以很容易地调节应用程序,因为其锁成本小于行级锁定。
- 行级锁定的优点:
在以下情况下,表锁定优先于页级或行级锁定:
- 表的大部分语句用于读取。
- 对严格的关键字进行读取和更新,你可以更新或删除可以用单一的读取的关键字来提取的一行:
1
2UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value; - SELECT 结合并行的INSERT语句,并且只有很少的UPDATE或DELETE语句。
· 在整个表上有许多扫描或GROUP BY操作,没有任何写操作。
如果想要在一个表上做大量的 INSERT 和 SELECT操作,但是并行的插入却不可能时,可以将记录插入到临时表中,然后定期将临时表中的数据更新到实际的表里。可以用以下命令实现:
1
2
3
4mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;MySQL表级锁的两种模式
表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。什么意思呢,就是说对MyISAM表进行读操作时,它不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写操作;而对MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作。
MyISAM表的读和写是串行的,即在进行读操作时不能进行写操作,反之也是一样。但在一定条件下MyISAM表也支持查询和插入的操作的并发进行,其机制是通过控制一个系统变量(concurrent_insert)来进行的,当其值设置为0时,不允许并发插入;当其值设置为1时,如果MyISAM表中没有空洞(即表中没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录;当其值设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。
MyISAM锁调度是如何实现的呢,这也是一个很关键的问题。例如,当一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同一表的写锁,此时MySQL将会如优先处理进程呢?通过研究表明,写进程将先获得锁(即使读请求先到锁等待队列)。但这也造成一个很大的缺陷,即大量的写操作会造成查询操作很难获得读锁,从而可能造成永远阻塞。所幸我们可以通过一些设置来调节MyISAM的调度行为。我们可通过指定参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利,设置其值为1(set low_priority_updates=1),使该连接发出的更新请求优先级降低。
- InnoDB有两种模式的行锁:
- 共享锁:允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
( Select * from table_name where ……lock in share mode) - 排他锁:允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。(select * from table_name where…..for update)
- 共享锁:允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
为了允许行锁和表锁共存,实现多粒度锁机制;同时还有两种内部使用的意向锁(都是表锁),分别为意向共享锁和意向排他锁。
InnoDB行锁是通过给索引项加锁来实现的,即只有通过索引条件检索数据,InnoDB才使用行级锁,否则将使用表锁!
- MySQL 的表级锁都是写锁优先,而且是采用排队机制,这样不会出现死锁的情况。对于 InnoDB 和 BDB 存储引擎来说,是可能产生死锁的。这是因为 InnoDB 会自动捕获行锁, BDB 会在执行 SQL 语句时捕获页锁的,而不是在事务的开始就这么做。
插入、更新性能优化的几个重要参数
bulk_insert_buffer_size
批量插入缓存大小
这个参数是针对MyISAM存储引擎来说的.适用于在一次性插入100-1000+条记录时, 提高效率.默认值是8M.可以针对数据量的大小,翻倍增加.
concurrent_insert
并发插入
当表没有空洞(删除过记录),在某进程获取读锁的情况下,其他进程可以在表尾部进行插入。
值 | 意义 | 是否默认 |
---|---|---|
0 | 不允许并发插 | |
1 | 当表没有空洞时, 执行并发插入 | 默认 |
2 | 不管是否有空洞都执行并发插入 |
delay_key_write
针对MyISAM的延迟更新索引
针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘. 值为 0不开启, 1开启. 默认开启.
delayed_insert_limit,delayed_insert_timeout,delayed_queue_size
延迟插入将数据先交给内存队列,然后慢慢地插入。
锁表锁行案例
注1: FOR UPDATE仅适用于InnoDB,且必须在交易区块(BEGIN/COMMIT)中才能生效。注2: 要测试锁定的状况,可以利用MySQL的Command Mode ,开二个视窗来做测试。
1
SELECT * FROM products WHERE id='3' FOR UPDATE;
主键明确,锁住的是行。如果没有记录,不锁定行。
1
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
主键不明确,锁住的是表。
事务来一波
事务的使用
- 全部的表类型都可以使用锁,但是只有 InnoDB 和 BDB 才有内置的事务功能。
- 使用 begin 开始事务,使用 commit 结束事务,中间可以使用 rollback 回滚事务。
- 在默认情况下, InnoDB 表支持一致读。
- 事务只用于 insert 和 update 语句来更新数据表,不能用于对表结构的更改。执行一条更改表结构或 begin 则会立即提交当前的事务。
事务的四个属性
InnoDB锁与MyISAM锁的最大不同在于:一是支持事务(TRANCSACTION),二是采用了行级锁。我们知道事务是由一组SQL语句组成的逻辑处理单元,其有四个属性(简称ACID属性),分别为:
- 原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全部执行,要么全都不执行;
- 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态;
- 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行;
- 持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
- 其他: 隔离性 (isolation)通过锁来实现,其他3个原子性(atomicity),一致性(consistency),持久性(durability)通过数据库的redo和undo来完成。
事务隔离级别:SQL标准中定义了 4 个隔离级别: read uncommited,read commited, repeatable read, serializable 。MySQL 允许利用
set transaction
来设置隔离级别- read uncommited 即脏读,一个事务修改了一行,ha,另一个事务也可以读到该行。如果第一个事务执行了回滚,那么第二个事务读取的就是从来没有正式出现过的值。 ?
- read commited 即一致读,试图通过只读取提交的值的方式来解决脏读的问题,但是这又引起了不可重复读取的问题。一个事务执行一个查询,读取了大量的数据行。在它结束读取之前,另一个事务可能完成了对数据行的更改。当第一个事务试图再次执行同一个查询,服务器就会返回不同的结果。
- repeatable read 即可重复读,在一个事务对数据行执行读取或写入操作时锁定了这些数据行。但是这种方式又引发了幻想读的问题。因为只能锁定读取或写入的行,不能阻止另一个事务插入数据,后期执行同样的查询会产生更多的结果。
- serializable 模式中,事务被强制为依次执行。这是 SQL 标准建议的默认行为。
如果多个事务更新了同一行,就可以通过回滚其中一个事务来解除死锁。
事务一致性
在事务开始和完成时,数据都必须保持一致状态。事务一致性 ,是由 原子性,隔离性,持久性,共同保持的。
例子:事务1需要将100元转入帐号A:先读取帐号A的值,然后在这个值上加上100。但是,在这两个操作之间,另一个事务2修改了帐号A的值,为它增加了100元。那么最后的结果应该是A增加了200元。但事实上,事务1最终完成后,帐号A只增加了100元,因为事务2的修改结果被事务1覆盖掉了。
mysql 配置信息
- 错误日志
1 |
|
#mysqlbinlog log_file
–binlog-do-db=db_name #如果当前的数据库是db_name,应该更新记录到二进制日志中
–binlog-ignore-db=db_name #如果当前的数据库是db_name,不应该更新记录到二进制日志中
1 | 如果多个数据库,可如下写法: |
–binlog-do-db=db1
–binlog-do-db=db2
1 | 单个库的主从同步: |
replicate_do_db=test
replicate_wild_do_table=test.%
1 | 或 |
replicate_ignore_db=mysql
replicate_wild_ignore_table=mysql.%
log-slave-updates = 1 #这个参数用来配置从服务器的更新是否写入二进制日志,这个选项默认是不打开的。
1 | 3. 查询日志 |
slow_query_log = 1
slow_query_log_file = /opt/logs/mysql5/mysql-slow.log
long_query_time = 1
1 | 如果慢查询日志中记录内容很多,可以使用mysqldumpslow工具来对慢查询日志进行分类汇总 |
#mysqldumpslow ****-slow.log
1 | 5,配置文件详解: |
wget http://cdn.mysql.com/archives/mysql-5.6/mysql-5.6.10.tar.gz
1 |
vi /etc/my.cnf
1 |
|
[mysqld]
character-set-server = utf8
user = mysql
port = 3306
socket = /data/mysql/3306/mysql.sock
basedir = /usr/local/webserver/mysql
datadir = /data/mysql/3306/data
log-error = /data/mysql/3306/mysql_error.log
pid-file = /data/mysql/3306/mysql.pid
default-storage-engine = MyISAM/InnoDB #设置默认存储引擎
skip-locking #避免MySQL的外部锁定,减少出错几率增强稳定性
#禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!
skip-name-resolve
开启该选项可以彻底关闭MySQL的TCP/IP连接方式,如果CentOS系统WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项!否则将无法正常连接!
skip-networking
启动mysql,不启动复制
skip-slave-start
指定MySQL可能的连接数量。当MySQL主线程在很短的时间内接收到非常多的连接请求,该参数生效,主线程花费很短的时间检查连接并且启动一个新线程。back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。默认值为50。对于Linux系统推荐设置为小于512的整数
back_log = 384
设置最大包,限制server接受的数据包大小,避免超长SQL的执行有问题,默认值为16M,当MySQL客户端或mysqld服务器收到大于max_allowed_packet字节的信息包时,将发出“信息包过大”错误,并关闭连接。对于某些客户端,如果通信信息包过大,在执行查询期间,可能会遇到“丢失与MySQL服务器的连接”错误
max_allowed_packet = 16M
指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意:该参数值设置的过大反而会是服务器整体效率降低!
key_buffer_size = 256M
查询排序时所能使用的缓冲区大小。注意:该参数对应的分配内存是每连接独占!如果有100个连接,那么实际分配的总共排序缓冲区大小为100×6=600MB。所以,对于内存在4GB左右的服务器推荐设置为6-8M。
sort_buffer_size = 6M
读查询操作所能使用的缓冲区大小。和sort_buffer_size一样,该参数对应的分配内存也是每连接独享!
read_buffer_size = 4M
这是一个重要变量。可以把这个值设为内存的70%-80%。和key_buffer相同,如果数据量比较小也不怎么增加,那么不要把这个值设太高也可以提高内存的使用率
innodb_buffer_pool_size = 4G
这个的效果不是很明显,至少是当操作系统能合理分配内存时。但你可能仍需要设成20M或更多一点以看Innodb会分配多少内存做其他用途
innodb_additional_mem_pool_size = 512M
对于写很多尤其是大数据量时非常重要。要注意,大的文件提供更高的性能,但数据库恢复时会用更多的时间。我一般用64M-512M,具体取决于服务器的空间。
innodb_log_file_size = 1G
这是InnoDB存储引擎的事务日志所使用的缓冲区。类似于BinlogBuffer,在写事务日志的时候,为了提高性能,也是先将信息写入Buffer中,当满足相应条件才会写入。
innodb_log_buffer_size = 256M
参数设置表高速缓存的数目。每个连接进来,都会至少打开一个表缓存。因此, table_cache 的大小应与 max_connections 的设置有关
table_cache = 512
默认大小是 32M。如果一张临时表超出该大小,MySQL产生一个 The table tbl_name is full 形式的错误,如果你做很多高级 GROUP BY查询,增加tmp_table_size 值
tmp_table_size = 256M
指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提示,则需要增大该参数值
max_connections = 768
如果某个用户发起的连接error超过该数值,则该用户的下次连接将被阻塞,直到管理员执行flush hosts ; 命令;防止黑客
max_connect_errors = 10000
指定一个请求的最大连接时间,对于4GB左右内存的服务器可以设置为5-10。
wait_timeout = 10
开启了Innodb的innodb_file_per_table这个参数之后[innodb_file_per_table = 1],也就是启用InnoDB的独立表空间模式,便于管理
innodb_file_per_table
首先需要大致了解一下mysql日志操作步骤:
log_buff –mysql写 (write) –> log_file –OS刷新 (flush)-> disk
参数解释:
0(延迟写): log_buff –每隔1秒–> log_file –实时-> disk
1(实时写,实时刷): log_buff –实时—> log_file –实时—> disk
2(实时写,延迟刷): log_buff –实时—> log_file –每隔1秒-> disk
innodb_flush_log_at_trx_commit = 2
1 | ### 主从同步配置以及延迟备份 |
CHANGE MASTER TO MASTER_DELAY = N;
1 | N为多少秒,该语句设置从数据库延时N秒后,再与主数据库进行数据同步复制 |
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 600;
mysql>start slave;
mysql>show slave status \G;
1 | 查看SQL_Delay的值为600,表示设置成功。 |
mysql>STOP SLAVE;
mysql>CHANGE MASTER TO MASTER_DELAY = 0, RELAY_LOG_FILE = ‘xxxxx-relay-bin.######’, RELAY_LOG_POS = ######;
mysql>START SLAVE;
```
- 数据库状态类型
- Aborted_clients 由于客户没有正确关闭连接已经死掉,已经放弃的连接数量。
- Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。
- Connections 试图连接MySQL服务器的次数。
- Created_tmp_tables 当执行语句时,已经被创造了的隐含临时表的数量。
- Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。
- Delayed_writes 用INSERT DELAYED写入的行数。
- Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。
- Flush_commands 执行FLUSH命令的次数。
- Handler_delete 请求从一张表中删除行的次数。
- Handler_read_first 请求读入表中第一行的次数。
- Handler_read_key 请求数字基于键读行。
- Handler_read_next 请求读入基于一个键的一行的次数。
- Handler_read_rnd 请求读入基于一个固定位置的一行的次数。
- Handler_update 请求更新表中一行的次数。
- Handler_write 请求向表中插入一行的次数。
- Key_blocks_used 用于关键字缓存的块的数量。
- Key_read_requests 请求从缓存读入一个键值的次数。
- Key_reads 从磁盘物理读入一个键值的次数。
- Key_write_requests 请求将一个关键字块写入缓存次数。
- Key_writes 将一个键值块物理写入磁盘的次数。
- Max_used_connections 同时使用的连接的最大数目。
- Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。
- Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。
- Open_tables 打开表的数量。
- Open_files 打开文件的数量。
- Open_streams 打开流的数量(主要用于日志记载)
- Opened_tables 已经打开的表的数量。
- Questions 发往服务器的查询的数量。
- Slow_queries 要花超过long_query_time时间的查询数量。
- Threads_connected 当前打开的连接的数量。
- Threads_running 不在睡眠的线程数量。
- Uptime 服务器工作了多少秒。