none
sql server 2005并发压力问题,请帮忙看看 RRS feed

  • 问题

  • 您好,我用一个数据库大约有11G,其中有约700张表(某些表做了分表处理),大部分表的记录不超过50万。日志用的是简单日志。

    目前batch requests在1500~3500之间,一般在2500的样子。我抓过十分钟的数据分析,平均每秒约500更新(460)及插入(40),200查询.

    数据库服务器配置为2 X 4核至强,16G内存,4X300SAS硬盘做raid10.

    运行的时候cpu并不高,不超过30%,内存从任务管理器看基本用完,15.9G。IO比较高,%read time平均178%,最大1529%。%write time平均3235%,最高6869%。

    数据库应用是web,PHP+sql server 2005 sp3,运行在windows 2003 sp2 (X64)上。因为freetds一般连接sql server会经常发生连接不上的问题,所以后来改用的待久连接。持久连接的并发用户为1600。

    不知道从上面的情况看是不是压力过大?应该怎么优化好?

    另外现在还出现一个现象,基本上是一两天一次,会出来batch requests非常平稳的现象,波动很小。而这个时候数据库速度变慢,PHP进程跑满,然后就是网站连接不上。重启sql server问题解决。不知道这个问题原因大致在哪?
    • 已编辑 三少爷 2009年12月18日 10:03
    2009年12月18日 9:58

答案

  • writelog是主要等待因素,说明是写日志的问题。日志文件是不是放在单独的磁盘上?
    观察一下disk writes/sec,如果和transaction/s差不多,说明事务并发不好。
    看看索引的统计,没什么用的索引就删除。这个配置每秒2000inserts没什么问题。

    在生产数据库中执行SELECT     TOP (100) QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id, ius.database_id))

                          + N'.' + QUOTENAME(OBJECT_NAME(i.object_id, ius.database_id)) AS ObjectName, i.name AS IndexName, ius.user_seeks, ius.user_scans, ius.user_lookups,

                          ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update

    FROM         sys.dm_db_index_usage_stats AS ius INNER JOIN

                          sys.indexes AS i ON ius.object_id = i.object_id AND ius.index_id = i.index_id

    WHERE     (ius.database_id = DB_ID())

    ORDER BY ius.user_seeks + ius.user_scans, ius.user_updates DESC

    想不想时已是想,不如不想都不想。
    2009年12月18日 18:11
    版主

全部回复

  • 怡红公子也在?是不是以前dev-club上的那个怡红公子。我是以前linux版版主。呵呵。
    2009年12月18日 10:00
  • dev-club现在萧条了啊。

    看来是服务器压力比较大。把SQL的最大内存改到14G,以免页面交换。

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure 'max server memory', 14000;

    GO

    RECONFIGURE;

    GO
    看看select * from sys.dm_os_wait_stats order by wait_time_ms desc的结果是什么。如果是Pageiolatch_XX的话,说明磁盘性能跟不上。
    查一下执行得比较频繁的语句,看logical_reads高不高,还有看看missing_index的统计,适当的建些索引。


    想不想时已是想,不如不想都不想。
    2009年12月18日 12:10
    版主
  • 呵呵,果然是您呀。是呀,dev-club确实不行了。


    select * from sys.dm_os_wait_stats order by wait_time_ms desc的结果:

    WRITELOG    41067659    606117468    3718    3003093
    LAZYWRITER_SLEEP    7337190    148572421    62984    330906
    BROKER_TASK_STOP    14917    74582671    10000    0
    SQLTRACE_BUFFER_FLUSH    18637    74544078    4031    62
    LCK_M_X    160155    73825406    3531    61218
    PAGEIOLATCH_EX    76450    22848265    3062    7078
    PAGEIOLATCH_SH    205168    15539625    4109    4734
    SLEEP_BPOOL_FLUSH    5128609    7067203    78    423359
    LOGBUFFER    10459    2141203    1468    3468
    SOS_SCHEDULER_YIELD    321107    1198140    218    1197609
    PAGEIOLATCH_UP    4581    881843    2015    125

    我们由于用了memcached等缓存,所以读还好,主要是写入太高,平均每秒约500更新(460)及插入(40)。missing_index也看了,基本上没什么大问题。

    基本上我们也确认是磁盘IO跟不上,准备分库了。上面我提到的会出来batch requests非常平稳的现象,然后突然变慢。重启数据库才好,不知道是不是也和磁盘有关系?我主要是想确认如果我磁盘IO能跟上的话,sql server2005是否能够支持更大(比如两倍于现在的)的并发压力?

    谢谢!!
    2009年12月18日 12:45
  • You may need check blocking other than disk io. Better to put data files, log files and tempdb on separate disks.
    2009年12月18日 15:01
  • writelog是主要等待因素,说明是写日志的问题。日志文件是不是放在单独的磁盘上?
    观察一下disk writes/sec,如果和transaction/s差不多,说明事务并发不好。
    看看索引的统计,没什么用的索引就删除。这个配置每秒2000inserts没什么问题。

    在生产数据库中执行SELECT     TOP (100) QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id, ius.database_id))

                          + N'.' + QUOTENAME(OBJECT_NAME(i.object_id, ius.database_id)) AS ObjectName, i.name AS IndexName, ius.user_seeks, ius.user_scans, ius.user_lookups,

                          ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update

    FROM         sys.dm_db_index_usage_stats AS ius INNER JOIN

                          sys.indexes AS i ON ius.object_id = i.object_id AND ius.index_id = i.index_id

    WHERE     (ius.database_id = DB_ID())

    ORDER BY ius.user_seeks + ius.user_scans, ius.user_updates DESC

    想不想时已是想,不如不想都不想。
    2009年12月18日 18:11
    版主
  • 另外,你这个主要是写入的话,考虑批量写入。
    想不想时已是想,不如不想都不想。
    2009年12月18日 18:12
    版主
  • 数据库11G.。。。内存16G,干嘛要分配那么多内存给SQL SERVER呢?
    分配8G RAM足够。。。留下的给WEB用。。。

    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com
    2009年12月19日 1:00
  • disk writes/sec
    disk transaction/s

    这两个数字基本差不多,平均700的样子,每一分钟左右会跳到1800的水平保持十秒的样子。

    disk writes byte/sec

    平均1948525,也是每一分钟的样子会跳高一次,最高16709464。

    日志文件不是放在单独的磁盘上,因为机器是租用的,目前并没有条件再放硬盘单独跑日志了。

    我看了下,数据文件和日志文件的写入分别为25%和75%。为什么使用简单日志后,还有这么大的写入量呢?

    多余的索引比较少,对整体性能没什么影响。都有试过。

    因为主要是update,又是web应用,批量写入比较难处理。

    我现在是一周做一次索引重建,重建后备份数据库,大小为9.5G,到第二天再备份,大小就变成了11.4G了。往后就基本是这么大了,增长比较小。

    有个现象有点怪,我拿那些sql在服务器上跑的时候,速度都是非常快的。基本上很少有超过0.1秒的。在出现batch requests非常平稳的问题的时候也是这样。这种情况怎么会造成PHP进程跑满呢?而且重启数据库就好了。
    2009年12月19日 2:20
  • blocking主要还是在disk io,详细的信息见下面的帖子。目前很难分到不同的硬盘上。
    2009年12月19日 2:22
  • 内存是不指望它再多出来用做其它的了,只要能保证我数据库正常,我就很高兴了。呵呵。

     只是我不知道对于sql server 2005,怎么评估一个数据库的内存使用呢?有类似mysql的内存使用的公式吗?
    2009年12月19日 2:24
  • recoverymode=simple不表示不写日志,只是日志自动截断。disk writes/sec和transaction/s差不多,说明你的事务并发度低,没有串行化。不过,你是不是看的disk transfers/sec?
    一分钟跳高一次的disk io是数据库的checkpoint。

    dmv里面有内存的详细使用情况,评估的话,你可以看performance monitor里面的sqlserver buffer manager:cache hit ratio,大于99%就说明内存足够用。但是一定要留出足够的空闲内存,新开连接之类的内存都不在sql的缓存管理里面的。

    如果sql和web在一起,一定要调小sql的最大内存,如果是单独的sql服务器,把最大内存调到14G就可以了。

    想不想时已是想,不如不想都不想。
    2009年12月19日 2:45
    版主
  • 我看的是disk transfers/sec,事务并发低是因为磁盘IO造成的吗?没有串行化是什么意思?如何解决呢?

    怎么确认数据库是不是在做checkpoint?sql server一般是多久做一次,可以配置的吗?

    2009年12月19日 14:35
  • Disk i/o will affect number of concurrent transactions. Sql does checkpoint every minute by default, but you can run it any time manually.
    2009年12月19日 16:56
  • checkpoint你可以不管它,事务并发低会造成写日志文件的次数增加,你的主要等待是writelog,不是pageiolatch。我说错了,不是没有串行化,是没有并行化。

    不过,每秒500更新,怎么disk writes/sec有700呢。你检查一下transaction/s,另外,你内存配置改没有?怀疑有内存的页交换。看看pages/sec
    想不想时已是想,不如不想都不想。
    2009年12月20日 6:00
    版主
  • checkpoint你可以不管它,事务并发低会造成写日志文件的次数增加,你的主要等待是writelog,不是pageiolatch。我说错了,不是没有串行化,是没有并行化。

    不过,每秒500更新,怎么disk writes/sec有700呢。你检查一下transaction/s,另外,你内存配置改没有?怀疑有内存的页交换。看看pages/sec
    想不想时已是想,不如不想都不想。
    是的,我的主要等待是writelog,不是pageiolatch。pages/sec是0,刚刚监控几分钟的数据看,disk writes/sec每秒560,sql server的transaction/s每秒490.disk writes/sec确实要比transaction/s大,这块我估计是windows系统本身及sql server好像内部也有跑一些进程造成的吧。
    • 已编辑 三少爷 2009年12月21日 1:12 增加
    2009年12月21日 1:11
  • Where did you put database log files? What kind of raid? Should be on their own raid10 disk.
    2009年12月21日 1:32
  • Where did you put database log files? What kind of raid? Should be on their own raid10 disk.
    datatbase log方向和data file放在同一个4X 300GSAS做的raid10上。目前给他一个单独的raid跑有困难。因为是机器自带的raid.盘位有限,只有两个空的硬盘盘位了。后面考虑再买两个硬盘做个raid1或raid0去跑日志,不知是否可行?
    2009年12月21日 2:07
  • Should add them asap to solve your disk bottleneck.
    2009年12月21日 2:41