kakapo's nest

从 max_connections 的实验带来的思考

Tue, 22 Apr 2008 10:13:31 +0000

      php没有数据库连接池的概念，一般情况下程序中使用mysql_connect()连接数据库，在php脚本执行完毕之后进程会释放掉连接资源所占的内存。访问每个php网页都会出现一个解析脚本的进程，那么数据库服务端也会出现一个connect连接。当然前提是只有一个数据库设计的系统。在高并发高流量的情况下，基于数据库驱动的应用系统很容易出现瓶颈，这个瓶颈首先就是max_connections，即数据库的同时最大连接数，在MySQL安装的时候默认只有100个。增大这个连接数能马上起到效果。但是并不是能无限量增加，我在window服务器下和linux服务器下分别做了实验。

实验准备:

我分别使用了php的MySQL客户端工具mysql_connect()和pdo做实验。本实验故意不采用长连接方式。关于长连接的作用会在以后讨论。以下是知识准备：
    resource mysql_connect ( [string $server [, string $username [, string $password [, bool $new_link [, int $client_flags]]]]] )

    mysql_connect()在4.2.0版本后添加 new_link 参数。如果用同样的参数第二次调用 mysql_connect()，将不会建立新连接，而将返回已经打开的连接标识。如果让new_link=true, 会使 mysql_connect() 总是打开新的连接，甚至当 mysql_connect() 曾在前面被用同样的参数调用过。

    pdo是PHP5内置的一个轻量级，统一接口访问数据库的抽象类。

my.cnf设置如下

[mysqld]
skip-name-resolve
port = 3306
socket = /opt/lampp/var/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections = 10000

注意max_connections = 10000；

实验代码

set_time_limit(0);
function getmicrotime()
  {
    list($usec, $sec) = explode(" ",microtime());
    return ((float)$usec + (float)$sec);
  }
$time = getmicrotime();
error_reporting(E_ALL);
//pdo
$dbsettings = array(
      'host' => 'localhost',
      'port'=> '3306',
      'type' => 'mysql',
      'user' => 'root',
      'passwd' => '',
      'dbname' => 'cdcol',
      'charset'=> 'utf8',
      'cache_time' => 3600
      );
$i = 0;
$db = array();
$d1 = '';
$dsn = $dbsettings['type'].":host=".$dbsettings['host'].";port=".$dbsettings['port'].";dbname=".$dbsettings['dbname'];
for($i=0;$i<10500;$i++){

           /**mysql_connect()的测试***/
//  $link = mysql_connect($dbsettings['host'], $dbsettings['user'], $dbsettings['passwd'],true);
//
//  if (!$link) {
//     $error = mysql_error();
//      @fwrite(@fopen(dirname(__FILE__)."/tmp/dberror.txt",'a'),date("Y-m-d H:i:s")." ".$dsn.'|'.$error."\n");
//  }else{
//    //mysql_select_db($dbsettings['dbname'], $link) or die ('Can\'t use foo : ' . mysql_error());
//    //$result = mysql_query("SELECT * FROM cds");
//    $db[] = $link;
//  }

               /***pdo的测试***/
  try{
    $d = new pdo($dsn,$dbsettings['user'],$dbsettings['passwd'],array(PDO::ATTR_PERSISTENT => false));
    if($d!=$d1){
      $db[] = $d;
    }
    $d1=$d;

    //$db -> getOne();

  }catch (PDOException $e){
    $error = $e->getMessage();
    echo $error."
";
//    $fp =fopen(dirname(__FILE__)."/tmp/dberror.txt",'a');
//    fwrite($fp  ,date("Y-m-d H:i:s")." ".$dsn.'|'.$error."\n");
//    fclose($fp);
  }

}

echo count($db);
echo "

";
echo getmicrotime() - time();
?>

实验结果：

在window下执行发现平均能到达平均1860个连接，然后报错
Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug。执行时间0.685220003128s

在linux下执行能达到8168个连接，然后报错，Can't create UNIX socket (24); 执行时间 0.406996965408 s

    由于不是使用长连接，php脚本执行完成之后资源马上被释放掉。php短连接数据库的执行效率还是相当不错的。但是本实验脚本还没有做实际的查询等操作，不能真实的模拟到用户的并发访问环境。但是从单独的创建连接数来看，单个MySQL服务器最多能支持8000个并发连接。如果是几万用户在线的网站，就需要想办法解决数据库的这个瓶颈了。虽然php提供了长连接这个选择，但是长连接的弊端也很明显，详细见这篇文章《PHP使用数据库永久连接方式操作MySQL的是与非》。

    至于window下跟linux下数据为什么差别会那么大呢？在MySQL官方文档中有这个说明:
The maximum number of connections MySQL can support depends on the quality of the thread library on a given platform.
跟MySQL运行平台操作系统的线程数量控制有关系。

解决方案：

    在数据库方面的解决方案主要是采用Master-Slaver的Cluster数据库集群方案，这个方案解决负载均衡，使连接数分流到 Slaver。另一个在php方面的解决方案是借助第三方软件SQL Relay来创建一个数据库连接池，从而减少单台MySQL服务器的并发连接数量，大大提高了数据库的访问效率。

Tags - max_connections,sql , relay

mySQL system error:111 113

Thu, 21 Feb 2008 04:49:37 +0000

最近遇到一个比较少见的问题，php连接mySQL的时候，会报错

Lost connection to MySQL server at 'reading initial communication packet', system error: 111

这种现象还不是不断重复的，出现的频率不确定，疑似mySQL数据丢包。google之后，得到解决办法，通过修改my.cnf

1. 注释掉 # skip-networking
2. 给MYSQL绑定IP bind-address = 123.45.67.89 # server IP address

关于MySQL5.0.3以上字符集支持与列类型注意事项

Wed, 10 Oct 2007 07:35:29 +0000

1. 从mysql 5.0.3之后 varchar 类型可以保存的有效最大字节数为65,535 bytes。

2. 关于如何计算 CHAR, VARCHAR, or TEXT 类型的列保存的字节数，需要考虑该列使用的字符集和字符数。

举例：varchar (300) 表示该列可以存300个字符，而不是字节；

当使用latin1字符集时，需要最大存储空间是300*1+2 = 302 bytes；其中2个字节是为了保存总长度，因为300 >255了；varchar(200) 需要的存储空间是200*1+1 = 201 bytes;

当使用utf8字符集时，需要最大存储空间是 300*3 +2 = 902；因为utf 可能遇到全部都是长度为3byte多字节字符(汉字，韩文，日文等)；

3.使用UTF8字符集时为了节省空间，使用VARCHAR而不要用CHAR。否则，MySQL必须为一个CHAR(10) CHARACTER SET utf8列预备30个字节，因为这是可能的最大长度。

Tags - mysql

[转]使用开源软件搭建的可扩展SNS网站

Thu, 30 Aug 2007 16:45:45 +0000

mixi.jp：使用开源软件搭建的可扩展SNS网站

于敦德 2006-6-27

Mixi目前是日本排名第三的网站，全球排名42，主要提供SNS服务：日记，群组，站内消息，评论，相册等等，是日本最大的SNS网站。Mixi从2003年12月份开始开发，由现在它的CTO - Batara Kesuma一个人焊，焊了四个月，在2004年2月份开始上线运行。两个月后就注册了1w用户，日访问量60wPV。在随后的一年里，用户增长到了21w，第二年，增长到了200w。到今年四月份已经增长到370w注册用户，并且还在以每天1.5w人的注册量增长。这些用户中70%是活跃用户（活跃用户：三天内至少登录一次的用户），平均每个用户每周在线时间为将近3个半小时。

下面我们来看它的技术架构。Mixi采用开源软件作为架构的基础：Linux 2.6，Apache 2.0，MySQL，Perl 5.8，memcached，Squid等等。到目前为止已经有100多台MySQL数据库服务器，并且在以每月10多台的速度增长。Mixi的数据库连接方式采用的是每次查询都进行连接，而不是持久连接。数据库大多数是以InnoDB方式运行。Mixi解决扩展问题主要依赖于对数据库的切分。

首先进行垂直切分，按照表的内容将不同的表划分到不同的数据库中。然后是水平切分，根据用户的ID将不同用户的内容再划分的不同的数据库中，这是比较通常的做法，也很管用。划分的关键还是在于应用中的实现，需要将操作封装在在数据层，而尽量不影响业务层。当然完全不改变逻辑层也不可能，这时候最能检验以前的设计是否到位，如果以前设计的不错，那创建连接的时候传个表名，用户ID进去差不多就解决问题了，而以前如果sql代码到处飞，或者数据层封装的不太好的话那就累了。

这样做了以后并不能从根本上解决问题，尤其是对于像mixi这种SNS网站，页面上往往需要引用大量的用户信息，好友信息，图片，文章信息，跨表，跨库操作相当多。这个时候就需要发挥memcached的作用了，用大内存把这些不变的数据全都缓存起来，而当修改时就通知cache过期，这样应用层基本上就可以解决大部分问题了，只会有很小一部分请求穿透应用层，用到数据库。Mixi的经验是平均每个页面的加载时间在0.02秒左右（当然根据页面大小情况不尽相似），可以说明这种做法是行之有效的。Mixi一共在32台机器上有缓存服务器，每个Cache Server 2G内存，这些Cache Server与App Server装在一起。因为Cache Server对CPU消耗不大，而有了Cache Server的支援，App Server对内存要求也不是太高，所以可以和平共处，更有效的利用资源。

图片的处理就显得相对简单的多了。对于mixi而言，图像主要有两部分：一部分是经常要使用到的，像用户头像，群组的头像等等，大概有100多GB，它们被Squid和CDN所缓存，命中率相对比较高；另一部分是用户上传的大量照片，它们的个体访问量相对而言比较小，命中率也比较低，使用Cache不划算，所以对于这些照片的策略是直接在用户上传的时候分发到到图片存储服务器上，在用户访问的时候直接进行访问，当然图片的位置需要在数据库中进行记录，不然找不到放在哪台服务器上就郁闷了。

参考网址：
http://www.javaeye.com/topic/21725?page=1
Tags - mysql , 设计

[转]MySpace的数据库构架发展之路

Thu, 30 Aug 2007 16:17:00 +0000

今天，MySpace已经成为全球众口皆碑的社区网站之王。尽管一流和营销和管理经验自然是每个IT企业取得成功的首要因素，但是本节中我们却抛弃这一点，而主要着眼于探讨在数次面临系统扩张的紧急关头MySpace是如何从技术方面采取应对策略的。

第一代架构—添置更多的Web服务器
MySpace最初的系统很小，只有两台Web服务器（分担处理用户请求的工作量）和一个数据库服务器（所有数据都存储在这一个地方）。那时使用的是Dell双CPU、4G内存的系统。在早期阶段，MySpace基本是通过添置更多Web服务器来对付用户暴增问题的。但到在2004年早期，在MySpace用户数增长到五十万后，其数据库服务器已经开始疲于奔命了。

第二代架构—增加数据库服务器
与增加Web服务器不同，增加数据库并没那么简单。如果一个站点由多个数据库支持，设计者必须考虑的是，如何在保证数据一致性的前提下让多个数据库分担压力。

MySpace运行在三个SQL Server数据库服务器上—一个为主，所有的新数据都向它提交，然后由它复制到其它两个；另两个数据库服务器全力向用户供给数据，用以在博客和个人资料栏显示。这种方式在一段时间内效果很好——只要增加数据库服务器，加大硬盘，就可以应对用户数和访问量的增加。

这一次的数据库架构按照垂直分割模式设计，不同的数据库服务于站点的不同功能，如登录、用户资料和博客。垂直分割策略利于多个数据库分担访问压力，当用户要求增加新功能时，MySpace只需要投入新的数据库加以支持。在账户到达二百万后，MySpace还从存储设备与数据库服务器直接交互的方式切换到SAN（存储区域网络）—用高带宽、专门设计的网络将大量磁盘存储设备连接在一起，而数据库连接到SAN。这项措施极大提升了系统性能、正常运行时间和可靠性。然而，当用户继续增加到三百万后，垂直分割策略也变得难以维持下去。

第三代架构—转到分布式计算架构
几经折腾，最终，MySpace将目光移到分布式计算架构——它在物理上分布的众多服务器，整体必须逻辑上等同于单台机器。拿数据库来说，就不能再像过去那样将应用拆分，再以不同数据库分别支持，而必须将整个站点看作一个应用。现在，数据库模型里只有一个用户表，支持博客、个人资料和其他核心功能的数据都存储在相同数据库。

既然所有的核心数据逻辑上都组织到一个数据库，那么MySpace必须找到新的办法以分担负荷——显然，运行在普通硬件上的单个数据库服务器是无能为力的。这次，不再按站点功能和应用分割数据库，MySpace开始将它的用户按每百万一组分割，然后将各组的全部数据分别存入独立的SQL Server实例。目前，MySpace的每台数据库服务器实际运行两个SQL Server实例，也就是说每台服务器服务大约二百万用户。据MySpace的技术人员说，以后还可以按照这种模式以更小粒度划分架构，从而优化负荷分担。

第四代架构—求助于微软方案
2005年早期，账户达到九百万，MySpace开始用微软的C#编写ASP.NET程序。在收到一定成效后，MySpace开始大规模迁移到ASP.NET。
账户达到一千万时，MySpace再次遭遇存储瓶颈问题。SAN的引入解决了早期一些性能问题，但站点目前的要求已经开始周期性超越SAN的I/O容量——即它从磁盘存储系统读写数据的极限速度。

第五代架构—增加数据缓存层并转到支持64位处理器的SQL Server 2005
2005年春天，MySpace账户达到一千七百万，MySpace又启用了新的策略以减轻存储系统压力，即增加数据缓存层——位于Web服务器和数据库服务器之间，其唯一职能是在内存中建立被频繁请求数据对象的副本，如此一来，不访问数据库也可以向Web应用供给数据。

2005年中期，服务账户数达到两千六百万时，MySpace因为我们对内存的渴求而切换到了还处于beta测试的支持64位处理器的SQL Server 2005。升级到SQL Server 2005和64位Windows Server 2003后，MySpace每台服务器配备了32G内存，后于2006年再次将配置标准提升到64G。

事实上，MySpace的Web服务器和数据库仍然经常发生超负荷，其用户频繁遭遇“意外错误”和“站点离线维护”等告示，他们不得不在论坛抱怨不停……
MySpace正是在这样不断重构站点软件、数据库和存储系统中，才一步步走到今天。事实上，MySpace已经成功解决了很多系统扩展性问题，其中存在相当的经验值得我们借鉴。MySpace系统架构到目前为止保持了相对稳定，但其技术人员仍然在为SQL Server支持的同时连接数等方面继续攻坚，尽可能把事情做到最好。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

上面的文章来源自互联网，不知道是否属实。但是这对于网站架构师来说是很难得的借鉴经验。从MySpace五代数据库构架发展历史中，可以发现自己平时的一些想法已经早就被应用过，所以有种相见恨晚的感觉，也可以增加点自信心，呵呵！

Tags - mysql , 架构 , 设计

在内网连接MySQL速度慢的原因

Mon, 27 Aug 2007 04:09:07 +0000

mysql 可以通过host =内网IP地址这种形式来访问，这点是很多人都知道的。我们公司在内网开发好的网站程序提交到服务器之后，总发现点击网页的链接之后，响应时快时慢，host=localhost的！经过重重排查最后确认是数据库响应时间有问题，可是让系统管理员上服务器直接使用mysql内部命令，查询速度却非常正常。后来写了一个简单的php链接mysql的程序测试，才发现问题的关键所在:服务器的dns解析没做好！解决办法是可以手动在host加入内网的IP对应别名或者域名
例如: 192.168.1.11 localhost, www.kakapo.cn

补充另外一个问题，host=ip地址，但是由于公司内部网络调整，连接到本地连接到另外一个MySQL数据库很慢，但是连接上后又操作正常。问题主要是每次mysql_connect的时候会出现。找到的一个解决办法是在my.cnf里修改配置，禁用DNS反向解析：
[mysqld]
--skip-name-resolve

补充--skip-name-resolve这个选项的作用：
mysql客户端每次访问db，mysql就会试图去解析来访问的机器的hostname，并缓存到hostname cache，如果这时解析不了，等一段时间会失败，数据才能被取过来。

Tags - mysql , dns , 内网

MySQL 用户管理常用命令

Thu, 24 May 2007 04:16:07 +0000

通过ROOT在客户端登陆之后，输入以下命令，如：

1。新增用户：
GRANT ALL PRIVILEGES ON shop.* TO 'shop'@'192.168.1.%' IDENTIFIED BY 'shoppwd';

这个命令是新增一个用户shop，密码为shoppwd，这个用户只能访问数据库shop，并且这用户机器IP只能在内网段192.168.1.*范围才能访问。

2.删除用户:

DROP User shop@'192.168.1.%';

以上两个是很常用的命令。
Tags - grant

一条复杂点的SQL语句

Mon, 16 Apr 2007 16:46:41 +0000

select a.title , a.path, a.publish_time,c.title as c_title
from category c,
(
select max(publish_time) as mx_publish_time, category_id from article group by category_id
) b, article a
where a.category_id=b.category_id
and a.category_id=c.category_id
and b.mx_publish_time=a.publish_time
order by a.publish_time limit 0,4

括号里面的group by 查询得到的结果作为一个表,目的是为了取得每个类别里面最早发布的文章;
整个查询语句是列出类表的所有分类, 同时取出每个分类的最新一篇发布文章,然后所有分类按照文章的发布时间倒排序,还要分页.
Tags - sql

MySQL客户端工具推荐

Mon, 26 Mar 2007 09:42:22 +0000

1.EMS SQL Manager 2005 Professional 功能很强大，对字符集编码也支持很好，就是软件大了一点。我一般和SQLyog结合使用。

2. SQLyog 是一个易于使用的、快速而简洁的图形化管理MYSQL数据库的工具，它能够在任何地点有效地管理你的数据库，而且它本身是完全免费的！！功能还是比较强大的，唯一缺点是对字符集编码支持不好。众所周知，MySQL4.1以上版本在字符集编码功能增强了不少。

功能：
1、快速备份和恢复数据；
2、以GRID / TEXT 格式显示结果；
3、支持客户端挑选、过滤数据；
4、批量执行很大的SQL脚本文件；
5、快速执行多重查询并能够返回每页超过1000条的记录集，而这种操作是直接生成在内存中的；
6、程序本身非常短小精悍！压缩后只有348 KB ；
7、完全使用MySQL C APIs程序接口；
8、以直观的表格界面建立或编辑数据表；
9、以直观的表格界面编辑数据；
10、进行索引管理；
11、创建或删除数据库；
12、操纵数据库的各种权限：库、表、字段；
13、编辑BLOB类型的字段，支持Bitmap/GIF/JPEG格式；
14、输出数据表结构/数据为SQL脚本；
15、支持输入/输出数据为CSV文件；
16、可以输出数据库清单为HTML文件；
17、为所有操作建立日志；
18、个人收藏管理操作语句；
19、支持语法加亮显示；
20、可以保存记录集为CSV、HTML、XML 格式的文件；
21、99% 的操作都可以通过快捷键完成；
22、支持对数据表的各种高级属性修改；
23、查看数据服务器的各种状态、参数等；
24、支持更改数据表类型为ISAM, MYISAM, MERGE, HEAP, InnoDB, BDB
25、刷新数据服务器、日志、权限、表格等；
26、诊断数据表：检查、压缩、修补、分析；

[转]MySQL 4.0升级到MySQL 4.1或更高版本中文乱码解决

Tue, 09 Jan 2007 03:19:47 +0000

原理
注意：本文档只对MySQL 4.1及以上的数据库版本有效，之前的MySQL版本，由于没有提供对字符集的完整支持，因此也不存在此类问题。

MySQL 4.1开始，对多语言的支持有了很大变化 (这导致了问题的出现)。尽管大部分的地方 (包括个人使用和主机提供商)，MySQL 3、4.0 仍然占主导地位；但 MySQL 4.1 是 MySQL 官方推荐的数据库，已经有主机提供商开始提供并将会越来越多；因为 latin1 在许多地方 (下边会详细描述具体是哪些地方) 作为默认的字符集，成功的蒙蔽了许多 PHP 程序的开发者和用户，掩盖了在中文等语言环境下会出现的问题。

MySQL 4.1 对于字符集的指定可以细化到一台机器上安装的 MySQL，其中的一个数据库，其中的一张表，其中的一栏，应该用什么字符集。但是，传统的 Web 程序在创建数据库和数据表时并没有使用那么复杂的配置，它们用的是默认的配置，那么，默认的配置从何而来呢？

编译 MySQL 时，指定了一个默认的字符集，这个字符集是 latin1；
安装 MySQL 时，可以在配置文件 (my.ini) 中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的；
启动 mysqld 时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的；
此时 character_set_server 被设定为这个默认的字符集；
当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为 character_set_server；
当选定了一个数据库时，character_set_database 被设定为这个数据库默认的字符集；
在这个数据库里创建一张表时，表默认的字符集被设定为 character_set_database，也就是这个数据库默认的字符集；
当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集；
这个字符集就是数据库中实际存储数据采用的字符集，mysqldump 出来的内容就是这个字符集下的；
想要进行“正确”的存储和得到“正确”的结果，最方便的是在所有query开始之前执行一下：

SET NAMES 'gbk';
其中gbk是数据库字符集。

常见问题解决方案
我的数据使用latin1或其他编码存储中文信息，但phpMyAdmin中中文为乱码
这问题是由于新版本的phpMyAdmin都是强制使用正确的字符集进行数据库连接和显示的，因此如果存储内码和实际内码不一致，phpMyAdmin是无法识别的。对于简体中文，phpMyAdmin可识别gbk/utf8；繁体中文，可识别big5/utf8。如果你确定想使用这种“不正确”的字符集（事实上通常在MySQL 4.1之前大家都是用“不正确”的字符集存储数据的）存储中文论坛数据，那么请使用phpMyAdmin 2.5.x的老版本，他会使用最老和最普通的方式连接数据库，这样便可以正常管理。

解决此类问题，有如下两种方案：

更改存储字符集
主要的思想就是把数据库的字符集有latin1改为gbk，big5,或者utf8; 以下操作必须拥有主机权限。假设当前操作的数据库名为：database

导出
首先需要把数据导为mysql4.0的格式，具体的命令如下： mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt databse > test.sql

--default-characte-set 以前数据库的字符集，这个一般情况下都是latin1的，
--set-charset 导出的数据的字符集，这个可以设置为gbk，utf8,或者big5
导入
首先使用下面语句新建一个GBK字符集的数据库（test）

CREATE DATABASE `test` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
然后把刚才导出的数据导入到当前的数据库中就ok了。

mysql -uroot -p --default-character-set=gbk -f test通过以上的导出和导入就把数据库的字符集改为正确的存储方式了。

总结：这种方案比较麻烦，但相对以后则一直都是使用MySQL“正确”的方式进行存储和数据连接，并且新版本phpMyAdmin不会乱码。

本文转载地址：http://www.upda.cn/?action_viewthread_tid_10918.html

Tags - mysql , 4.0 , 升级