今天公司数据库出现了停顿和阻塞的问题，检查数据库的时候发现，show processlist;可以看到大量： …. : 3436942 : unauthenticated user : 192.168.0.4:49607 : : Connect : : login : : 3436943 : unauthenticated user : 192.168.0.4:49608 : : Connect : : login : ….. 不断有未验证的用户尝试登录却没有通过，有同学Google出来，发现是和域名解析有关系： 不管什么客户端连接上来，服务器端都会对客户端进行DNS反查，来获得客户端的域名或主机名。 很有可能是因为DNS服务器出了问题，才导致无法解析——虽然解析出来的结果应该是没有 同学通过在my.cnf中加入skip-name-resolve来禁止反向域名解析（或者在启动命令行中添加参数–skip-name-resolve。 这种方式也是推荐的MySQL优化方式之一。 但这种方式的缺点是，权限中的host不能再使用主机名，而要使用IP地址，所幸我们配置的是“%”，因此我比较倾向于使用另一种方式，就是在/etc/hosts文件中添加对应的host记录： 192.168.1.4 frontend
Tag Archives: mysql
Rails SQL Session Store优化版
2008年12月22日 – 9:58 下午
问题根源 原始的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插件十分简单，只需以下几个步骤： 将该插件代码从我们的SVN库中安装到vendor/plugins目录中（可以使用./script/plugin install安装，或者piston import命令进行安装——看你喜欢）例如： $ piston import http://rails-fast-sessions.googlecode.com/svn/trunk/ vendor/plugins/fast_sessions 在config/environment.rb文件中启用ActiveRecord会话仓库： Rails::Initializer.run do |config| …… config.action_controller.session_store = :active_record_store …… end 为新的会话表创建数据库迁移： [...]
横向扩展（Facebook）
2008年10月13日 – 10:46 上午
原文：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层可能总是同一个版本而非正确的数据。 考虑下面的例子： 我将我的名字从“Jason”改成了“Monkey” 我们把“Monkey”写入了加利福尼亚的主数据库并且从加利福尼亚和弗吉尼亚的memcache中删除了原来的名字 有个人在弗吉尼亚访问我的信息 在memcache中没有找到我的姓名信息，所哟我们从弗吉尼亚的从数据库中读取，由于复制的延迟获得了“Jason” 我们将姓名“Jason”存入弗吉尼亚的memcache 同步复制上来了，我们将名字信息在从数据库中更新为“Monkey” 另一个人在弗吉尼亚访问我的信息 我们在memcache中找到了名字并返回“Jason”。 在我再更新我的名字或者数据过期需要再访问数据库之前，我的名字在弗吉尼亚会一直显示为“Jason”，在加利福尼亚显示为“Monkey”。混乱吧？确实。欢迎来到分布式系统的世界，在这里一致性确实是一个难题。 幸好，解决方案要比问题容易解释。我们对MySQL做了一个小小的改动，让MySQL能在同步复制流中附加一个额外的信息。我们利用这个功能将要变更的所有数据对象追加到给定查询上，然后当从数据库“看到”这些对象后，要负责在进行了数据库更新后将这些值从缓存中删除。 我们是怎么做到的呢？MySQL是用了一个词法解析器和yacc语法来定义查询的结构然后对其进行解析。为了解释方便，我对其进行了简化，这个语法最顶层差不多如下： query: statement END_OF_INPUT {}; statement: alter | analyze | backup | call [...]
ORDER BY RAND()
2008年08月30日 – 11:14 下午
原文地址：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秒。已经是相当慢的了。如何优化，请往下看：
MySQL初级性能调整脚本
2008年04月12日 – 11:37 上午
来源：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
2008年04月7日 – 11:05 下午
声明：此对比仅限于本人对公司的服务器情况所做的比较，并非通用和全面的比较。 随着公司业务的进一步扩展，服务器所承受的用户也越来越多，我也琢磨着怎么进一步提高数据库服务器的负载能力。我查到一些资料，InnoDB支持事务，支持行级锁，而且比MyISAM更具有伸缩性，MyISAM对于读取多写入少的数据库更好。 公司只有一台数据库服务器，是租用的万网双线机房的服务器（独享II型），配置并不好： CPU：PD2.8G(双核) 内存：1G DDRII 硬盘：SATA -80G 网卡：双1000M 安装的CentOS 3.2——万网没有别的Linux操作系统，可能都是预先安装好，拿来就可以用。 我安装了MySQL 5.0，用的是从MySQL网站上下载的RPM包。 公司的数据库的操作中，更新和插入占了大部分，而一般的网页型的网站则大部分仅仅是查询，很多还可以被缓存，将公司的数据库查询的记录和我的BLOG的数据库查询的记录做个对比： （注：公司的服务器上使用了memcached，应用层将大部分记录缓存于此减轻数据库压力，所以在图上看不到多少Cache hit） 这样，我武断地认为，因为公司的数据库主要是插入和更新这些操作，所以应该采用InnoDB格式才能更好地发挥效果。而事与愿违，在转换了格式之后，性能却出现了大幅度下滑，在每天的定时数据统计期间，还可能会导致数据库大量连接阻塞，查看了性能图像之后，发现是CPU大量的时间都用于I/O等待上了： 对应的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
2007年04月6日 – 6:17 下午
如果在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兼容性
2007年03月26日 – 6:19 下午
这次有台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文件，然后便成功了，特此备忘。 老外文章的地址： http://edin.dk/plugin/tag/php4+mysql5+windows+build 补丁下载地址：php-4.4.6-w-mysql-5.0.37.zip
Python MySQLdb的重大疑问
2006年10月13日 – 6:35 下午
最近开发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。
