多进程共用mysql链接引发的一个问题

多进程共用mysql链接引发的一个问题

我们知道,每次fork操作系统都会复制进程的所有信息产生一个子进程(部分写时复制),其中就包括socket链接资源符。每个进程在PCB(Process Control Block)中都保存着一份文件描述符表,文件描述符就是这个表的索引,每个表项都有一个指向已打开文件的指针,现在我们明确一下:已打开的文件在内核中用file结构体表示,文件描述符表中的指针指向file结构体。实际上包括文件描述符,socket等资源符号,操作系统并没有真的复制,而是在父子进程之间复制了资源符的指针。 操作系统允许多个进程公用一个socket资源符,但是会有一定的风险。例如:两个进程同一时刻接收服务器发来的响应,就有可能抢读到对方的响应。 今天就遇到了与父子进程共用socket链接的一个问题,先上代码: $mysql = mysql_connect(‘127.0.0.1’, ‘root’, ‘123456’); $process_count = 0; while(true){ $pid = pcntl_fork(); if($pid<0){ echo “fork error” . PHP_EOL; }elseif($pid == 0){ $process_count++; if($process_count > 3){ pcntl_wait($status); $process_count–; } }else{ $mysql = mysql_connect(‘127.0.0.1’, ‘root’, ‘123456’); // do something exit; } } 我们看到,子进程会重新建立mysql链接,执行完任务后退出。退出时,链接自动关闭。最开始并没有出现问题,运行了过一会,出现了mysql server has gone away的错误。而重新建立mysql链接总是返回false。 后来与人讨论,才想起mysql_connect函数的第四个参数new_link默认为flase,也就是说,在参数相同的情况下,mysql会复用没有关闭的mysql链接。 这里我们回想下,上面的程序fork第一个子进程,由于new_link参数默认为false,所以实际上是复用了父进程的mysql链接,而当这个进程退出时,链接被关闭,mysql关闭会话线程。而父进程再次创建子进程时,同样继承了mysql资源服,而父进程并不知道mysql链接已经被第一个子进程关闭,所以被fork出来的第二个子进程同样不知道mysql链接不可用(服务端已关闭)。于是mysql_connect时依然重用了之前的mysql链接,必然是链接不成功的,返回false。 问题虽然不大,但是折腾了好久,最后总算是弄清楚了原因。 写代码在于折腾。 原创文章,转载请注明:
Continue reading 多进程共用mysql链接引发的一个问题

PHP异步并发访问mysql简单实现

PHP异步并发访问mysql简单实现

在实际的开发过程中,我们常常会遇到需要操作多张表,多个库的情况。有时因为一些限制我们不能进行连表(例如,异地数据库),所以只能用php串行访问后再在php里进行合并,有时还需要模拟mysql对合并后的结果进行排序、归并等。 这里产生的一个问题就是串行带来的访问时间问题。由于传统的串行访问方式,我们只能等到一条sql执行完毕后才可以执行下一条,所以执行时间是累加的。PHP官方手册提供了一种可以异步并发访问mysql的方式,详见:http://php.net/manual/zh/mysqli.poll.php,参考资料:https://svn.osgeo.org/mapguide/sandbox/rfc94/Oem/php/ext/mysqli/tests/mysqli_poll.phpt,使用此种方式,我们可以对Mysql进行异步并发访问,访问时间不再是串行累加,而是取决于执行时间最长的sql。 项目地址:https://github.com/huyanping/async-mysql-php show you the code: try{ $async_mysql = new \Jenner\Mysql\Async(); $async_mysql->attach( [‘host’ => ‘127.0.0.1’, ‘user’ => ‘root’, ‘password’ => ”, ‘database’ => ‘test’], 'select * from stu' ); $async_mysql->attach( [‘host’ => ‘127.0.0.1’, ‘user’ => ‘root’, ‘password’ => ”, ‘database’ => ‘test’], 'select * from stu limit 0, 3' ); $result = $async_mysql->execute(); print_r($result);
Continue reading PHP异步并发访问mysql简单实现

SQL查询优化——索引设计

SQL查询优化——索引设计

在上一个章节(SQL查询优化——数据结构设计)中,我们讨论了数据结构的重要性。这一章节,我们将继续讨论SQL查询优化中的索引设计。 良好的查询性能,是建立在上一章节中陈述的几个问题的基础上的,而在这几点中,数据结构以及索引设计是最为重要的两点。良好的索引,必然是基于良好的数据结构之上的。索引设计不能独立于数据结构。糟糕的数据结构不仅仅会影响到索引性能,上文已经列举了几点坏处,这里不再累赘。 什么样的索引才是好的索引?和数据结构一样,这也没有一个通用的标准,具体要看业务场景。如何建立好的索引,并不是一簇而就的过程。大概的过程应该是: 根据业务读写场景,确定数据结构分布 给出数据结构字典 列出大部分主要的读写SQL,根据SQL设计索引 索引合并、优化 1、根据业务读写场景,确定数据结构分布 笔者比较喜欢接近0冗余的数据结构设计,坚持这种观点有以下几个原因: 接近0冗余的数据结构设计,将能给项目带来更高的可扩展性 表的体积更小,索引更小 数据结构分散规范,程序编写会更合理,处理往往更方便 有了接近0冗余的数据,数据汇总更容易,数据清洗更容易 接近0冗余的数据结构设计,并不是一定的,在某些场景,我们需要对数据进行汇总、冗余等操作。把数据按照细粒度分层,对于数据库设计、项目扩展性、数据库性能,从长远来看都是一个不错的方案。如果接近0冗余的数据结构,造成读写瓶颈,我们可以借助汇总和冗余解决这样的问题。orcale提供的物化视图更适合做自动化的数据汇总,mysql本身并没有提供物化视图,但是在github上有一个flexviews的项目可以提供支持。它是从binlog中分析出数据的变化,并实时的反映到物化视图中,不会影响现有数据库的性能。但是该项目貌似很少得到应用,最少目前还没有了解到关于它的更多消息,确实有些遗憾,可能在稳定性健壮性方面还有一些问题。 2、给出数据结构字典 确定了数据结构分布之后,我们要做的就是给出大概的一个表结构。可以借助excel或者EA等工具做图标、画图。例如下面的这张图表 这样我们就有了一个大概的表结构,当然SQL语句可以放到最后面再写。 3、列出大部分读写SQL、根据SQL设计索引 好的索引是能够满足业务查询的索引,没有绝对的好索引,只有能尽量满足查询的好索引。我们要考虑具体的业务查询场景,才可以根据查询场景设计出合理的SQL语句。不考虑查询场景而设计出来的索引,往往包含低效甚至没有用的索引,既不能帮助优化查询性能,也会影响表的性能。 我们可能不需要写出所有的SQL,但是至少我们应该知道,我们要从哪些维度(哪些列)去读取数据。然后根据这些列的离散度(可能需要你做下简单的运算),使用概率等因素列举出最适合做索引的几个字段,然后考虑是否需要组合索引,哪些列做组合做引更为合适,能够满足更多的查询场景。组合索引的顺序有时会影响到查询的性能,但大多数情况下,组合索引的顺序是由查询场景决定的。如果查询场景对组合所的顺序没有要求,则离散度更高的列在前面再一定程度上会提升索引的性能。 组合索引是提升查询性能的一大利器,但也是影响写入性能的一个重要因素。过多的组合索引不适合写操作过多的场景,这一条同样适用于单列索引。在很多场景中,我们的查询都会带有时间字段,以时间字段为最左键建立组合索引是更好的选择。组合索引不一定要满足所有字段的条件才会使用,只需要满足连续的左边的列,即可。组合索引的最左列,可以用来做单列索引,这个大多数据人都知道。但是笔者还是见过这样糟糕的设计,我更愿意相信可能是开发者太忙了,没有顾及到这些细节问题。 需要连表的字段,最好加上索引,这会大大提升连表查询的效率。具体在哪一张表上加索引,要具体分析到SQL语句的执行计划,你需要确认EXPLAIN的结果,哪个表是优先被扫描的,后被扫描的表往往会用到连表字段的索引。这样你只需要在第二张表的连表字段上加索引就可以了;第一张表上加,如果不是业务场景要求,往往这个索引是没有任何用处的。 表的读写属性,也是我们设计索引的一个考量因素。如果表写入频繁,我们索引应该优先适合UPDATE、DELETE后面的WHERE字句,甚至有时为了提高写入性能,我们会牺牲掉一些使用概率小的索引(有一些开源统计工具可以给你提供一些舒服的报表)。如果是读取频繁的,在写入没有瓶颈的情况下,我们可以建立更多的合适的索引。但是多,不意味着你要建立一些使用概率极小、重要度不低,甚至不会被用到的索引。 最后,在表结构建立之后,你可能需要测试下你的SQL是否能够按照你设计的索引来执行,如果不行,可能你的索引并不是最优的。 4、索引合并、优化 表设计完成后,并不意味着你的工作已经结束的。可能在最开始的时候,你的索引是最优的,但随着时间的流逝,你的数据量可能会翻几倍,一些存储引擎的索引基数可能也会有问题,可能会出现一些平时运行良好的查询语句,变得缓慢,甚至成为系统的瓶颈。这时我们需要重新根据表结构、查询场景、数据量、索引基数等多方面因素,对索引进行合并和优化。 如果你的表出现了写瓶颈,往往是因为数据量过多,索引体积增大,数据的写入需要维护过多的索引。可能你需要删掉一些索引,但这往往是很难操作的,因为最初设计的索引是为了满足业务查询,而这些查询的性能也尤为重要。如果你不能删掉一些索引,做索引合并是一个更好的选择。你可以牺牲掉一些单列索引,转换成组合索引,或者牺牲掉组合索引变成单列索引。如果索引合并在你的业务场景中依然做不到,那可能你需要做些数据汇总的工作,这些查询已经不适合在这样一张写入瓶颈的表上运行了。 如果你的表出现的是读瓶颈,原因会有很多。例如: 数据量增多,部分列的离散程度变低 大型连表操作 出现全表扫描 锁表 索引基数异常 上面只是列举了几个常见的问题,还有很多的其他原因。针对这些问题,我们可以通过如下办法解决: 删除一些离散度低的列索引,转而关注离散度更高的查询列,可以解决数据量增多,离散度变低的问题。大型表的连表操作,如果索引设计的不合理,往往会成为性能杀手,但这不是绝对的(连表意味着你可以使用多个索引)。如果连表条件过于复杂,也会造成查询缓慢,例如某些列用了随机数赋值(这是一个比较极端的例子)。针对连表SQL,重新考虑索引设计,是解决这个问题的办法。如果行不通,那么把连表的操作迁移到程序中,在程序中做处理是更好的解决方案(不要什么事情都给数据库去做,数据库君很委屈好么)。你可以针对这样的连表操作,建立一套简单的可复用框架,在程序中完成数据拼接剔除的工作。 全表扫描是因为你的查询不适合任何一个SQL,数据库认为根据它所收集的索引基数,全表扫描是一个更高效的选择。造成这一结果的原因可能是你没有未这类查询建立索引,或者这个SQL写的有问题(后面章节会继续关于编写SQL的讨论)。建立新的索引,或者修改查询条件使之适用现有索引是一个好的解决方案。 索引基数异常的问题的并不是很常见,而且只有mysql的isam引擎会出现这样的问题。针对这样的表,你可能需要定期重新生成索引基数,这样不至于表运行了一段时间后由于收集到的索引基数和实际索引基数差别太大而造成查询效率低下(错误的索引基数导致mysql生成低效的查询计划)。笔者曾经遇到过isam引擎莫名其妙的所有索引基数都变成了NULL,可能是MYSQL的一个BUG,所有的查询都变成了全表扫描。后台通过几个命令解决了这个问题,不过最好写一些定时任务,定期重新生成索引基数,这样能使你的表一直处于一个比较良好的状态。   原创文章,转载请注明: 转载自始终不够 本文链接地址: SQL查询优化——索引设计

SQL查询优化——数据结构设计

SQL查询优化——数据结构设计

本文部分内容会涉及mysql,可能在其他数据库中并不适用。本章节只针对数据库结构设计做讨论,查询优化的其他内容待续。 数据库设计及使用是WEB开发程序员必备的一项基础技能,在大数据量和高并发场景,合理的数据结构及SQL查询优化对项目来说都会显得格外重要。大部分有经验的程序员都能了解到,程序的瓶颈往往不在程序本身,而在数据访问层。造成数据访问效率低下的原因有很多,如何解决这些问题,直接影响到应用的稳定性、健壮性。以下列举几个常见的问题: 数据库锁表,查询阻塞 高并发场景下,链接数量瓶颈 查询效率低下,程序长时间无法退出 写入性能低下,造成读写竞争激烈 以上只是列出了数据库使用过程中比较常见的问题,出现这些问题的常见原因列举如下: 数据结构设计不合理 索引设计糟糕 程序维护数据链接不合理 程序员太懒惰,数据库做了不擅长的工作 数据冗余 SQL太渣 本节只对数据结构设计不合理进行讨论,后续章节会继续讨论其他内容。 一直觉得作为一个中级以上水平的程序员,查询优化是一项必备的基础技能。良好的数据结构设计,直接影响到后期软件的性能、健壮性、可维护性、可扩展性。见过很多因为数据结构设计不合理而造成软件最终难以扩展,难以维护的场景。要避免这些问题,我们就要掌握良好的数据结构设计能力。 怎样的数据结构才是合理的?这并没有一个完美通用的解决方案,要考虑具体的应用场景。但有一些准则,使我们应该尝试去遵守的。列举如下: 根据业务查询场景,考虑数据结构分布 如果没有业务主键,应建立ID自增主键 保证使用较小的数据类型,避免空间浪费 合理控制表的字段数量,必要时分表存储 添加字段注释 针对以上几点,分别详述如下: 1、根据业务场景,考虑数据结构分布 业务场景,决定了你要存储什么样的数据,但它不会决定你要如何存储这些数据。你可以简单的将这些信息存储到一张表里,例如user表。但当我们需要更多的信息,例如用户的附属属性(学校,住址等),如果全部塞到一张表里,对于小数据量的数据库不会有太大问题,但当遇到大数据量的场景时,查询就有可能变的缓慢。分表会是一个更好的解决方案,根据不同的业务场景,将这些信息分为两类,存储在不同的表里,是更加合理的解决方案。 这里要说的其实是,不要为了方便把所有的东西都塞到一张表里,虽然这样会让你的程序编写起来容易很多,但是会造成更多的问题。例如有些人会把1:N的关系存储到一张表里,这样就会带来数据冗余,坏处有很多,例如:针对N的写改删查都会变得很复杂;表体积变大、字段增多,造成查询缓慢;其他表链表查询时速度缓慢等等。 2、如果没有业务主键,应建立ID自增主键 主键是一条记录的唯一标志,没有主键在很多时候我们无法得心应手的操作数据。可能在某些场景下,我们确实没有设置主键的必要,但无论你是否主动设置主键,数据库都会有一个主键(如果你没有主动设置,数据库默认会有一个ROW_ID列,而这一列是你看不到的)。主键在连表、查询等方面业务提供很大帮助,所以无论如何,建立一个主键是很必要的 3、保证较小的数据类型,避免空间浪费 较小的数据类型意味着较小的存储代价,且数据库能够更高效的利用缓存空间。存储引擎都会采用不同的方式对索引或者数据缓存在内容中,较小的数据类型意味着在有限的内容空间中,你能够存储更多有价值的数据。对于可变长度的varchar类型,如果我们设置的是20长度,但实际占用的只有10个长度,在载入内存时,占用的空间依旧是20而不是10。所以对于可变长度类型,合理的长度更为重要。 4、合理控制表的字段数量,必要时分表存储 字段数量过多如果不是因为业务需且数据结构设计合理,大多会产生以下几个问题: 数据冗余 索引过多 表体积大 这里要提醒避免不必要的数据冗余,针对数据冗余的讨论我们暂且放在后面。 因为字段数据量多,往往查询场景也会非常复杂多变,所以索引也就跟着变多了。索引多会直接影响到表的写入性能,这个性能的损耗是非常大的,可能是数以十倍计算的时间损耗。在写入频繁的场景,有可能会出现写入瓶颈。由于写入而影响读取性能的问题也很多。 表体积大意味着数据库在读取数据的时候需要扫描更多更大的数据块,载入内存做缓存时也不能充分利用缓存带来的效果。表大小对于表的性能也是由为重要的。 分表是解决字段过多的一个解决方案,数据库分表后,程序可能会改动比较大,但我们应该追求合理完美的软件设计,摒弃糟粕。分表后使用链表查询,或者在程序中做两次查询。有些人可能会觉得连表,性能一定很差,其实不然。连表意味着我们在同一个SQL中,可以使用两个索引,但是单表查询我们只能使用一个索引。如果索引设计合理,在大多数场景下(应该是大数据量场景),连表查询会比单表查询性能更高,甚至高出太多。曾经有过这样的场景,优化分表后画面变得没好多了。 5、添加字段注释 这里只是为了提示规范化数据库设计。   原创文章,转载请注明: 转载自始终不够 本文链接地址: SQL查询优化——数据结构设计

mysql复制的一例错误:Relay_Log_Pos不变

mysql复制的一例错误:Relay_Log_Pos不变

最近公司的一台Mysql从机延迟了18W秒,两天前发现延迟时,观察SLAVE STATUS,一切正常,以为近期业务调整造成写操作过多,从机延迟的比较厉害,就没有做过多注意。元旦放假回来后发下延迟时间竟然已经有18W。 具体的情况描述如下:IO线程和SQL线程运行正常,master日志拷贝正常。唯一不正常的问题就是Relay_Log_Pos一直不变,且SLAVE STATUS状态并没有报错。以为某一条语句长时间锁住的某张表造成的,但观察processlist发现数据库中并没有任何语句在运行。为了不影响业务运行,我们将所有读操作迁移到了另外一台,并对这台机器进行原因分析。SLAVE STATUS状态如下图: 我们经过几次SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1操作(跳过当前执行位置),发现依然会存在卡住的问题。于是我们想找出卡住的原因,根据上图给出的位置,我们队relay_log进行了分析mysqlbinlog relaylog.008077 –start-position=223496095 -f -d xxx | more 。结果如下图: 我们发现每次卡住的位置都在unkown event事件前面,每次跳过后碰到这个就会卡住。 中继日志中产生这样的时间的原因无从得知,可能是我们调整了表结构分区造成的。 最后我们多次执行SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1操作后不再存在卡住的情况。 原创文章,转载请注明: 转载自始终不够 本文链接地址: mysql复制的一例错误:Relay_Log_Pos不变

使用SQL语句导出MYSQL数据库及基本表的创建SQL语句

因为之前开发项目对于MYSQL的数据库备份一直都是很模糊的,所以今天百度了下各种数据库备份的PHP类,有幸看到几个比较牛逼的类,综合其优缺点,争取组合成一个接口更加合理,更加简单易用的类。 在这个过程中,看到了一段有意思的SQL代码,所以决定在博客里面写出来给大家一起分享:$result=$this->db->Query(“show create table $tableName”); 上段代码是某个网友写的一个类库里面的一句,我们都看到了这个特殊的SQL语句,经过测试,这个SQL语句可以返回创建基本表或数据库的SQL语句,这样我们可以不必根据数据库或基本表的结构自己来动态生成SQL语句。即使我们能做到这一点,我们也不能保证代码的正确性以及完整性。 本文允许转载,转载请保留源地址: http://huyanping.sinaapp.com/?p=81 原创文章,转载请注明: 转载自始终不够 本文链接地址: 使用SQL语句导出MYSQL数据库及基本表的创建SQL语句

MYSQL ERRNO:#1005 – Can’t create table ‘./my_news_release_system/user.frm’ (errno: 150)原因总结

#1005 – Can’t create table ‘./my_news_release_system/user.frm’ (errno: 150)   MySQL Error Number 1005 Can’t create table ‘XX.frm’ (errno: 150) 默认分类 2010-04-28 16:04:41 阅读364 评论0   字号:大中小 订阅 烦了我一下午  终于解决了  在这里分享下  一篇彻底解析这个错误的文章。 当你试图在mysql中创建一个外键的时候,这个出错会经常发生,这是非常令人沮丧的。像这种不能创建一个.frm 文件的报错好像暗示着操作系统的文件的权限错误或者其它原因,但实际上,这些都不是的,事实上,这个mysql报错已经被报告是一个mysql本身的bug并出现在mysql 开发者列表当中很多年了,然而这似乎又是一种误导。   在很多实例中,这种错误的发生都是因为mysql一直以来都不能很好的支持的关系的问题, 更不幸的是它也并没有指明到底是哪一个问题会导致上面那种错误,下面我把导致这个可怕 的150错误的常见原因列出来了,并且我以可能性的大小作了排序   已知的原因:   1, 两个字段的类型或者大小不严格匹配,例如,如果一个是INT(10), 那么外键也必须设置成INT(10), 而不是 INT(11) 也不能是 TINYINT. 你得使用 SHOW 命令来查看字段的大小,因为一些查询浏览器有时候把 int(10) 和int(11) 都显示为integer。另外,你还必须确定两个字段是否一个为 SIGNED,而另一个又是UNSIGNED, 这两字段必须严格地一致匹配,更多关于signed 和 unsigned 的信息,请参阅:http://www.verysimple.com/blog/?p=57   2, 你试图引用的其中一个外键没有建立起索引,或者不是一个primary key , 如果其中一个不是primary key 的放,你必须为它创建一个索引。   3, 外键的名字是一个已经存在的一个键值了,这个时候,你应该检查你的数据库以确保外健名字是唯一的,或者你在键名后面加上几个随机的字符以测试是否是这个原因。   4, 其中一个或者两个表是MyISAM引擎的表,若想要使用外键约束,必须是InnoDB引擎,(实际上,如果两个表都是MyISAM 引擎的,这个错误根本不会发生,但也不会产生外键),你可以通过查询浏览器来设置表的引擎类型   5, 你可能设置了ON DELETE SET NULL, 但是相关的键的字段又设置成了NOTS NULL 值。你可能通过修改cascade 的属性值或者把字段属性设置成 allow null 来搞定这个bug.   6, 请确定你的Charset 和 Collate 选项在表级和字段级上的一致   7, 你可能设置为外键设置了一个默认值,如 default=0   8, 在这个关系里面,其中的一个字段是一个混合键值中的一个,它没有自己独立的索引,这时,你必须为它创建一个独立的索引。   9, ALTER 声明中有语法错误 我的错误,SQL和MYSQL语法差别 SQL:
Continue reading MYSQL ERRNO:#1005 – Can’t create table ‘./my_news_release_system/user.frm’ (errno: 150)原因总结