none
SQL 2005 散發主機複寫效能異常緩慢,請問如何排解? RRS feed

  • 一般討論

  • 各位先進:

    請教一個複寫的效能問題;我實作了發行-散發-訂閱者的架構,三台機器作業系統以及硬體組態相同,如下說明:

     

    • Windows 2003 R2 , Enterprise Edition, Sp2
    • 8 核心, 16GB RAM, H.D 890GB 以上
    • SQL 2005 sp3 

     

    以下分述各伺服器的工作:

    發行者(SQLPub):即時行情的Data Mart,包含台港滬深個股股價即時盤、台股期權即時盤。即時行情已設定為交易式發行集。

    散發者(SQLDistb):做為SQLPub的散發者。同時,也在股市開盤期間,同步Oracle 主機的電子交易資料。設定以每分鐘執行一次散發。

    訂閱者:(SQLSub):訂閱SQLDistb所散發的資料。

     

    今天開盤到9:30之前,交易資料一直很順暢的散發到SQLSub,但9:30之後,發現SQLDistb一直在執行以下訊息,資料並未同步到SQLSub:

     

    發行者到散發者歷程紀錄:

    記錄讀取器代理程式正在針對要複製的命令掃描交易記錄。在 # 3 個行程中已掃描大約 500000 筆記錄,其中 450221 標示為要進行複寫,經過時間 153 (毫秒)。

    記錄讀取器代理程式正在針對要複製的命令掃描交易記錄。在 # 1 個行程中已掃描大約 1500000 筆記錄,其中 1242070 標示為要進行複寫,經過時間 1578 (毫秒)。

    記錄讀取器代理程式正在針對要複製的命令掃描交易記錄。在 # 1 個行程中已掃描大約 1000000 筆記錄,其中 827079 標示為要進行複寫,經過時間 1125 (毫秒)。

    ....

     

    散發者到訂閱者歷程紀錄:

    發行集 'XXXX' 的並行快照集尚未完全產生,或者未執行記錄讀取器代理程式來啟動快照集,因此無法使用。如果並行快照集產生時發生中斷,則必須重新啟動發行集的快照集代理程式,直到產生完整的快照集為止。

     

    直到 12:41SQLDistb才把資料寫到SQLSub,期間三個小時都未同步資料。這情形第一次發生,平常並不會這樣。請問有什麼地方我該注意以及調效的。

    • 已編輯 Alex ChuoModerator 2010年7月5日 上午 01:52 移除所指定的字型大小,因為看去很傷眼
    • 已變更類型 Alex ChuoModerator 2010年7月15日 上午 01:57 資訊不足
    2010年6月28日 上午 07:43

所有回覆

  • Which replication do you use? What's network speed among those servers? How did you config disk on sql servers? Checked disk i/o and cpu time? How big the db is? ... All those can affect replication performance, hard to guess without details. 
    2010年6月28日 下午 01:46
  • Dear rmiao:

     

    Appreciate for your options. I details them below:

    1. As mentioned above, the replication is configured as Transaction type.

    2. All data files are located on the disks configured as RAID 5.

    3. The capability of  network is 10/100M among the servers.

    4. The overload of Write/sec on the disks where data files located is much higher than others. 

    5. The size of publication db is about 1.8GB.

     

    David

     

     

    2010年6月28日 下午 05:38
  • 2. better to separate data files from log files and put tempdb on its own array.

    3. 10m or 100m is 10 times difference, possible to go with 1g nic?

    4. possible to add more data files on other arrays?

    2010年6月29日 上午 12:41
  • Dear rmiao:

     

    It might be difficult to optimize the I/O speed by rearranging location of data files/log files  or adding others arrays to the current storage system, unless the servers could be reinstalled and configured or increasing the budget to buy the disks. And generally, network speed goes to 100M right now, but it's a great idea to go with 1G.

     

    It seems to be the hardware issues according to your reply, is there any idea about "soft" optimization by configuring the settings of SOL Server? The replication works fine right now as usual, I really wanna find out what's going wrong there.

     

    Could you direct me to check the settings those should be noticed?

    Thanks advance.

     

    zerome

     

    2010年6月29日 下午 04:14
  • Didn't see other 'soft' settings to improve performance in this case based on what you described.
    2010年6月30日 上午 12:33
  • Okay. Would you please tell me what else you need to identify the configuration settings?
    2010年6月30日 上午 01:10
  • Whatever you do will go back to basics: disk i/o and link speed.
    2010年6月30日 上午 01:51
  • Indeed. However, how do I know that is everything OK for the settings of replication besides the configurations of hardwares?

    Let's go back the basics,  a performance tuning could include the issues about software and hardware architecture. I found some unusual messages from the Replication Monitor, it led me to consider which part (software or hardware) should be checked first. As mentioned above, it is difficult to change hardware configuration,  so that consequently the tuning of replication settings is considered first. 

    I am a rookie of replication, and sure I know any setting about replication would finally influence the performance of I/O. But don't we have to check any setting of replication according to messages provided by Replication Monitor? That's saying, there is nothing wrong about replication settings in this case, the hardware configuration is the root problem, isn't it?

     

     

     

    2010年6月30日 上午 02:50
  • Messages you posted indicate distributor can't deliver transactions fast enough. How did you set replication? Real time or in batch? What's transaction rate in the db? Can only make suggestions based on what you tell.
    2010年6月30日 上午 03:31