MySQL 大量 unauthenticated user

今天公司数据库出现了停顿和阻塞的问题,检查数据库的时候发现,show processlist;可以看到大量:

不断有未验证的用户尝试登录却没有通过,有同学Google出来,发现是和域名解析有关系:

不管什么客户端连接上来,服务器端都会对客户端进行DNS反查,来获得客户端的域名或主机名。

很有可能是因为DNS服务器出了问题,才导致无法解析——虽然解析出来的结果应该是没有

同学通过在my.cnf中加入skip-name-resolve来禁止反向域名解析(或者在启动命令行中添加参数--skip-name-resolve

这种方式也是推荐的MySQL优化方式之一。

但这种方式的缺点是,权限中的host不能再使用主机名,而要使用IP地址,所幸我们配置的是“%”,因此我比较倾向于使用另一种方式,就是在/etc/hosts文件中添加对应的host记录:

Rails SQL Session Store优化版

问题根源

原始的ActiveRecord会话仓库很慢。对于低流量的网站而言没有什么问题,但是对于大一点的而言就慢了。首先,它的慢是因为ActiveRecord本身比较慢。虽然这是一个强大的ORM框架,但对于像会话管理这种简单的任务而言就是杀鸡用牛刀了。

还有其他的解决方案如cookie会话仓库(会话长度有限,不能在会话中存放敏感数据),memcached(无法持久化+难以实现高可用性方案)。

这就是为何要创建SqlSession仓库的原因。它直接操作mysql的数据库API,要比原始的AR会话仓库快很多。不过有时候它还是比较慢,因为:

  • 每次访问都会创建、更新会话 – 任何机器人或者偶然的访客都会在数据库中创建一条会话记录,最后导致会话表里面会有成千上万的无用记录,但其中99%的访问其实是不需要任何会话更新的。
  • 它使用32位字符串作为会话记录的键 – 所有RDBMS在处理字符串索引都要比整数慢很多,所以使用整数更好,然而我们的会话ID非常长,同时这些会话仓库都直接使用他们作为表索引。
  • 使用了auto_increment 主键,对于MySQL 5.1.21之前的版本都会导致InnoDB使用表级锁。在不必要的插入上使用表级锁会给大型网站造成奇怪的问题。

解决方案

FastSessions Rails插件便是作为对于以上几个问题的解决方案而诞生的。

首先,我们从会话表中去掉了id字段,所以我们无须用到auto-increment锁。接下来为了让查找更快,我们使用了以下这些技术:不使用(session_id)作为查找索引,使用(CRC32(session_id), session_id)——双列键,可以帮助MySQL更快地找到会话记录因为键的基数更高(这样,mysql可以更快地找到记录而不用检查很多索引行)。我们测试过这种方法,在大会话表中显示了10-15%的性能提升

最后,也是最强的优化是,对于空会话不创建数据库记录,同时如果数据没有在请求处理过程中没有更新过,则会话数据不会存回数据库。这个更改基本上可以减少50-90%的插入数量(根据应用程序的情况)。

那么,你肯定在想,用了这个插件之后会话究竟能快多少?这很难说。结果要看你是怎么操作会话的:如果你很少更改会话数据(如登录时储存用户id),那么可能会有90%的性能提升,但如果每次访问,你都要给用户会话写入些信息(比如最后一次访问时间last_visit_time),那么根据服务器的负载和会话表的大小,可能有5-15%的性能提升。

你只需要安装一个简单的插件,便可以自动实现上面这些更改。

我们解决了AUTO_INC锁的问题并去掉auto-increment键之后,又引入了一个新的问题,我在这里想说一下。这个问题是这样的。InnoDB会根据主键将所有数据分组。这意味着当我们使用自增长主键并向表插入记录时,会话记录会被组到一起,顺序地存储在磁盘上。然而如果使用比较随机的值(如一个随机会话id的crc32值)作为主键,那么每一个会话记录会被插入到属于它自己的不同的地方,那么会产生一些随机I/O,这对于I/O有限制的服务器不是很理想。所以,我们决定让用户自己选择在部署的时候使用何种主键,当你想在MySQL 5.1.22+上使用这个模块的话,可以设置

CGI::Session::ActiveRecordStore::FastSessions.use_auto_increment = true

这样在InnoDB中会连贯地插入数据。另一个情况当你的MySQL服务器的I/O有限制,不想因为随机主键而增加随机I/O,也可以这样设置。

如果你不想丢失使用AR会话插件创建的旧会话数据,你可以设置

CGI::Session::ActiveRecordStore::FastSessions.fallback_to_old_table = true

这样当某些session_id在新的会话表中找不到的时候,就会回去访问旧的会话表。旧会话表的名字可以使用CGI::Session::ActiveRecordStore::FastSessions.old_table_name 变量进行设置.

这个选项会使会话变慢所以我建议只要在升级到新会话表的时候才使用。在这种情况下,新的会话数据就会存入新表中,当到了会话超时期限的时候,就可以删除旧表了(我们用了两个月的期限,过了两个月之后,我们就可以删除旧表,并将此选项关闭。)。

安装

安装FastSessions插件十分简单,只需以下几个步骤:

  1. 将该插件代码从我们的SVN库中安装到vendor/plugins目录中(可以使用./script/plugin install安装,或者piston import命令进行安装——看你喜欢)例如:

    $ piston import http://rails-fast-sessions.googlecode.com/svn/trunk/ vendor/plugins/fast_sessions
  2. 在config/environment.rb文件中启用ActiveRecord会话仓库:

    Rails::Initializer.run do |config|
    ......
    config.action_controller.session_store = :active_record_store
    ......
    end
  3. 为新的会话表创建数据库迁移:

    $ ./script/generate fast_session_migration AddFastSessions
  4. 如果需要,可以打开新创建的迁移脚本并更改表名table_name和插件use_auto_increment参数。
  5. 运行数据库迁移:

    $ rake db:migrate
  6. 启动应用程序并尝试进行一定会保存数据到会话的操作。然后检查fast_sessions会话表(如果你没有改名字的话)有没有这条记录。

下载

该插件的最新版本可以在它的项目网站或在 SVN仓库中找到。该插件由Alexey KovyrinPercona的MySQL性能专家)制作。开发由Scribd.com赞助。

横向扩展(Facebook)

原文:Scaling Out

作者:Jason Sobel (notes)

翻译:ShiningRay

我于2007年四月加入了Facebook,在结束了几周的课程之后,我的经理Robert Johnson来找我。我们谈了很久,不过内容可以归结为:

Bobby: “那么,Jason,我们要在2008年之前在弗吉尼亚开一个新的数据中心。你能去帮点忙吗?”
Me: “呃…. 可以?”
Bobby: “很好!”

我在Facebook的第一个项目上投入的要比我预期的多一点点,但是我认为这是为何我们拥有如此一个非常强大的工程组织的原因;我们还有很多难题有待解决,这里每个人都迫不及待要立刻去解决他们。我开始了解为何我们需要建造一个新的数据中心以及我们需要解决什么问题才能让他正常工作。

有何必要?

在东海岸建造一个新的数据中心的主要原因就是“延迟”。在一个高速连接上发送一个包横穿大陆需要大概70微秒的时间,而对于普通的互联网用户而言,可能会需要的时间就长得多。通过将服务器放在弗吉尼亚,我们可以大大减少给东海岸和欧洲的用户传送网页的时间。

第二个关注点是空间、能源和灾难恢复。在我们位于加利福尼亚的主数据中心中已经没有多少物理空间了,而弗吉尼亚的点可以给我们充分的空间添加东西。我们还有一个类似问题就是要给予充足的电能驱动所有的服务器。最后,如果把我们限制在某个单独的地方,意味着如果出现灾难事件(断电、地震、怪兽),可能会导致Facebook长时间无法访问。

开始构建!

在我们能处理应用级的问题之前,我们的小组在弗吉尼亚投入了大量的心血构建服务器和物理空间。他们还完成了数据中心之间的网络和低延迟光线通道连接。这些工作是非常巨大的工程,然而我们顶尖的团队使之看上去像是小菜一碟。

网络和硬件都到位后,我们搭建了标准的3层架构:Web服务器,memcache服务器和MySQL数据库。在弗吉尼亚的MySQL数据库作为西海岸的数据库的从数据库(Slave)运行,所以我们花了几周的时间复制所有的数据,然后建立同步复制流(replication stream)。

现在硬件、网络和基础的设备都已经建立好,那现在就要面对两个主要的应用级的挑战:缓存一致性(Cache Consistency)和流量路径选择(traffic routing)。

缓存一致性

先说一下我们的缓存模型:当一个用户修改了数据对象后,我们的底层设施会向数据库写入新的值,并且从memcache中删除旧的值(如果存在)。下一次用户请求该用户对象的时候,我们从数据库中取出新的结果并写入memcache。后续的请求就会直接从从memcache中取出数据直到缓存过期或者被另外一次更新删除。

这种设置在只有一套数据库的时候运行得很好,因为我们只有当数据库完成了新值的写操作之后才删除memcache中的值。这种方式保证了我们能够从数据库中获得新值并且放入memcache中。然而,当在东海岸有一个从数据库后,情况就有些棘手了。

当我们在西海岸的主数据库中更新了一些数据之后,在东海岸的从数据库能正确反映这些新数值之前,中间有一个同步复制的延迟。通常这个延迟小于一秒钟,但是在高峰时期,它可能会延长到20秒。

现在我们假设在更新了加利福尼亚的主数据库的同时,我们从弗吉尼亚的memcache层中删除了旧值。然后有一个对弗吉尼亚的从数据库的读操作可能由于复制延迟还是看到的旧数值。然后弗吉尼亚的memcache可能会更新为旧的(不正确)的数值,然后它可能被“困住”直到被删除。如你所见,最差的情况是弗吉尼亚的memcache层可能总是同一个版本而非正确的数据。

考虑下面的例子:

  1. 我将我的名字从“Jason”改成了“Monkey”
  2. 我们把“Monkey”写入了加利福尼亚的主数据库并且从加利福尼亚和弗吉尼亚的memcache中删除了原来的名字
  3. 有个人在弗吉尼亚访问我的信息
  4. 在memcache中没有找到我的姓名信息,所哟我们从弗吉尼亚的从数据库中读取,由于复制的延迟获得了“Jason”
  5. 我们将姓名“Jason”存入弗吉尼亚的memcache
  6. 同步复制上来了,我们将名字信息在从数据库中更新为“Monkey”
  7. 另一个人在弗吉尼亚访问我的信息
  8. 我们在memcache中找到了名字并返回“Jason”。

在我再更新我的名字或者数据过期需要再访问数据库之前,我的名字在弗吉尼亚会一直显示为“Jason”,在加利福尼亚显示为“Monkey”。混乱吧?确实。欢迎来到分布式系统的世界,在这里一致性确实是一个难题。

幸好,解决方案要比问题容易解释。我们对MySQL做了一个小小的改动,让MySQL能在同步复制流中附加一个额外的信息。我们利用这个功能将要变更的所有数据对象追加到给定查询上,然后当从数据库“看到”这些对象后,要负责在进行了数据库更新后将这些值从缓存中删除。

我们是怎么做到的呢?MySQL是用了一个词法解析器和yacc语法来定义查询的结构然后对其进行解析。为了解释方便,我对其进行了简化,这个语法最顶层差不多如下:

很直观吧?一个query(查询)是一个能分解成某种我们熟知的MySQL表达式的statement(语句)。我们将这个语法修改为允许在任意查询后追加memcache键,如下:

查询现在可以有一个额外的组件;在语句statement之后有mc_dirty可以为空或者为一个关键词MEMCACHE_DIRTY后面跟着一个mc_key_list。一个mc_key_list只是一个逗号隔开的字符串列表,该规则会告诉解析器将所有字符串一个接一个存入某个叫做mc_key_list向量中,这个向量将被存入每查询解析器对象中。

看个例子,某个老式的查询看上去像:
REPLACE INTO profile (first_name) VALUES ('Monkey') WHERE user_id='jsobel'
在新语法下会变成:a
REPLACE INTO profile (first_name) VALUES ('Monkey') WHERE user_id='jsobel' MEMCACHE_DIRTY 'jsobel:first_name'

新的查询会告诉MySQL,除了要将我的名字更改为Monkey外,它还需要将一个对应的memcache键设脏。这很容易实现。由于每对象解析器对象现在储存了所有的memcache键,我们在mysql_execute_command最后添加了一小段代码——如果查询成功了,就设脏这些键。看看,我们成功地按照我们的目的——缓存一致性——劫持了MySQL同步复制流。

新的工作流变成了(更改的内容为粗体):

  1. 我将我的名字从“Jason”改为“Monkey”。
  2. 我将“Monkey”写入加利福尼亚的主数据库并从加利福尼亚的memcache中删除我的名字,但不包括弗吉尼亚的memcache
  3. 某个人在弗吉尼亚访问了我信息。
  4. 在memcache中找到了我的名字,并返回“Jason”。
  5. 同步复制到了之后,将从数据库中我的名字更新为“Monkey”。还需要从弗吉尼亚的memcache中删除我的名字因为缓存对象出现在同步复制流中了。
  6. 另一个人在弗吉尼亚访问了我的信息
  7. 没有在memcache中找到我的名字,所以从从数据库读出名字,得到了“Monkey”。

页面路径选择

我们还需要解决的另一个主要的问题是只有在加利福尼亚州的主数据库才可能接受写操作。这个情况就是说我们需要避免在弗吉尼亚服务那些需要进行数据库写操作的页面,因为他们都需要穿越整个大陆访问我们在加利福尼亚的主数据库。幸好,我们最频繁访问的页面(首页、档案、照片页面)在正常情况下都不会进行写操作。这样这个问题就归结于,当一个用户请求某个页面时,我们怎么判断它是否可以被“安全”地送到弗吉尼亚,或者它必须被引导到加利福尼亚?

这个问题最后有一个比较直观的答案。某个用户请求Facebook时,命中了第一批服务器其中的一个,这个服务器称之为负载均衡器;该机器的主要职责是选择一个Web服务器来处理该请求,不过它也进行一些其他目的的服务:防御拒绝服务攻击,多路复用用户连接等。这个负载均衡器拥有可以在第7层模式运行的能力,这样他可以检查用户请求的URI并根据这个信息进行路由选择决定。这个特性意味着,我们可以很容易地告诉负载均衡器哪些是“安全”页面,然后可以根据页面的名字和用户的位置决定是否要将请求发送到弗吉尼亚或者是加利福尼亚。

不过,这里还有一点问题。假设你访问editprofile.php来更改家乡信息。该页面没有被标记为安全所以他被引导到了加利福尼亚,并且进行了更改。然后你访问你的档案页面,同时由于这个页面是安全页面,所以被引导到了弗吉尼亚。然而因为前面提到的同步复制延迟,你可能不能立刻看到你刚刚做过的改动!这种体验会令用户感到非常混乱,同时会导致双重提交。我们通过在浏览器中设置一个包含(有过写入数据库操作的)当前时间cookie来绕开这个问题。负载均衡器会查看该cookie,如果它注意到20秒内你写入了些东西,将无条件地传送到加利福尼亚。过了20秒之后,我们确保数据已经同步到弗吉尼亚,这时便允许你回来访问安全页面。

回顾

从我们第一个用户在弗吉尼亚数据中心访问页面后的九个月中我们一直在运行同样架构获得了很好的效果。当然,一路上还有挫折;在头一两个月中,缓存一致性的框架非常地不稳定,逼我们在诊断和修复错误的时候每隔一段时间就要把流量从弗吉尼亚转移出去。当然,过了一段时间,我们消灭了这个问题,现在这个数据中心在Facebook的流量中占了很大的比重。

这个架构中主要的伸缩方面的挑战很明显:所有的写操作必须在同一个地方发生。更进一步我们对开发新的可以让我们在任何位置进行写操作的技术非常感兴趣。我们也在思考如何将新的数据中心做成一个灾难恢复点,以防怪兽要进攻加利福尼亚!想来帮帮我们吗?www.facebook.com/jobs!

ORDER BY RAND()

原文地址:http://jan.kneschke.de/projects/mysql/order-by-rand

翻译:ShiningRay

译者序
之前有位朋友提到从MySQL随机取1条记录其实只要SELECT * FROM table ORDER BY RAND() LIMIT 1即可。其实这个语句有很大的性能问题,对于大表的效率是非常低下的。我们可以看一下MySQL对其的解释:

EXPLAIN SELECT *
FROM money_logs
ORDER BY RAND( )
LIMIT 1
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table ALL NULL NULL NULL NULL 173784 Using temporary; Using filesort

这个SQL语句无法使用任何索引,还必须使用临时表和文件排序,在一个15万条记录的MyISAM表需要花大约0.3秒。已经是相当慢的了。如何优化,请往下看:

继续阅读“ORDER BY RAND()”

MySQL初级性能调整脚本

来源:http://www.day32.com/MySQL/

MySQL初级性能调整脚本

该脚本从“SHOW STATUS LIKE…”和“SHOW VARIABLES LIKE…”中提取信息来给出调整服务器变量的合理建议。他可以兼容所有MySQL 3.23以及更高版本(包括5.1)。

目前它会对以下内容做出建议:

  • Slow Query Log
  • Max Connections
  • Worker Threads
  • Key Buffer
  • Query Cache
  • Sort Buffer
  • Joins
  • Temp Tables
  • Table (Open & Definition) Cache
  • Table Locking
  • Table Scans (read_buffer)
  • Innodb Status

MySQL复制从服务器延迟跟踪器

检查MySQL复制从服务器状态

MYSQL_BACKUP.sh

更多内容请看原页面:http://www.day32.com/MySQL/

MyISAM vs InnoDB

声明:此对比仅限于本人对公司的服务器情况所做的比较,并非通用和全面的比较。

随着公司业务的进一步扩展,服务器所承受的用户也越来越多,我也琢磨着怎么进一步提高数据库服务器的负载能力。我查到一些资料,InnoDB支持事务,支持行级锁,而且比MyISAM更具有伸缩性,MyISAM对于读取多写入少的数据库更好。

公司只有一台数据库服务器,是租用的万网双线机房的服务器(独享II型),配置并不好:

     

  • CPU:PD2.8G(双核)
  •  

  • 内存:1G DDRII
  •  

  • 硬盘:SATA -80G
  •  

  • 网卡:双1000M

安装的CentOS 3.2——万网没有别的Linux操作系统,可能都是预先安装好,拿来就可以用。

我安装了MySQL 5.0,用的是从MySQL网站上下载的RPM包。

公司的数据库的操作中,更新和插入占了大部分,而一般的网页型的网站则大部分仅仅是查询,很多还可以被缓存,将公司的数据库查询的记录和我的BLOG的数据库查询的记录做个对比:

公司本周MySQL查询
我的网站本周MySQL查询

(注:公司的服务器上使用了memcached,应用层将大部分记录缓存于此减轻数据库压力,所以在图上看不到多少Cache hit)
这样,我武断地认为,因为公司的数据库主要是插入和更新这些操作,所以应该采用InnoDB格式才能更好地发挥效果。而事与愿违,在转换了格式之后,性能却出现了大幅度下滑,在每天的定时数据统计期间,还可能会导致数据库大量连接阻塞,查看了性能图像之后,发现是CPU大量的时间都用于I/O等待上了:

公司服务器本周CPU占用率

对应的IO状态的性能图志:

公司本周IO状态

大家可以从图看上到,使用了InnoDB的几天在图中间反应为大量的磁盘写操作,于是之后我又切换回MyISAM,可见MyISAM的写操作少得多。

为什么会导致这种情况发生呢?

可能和InnoDB的性质有关。InnoDB是ACID的,这种ACID的数据库都对磁盘的IO性能要求很高,也要求更大的内存。很多数据库服务器往往都使用SCSI RAID,动辄4G、8G甚至16G内存——由于我对数据库的研究并不是很深,我隐约记得InnoDB有个会导致性能下降的双重写入问题,以及log的flush问题(向达人求证)。而公司租用的这台服务器,内存小,硬盘也只是SATA的,瓶颈是十分明显的。

当然,据很多测试表明,在高配的情况下,InnoDB有更大的优势——就好比谁会在这种机器上装Oracle呢?即使Oracle在这种机器上表现差,又有谁会质疑Oracle的强大呢?

所以我的结论是,如果数据库的硬件配置低,而应用又对事务等高级特性要求不是很高的话,可以依然采用MyISAM。

ruby & mysql

如果在Ruby中使用MySQL遇到

undefined method `each’ for #<mysql:???? >

考虑升级MySQL到最新版本
ruby的mysql-win 2.7.3似乎不能与mysql 5.0.24兼容(倒是可以和mysql5.0.20兼容)

PHP4.4.6和Mysql 5.0兼容性

这次有台Windows服务器拿到手中,需要装AMP,我一激进,装了Apache2.2.4 + Php5.2.1 + mysql-5.0.37。事后发现,网站程序用PHP4写的,和PHP5.2.1有点兼容性问题,于是乎换装PHP4,发现PHP4没有Apache2.2的模块,只好用CGI方式安装。接下来程序运行报错数据库无法连接,猜测是php4的mysql扩展不兼容mysql5。在网上找了半天,发现有老外自己编译了一个php4.4.6的补丁,下载覆盖了原来的dll文件,然后便成功了,特此备忘。

Python MySQLdb的重大疑问

最近开发Python,数据库操作一直用的是SQLObject,但有个问题很让我头疼,就是MySQL的数据库的编码问题,主要是MySQL的。
起初我现在我在SQLite上测试开发,并没有出现问题。SQLObject的UnicodeCol工作很正常。而同时起初数据库中并没有任何非ASCII字符(也就是全英文),而后需求变化,增加了欧洲的一些内容,就涉及到latin1编码了,但奇怪的是,只要超出ascii范围(比如中文),即便通过Python将其转化为Unicode或者UTF-8编码的str(使用decode和encode方法),SQLObject在插入的时候就会出错。后来经过反复的检查,是MySQLdb的一个问题,SQLObject会通过获取数据库链接的character_set_name(),取得链接的字符集,然后对查询进行编码以符合这个字符集,但据调试,无论我用什么方法,比如链接的set_character_set()方法、执行“SET NAMES UTF8”这个语句,character_set_name()都总是返回“latin1”,这些可苦了我了,不知道这是不是算一个Bug。