标签为 "mysql" 的存档

一次线上业务跨IDC高延迟问题解决的案例分享

最近在梳理某项目上各服务接口的性能情况,遇到两个问题。以下是定位和解决问题的一个思路,分享给大家。
业务之前并没有详细的性能日志记录,仅在电信机房(T机房)进行了性能测试,结果是各接口满足预期,服务上线。
在进一步对接口进行性能分析时,对各业务接口的关键路径添加了日志统计,通过日志进行分析,将接口的延迟进行统计,接入Grafana,观察数据后,发现两类问题。

  1. 连接MongoDB的服务,网通机房(C机房)延迟比电信机房(T机房)要高。
  2. 连接Mysql的服务,网通机房(C机房)延迟比电信机房(T机房)高。

    NOTE: 这些服务接口,都是只读,没有写操作。

    对两类问题分别进行排查:

MongoDB

简单的排查后发现,MongoDB实例有过一次迁移,并且迁移后只保留了电信机房(T机房)的实例,网通机房(C机房)没有从库,所以网通机房(C机房)延迟比电信机房(T机房)高。对网通机房(C机房)部署了从库实例后,却意外发现电信机房(T机房)的延迟比网通机房(C机房)高了。再次排查后发现,代码中配置的MongoDB的读策略是secondary(从库优先),所以网通机房(C机房)有从库后,电信机房(T机房)也去网通机房(C机房)读取,导致了电信机房(T机房)的延迟变高。更改读策略为nearest(就近优先),有所好转,但并没有预想的效果那么好。仔细看下官方文档

The driver reads from a random member of the set that has a ping time that is less than 15ms slower than the member with the lowest ping time. Reads in the MongoClient::RP_NEAREST mode do not consider the member’s type and may read from both primaries and secondaries.

就会发现,nearest是在客户端维护一个到各个实例延迟小于15ms的集合,而我们电信机房(T机房)到网通机房(C机房)是光纤直连,延迟在12ms左右,所以,每次客户端可能会连接到电信机房(T机房),也可能到网通机房(C机房)。
这点在以后的应用中,大家可以注意下。

Mysql

在所有的服务中,只有一个服务接口是读mysql实现的,而这个接口的表现更是奇怪,网通机房(C机房)的延迟比电信机房(T机房)多100 ms+。

开始时猜测有可能业务内做了某些写主库的操作,比如写mysql,或写redis之类的,跨机房写导致的延迟高。
实际分析后发现,业务内并没有写操作,多出的时间就是读mysql的时间。
mysql是有网通机房(C机房)的从库的,为什么读取从库的数据,延迟还会这么高呢。在我们服务端ping 网通机房(C机房)的mysql ip,发现延迟正常,只有零点几毫秒,不存在网络问题。
下一步就是通过抓包,分析下我们服务端跟mysql间到底有哪些交互,到底是哪个环节慢了。

根据抓包结果发现,正常的select查表请求很快能得到响应,但当从我们服务端发送一个 “Describe tableName”的请求到mysql 服务端时,服务端等待了较长时间(30ms+)才返回结果,而且一次接口服务请求中,有多次Describe的请求,这样,导致网通机房(C机房)最终延迟很高。
问题定位后,开始尝试解决。
解决问题前需要先理清思路:

  1. Describe TableName 这个命令是干什么用的,业务里并没有显式调用。这个请求能不能去掉。
  2. 如果不能去掉,那它的延迟为什么这么高,能不能优化?

第一个问题比较简单,Describe 命令是现在ORM中比较通用的做法,通过获取数据库的表结构,来动态的创建Model。如果不调用Describe命令,当然也可以做到,那样就需要自己业务端对每个model进行声明,这样开发成本会大大增加,这个方式不可取。所以需要保留Describe命令。

第二个问题,延迟为什么高,这个命令是很简单的一个命令,没有任何复杂的操作,而且主库上都没有这个问题。结合DBA同学在Mysql上使用了Atlas中间件,可以大胆猜想下,应该是这个中间件搞的鬼,把select请求分配到从库执行,但是把Describe分配到了主库执行,有可能是因为Atlas中间件只考虑了一些读的SQL,把这类请求分配到从库,而其它各种请求,可能由于过于复杂,就默认分配到主库去执行。当然这只是猜测,没有查看过Atlas的源码,所以不能妄下结论。结合已经整理到的线索,跟DBA同学进行了确认,确定 Describe命令确实被Atlas中间件发送到主库去执行了,至于这么做的原因,是为了避免主从结构不一致时,从库拿到的表结构错误。这种情况下,我们也不能评价说中间件做的到底合理不合理,所以我们需要从自己的角度再思考下能不能优化。如果说希望避免每次请求都执行Describe命令,除了说刚才提到的自己声明,另外一个方式就是cache了,因为表结构变化的频次太低了,我们完全可以设置一个较长时间的cache,来避免频繁的这种请求。业务使用的是Phalcon框架,这个框架中已经提供了这种meta-Data cache的方案,Yii中也有类似的实现: schemaCaching
当启用这种cache后,效果就很明显,可以看到:
网通机房(C机房)延迟从原来的120ms降到7ms, 电信机房(T机房)延迟从原来的10ms降低到5ms.

后续需要考虑的就是,如果表结构发生变化,如何在不影响业务的情况下进行更新。这个也可以有多种实现的方案,大家可以自己想下。

总结

解决问题的思路就在于,遵循最小化原则,先对可能产生这种问题原因进行大胆猜测,然后快速验证,逐步缩小范围,将问题定位到一个最小可复现的范围内,再深入分析具体原因。当然这一切都要有数据说话,如果平时开发中,能提供足够丰富的日志数据,就可以很快的定位问题,甚至提前发现问题。

DB Proxy 原理[转]

在大型互联网站的数据库部署中,部署最多的数据库为MySQL。随着MySQL中Innodb存储引擎对事物的支持,MySQL在互联网公司部署中,应用量越来越多。典型应用MySQL的公司有Google、Taobao、Baidu等大型互联网公司。MySQL的优势在于其高扩展性和价格优势等。实际上,MySQL可以免费应用于企业级的部署中。

在MySQL复制方式部署中,有两种部署方式:同步复制和异步复制。同步复制采用NDB存储引擎,异步复制需要使用mysql-proxy结合master-slave实现。

mysql-proxy是一个MySQL的代理服务器,用户的请求先发向mysql-proxy,然后mysql-proxy对用户的数据包进行分析,从下一层的mysql 数据库中选择一台数据库,将用户的请求包交给mysql处理。

首先MySQL Proxy 以服务器的身份接受客户端的请求,根据相应配置对这些请求进行分析处理,然后以客户端的身份转发给相应的后端数据库服务器,再接受服务器的信息,然后返回给客户端。所以MySQL Proxy需要同时实现客户端和服务器的协议。由于要对客户端发送过来的SQL语句进行分析,还需要包含一个SQL解析器。MySQL Proxy通过使用lua脚本,来实现复杂的连接控制和过滤,从而实现读写分离和负载平衡。所以部署MySQL-Proxy需要安装运行Lua语言的环境。典型的MySQL-Proxy应用为实现读写分离。

异步复制主要为了解决读写分离的问题。因为用户对网站的访问有读操作多,写操作少的特点。甚至像taobao.com这样的网站读写比例高达10:1,所以采用MySQL-Proxy结合主从异步复制实现读写分离是非常重要的增快访问速度的方法。这样如果有更高的用户访问需求,通过增加slave机器,不会对现有系统提供的服务产生影响而实现很好的、很灵活的业务扩展。

Read more…

mysql中使用变量执行sql语句

需要保存一些创建表的语句,这些语句的表前缀是可以设置的一个变量,就需要用到以下方法了,如果谁有更好的方法,请告知,谢谢。
mysql中使用变量执行sql语句:


set @PREFIX = 'fk_';
create database fk_manage;
use fk_manage;
set @QUERY = concat("create table ", @PREFIX, "user(
    id int(10) primary key auto_increment,
    name varchar(25) not null,
    password varchar(32) not null,
    email varchar(50),
    reg_date TIMESTAMP default 0,
    last_login TIMESTAMP default CURRENT_TIMESTAMP on update current_timestamp);");
prepare execsql from @QUERY;
execute execsql;

set @QUERY = concat("create table ", @PREFIX, "role(
    id int(10) primary key auto_increment,
    name varchar(25) not null,
    action varchar(32) not null,
    inuse tinyint default 1 comment '1 for use,0 for stop',
    create_date TIMESTAMP default CURRENT_TIMESTAMP);");
prepare execsql from @QUERY;
execute execsql;

deallocate prepare execsql;

PHP,Mysql-根据一个给定经纬度的点,进行附近地点查询–合理利用算法,效率提高2125倍

目前的工作是需要对用户的一些数据进行分析,每个用户都有若干条记录,每条记录中有用户的一个位置,是用经度和纬度表示的。
还有一个给定的数据库,存储的是一些已知地点以及他们的经纬度,内有43W多条的数据。
现在需要拿用户的经纬度和已知地点进行距离匹配,如果它们之间的距离小于一定的数据,比如说500米,就认为用户是在这个地点。
MYSQL本身是支持空间索引的,但是在5.x的版本中,取消了对Distance()和Related()的支持,参考这里:MySQL 5.1参考手册 :: 19. 中的空间扩展 19.5.6. 测试几何类之间空间关系的函数,无法使用空间的距离函数去直接去查询距离在一定范围内的点。所以,我首先想到的是,对每条记录,去进行遍历,跟数据库中的每一个点进行距离计算,当距离小于500米时,认为匹配。这样做确实能够得到结果,但是效率极其低下,因为每条记录都要去循环匹配40W条数据,其消耗的时间可想而知。经过记录,发现每条记录处理的时间消耗达到1700ms,针对每天上亿的数据量,这样一个处理速度,让人情何以堪啊。。。
我自己也有个想法,就是找到每条记录所在点的经纬度周围的一个大概范围,比方说正方形的四个点,然后使用mysql的空间计算,使用MBR去得出点在这个矩形内的已知记录,然后进行匹配。可惜,自己没想出能计算到四个点经纬度的方法。
意外的,查询到了一个关于这个计算附近地点搜索初探,里面使用python实现了这个想法。
所以参考了一下原文中的算法,使用PHP进行了实现。
实现原理也是很相似的,先算出该点周围的矩形的四个点,然后使用经纬度去直接匹配数据库中的记录。

红色部分为要求的搜索范围,绿色部分我们能间接得到的结果范围

红色部分为要求的搜索范围,绿色部分我们能间接得到的结果范围

Read more…

mysql 命令行 查看mysql数据库大小、表大小、数据大小、索引大小

如何使用mysql 命令行 查看mysql数据库大小、表大小、数据大小、索引大小:

select * from information_schema.TABLES 
  where information_schema.TABLES.TABLE_SCHEMA='databasename'
  and information_schema.TABLES.TABLE_NAME='tablename'G

返回结果:

*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: databasename
     TABLE_NAME: tablename
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 6422930
 AVG_ROW_LENGTH: 213
    DATA_LENGTH: 1370884700
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 412930048
      DATA_FREE: 0
 AUTO_INCREMENT: 6422931
    CREATE_TIME: 2012-05-11 05:00:02
    UPDATE_TIME: 2012-05-22 15:12:06
     CHECK_TIME: 2012-05-11 09:58:52
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 'table comment'
1 row in set (0.00 sec)

其中:
information_schema.TABLES.TABLE_SCHEMA = ‘databasename’ and information_schema.TABLES.TABLE_NAME = ‘tablename’

信息存储在information_schema.TABLES这个表中,TABLE_SCHEMA 对应数据库名,TABLE_NAME 对应表名。
# TABLE_ROWS 代表拥有的数据行数。
# 总大小 = DATA_LENGTH(数据大小) + INDEX_LENGTH(索引大小)

结果以字节为单位,除1024为K,除1048576(=1024*1024)为M。

Read more…

mysql mysqldump 导入导出大文件 – MySQL server has gone away

在本机搭建测试环境时,使用了mysqldump导出了一个数据库文件,将近500M,即使是压缩后也有100M左右,在通过mysqldump导入时,发生了错误: “MySQL server has gone away.”。
个人猜测,发生 MySQL server has gone away 问题的原因很可能就是数据文件过大,导致超时。
所以对mysql进行修改:修改my.ini(在lnix/unix下是my.cnf)文件,加大超时参数

wait_timeout=2888888

如果没有此参数,直接在my.ini/my.cnf文件末尾一行添加上即可。
重启mysql。
最后再次执行导入语句

mysql -uroot -p123456 test_data < D:Xampsqldatatest.sql

注:在使用mysqldump时要注意,客户端mysqldump工具的版本要高于等于服务器的版本。
Read more...

MySQL怎样优化WHERE子句

此次讨论的为处理WHERE子句而进行的优化。例子中使用了 SELECT 语句,同样也适用于DELETE和UPDATE语句中的WHERE子句。

下面列出了MySQL执行的部分优化:

去除不必要的括号:

((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)
常量重叠:

(a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5
去除常量条件(由于常量重叠需要):

(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6
索引使用的常数表达式仅计算一次。

Read more…

SQL优化实例,看我如何把一个耗时1分钟的请求优化到9秒。

这主要是一个数据统计的web页面,展现统计结果。由于当时需求很紧,要求尽快能出来查询结果就行,而且是说以后会重新对这部分功能进行规划再开发。所以也没有考虑任何性能相关,劈哩啪啦的一天把很多数据统计的功能实现了。
现在,数据量已然有点规模了,但是还没有再开发的计划,目前还使用这个统计程序,但是由于有些数据量了,并且没有考虑过优化,所以目前查询速度很慢。尤其是其中有一个查询,居然耗时1分多,问题很大啊,今天抽点时间,找找问题,优化一下,主要做的工作的是改进sql语句,添加合适的索引。
存储的数据是根据用户的ID末尾的值来简单分表的,所以共有0到9一共10个表,需要遍历这10个表,然后把最后的数据相加,再处理后得到最后结果。由于需求还算比较简单,所以没有采用定时脚本去处理前几日的数据后入库保存的做法,如果采用以上做法,速度会快更多。

Read more…

求解,mysql查询建立索引后查询速度反而不如未建立索引以前

mysql 符合索引,未建立以前用时1.72s,建立一个符合索引后,反而用时2.44s,时间反而更长,是哪的问题呢,求解。。
以下是表结构和mysql explain 后的结果:

mysql> show create table new_bind_mp_weibo_user_info_0;
+----+| Table   | Create Table      |+---+
| new_bind_mp_weibo_user_info_0 | CREATE TABLE `new_bind_mp_weibo_user_info_0` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `weiboid` varchar(20) NOT NULL,
  `weiboun` varchar(100) NOT NULL,
  `mpnumber` varchar(11) NOT NULL,
  `apmac` varchar(100) NOT NULL,
  `bindtime` datetime NOT NULL,
  `source` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `search_key` (`bindtime`)
) ENGINE=MyISAM AUTO_INCREMENT=2768 DEFAULT CHARSET=utf8 | 
+-------------------------------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table weibo_user_visit_info_0;
+-----------------+| Table    | Create Table |+---------------------------------+
| weibo_user_visit_info_0 | CREATE TABLE `weibo_user_visit_info_0` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `weiboid` varchar(20) NOT NULL,
  `weiboun` varchar(50) NOT NULL,
  `apmac` varchar(100) DEFAULT NULL,
  `usermac` varchar(100) DEFAULT NULL,
  `mpnumber` varchar(11) DEFAULT NULL,
  `visittime` datetime NOT NULL,
  `source` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `weiboid` (`weiboid`)
) ENGINE=MyISAM AUTO_INCREMENT=17952 DEFAULT CHARSET=utf8 | 
+-------------------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table weibo_user_visit_info_0 add index `search_index` (`apmac`(17),`visittime`);
Query OK, 17944 rows affected (0.21 sec)
Records: 17944  Duplicates: 0  Warnings: 0
mysql> explain select sql_no_cache count(distinct(a.mpnumber)) as mpnumber, date_format(a.bindtime,'%Y-%m-%d') as gDate from new_bind_mp_weibo_user_info_0 a left join weibo_user_visit_info_0 b on a.mpnumber=b.mpnumber  and a.apmac=b.apmac   where b.id is NULL and a.apmac in (select ap_mac from newwifi.business_installedAP_info where businessid=100064)  and a.bindtime between '2012-03-01' and '2012-03-23 23:59:59' group by gDate order by gDate;
+----+--------------------+---------------------------+-------+---------------+--------------+---------+----------------------------+------+-----------------------------+
| id | select_type        | table                     | type  | possible_keys | key          | key_len | ref                        | rows | Extra                       |
+----+--------------------+---------------------------+-------+---------------+--------------+---------+----------------------------+------+-----------------------------+
|  1 | PRIMARY            | a                         | range | search_key    | search_key   | 8       | NULL                       |  394 | Using where; Using filesort | 
|  1 | PRIMARY            | b                         | ref   | search_index  | search_index | 54      | newwifi_visit_info.a.apmac |  142 | Using where; Not exists     | 
|  2 | DEPENDENT SUBQUERY | business_installedAP_info | ref   | search_key    | search_key   | 306     | const,func                 |    1 | Using index                 | 
+----+--------------------+---------------------------+-------+---------------+--------------+---------+----------------------------+------+-----------------------------+
3 rows in set (0.00 sec)

mysql> select sql_no_cache count(distinct(a.mpnumber)) as mpnumber, date_format(a.bindtime,'%Y-%m-%d') as gDate from new_bind_mp_weibo_user_info_0 a left join weibo_user_visit_info_0 b on a.mpnumber=b.mpnumber  and a.apmac=b.apmac   where b.id is NULL and a.apmac in (select ap_mac from newwifi.business_installedAP_info where businessid=100064)  and a.bindtime between '2012-03-01' and '2012-03-23 23:59:59' group by gDate order by gDate;
+----------+------------+
| mpnumber | gDate      |
+----------+------------+
|        4 | 2012-03-01 | 
|       10 | 2012-03-02 | 
|        3 | 2012-03-03 | 
|       10 | 2012-03-04 | 
|       12 | 2012-03-05 | 
|       10 | 2012-03-08 | 
|        4 | 2012-03-09 | 
|       14 | 2012-03-12 | 
|       14 | 2012-03-13 | 
|        7 | 2012-03-14 | 
|       12 | 2012-03-15 | 
|        8 | 2012-03-16 | 
|        7 | 2012-03-17 | 
|       11 | 2012-03-18 | 
|       11 | 2012-03-19 | 
|        8 | 2012-03-20 | 
|        8 | 2012-03-21 | 
|        9 | 2012-03-22 | 
+----------+------------+
18 rows in set (2.44 sec)

mysql> alter table weibo_user_visit_info_0 drop index search_index;
Query OK, 17944 rows affected (0.13 sec)
Records: 17944  Duplicates: 0  Warnings: 0

mysql> explain select sql_no_cache count(distinct(a.mpnumber)) as mpnumber, date_format(a.bindtime,'%Y-%m-%d') as gDate from new_bind_mp_weibo_user_info_0 a left join weibo_user_visit_info_0 b on a.mpnumber=b.mpnumber  and a.apmac=b.apmac   where b.id is NULL and a.apmac in (select ap_mac from newwifi.business_installedAP_info where businessid=100064)  and a.bindtime between '2012-03-01' and '2012-03-23 23:59:59' group by gDate order by gDate;
+----+--------------------+---------------------------+-------+---------------+------------+---------+------------+-------+----------------------------------------------+
| id | select_type        | table                     | type  | possible_keys | key        | key_len | ref        | rows  | Extra                                        |
+----+--------------------+---------------------------+-------+---------------+------------+---------+------------+-------+----------------------------------------------+
|  1 | PRIMARY            | a                         | range | search_key    | search_key | 8       | NULL       |   394 | Using where; Using temporary; Using filesort | 
|  1 | PRIMARY            | b                         | ALL   | NULL          | NULL       | NULL    | NULL       | 17944 | Using where; Not exists                      | 
|  2 | DEPENDENT SUBQUERY | business_installedAP_info | ref   | search_key    | search_key | 306     | const,func |     1 | Using index                                  | 
+----+--------------------+---------------------------+-------+---------------+------------+---------+------------+-------+----------------------------------------------+
3 rows in set (0.00 sec)
mysql> select sql_no_cache count(distinct(a.mpnumber)) as mpnumber, date_format(a.bindtime,'%Y-%m-%d') as gDate from new_bind_mp_weibo_user_info_0 a left join weibo_user_visit_info_0 b on a.mpnumber=b.mpnumber  and a.apmac=b.apmac   where b.id is NULL and a.apmac in (select ap_mac from newwifi.business_installedAP_info where businessid=100064)  and a.bindtime between '2012-03-01' and '2012-03-23 23:59:59' group by gDate order by gDate;
+----------+------------+
| mpnumber | gDate      |
+----------+------------+
|        4 | 2012-03-01 | 
|       10 | 2012-03-02 | 
|        3 | 2012-03-03 | 
|       10 | 2012-03-04 | 
|       12 | 2012-03-05 | 
|       10 | 2012-03-08 | 
|        4 | 2012-03-09 | 
|       14 | 2012-03-12 | 
|       14 | 2012-03-13 | 
|        7 | 2012-03-14 | 
|       12 | 2012-03-15 | 
|        8 | 2012-03-16 | 
|        7 | 2012-03-17 | 
|       11 | 2012-03-18 | 
|       11 | 2012-03-19 | 
|        8 | 2012-03-20 | 
|        8 | 2012-03-21 | 
|        9 | 2012-03-22 | 
+----------+------------+
18 rows in set (1.72 sec)

Read more…

在mysql中获取insert插入数据的id的方法

我们在写数据库程序的时候,经常会需要获取某个表中的最大序号数,或者刚插入的数据的ID值。
一般情况下获取刚插入的数据的id,使用select max(id) from table 是可以的。
但在多线程,高并发的情况下,就不行了。
开始的时候我想的是使用mysql_insert_id(),不知道会不会在并发的时候产生影响,查询了下手册,也是才发现,是根据connection来的,不同用户间不会产生影响。所以也不用去想先把表锁起来,插入取得ID值后再解锁。直接正常插入,然后取值,即可。

int mysql_insert_id ([ resource $link_identifier ] )

mysql_insert_id()返回给定的 link_identifier中上一步 INSERT 查询中产生的 AUTO_INCREMENT 的 ID 号。如果没有指定 link_identifier,则使用上一个打开的连接。

如果上一查询没有产生 AUTO_INCREMENT 的值,则 mysql_insert_id()返回 0。如果需要保存该值以后使用,要确保在产生了值的查询之后立即调用 mysql_insert_id()。
warning: mysql_insert_id() 将 MySQL 内部的 C API 函数 mysql_insert_id()的返回值转换成 long(PHP 中命名为 int)。如果 AUTO_INCREMENT 的列的类型是 BIGINT,则 mysql_insert_id()返回的值将不正确。可以在 SQL 查询中用 MySQL 内部的 SQL 函数 LAST_INSERT_ID() 来替代。

此外,还可以通过QUERY两个SQL语句来执行:

--1 ,查询 LAST_INSERT_ID()
mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 | 
+------------------+
1 row in set (0.00 sec)

--2 查询 @@IDENTITY
mysql> select @@IDENTITY;
+------------+
| @@IDENTITY |
+------------+
|          4 | 
+------------+
1 row in set (0.00 sec)

Read more…