MySQL配置优化

mysql配置优化

网 站访问量越来越大,MySQL自然成为瓶颈,因此最近我一直在研究 MySQL 的优化,第一步自然想到的是 MySQL 系统参数的优化,作为一个访问量很大的网站(日20万人次以上)的数据库系统,不可能指望 MySQL 默认的系统参数能够让 MySQL运行得非常顺畅。

通过在网络上查找资料和自己的尝试,我认为以下系统参数是比较关键的:

(1)、back_log:
要求 MySQL 能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。
back_log 值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值 对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。
当你观察你的主机进程列表,发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时,就要加大 back_log 的值了。默认数值是50,我把它改为500。

(2)、interactive_timeout:
服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。 默认数值是28800,我把它改为7200。

(3)、key_buffer_size:
索 引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),到你能 负担得起那样多。如果你使它太大,系统将开始换页并且真的变慢了。默认数值是8388600(8M),我的MySQL主机有2GB内存,所以我把它改为 402649088(400MB)。

(4)、max_connections:
允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加,否则,你将经常看到 Too many connections 错误。 默认数值是100,我把它改为1024 。

(5)、record_buffer:
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。默认数值是 131072(128K),我把它改为16773120 (16M)

(6)、sort_buffer:
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M),我把它改为 16777208 (16M)。
(7)、table_cache:
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符。默认数值是64,我把它改为512。

(8)、thread_cache_size:
可 以复用的保存在中的线程的数量。如果有,新的线程从缓存中取得,当断开连接的时候如果有空间,客户的线置在缓存中。如果有很多新的线程,为了提高性能可以 这个变量值。通过比较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。我把它设置为 80。

(10)、wait_timeout:
服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800,我把它改为7200。

注:参数的调整可以通过修改 /etc/my.cnf 文件并重启 MySQL 实现。这是一个比较谨慎的工作,上面的结果也仅仅是我的一些看法,你可以根据你自己主机的硬件情况(特别是内存大小)进一步修改。

Linux代理服务全攻略——Squid篇

  代理提供两个方面的服务:一是让不能直接访问Internet的用户访问Internet,因为允许一台权限较大的计算机直接连接网络更安全也更易于管理;二是让那些已经能够访问Internet的用户可以更快或更广泛地访问,因为代理服务器可以将访问较为频繁的网页缓存到本地,当有人再次访问同一页面时,代理可以直接发送本地页面而无需浪费网络带宽。当然也可以二者兼而有之。
  
  我们以应用最广泛的Squid为例,讨论 Linux下的代理服务器。这里所指的仅仅是服务器端应用,不包括客户端配置。Squid的优点是功能强大、配置简单、文档丰富;缺点是目前支持的协议尚不够广泛,对超大型应用略感吃力,不过这些不足都在慢慢完善之中。对于初学者和普通的应用而言,Squid仍是最佳选择。
  
  在此,我们要配置一个只对内部网络提供代理服务的Proxy Server。它将用户分为高级用户和普通用户两种,对高级用户采用网卡物理地址识别的方法,普通用户则需要输入用户名和口令才能正常使用。高级用户没有访问时间和文件类型的限制,而普通用户只在上班时可以访问以及一些其它的限制。
  
  安装
  可以从Squid 站点www.squid-cache.org获取该软件的源代码安装包,包括gz和bz2两种压缩方式。也可以使用Linux的发行版,如Red Hat提供的RPM包。
  
  RPM方式安装很简单,命令如下:
  
  $ rpm -ivh Squid-2.x.STALBx.i386.rpm
  
  不过笔者认为,即便是系统中已经默认安装了Squid,也应当先删掉然后安装最新的源代码包。因为开源软件会不断修正问题、提供更新的功能,使用最新版本可以保证最高的性能及安全,而且源代码方式可以完全定制系统。不过 STABLE稳定版、DEVEL版通常是提供给开发人员测试程序的,假定下载了最新的稳定版squid-2.5.STABLE2.tar.gz,用以下命令解开压缩包:
  
  $ tar xvfz squid-2.5.STABLE.tar.gz
  
  用bz2方式压缩的包可能体积更小,相应的命令是:
  
  $ tar xvfj squid-2.5.STABLE.tar.bz2
  
  然后,进入相应目录对源代码进行配置和编译,命令如下:
  
  $ cd squid-2.5.STABLE2
  
  配置命令configure有很多选项,如果不清楚可先用“-help”查看。通常情况下,用到的选项有以下几个:
  
   –prefix=/web/squid
  #指定Squid的安装位置,如果只指定这一选项,那么该目录下会有bin、sbin、man、 conf等目录,而主要的配置文件此时在conf子目录中。为便于管理,最好用参数–sysconfdir=/etc把这个文件位置配置为/etc。
  –enable-storeio=ufs,null
  #使用的文件系统通常是默认的ufs,不过如果想要做一个不缓存任何文件的代理服务器,就需要加上null文件系统。
  –enable-arp-acl
  #这样可以在规则设置中直接通过客户端的MAC地址进行管理,防止客户使用IP欺骗。
  –enable-err-languages=”Simplify_Chinese”
   –enable-default-err-languages=”Simplify_Chinese”
  #上面两个选项告诉Squid编入并使用简体中文错误信息。
  –enable-linux-netfilter
  #允许使用Linux的透明代理功能。
   –enable-underscore
  #允许解析的URL中出现下划线,因为默认情况下Squid会认为带下划线的URL是非法的,并拒绝访问该地址。
  
  整个配置编译过程如下:
  
  ./configure –prefix=/var/squid
  –sysconfdir=/etc
  –enable-arp-acl
  –enable-linux-netfilter
  –enable-pthreads
  –enable-err-language=”Simplify_Chinese”
   –enable-storeio=ufs,null
   –enable-default-err-language=”Simplify_Chinese”
   –enable-auth=”basic”
  –enable-baisc-auth-helpers=”NCSA”
   –enable-underscore
  
  其中一些选项有特殊作用,将在下面介绍它们。
  
  最后执行make 和make install两条命令,将源代码编译为可执行文件,并拷贝到指定位置。
  
  基本配置
  安装完成后,接下来要对Squid的运行进行配置(不是前面安装时的配置)。所有项目都在squid.conf中完成。Squid自带的squid.conf包括非常详尽的说明,相当于一篇用户手册,对配置有任何疑问都可以参照解决。
  
  在这个例子中,代理服务器同时也是网关,内部网络接口 eth0的IP地址为192.168.0.1,外部网络接口eth1的IP地址为202.103.x.x。下面是一个基本的代理所需要配置选项:
  
  http_port 192.168.0.1:3128
  
  默认端口是3128,当然也可以是任何其它端口,只要不与其它服务发生冲突即可。为了安全起见,在前面加上IP地址,Squid就不会监听外部的网络接口。
  
  下面的配置选项是服务器管理者的电子邮件,当错误发生时,该地址会显示在错误页面上,便于用户联系:
  
  cache_mgr netsnake@963.net
  
  以下这些参数告诉Squid缓存的文件系统、位置和缓存策略:
  
  cache_dir ufs /var/squid
  cache_mem 32MB
  cache_swap_low 90
  cache_swap_high 95
  
  在这里,Squid会将/var/squid目录作为保存缓存数据的目录,每次处理的缓存大小是32兆字节,当缓存空间使用达到95%时,新的内容将取代旧的而不直接添加到目录中,直到空间又下降到90%才停止这一活动。如果不想Squid缓存任何文件,如某些存储空间有限的专有系统,可以使用null 文件系统(这样不需要那些缓存策略):
  
  cache_dir null /tmp
  
  下面的几个关于缓存的策略配置中,较主要的是第一行,即用户的访问记录,可以通过分析它来了解所有用户访问的详尽地址:
  
   cache_access_log /var/squid/access.log
  cache_log /var/squid/cache.log
  cache_store_log /var/squid/store.log
  
  下面这行配置是在较新版本中出现的参数,告诉Squid在错误页面中显示的服务器名称:
  
  visible_hostname No1.proxy
  
  以下配置告诉Squid如何处理用户,对每个请求的IP地址作为单独地址处理:
  
   client_mask 255.255.255.255
  
  如果是普通代理服务器,以上的配置已经足够。但是很多Squid都被用来做透明代理。所谓透明代理,就是客户端不知道有代理服务器的存在,当然也不需要进行任何与代理有关的设置,从而大大方便了系统管理员。相关的选项有以下几个:
  
  httpd_accel_host virtual
  httpd_accel_port 80
   httpd_accel_with_proxy on
  httpd_accel_user_host_header on
  
  在Linux上,可以用iptables/ipchains直接将对Web端口80的请求直接转发到Squid端口3128,由Squid接手,而用户浏览器仍然认为它访问的是对方的80端口。例如以下这条命令:
  
  iptables -t nat -A PREROUTING -s 192.168.0.200/32 -p tcp –dport 80 -j REDIRECT 3128
  
  就是将 192.168.0.200的所有针对80端口的访问重定向到3128端口。
  
  所有设置完成后,关键且重要的任务是访问控制。 Squid支持的管理方式很多,使用起来也非常简单(这也是有人宁愿使用不做任何缓存的Squid,也不愿意单独使用iptables的原因)。 Squid可以通过IP地址、主机名、MAC地址、用户/密码认证等识别用户,也可以通过域名、域后缀、文件类型、IP地址、端口、URL匹配等控制用户的访问,还可以使用时间区间对用户进行管理,所以访问控制是Squid配置中的重点。Squid用ACL(Access Control List,访问控制列表)对访问类型进行划分,用http_access deny 或allow进行控制。根据需求首先定义两组用户advance和normal,还有代表所有未指明的用户组all及不允许上网的baduser,配置代码如下:
  
  acl advance 192.168.0.2-192.168.0.10/32
  acl normal src 192.168.0.11-192.168.0.200/32
  acl baduser src 192.168.0.100/32
  acl baddst dst www.somebadsite.com
  acl all src 0.0.0.0/0
  
  http_access deny baduser
  http_access allow advance
   http_access allow normal
  
  可以看出,ACL的基本格式如下:
  
  acl 列表名称 控制方式 控制目标
  
  比如acl all src 0.0.0.0/0,其名称是all,控制方式是src源IP地址,控制目标是0.0.0.0/0的IP地址,即所有未定义的用户。出于安全考虑,总是在最后禁止这个列表。
  
  下面这个列表代表高级用户,包括IP地址从192.168.0.2到192.168.0.10的所有计算机:
  
  acl advance 192.168.0.2-192.168.0.20/32
  
  下面这个baduser列表只包含一台计算机,其IP地址是192.168.0.100:
  
  acl baduser 192.168.0.100/32
  
  ACL写完后,接下来要对它们分别进行管理,代码如下:
  
  http_access deny baduser
  http_access allow advance
  http_access allow normal
  
  上面几行代码告诉Squid不允许baduser组访问Internet,但advance、normal组允许(此时还没有指定详细的权限)。由于Squid是按照顺序读取规则,会首先禁止baduser,然后允许normal。如果将两条规则顺序颠倒,由于baduser在normal范围中,Squid先允许了所有的normal,那么再禁止baduser就不会起作用。
  
  特别要注意的是,Squid将使用allow-deny- allow-deny……这样的顺序套用规则。例如,当一个用户访问代理服务器时,Squid会顺序测试Squid中定义的所有规则列表,当所有规则都不匹配时,Squid会使用与最后一条相反的规则。就像上面这个例子,假设有一个用户的IP地址是192.168.0.201,他试图通过这台代理服务器访问Internet,会发生什么情况呢?我们会发现,他能够正常访问,因为Squid找遍所有访问列表也没有和192.168.0.201有关的定义,便开始应用规则,而最后一条是deny,那么Squid默认的下一条处理规则是allow,所以192.168.0.201反而能够访问Internet 了,这显然不是我们希望的。所以在所有squid.conf中,最后一条规则永远是http_access deny all,而all就是前面定义的“src 0.0.0.0”。
  
  高级控制
  前面说过,Squid的控制功能非常强大,只要理解Squid的行为方式,基本上就能够满足所有的控制要求。下面就一步一步来了解Squid是如何进行控制管理的。
  
  通过IP地址来识别用户很不可靠,比IP地址更好的是网卡的MAC物理地址。要在Squid中使用MAC地址识别,必须在编译时加上 “–enable-arp-acl”选项,然后可以通过以下的语句来识别用户:
  
  acl advance arp 00:01:02:1f:2c:3e 00:01:02:3c:1a:8b …
  
  它直接使用用户的MAC地址,而MAC地址一般是不易修改的,即使有普通用户将自己的IP地址改为高级用户也无法通过,所以这种方式比IP地址可靠得多。
  
  假如不想让用户访问某个网站应该怎么做呢?可以分为两种情况:一种是不允许访问某个站点的某个主机,比如sinapage的主机是 sinapage4.sina.com.cn,而其它的新浪资源却是允许访问的,那么ACL可以这样写:
  
  acl  sinapage dstdomain sinapage4.sina.com.cn
  … …
  http_access deny sinapage
  … …
  
  由此可以看到,除了sinapage4,其它如 www.sina.com.cn、news.sina.com.cn都可以正常访问。
  
  另一种情况是整个网站都不许访问,那么只需要写出这个网站共有的域名即可,配置如下:
  
  acl qq dstdomain .tencent.com.cn
  
  注意tencent前面的“.”,正是它指出以此域名结尾的所有主机都不可访问,否则就只有tencent.com.cn这一台主机不能访问。
  
  如果想禁止对某个IP地址的访问,如202.108.0.182,可以用dst来控制,代码如下:
  
  acl badaddr dst 202.108.0.182
  
  当然,这个dst也可以是域名,由Squid查询DNS服务器将其转换为 IP。
  
  还有一种比较广泛的控制是文件类型。如果不希望普通用户通过代理服务器下载MP3、AVI等文件,完全可以对他们进行限制,代码如下:
  
  acl mmxfile urlpath_regex \.mp3$ \.avi$ \.exe$
   http_access deny mmxfile
  
  看到regex,很多读者应该心领神会,因为这条语句使用了标准的规则表达式(又叫正则表达式)。它将匹配所有以.mp3、.avi等结尾的URL请求,还可以用-i参数忽略大小写,例如以下代码:
  
  acl mmxfile urlpath_regex -i \.mp3$
  
  这样,无论是.mp3还是.MP3都会被拒绝。当然,-i参数适用于任何可能需要区分大小写的地方,如前面的域名控制。
  
  如果想让普通用户只在上班时间可以上网,而且是每周的工作日,用 Squid应当如何处理呢?看看下面的ACL定义:
  
  acl worktime time MTWHF 8:30-12:00 14:00-18:00
  http_access deny !worktime
  
  首先定义允许上网的时间是每周工作日(星期一至星期五)的上午和下午的固定时段,然后用http_access 定义所有不在这个时间段内的请求都是不允许的。
  
  或者为了保证高级用户的带宽,希望每个用户的并发连接不能太多,以免影响他人,也可以通过Squid控制,代码如下:
  
  acl conncount maxconn 3
  http_access deny conncount normal
   http_access allow normal
  
  这样,普通用户在某个固定时刻只能同时发起三个连接,从第四个开始,连接将被拒绝。
  
  总之,Squid的ACL配置非常灵活、强大,更多的控制方式可以参考squid.conf.default。
  
  认证
  用户/密码认证为Squid管理提供了更多便利,最常用的认证方式是NCSA。从Squid 2.5版本开始,NCSA认证包含在了basic中,而非以前单独的认证模块。下面来看看实现认证的具体操作。
  
  首先在编译时配置选项应包括以下配置:
  
  –enable-auth=”basic” –enable-basic-auth-helpers=”NCSA”
  
  “make install”以后,需要将“helpers/basic_auth/NCSA/ncsa_auth”拷贝到用户可执行目录中,如/usr/bin(如果在该目录中找不到这个执行文件,在编译时请使用make all而不是make,或者直接在该目录中执行make),然后需要借助Apache的密码管理程序htpasswd来生成用户名/密码对应的文件,就像下面这行代码:
  
  htpasswd -c /var/squid/etc/password guest
  
  在输入两遍guest用户的密码后,一个guest用户就生成了。如果以后需要添加用户,把上面的命令去掉-c参数再运行即可。
  
   Squid 2.5在认证处理上有了较大的改变,这里就只讨论2.5版本的处理方法,2.4及以下版本请参考squid.conf.default。在2.5版的 squid.conf中,包括以下几个相关选项:
  
  #该选项指出了认证方式(basic)、需要的程序(ncsa_auth)和
  对应的密码文件(password)
  auth_param basic program /usr/bin/ncsa_auth /var/squid/etc/password
  # 指定认证程序的进程数
  auth_param basic children 5
  # 浏览器显示输入用户/密码对话框时的领域内容
  auth_param basic realm My Proxy Caching Domain
  # 基本的认证有效时间
  auth_param basic credentialsttl 2 hours
  # 普通用户需要通过认证才能访问Internet
  acl normal proxy_auth REQUIRED
  http_access allow normal
  
  通过以上的配置即可完成认证工作。有的读者可能要问:认证只针对普通用户,而高级用户是直接上网的,该怎么处理呢?其实,这两种用户是可以共存的。如前所述,Squid是顺序处理http_access的,所以在 http_access处理过程中,如果先处理normal用户,那么当前用户无论是否属于高级用户,都会被要求进行认证;相反如果先处理高级用户,剩下的就只有需要认证的普通用户了。例如以下配置代码:
  …
  http_access allow normal (需要认证)
  http_access allow advance (不需要认证)
  …
  
  不管是否为noauth用户,都要求进行用户名/密码验证。正确的方法是将二者位置交换,代码如下:
  …
  http_access allow advance
  http_access allow normal
  …
  
  这时,高级用户不会受到任何影响。
  
  总结
  下面把整个squid.conf总结一下:
  
  # 服务器配置
  http_port 192.168.0.1:3128
  cache_mgr netsnake@963.net
  cache_dir null /tmp
  cache_access_log /var/squid/access.log
  cache_log /var/squid/cache.log
  cache_store_log /var/squid/store.log
   visible_hostname No1.proxy
  client_mask 255.255.255.255
   httpd_accel_host virtual
  httpd_accel_port 80
   httpd_accel_with_proxy on
  httpd_accel_user_host_header on
  
  # 用户分类
  acl advance arp 00:01:02:1f:2c:3e 00:01:02:3c:1a:8b …
  acl normal proxy_auth REQUIED
  acl all src 0.0.0.0
  
  # 行为分类
  acl mmxfile urlpath_regex \.mp3$ \.avi$ \.exe$
  acl conncount maxconn 3
  acl worktime time MTWHF 8:30-12:00 14:00-18:00
  acl sinapage dstdomain sinapage4.sina.com.cn
  acl qq dstdomain .tencent.com.cn
  
  # 处理
  http_access allow advance
   http_access deny conncount normal
  http_access deny !worktime
   http_access deny mmxfile
  http_access deny sinapage
  http_access deny qq
  http_access allow normal
  
  配置后的状况是,advance组可以不受任何限制地访问Internet,而normal组则只能在工作时间上网,而且不能下载多媒体文件,不能访问某些特定的站点,而且发送请求不能超过3个。
  
  通过本文的介绍,它可以了解Squid的基本能力。当然,它的能力远不止此,可以建立强大的代理服务器阵列,可以帮助本地的Web服务器提高性能,可以提高本地网络的安全性等。要想发挥它的功效,还需要进一步控制。