Chinaunix
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
[打印本页]
作者:
learningunix
时间:
2003-06-25 11:31
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
多个20-30用户同时对同一张表做insert,需要怎么优化
请高手帮忙看一下,谢谢
作者:
chenfeng825
时间:
2003-06-25 11:40
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
原帖由 "learningunix" 发表：
多个20-30用户同时对同一张表做insert,需要怎么优化
请高手帮忙看一下,谢谢
建议表分区并建立cluster index，避免最后一页成为热点。
bind data cache
小事务，多提交！
你的应用是什么，同时真的有20~30用户insert?
作者:
learningunix
时间:
2003-06-25 11:43
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
这些我都试了,除了bind data cache,现在是客户的任务太多,压得没有办法,所以20-30人同时录入
作者:
learningunix
时间:
2003-06-25 11:45
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
建立clustered索引,会不会在提交的时候,要重新排序,影响速度呀
作者:
chenfeng825
时间:
2003-06-25 11:47
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
建立clustered于多个分区,插入时有多个最后一页.锁定方案是allpages or datarows?
作者:
learningunix
时间:
2003-06-25 11:49
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
锁定方案是datarows,隔离级别是0,5个分区,主要是提交的时候比较慢
现在大概有20万条记录,有一个nonclustered索引
作者:
chenfeng825
时间:
2003-06-25 11:53
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
那你能否看看os端disk io的状况.看看是否发生插入热点在最后一页。你的cache configure怎样？sp_sysmon "00:02:00"看看有关磁盘和缓存的信息。你的问题应该就是没有clustered。
作者:
learningunix
时间:
2003-06-25 12:13
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
谢谢,我在看看吧,如果clustered索引不影响速度的话,我就会用clustered索引
作者:
chenfeng825
时间:
2003-06-25 12:38
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
你的分区表是否建立在不同的磁盘上或不同的磁盘通道上?如果是同一个磁盘或者raid的统一通道上除了解决分配问题对性能不会有提升。我也很想看看你的问题如何解决
作者:
learningunix
时间:
2003-06-25 13:07
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
应该是建立在同一个磁盘上,要是建立在不同的磁盘上该怎么做
谢谢
作者:
jazy
时间:
2003-06-25 13:24
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
你可以执行一下sp_sysmon,将结果贴上来分析！
作者:
learningunix
时间:
2003-06-25 13:34
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
谢谢各位,下午三点半,我把结果贴上来,
作者:
learningunix
时间:
2003-06-25 15:40
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
===============================================================================
Sybase Adaptive Server Enterprise System Performance Report
===============================================================================
Server Version: Adaptive Server Enterprise/12.0/P/Sun_svr4/OS 5.6/1576/3
Server Name: syb_xx
Run Date: Jun 25, 2003
Statistics Cleared at: 15:30:35
Statistics Sampled at: 15:35:35
Sample Interval: 00:05:00
===============================================================================
Kernel Utilization
------------------
Engine Busy Utilization
Engine 0 1.2 %
Engine 1 3.5 %
----------- --------------- ----------------
Summary Total 4.7 % Average 2.4 %
CPU Yields by Engine per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total CPU Yields 0.0 0.0 0 n/a
Network Checks
Non-Blocking 8421.7 49539.5 2526516 97.5 %
Blocking 213.5 1255.7 64043 2.5 %
------------------------- ------------ ------------ ----------
Total Network I/O Checks 8635.2 50795.3 2590559
Avg Net I/Os per Check n/a n/a 0.00046 n/a
Disk I/O Checks
Total Disk I/O Checks 8637.1 50806.2 2591118 n/a
Checks Returning I/O 5117.3 30101.7 1535185 59.2 %
Avg Disk I/Os Returned n/a n/a 0.00028 n/a
===============================================================================
Worker Process Management
-------------------------
per sec per xact count % of total
------------ ------------ ---------- ----------
Worker Process Requests
Total Requests 0.0 0.0 0 n/a
Worker Process Usage
Total Used 0.0 0.0 0 n/a
Max Ever Used During Sample 0.0 0.0 0 n/a
Memory Requests for Worker Processes
Total Requests 0.0 0.0 0 n/a
===============================================================================
Parallel Query Management
-------------------------
Parallel Query Usage per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Parallel Queries 0.0 0.0 0 n/a
Merge Lock Requests per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total # of Requests 0.0 0.0 0 n/a
Sort Buffer Waits per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total # of Waits 0.0 0.0 0 n/a
===============================================================================
Task Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Connections Opened 0.0 0.0 0 n/a
Task Context Switches by Engine
Engine 0 7.2 42.5 2169 72.0 %
Engine 1 2.8 16.6 845 28.0 %
------------------------- ------------ ------------ ----------
Total Task Switches: 10.0 59.1 3014
Task Context Switches Due To:
Voluntary Yields 0.7 4.2 214 7.1 %
Cache Search Misses 0.1 0.6 32 1.1 %
System Disk Writes 0.2 1.0 49 1.6 %
I/O Pacing 0.4 2.1 108 3.6 %
Logical Lock Contention 0.0 0.0 0 0.0 %
Address Lock Contention 0.0 0.0 0 0.0 %
Latch Contention 0.0 0.0 0 0.0 %
Log Semaphore Contention 0.0 0.0 0 0.0 %
PLC Lock Contention 0.0 0.0 0 0.0 %
Group Commit Sleeps 0.0 0.1 6 0.2 %
Last Log Page Writes 0.3 1.8 92 3.1 %
Modify Conflicts 0.0 0.0 2 0.1 %
I/O Device Contention 0.0 0.0 0 0.0 %
Network Packet Received 1.9 11.0 563 18.7 %
Network Packet Sent 2.1 12.5 635 21.1 %
Other Causes 4.4 25.7 1313 43.6 %
===============================================================================
Application Management
----------------------
Application Statistics Summary (All Applications)
-------------------------------------------------
Priority Changes per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
To High Priority 0.0 0.0 0 0.0 %
To Medium Priority 0.7 4.2 215 50.0 %
To Low Priority 0.7 4.2 215 50.0 %
------------------------- ------------ ------------ ----------
Total Priority Changes 1.4 8.4 430
Allotted Slices Exhausted per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
High Priority 0.0 0.0 0 0.0 %
Medium Priority 0.0 0.1 5 100.0 %
Low Priority 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Slices Exhausted 0.0 0.1 5
Skipped Tasks By Engine per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Engine Skips 0.0 0.0 0 n/a
Engine Scope Changes 0.0 0.0 0 n/a
===============================================================================
ESP Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
ESP Requests 0.0 0.0 0 n/a
===============================================================================
Housekeeper Task Activity
-------------------------
per sec per xact count % of total
------------ ------------ ----------
Garbage Collections 0.3 2.0 100 n/a
Pages Processed in GC 0.0 0.0 0 n/a
Statistics Updates 0.3 2.0 100 n/a
===============================================================================
Monitor Access to Executing SQL
-------------------------------
per sec per xact count % of total
------------ ------------ ---------- ----------
Waits on Execution Plans 0.0 0.0 0 n/a
Number of SQL Text Overflows 0.0 0.0 0 n/a
Maximum SQL Text Requested n/a n/a 0 n/a
(since beginning of sample)
===============================================================================
Transaction Profile
-------------------
Transaction Summary per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Committed Xacts 0.2 n/a 51 n/a
Transaction Detail per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Inserts
APL Heap Table 0.2 1.1 57 64.8 %
APL Clustered Table 0.0 0.0 0 0.0 %
Data Only Lock Table 0.1 0.6 31 35.2 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Inserted 0.3 1.7 88 73.9 %
Updates
Total Rows Updated 0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total Rows Updated 0.0 0.0 0 0.0 %
Data Only Locked Updates
Total Rows Updated 0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total DOL Rows Updated 0.0 0.0 0 0.0 %
Deletes
APL Deferred 0.0 0.1 5 16.1 %
APL Direct 0.0 0.0 1 3.2 %
DOL 0.1 0.5 25 80.6 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Deleted 0.1 0.6 31 26.1 %
========================= ============ ============ ==========
Total Rows Affected 0.4 2.3 119
===============================================================================
Transaction Management
----------------------
ULC Flushes to Xact Log per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
by Full ULC 0.0 0.1 7 2.8 %
by End Transaction 0.3 1.5 78 31.5 %
by Change of Database 0.0 0.1 7 2.8 %
by System Log Record 0.1 0.6 33 13.3 %
by Other 0.4 2.4 123 49.6 %
------------------------- ------------ ------------ ----------
Total ULC Flushes 0.8 4.9 248
ULC Log Records 2.0 12.0 611 n/a
Max ULC Size During Sample n/a n/a 2704 n/a
ULC Semaphore Requests
Granted 5.6 33.1 1688 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total ULC Semaphore Req 5.6 33.1 1688
Log Semaphore Requests
Granted 0.6 3.2 165 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Log Semaphore Req 0.6 3.2 165
Transaction Log Writes 0.5 2.7 136 n/a
Transaction Log Alloc 0.1 0.7 37 n/a
Avg # Writes per Log Page n/a n/a 3.67568 n/a
===============================================================================
Index Management
----------------
Nonclustered Maintenance per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Ins/Upd Requiring Maint 0.0 0.0 0 n/a
# of NC Ndx Maint 0.0 0.0 0 n/a
Deletes Requiring Maint 0.0 0.0 1 n/a
# of NC Ndx Maint 0.0 0.0 1 n/a
Avg NC Ndx Maint / Op n/a n/a 1.00000 n/a
RID Upd from Clust Split 0.0 0.0 0 n/a
# of NC Ndx Maint 0.0 0.0 0 n/a
Upd/Del DOL Req Maint 0.1 0.5 25 n/a
# of DOL Ndx Maint 0.2 1.2 61 n/a
Avg DOL Ndx Maint / Op n/a n/a 2.44000 n/a
Page Splits 0.0 0.2 9 n/a
Retries 0.0 0.0 0 0.0 %
Deadlocks 0.0 0.0 0 0.0 %
Add Index Level 0.0 0.0 0 0.0 %
Page Shrinks 0.0 0.0 0 n/a
Index Scans per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Ascending Scans 7.4 43.4 2212 55.8 %
DOL Ascending Scans 5.8 34.0 1734 43.7 %
Descending Scans 0.0 0.3 13 0.3 %
DOL Descending Scans 0.0 0.1 5 0.1 %
------------ ------------ ----------
Total Scans 13.2 77.7 3964
===============================================================================
Metadata Cache Management
-------------------------
Metadata Cache Summary per sec per xact count % of total
------------------------ ------------ ------------ ---------- ----------
Open Object Usage
Active 1.7 9.8 498 n/a
Max Ever Used Since Boot 1.7 9.8 500 n/a
Free 0.0 0.0 2 n/a
Reuse Requests
Succeeded 11.1 65.5 3340 n/a
Failed 0.0 0.0 0 n/a
Open Index Usage
Active 1.7 9.8 500 n/a
Max Ever Used Since Boot 1.7 9.8 500 n/a
Free 0.0 0.0 0 n/a
Reuse Requests
Succeeded 4.5 26.5 1354 n/a
Failed 0.0 0.0 0 n/a
Open Database Usage
Active 0.0 0.2 8 n/a
Max Ever Used Since Boot 0.0 0.2 8 n/a
Free 0.0 0.1 4 n/a
Reuse Requests
Succeeded 0.0 0.0 0 n/a
Failed 0.0 0.0 0 n/a
Object Spinlock Contention n/a n/a n/a 0.0 %
Index Spinlock Contention n/a n/a n/a 0.0 %
Hash Spinlock Contention n/a n/a n/a 0.0 %
===============================================================================
Lock Management
---------------
Lock Summary per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Lock Requests 39.8 234.1 11941 n/a
Avg Lock Contention 0.0 0.0 0 0.0 %
Deadlock Percentage 0.0 0.0 0 0.0 %
Lock Hashtable Lookups 39.9 234.8 11977 n/a
Avg Hash Chain Length n/a n/a 0.00083 n/a
Lock Detail per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Exclusive Table
Granted 0.1 0.4 21 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Table Requests 0.1 0.4 21 0.2 %
Shared Table
Granted 0.0 0.0 1 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Table Requests 0.0 0.0 1 0.0 %
Exclusive Intent
Granted 0.6 3.3 169 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Intent Requests 0.6 3.3 169 1.4 %
Shared Intent
Granted 6.4 37.6 1920 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Intent Requests 6.4 37.6 1920 16.1 %
Exclusive Page
Granted 0.0 0.2 11 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Page Requests 0.0 0.2 11 0.1 %
Update Page
Granted 0.0 0.3 14 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total UP-Page Requests 0.0 0.3 14 0.1 %
Shared Page
Granted 9.3 54.5 2781 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Page Requests 9.3 54.5 2781 23.3 %
Exclusive Row
Granted 0.4 2.6 133 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Row Requests 0.4 2.6 133 1.1 %
Update Row
Granted 0.0 0.0 1 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total UP-Row Requests 0.0 0.0 1 0.0 %
Shared Row
Granted 15.5 91.1 4646 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Row Requests 15.5 91.1 4646 38.9 %
Next-Key
Total Next-Key Requests 0.0 0.0 0 n/a
Exclusive Address
Granted 0.2 1.4 69 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Address Requests 0.2 1.4 69 0.6 %
Shared Address
Granted 7.3 42.6 2175 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Address Requests 7.3 42.6 2175 18.2 %
Last Page Locks on Heaps
Granted 0.2 1.1 57 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total Last Pg Locks 0.2 1.1 57 100.0 %
Deadlocks by Lock Type per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Deadlocks 0.0 0.0 0 n/a
Deadlock Detection
Deadlock Searches 0.0 0.0 0 n/a
Lock Promotions
Total Lock Promotions 0.0 0.0 0 n/a
Lock Timeouts by Lock Type per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Timeouts 0.0 0.0 0 n/a
===============================================================================
Data Cache Management
---------------------
Cache Statistics Summary (All Caches)
-------------------------------------
per sec per xact count % of total
------------ ------------ ---------- ----------
Cache Search Summary
Total Cache Hits 308.8 1816.5 92640 99.9 %
Total Cache Misses 0.4 2.3 118 0.1 %
------------------------- ------------ ------------ ----------
Total Cache Searches 309.2 1818.8 92758
Cache Turnover
Buffers Grabbed 0.4 2.1 108 n/a
Buffers Grabbed Dirty 0.0 0.0 0 0.0 %
Cache Strategy Summary
Cached (LRU) Buffers 308.5 1814.8 92553 100.0 %
Discarded (MRU) Buffers 0.1 0.6 32 0.0 %
Large I/O Usage
0.0 0.0 0 n/a
Large I/O Effectiveness
Pages by Lrg I/O Cached 0.0 0.0 0 n/a
Asynchronous Prefetch Activity
APFs Issued 0.0 0.0 0 0.0 %
APFs Denied Due To
APF I/O Overloads 0.0 0.0 0 0.0 %
APF Limit Overloads 0.0 0.0 0 0.0 %
APF Reused Overloads 0.0 0.0 0 0.0 %
APF Buffers Found in Cache
With Spinlock Held 0.0 0.0 0 0.0 %
W/o Spinlock Held 106.4 625.8 31916 100.0 %
------------------------- ------------ ------------ ----------
Total APFs Requested 106.4 625.8 31916
Other Asynchronous Prefetch Statistics
APFs Used 0.0 0.0 0 n/a
APF Waits for I/O 0.0 0.0 0 n/a
APF Discards 0.0 0.0 0 n/a
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
-------------------------------------------------------------------------------
Cache: default data cache
per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Spinlock Contention n/a n/a n/a 0.0 %
Utilization n/a n/a n/a 100.0 %
Cache Searches
Cache Hits 308.8 1816.5 92640 99.9 %
Found in Wash 0.1 0.8 39 0.0 %
Cache Misses 0.4 2.3 118 0.1 %
------------------------- ------------ ------------ ----------
Total Cache Searches 309.2 1818.8 92758
Pool Turnover
2 Kb Pool
LRU Buffer Grab 0.4 2.1 108 100.0 %
Grabbed Dirty 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Turnover 0.4 2.1 108
Buffer Wash Behavior
Statistics Not Available - No Buffers Entered Wash Section Yet
Cache Strategy
Cached (LRU) Buffers 308.5 1814.8 92553 100.0 %
Discarded (MRU) Buffers 0.1 0.6 32 0.0 %
Large I/O Usage
Total Large I/O Requests 0.0 0.0 0 n/a
Large I/O Detail
No Large Pool(s) In This Cache
Dirty Read Behavior
Page Requests 0.0 0.0 0 n/a
===============================================================================
Procedure Cache Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Procedure Requests 0.0 0.0 0 n/a
===============================================================================
Memory Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Pages Allocated 0.1 0.4 19 n/a
Pages Released 0.1 0.4 19 n/a
===============================================================================
Recovery Management
-------------------
Checkpoints per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
# of Normal Checkpoints 0.0 0.1 7 100.0 %
# of Free Checkpoints 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Checkpoints 0.0 0.1 7
Avg Time per Normal Chkpt 0.42857 seconds
===============================================================================
Disk I/O Management
-------------------
Max Outstanding I/Os per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Server n/a n/a 16 n/a
Engine 0 n/a n/a 10 n/a
Engine 1 n/a n/a 16 n/a
I/Os Delayed by
Disk I/O Structures n/a n/a 0 n/a
Server Config Limit n/a n/a 0 n/a
Engine Config Limit n/a n/a 0 n/a
Operating System Limit n/a n/a 0 n/a
Total Requested Disk I/Os 1.5 8.6 437
Completed Disk I/O's
Engine 0 0.4 2.4 120 27.5 %
Engine 1 1.1 6.2 317 72.5 %
------------------------- ------------ ------------ ----------
Total Completed I/Os 1.5 8.6 437
Device Activity Detail
----------------------
Device:
/dev/md/rdsk/d2
sysprocsdev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
-----------------------------------------------------------------------------
Device:
/dev/md/rdsk/d3
Data1_dev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
-----------------------------------------------------------------------------
Device:
/dev/md/rdsk/d4
Log_dev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.0 0.0 0 0.0 %
Writes 0.4 2.5 127 100.0 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.4 2.5 127 29.1 %
Device Semaphore Granted 0.4 2.5 127 100.0 %
Device Semaphore Waited 0.0 0.0 0 0.0 %
-----------------------------------------------------------------------------
Device:
/dev/md/rdsk/d5
Data2_dev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.1 0.6 32 12.6 %
Writes 0.7 4.4 222 87.4 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.8 5.0 254 58.1 %
Device Semaphore Granted 0.8 5.0 254 100.0 %
Device Semaphore Waited 0.0 0.0 0 0.0 %
-----------------------------------------------------------------------------
Device:
/dev/md/rdsk/d6
Data3_dev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
-----------------------------------------------------------------------------
Device:
/home/sybase/Data/Dev/syb_xx_rs_RSSD_dev.dat
syb_xx_rs_RSSD_dev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
-----------------------------------------------------------------------------
Device:
/home/sybase/Data/Dev/syb_xx_rs_RSSD_log_dev.dat
syb_xx_rs_RSSD_log_dev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 n/a
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.0 0.0 0 0.0 %
-----------------------------------------------------------------------------
Device:
/home/sybase/Data/Dev/tempdb_dev.dat
tempdb_dev per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.0 0.0 0 0.0 %
Writes 0.1 0.5 25 100.0 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.1 0.5 25 5.7 %
Device Semaphore Granted 0.1 0.5 25 100.0 %
Device Semaphore Waited 0.0 0.0 0 0.0 %
-----------------------------------------------------------------------------
Device:
d_master
master per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Reads
APF 0.0 0.0 0 0.0 %
Non-APF 0.0 0.0 0 0.0 %
Writes 0.1 0.6 31 100.0 %
------------------------- ------------ ------------ ---------- ----------
Total I/Os 0.1 0.6 31 7.1 %
Device Semaphore Granted 0.1 0.6 31 100.0 %
Device Semaphore Waited 0.0 0.0 0 0.0 %
-----------------------------------------------------------------------------
===============================================================================
Network I/O Management
----------------------
Total Network I/O Requests 4.0 23.4 1195 n/a
Network I/Os Delayed 0.0 0.0 0 0.0 %
Total TDS Packets Received per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 1.5 8.8 449 79.8 %
Engine 1 0.4 2.2 114 20.2 %
------------------------- ------------ ------------ ----------
Total TDS Packets Rec'd 1.9 11.0 563
Total Bytes Received per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 138.8 816.2 41626 73.0 %
Engine 1 51.4 302.2 15413 27.0 %
------------------------- ------------ ------------ ----------
Total Bytes Rec'd 190.1 1118.4 57039
Avg Bytes Rec'd per Packet n/a n/a 101 n/a
-----------------------------------------------------------------------------
Total TDS Packets Sent per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 1.7 9.8 499 78.6 %
Engine 1 0.5 2.7 136 21.4 %
------------------------- ------------ ------------ ----------
Total TDS Packets Sent 2.1 12.5 635
Total Bytes Sent per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Engine 0 178.3 1048.7 53482 71.9 %
Engine 1 69.6 409.5 20886 28.1 %
------------------------- ------------ ------------ ----------
Total Bytes Sent 247.9 1458.2 74368
Avg Bytes Sent per Packet n/a n/a 117 n/a
=============================== End of Report =================================
(1 row affected)
(return status = 0)
作者:
learningunix
时间:
2003-06-25 15:49
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
请高手指点
作者:
learningunix
时间:
2003-06-25 16:08
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
我自己觉得,单从这个结果来看,似乎cpu,网络,缓存,都不是问题,不过提交还是会慢
作者:
alex2003
时间:
2003-06-25 18:00
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
给你支几个招：
1。从应用上找办法，很简单，多建几个结构一样的表，每个人的程序往一个表里插入。全部完成后再用一个语句合并所有数据。怎么样，这个办法好吧！（主要用于临时情况)
2。建立clustered index不能提高插入性能！！！相反会降低性能，因为再插入是需要排序。同样表分区是对堆表(non-clustered index)而言的，如果做了clustered index，表分区就没有用了！！！所以不能用clustered index.
推荐第一种办法。
作者:
Blackrose
时间:
2003-06-25 18:37
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
一般来说也就是
表分区 不建立聚簇索引
作者:
learningunix
时间:
2003-06-26 10:11
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
alex2003:这个方法不错,不过不便于管理,对于编码也不便维护
Blackrose:谢谢了
作者:
jazy
时间:
2003-06-26 20:24
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
你只监控了5分钟的时间，不知道这个时候是否是业务繁忙的时候，但从日志看来，除了删除性能不是很好
Deletes
APL Deferred 0.0 0.1 5 16.1 %
APL Direct 0.0 0.0 1 3.2 %
DOL 0.1 0.5 25 80.6 %
Inserts
APL Heap Table 0.2 1.1 57 64.8 %
APL Clustered Table 0.0 0.0 0 0.0 %
Data Only Lock Table 0.1 0.6 31 35.2 %
可见，你主要针对了DOL表操作，由于DOL表是逻辑删除，天长日久就会产生大量的岁碎片，浪费I/O，直接表现是系统变慢，从目前来看，可能和碎片有些关系，建议在业务闲的时候使用reorg进行整理一下。
作者:
learningunix
时间:
2003-06-27 16:45
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
谢谢jazy老兄,你帮小弟的忙可不少了,什么时候再来郑州
作者:
zhangyh123
时间:
2003-06-27 18:39
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
[quote]
原帖由 "learningunix"]我自己觉得,单从这个结果来看,似乎cpu,网络,缓存,都不是问题,不过提交还是会慢[/quote 发表：
首先你的采样时间不是很长 最好在15min 以上
另外你运行sysmonitor 的时间是下午，这个时候是不是 服务器最忙的时候？
如果插入慢，最好不要建立太多的索引 ，或者建立 non clustered index
而不要 clustered index
作者:
learningunix
时间:
2003-07-01 09:25
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
多谢各位,下午的时候业务也很忙,我用reorg rebuild试一下.
作者:
唐小羊
时间:
2004-03-31 02:52
标题:
请问:多个20-30用户同时对同一张表做insert,需要怎么优化
[quote]
原帖由 "learningunix"]多谢各位,下午的时候业务也很忙,我用reorg rebuild试一下.[/quote 发表：
试过了么,最后怎么样了?
欢迎光临 Chinaunix (http://bbs.chinaunix.net/)
Powered by Discuz! X3.2