最新消息:比度技术-是关注互联网技术的个人博客,大部分内容来自互联网,以作为笔记查阅。

mysql链接超时&insert避免重复数据的策略&Mysql 查看连接数,状态

分布式存储 bidu 718浏览

mysql链接超时

应用程序和mysql数据库建立连接,如果超过8小时应用程序不去访问数据库,数据库就断掉连接 。这时再次访问就会抛出异常。在mysql中有相关参数设定,当数据库连接空闲一定时间后,服务器就会断开等待超时的连接: 相关参数:

MySQL [mysql]> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 28800    |
+-----------------------------+----------+
13 rows in set (0.01 sec)

同一时间,interactivetimeout,waittimeout 这两个参数只有一个起作用。

  • 修改参数 这两个参数的默认值是8小时(60608=28800)。测试过将这两个参数改为0,系统自动将这个值设置为1。也就是说,不能将该值设置为永久。 将这2个参数设置为24小时(606024=86400)。 set interactivetimeout=86400; set waittimeout=86400; 也可以修改my.cof,修改后重起mysql 打开/etc/my.cnf,在属性组mysqld下面添加参数如下: [mysqld] interactivetimeout=28800000 waittimeout=28800000 如果一段时间内没有数据库访问则mysql自身将切断连接.
  • MySQL解决8小时连接失效办法

1、即使在创建Mysql时url中加入了autoReconnect=true参数,一但这个连接两次访问数据库的时间超出了服务器端wait_timeout的时间限制,还是会断开连接。

2、服务器端的参数可以用

show global variables like ‘wait_timeout’;

set global wait_timeout=10;

来进行设置,但是wait_timeout值不应该设的太高.

修改 /etc/mysql/my.cnf文件,在 [mysqld] 节中设置:

# Set a connection to wait 8hours in idle status. wait_timeout =86400

3、 较好的策略是对处于idle状态的connection定时发送一个sql,来刷新服务器上的时间戳。 Go语言: 可以每隔一段时间Ping一次来保持连接:

go func(f *DBMysql) {
	for {
		err := f.db.Ping()
		if err != nil {
			loger.ErrorLog(err)
			// 这里需要策略报警
		}
		time.Sleep(time.Second * 100) //避免连接失效,mysql 默认8小时不访问,连接失效自动断开
	}

}(f)

insert避免重复数据的策略

create database vanishcache;
DROP TABLE IF EXISTS `vanishcache`.`vanish_url_params`;
CREATE TABLE  `vanishcache`.`vanish_url_params` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT 'url 参数表',
  `name` varchar(64) NOT NULL COMMENT '参数名字',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT 'vanish url 参数 后面根据这个表生成数据表,故这是一个临时的表';

三种在mysql中避免重复插入记录方法,即ignore,Replace,ON DUPLICATE KEY UPDATE三种方法

使用ignore关键字

如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用:

INSERT IGNORE INTO vanish_url_params(name) VALUES('charset'),('responseformat')

这样当有重复记录就会忽略,执行后返回数字0

还有个应用就是复制表,避免重复记录:

INSERT IGNORE INTO `table_1` (`name`) SELECT `name` FROM `table_2`;

使用Replace
REPLACE INTO `table_name`(`col_name`, ...) VALUES (...);
REPLACE INTO `table_name` (`col_name`, ...) SELECT ...;
REPLACE INTO `table_name` SET `col_name`='value',

算法说明: REPLACE的运行与INSERT很相像,但是如果旧记录与新记录有相同的值,则在新记录被插入之前,旧记录被删除,即: 尝试把新行插入到表中 当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时: 从表中删除含有重复关键字值的冲突行 再次尝试把新行插入到表中 旧记录与新记录有相同的值的判断标准就是: 表有一个PRIMARY KEY或UNIQUE索引,否则,使用一个REPLACE语句没有意义。该语句会与INSERT相同,因为没有索引被用于确定是否新行复制了其它的行。

返回值:

REPLACE语句会返回一个数,来指示受影响的行的数目。该数是被删除和被插入的行数的和 受影响的行数可以容易地确定是否REPLACE只添加了一行,或者是否REPLACE也替换了其它行:检查该数是否为1(添加)或更大(替换)。 示例:

# eg:(phone字段为唯一索引)
REPLACE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test569', '99999', '123');

作为等价操作:在 SQL Server 中可以这样处理:
if not exists (select phone from t where phone= '1')   insert into t(phone, update_time) 
values('1', getdate()) else    update t set update_time = getdate() where phone= '1'
使用ON DUPLICATE KEY UPDATE

可以在INSERT INTO…..后面加上 ON DUPLICATE KEY UPDATE方法来实现。如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE。

例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:

INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=`c`+1; 

UPDATE `table` SET `c`=`c`+1 WHERE `a`=1;

如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。

注释:如果列b也是唯一列,则INSERT与此UPDATE语句相当:

UPDATE `table` SET `c`=`c`+1 WHERE `a`=1 OR `b`=2 LIMIT 1;

如果a=1 OR b=2与多个行向匹配,则只有一个行被更新。通常,您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。 您可以在UPDATE子句中使用VALUES(colname)函数从INSERT…UPDATE语句的INSERT部分引用列值。换句话说,如果没有发生重复关键字冲突,则UPDATE子句中的VALUES(colname)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL。

INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3), (4, 5, 6) ON DUPLICATE KEY UPDATE `c`=VALUES(`a`)+VALUES(`b`);

本语句与以下两个语句作用相同:
INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=3; 
INSERT INTO `table` (`a`, `b`, `c`) VALUES (4, 5, 6) ON DUPLICATE KEY UPDATE c=9;

注意:当您使用ON DUPLICATE KEY UPDATE时,DELAYED选项被忽略。

在实际项目中用到的:是将一个表的数据导入到另外一个表中,数据的重复性就得考虑(如下),唯一索引为:email:

 INSERT INTO `table_name1` (`title`, `first_name`, `last_name`, `email`, `phone`, `user_id`, `role_id`, `status`, `campaign_id`) 
  SELECT '', '', '', `table_name2`.`email`, `table_name2`.`phone`, NULL, NULL, 'pending', 29 FROM `table_name2` 
 WHERE `table_name2`.`status` = 1 
  ON DUPLICATE KEY UPDATE `table_name1`.`status`='pending'

另一个例子:
INSERT INTO `class` SELECT * FROM `class1` ON DUPLICATE KEY UPDATE `class`.`course`=`class1`.`course`

其它关键:DELAYED 做为快速插入,并不是很关心失效性,提高插入性能。 IGNORE 只关注主键对应记录是不存在,无则添加,有则忽略。

  • 特别说明:在MYSQL中UNIQUE索引将会对null字段失效,也就是说(a字段上建立唯一索引):

INSERT INTO test (a) VALUES (NULL);

是可以重复插入的(联合唯一索引也一样)

INSERT DELAYED INTO

说明: INSERT DELAYED INTO,是客户端提交数据给MySQL,MySQL返回OK状态给客户端。而这是并不是已经将数据插入表,而是存储在内存里面等待排队。当mysql有空余时,再插入。 这样的好处是,提高插入的速度,客户端不需要等待太长时间。坏处是,不能返回自动递增的ID,以及系统崩溃时,MySQL还没有来得及插入数据的话,这些数据将会丢失。

DELAYED 的使用 使用延迟插入操作 DELAYED调节符应用于INSERT和REPLACE语句。当DELAYED插入操作到达的时候, 服务器把数据行放入一个队列中,并立即给客户端返回一个状态信息,这样客户 端就可以在数据表被真正地插入记录之前继续进行操作了。如果读取者从该数据 表中读取数据,队列中的数据就会被保持着,直到没有读取者为止。接着服务器 开始插入延迟数据行(delayed-row)队列中的数据行。在插入操作的同时,服务器 还要检查是否有新的读取请求到达和等待。如果有,延迟数据行队列就被挂起, 允许读取者继续操作。当没有读取者的时候,服务器再次开始插入延迟的数据行。 这个过程一直进行,直到队列空了为止。 几点要注意事项: · INSERT DELAYED应该仅用于指定值清单的INSERT语句。服务器忽略用于INSERT DELAYED…SELECT语句的DELAYED。 · 服务器忽略用于INSERT DELAYED…ON DUPLICATE UPDATE语句的DELAYED。 · 因为在行被插入前,语句立刻返回,所以您不能使用LASTINSERTID()来获取AUTOINCREMENT值。AUTOINCREMENT值可能由语句生成。 · 对于SELECT语句,DELAYED行不可见,直到这些行确实被插入了为止。 · DELAYED在从属复制服务器中被忽略了,因为DELAYED不会在从属服务器中产生与主服务器不一样的数据。 注意,目前在队列中的各行只保存在存储器中,直到它们被插入到表中为止。这意味着,如果您强行中止了mysqld(例如,使用kill -9) 或者如果mysqld意外停止,则所有没有被写入磁盘的行都会丢失。

参考:

http://blog.csdn.net/jbboy/article/details/46828917

Mysql 查看连接数,状态

命令: show processlist;

如果是root帐号,你能看到所有用户的当前连接。如果是其它普通帐号,只能看到自己占用的连接。 show processlist;只列出前100条,如果想全列出请使用show full processlist;

MySQL> show processlist;

命令: show status;

命令:show status like '%下面变量%';

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 服务器工作了多少秒。


####My.ini配置 虚拟内存
innodb_buffer_pool_size=576M   ->128M InnoDB引擎缓冲区

query_cache_size=100M             ->32 查询缓存
tmp_table_size=102M                  ->32M 临时表大小
key_buffer_size=16m                  ->8M

设置max_connections

命令:show variables like '%max_connections%'
(这个办法在debian+mysql  Ver 12.22 Distrib 4.0.22, for pc-linux (i386)
里实验了)
设置办法是在my.cnf文件中,添加下面的最后红色的一行:
[mysqld] 
port=3306 
#socket=MySQL 
skip-locking 
set-variable = key_buffer=16K 
set-variable = max_allowed_packet=1M 
set-variable = thread_stack=64K 
set-variable = table_cache=4 
set-variable = sort_buffer=64K 
set-variable = net_buffer_length=2K 
set-variable = max_connections=32000 

修改完毕后,重启MySQL即可。当然,为了确保设置正确,应该查看一下max_connections。

注意:

1、虽然这里写的32000。但实际MySQL服务器允许的最大连接数16384;

2、除max_connections外,上述其他配置应该根据你们系统自身需要进行配置,不必拘泥;

3、添加了最大允许连接数,对系统消耗增加不大。

修改mysql默认连接数

从以下几种方法都可以实现修改连接数的目的。

  • 1、进入到mysql的安装目录找到mysqldsafe(我的安装路径是:cd /opt/mysql-5.0.27/bin/mysqldsafe);
    vi mysqld_safe
    找到
then $NOHUP_NICENESS $ledir/$MYSQLD
  $defaults --basedir=$MY_BASEDIR_VERSION
  --datadir=$DATADIR $USER_OPTION
  --pid-file=$pid_file
  --skip-external-locking
  -O max_connections=1500  // 添加
  >> $err_log 2>&1 else
  eval "$NOHUP_NICENESS $ledir/$MYSQLD
  $defaults --basedir=$MY_BASEDIR_VERSION
  --datadir=$DATADIR $USER_OPTION
  --pid-file=$pid_file
  --skip-external-locking $args
  -O max_connections=1500 >> // 添加
  $err_log 2>&1"

即添加-O max_connections=1500 重启mysql服务,即可。

  • 2、相对来说比较简单,直接更改mysql的配置文件my.cnf找到max_connections将默认值改为你需要的值,保存退出,重启服务即可。
  • 3、命令行方式更改最大连接数:以管理员身份登录到数据库:
  •   mysql>set GLOBAL max_connections=200

显示当前运行的Query:

mysql> show processlist

显示当前状态:

mysql> show status

  • 4、修改原代码

解开MySQL的原代码,进入里面的sql目录修改mysqld.cc找到下面一行:

{ “maxconnections”, (long*) &maxconnections,1000,1,16384,0,1},

把它改为:

{ “maxconnections”, (long*) &maxconnections,1000,1,16384,0,1},

存盘退出,然后./configure ;make;make install可以获得同样的效果。

转载请注明:比度技术-关注互联网技术的个人博客 » mysql链接超时&insert避免重复数据的策略&Mysql 查看连接数,状态