個人經驗總結:Sybase數據庫性能優化

這篇論壇文章(賽迪網技術社區)用一個實例講解了Sybase數據庫性能優化的具體過程,具體內容請參考下文:

共享鎖

sp_getapplock 鎖定應用程序資源

sp_releaseapplock 爲應用程序資源解鎖

SET LOCK_TIMEOUT 1800 鎖超時期限設置

sp_configure 'deadlock checking period',5000 設置鎖檢測周期

sp_configure 'lock wait period',5000 設置鎖的等待時間

sp_setrowlockpromote 設置基本個表的最大行鎖升級數(鎖數)

sp_setrowlockpromote 'TABLE',TREECODE,500,500,100

sp_setrowlockpromote 'TABLE',LCD05,500,500,100

[Lock Manager]

number of locks = 50000 #鎖數

deadlock checking period = DEFAULT

freelock transfer block size = DEFAULT

max engine freelocks = DEFAULT

lock spinlock ratio = DEFAULT

lock hashtable size = DEFAULT

lock scheme = DEFAULT

lock wait period = DEFAULT

read committed with lock = DEFAULT

當很多事務同時訪問同一個數據庫時,會加劇鎖資源爭奪,嚴重時事務之間會發生死鎖。可用sp_object_stats查明死鎖位置。該過程報告資源爭奪最激烈的10張表、一個數據庫中資源爭奪的表和單個表的爭奪情況。語法爲sp_object_stats interval [, top_n [, dbname [, objname [, rpt_option ]]]],查看鎖爭奪情況只需設置interval爲“hh:mm:ss”。如果顯示每種鎖的爭奪程度超過15%,應該改變加鎖方式,比如表的全頁鎖改成數據頁鎖,數據頁鎖改成數據行鎖等。

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

allow remote access 1 0 1 1

print recovery information 0 0 0 0

recovery interval in minutes 5 0 5 5

tape retention in days 0 0 0 0

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

global async prefetch limit 10 0 10 10

global cache partition number 1 0 1 1

memory alignment boundary 2048 0 2048 2048

number of index trips 0 0 0 0

number of oam trips 0 0 0 0

procedure cache percent 20 22426 20 20

total data cache size 0 89698 0 89698

total memory 47104 196608 98304 98304

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

cis bulk insert batch size 0 0 0 0

cis connect timeout 0 0 0 0

cis cursor rows 50 0 50 50

cis packet size 512 0 512 512

cis rpc handling 0 0 0 0

enable cis 1 0 1 1

max cis remote connections 0 0 0 0

max cis remote servers 25 19 25 25

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

dtm detach timeout period 0 0 0 0

dtm lock timeout period 300 0 300 300

enable xact coordination 1 0 1 1

number of dtx participants 500 149 500 500

strict dtm enforcement 0 0 0 0

txn to pss ratio 16 3692 16 16

xact coordination interval 60 0 60 60

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

average cap size 200 0 200 200

caps per ccb 50 0 50 50

dump on conditions 0 0 0 0

maximum dump conditions 10 0 10 10

number of ccbs 0 0 0 0

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

allow sql server async i/o 1 0 1 1

disable disk mirroring 0 0 0 0

disk i/o structures 256 31 256 256

number of devices 10 #5 10 10

page utilization percent 95 0 95 95

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

event log computer name LocalSystem 0 LocalSystem LocalSystem

event logging 1 0 1 1

log audit logon failure 0 0 0 0

log audit logon success 0 0 0 0

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

esp execution priority 8 0 8 8

esp execution stacksize 77824 0 77824 77824

esp unload dll 0 0 0 0

start mail session 0 0 0 0

xp_cmdshell context 1 0 1 1

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

configuration file 0 0 0 /sybase/hgd

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

enable java 0 0 0 0

size of global fixed heap 300 0 300 300

size of process object heap 300 0 300 300

size of shared class heap 3072 0 3072 3072

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

default character set id 1 0 1 1

default language id 0 0 0 0

default sortorder id 50 0 50 50

disable character set conversi 0 0 0 0

enable unicode conversions 0 0 1 1

number of languages in cache 3 4 3 3

size of unilib cache 0 140 0 0

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

deadlock checking period 500 0 500 500

freelock transfer block size 30 0 30 30

lock address spinlock ratio 100 0 100 100

lock hashtable size 2048 48 2048 2048

lock scheme allpages 0 allpages allpages

lock spinlock ratio 85 0 85 85

lock table spinlock ratio 20 0 20 20

lock wait period 2147483647 0 2147483647 2147483647

max engine freelocks 10 0 10 10

number of locks 5000 2344 10000 10000

print deadlock information 0 0 1 1

read committed with lock 0 0 0 0

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

additional network memory 0 0 0 0

allow resource limits 0 0 0 0

audit queue size 100 42 100 100

average cap size 200 0 200 200

caps per ccb 50 0 50 50

deadlock pipe max messages 0 0 0 0

default network packet size 512 #505 512 512

disk i/o structures 256 31 256 256

enable rep agent threads 0 0 0 0

errorlog pipe max messages 0 0 0 0

event buffers per engine 100 #11 100 100

executable codesize + overhead 0 20261 0 20261

lock hashtable size 2048 48 2048 2048

lock spinlock ratio 85 0 85 85

max cis remote servers 25 19 25 25

max number network listeners 5 868 5 5

max online engines 1 216 1 1

max roles enabled per user 20 #22 20 20

memory per worker process 1024 0 1024 1024

number of alarms 40 3 40 40

number of aux scan descriptors 200 #258 200 200

number of ccbs 0 0 0 0

number of devices 10 #5 10 10

number of languages in cache 3 4 3 3

number of large i/o buffers 6 97 6 6

number of locks 5000 2344 10000 10000

number of mailboxes 30 1 30 30

number of messages 64 3 64 64

number of open databases 12 1239 12 12

number of open indexes 500 512 500 500

number of open objects 500 561 500 500

number of remote connections 20 86 50 50

number of remote logins 20 23 20 20

number of remote sites 10 1729 10 10

number of user connections 25 43141 250 250

number of worker processes 0 0 0 0

partition groups 1024 904 1024 1024

permission cache entries 15 #227 15 15

plan text pipe max messages 0 0 0 0

procedure cache percent 20 22426 20 20

process wait events 0 0 0 0

remote server pre-read packets 3 #83 3 3

size of global fixed heap 300 0 300 300

size of process object heap 300 0 300 300

size of shared class heap 3072 0 3072 3072

size of unilib cache 0 140 0 0

sql text pipe max messages 0 0 0 0

stack guard size 4096 #1108 4096 4096

stack size 86016 #23269 86016 86016

statement pipe max messages 0 0 0 0

total data cache size 0 89698 0 89698

total memory 47104 196608 98304 98304

txn to pss ratio 16 3692 16 16

wait event timing 0 0 0 0

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

number of open databases 12 1239 12 12

number of open indexes 500 512 500 500

number of open objects 500 561 500 500

open index hash spinlock ratio 100 0 100 100

open index spinlock ratio 100 0 100 100

open object spinlock ratio 100 0 100 100

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

Q diagnostics active 0 0 0 0

SQL batch capture 0 0 0 0

deadlock pipe active 0 0 0 0

deadlock pipe max messages 0 0 0 0

errorlog pipe active 0 0 0 0

errorlog pipe max messages 0 0 0 0

object lockwait timing 0 0 0 0

per object statistics active 0 0 0 0

plan text pipe active 0 0 0 0

plan text pipe max messages 0 0 0 0

process wait events 0 0 0 0

sql text pipe active 0 0 0 0

sql text pipe max messages 0 0 0 0

statement pipe active 0 0 0 0

statement pipe max messages 0 0 0 0

statement statistics active 0 0 0 0

wait event timing 0 0 0 0

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

additional network memory 0 0 0 0

allow remote access 1 0 1 1

allow sendmsg 0 0 0 0

default network packet size 512 #505 512 512

max network packet size 512 0 512 512

max number network listeners 5 868 5 5

number of remote connections 20 86 50 50

number of remote logins 20 23 20 20

number of remote sites 10 1729 10 10

remote server pre-read packets 3 #83 3 3

syb_sendmsg port number 0 0 0 0

tcp no delay 0 0 0 0

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

max async i/os per engine 2147483647 0 2147483647 2147483647

max async i/os per server 2147483647 0 2147483647 2147483647

o/s file descriptors 0 0 0 1024

tcp no delay 0 0 0 0

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

max parallel degree 1 0 1 1

max scan parallel degree 1 0 1 1

memory per worker process 1024 0 1024 1024

number of worker processes 0 0 0 0

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

additional network memory 0 0 0 0

lock shared memory 0 0 0 0

max SQL text monitored 0 7 0 0

shared memory starting address 0 0 0 0

total memory 47104 196608 98304 98304

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

max online engines 1 216 1 1

min online engines 1 0 1 1

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

enable rep agent threads 0 0 0 0

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

SQL Perfmon Integration 1 0 1 1

abstract plan cache 0 0 0 0

abstract plan dump 0 0 0 0

abstract plan load 0 0 0 0

abstract plan replace 0 0 0 0

allow backward scans 1 0 1 1

allow nested triggers 1 0 1 1

allow resource limits 0 0 0 0

allow updates to system tables 0 0 1 1

audit queue size 100 42 100 100

cpu accounting flush interval 200 0 200 200

cpu grace time 500 0 500 500

deadlock retries 5 0 5 5

default database size 2 0 2 2

default exp_row_size percent 5 0 5 5

default fill factor percent 0 0 0 0

enable DTM 0 0 0 0

enable HA 0 0 0 0

enable housekeeper GC 1 0 1 1

enable sort-merge join and JTC 0 0 0 0

event buffers per engine 100 #11 100 100

housekeeper free write percent 1 0 1 1

i/o accounting flush interval 1000 0 1000 1000

i/o polling process count 10 0 10 10

identity burning set factor 5000 0 5000 5000

identity grab size 1 0 1 1

license information 25 0 25 25

number of alarms 40 3 40 40

number of aux scan descriptors 200 #258 200 200

number of large i/o buffers 6 97 6 6

number of mailboxes 30 1 30 30

number of messages 64 3 64 64

number of open databases 12 1239 12 12

number of open indexes 500 512 500 500

number of open objects 500 561 500 500

number of pre-allocated extent 2 0 2 2

number of sort buffers 500 0 500 500

page lock promotion HWM 200 0 200 200

page lock promotion LWM 200 0 200 200

page lock promotion PCT 100 0 100 100

partition groups 1024 904 1024 1024

partition spinlock ratio 10 0 10 10

print deadlock information 0 0 1 1

row lock promotion HWM 200 0 200 200

row lock promotion LWM 200 0 200 200

row lock promotion PCT 100 0 100 100

runnable process search count 2000 0 2000 2000

size of auto identity column 10 0 10 10

sql server clock tick length 100000 0 100000 100000

text prefetch size 16 0 16 16

time slice 100 0 100 100

upgrade version 1100 0 12000 12000

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

allow procedure grouping 1 0 1 1

auditing 0 0 0 0

check password for digit 0 0 0 0

curread change w/ open cursors 1 0 1 1

current audit table 1 0 1 1

max roles enabled per user 20 #22 20 20

maximum failed logins 0 0 0 0

minimum password length 6 0 6 6

msg confidentiality reqd 0 0 0 0

msg integrity reqd 0 0 0 0

secure default login guest 0 guest guest

select on syscomments.text 1 0 1 1

suspend audit when device full 1 0 1 1

unified login required 0 0 0 0

use security services 0 0 0 0

Parameter Name Default Memory Used Config Value Run Value

-------------- ------- ----------- ------------ ---------

default network packet size 512 #505 512 512

number of pre-allocated extent 2 0 2 2

number of user connections 25 43141 250 250

permission cache entries 15 #227 15 15

stack guard size 4096 #1108 4096 4096

stack size 86016 #23269 86016 86016

systemwide password expiration 0 0 0 0

user log cache size 2048 0 2048 2048

user log cache spinlock ratio 20 0 20 20

個人經驗總結:Sybase數據庫的全量備份方案
Sybase數據庫産品自從1987年問世以來,它的客戶機/服務器(client/server)工作模式、分布處理的基礎體系結構、完善的安全保密性能、高速快捷的運行方式、多平台跨操作系統的廣泛應用等優點,迅速被IT界接受並推廣應用...查看完整版>>個人經驗總結:Sybase數據庫的全量備份方案
 
經驗總結:DB2數據庫功能及性能使用方法
◆1.安裝DB2後可以通過命令窗口這樣的命令行方式或控制中心這樣的圖形界面方式來操作,而如果你的數據庫服務端不在本機,則還需要在“客戶機配置輔助程序”中做一個客戶端連接的配置。 ◆2.控制中心中無法增刪改數據...查看完整版>>經驗總結:DB2數據庫功能及性能使用方法
 
個人網站建設與優化推廣經驗總結
  因爲工作原因,我開始了網絡營銷。剛開始是通過QQ的方式進行客戶的開發。後來發現在網上搜索一些關鍵字的時候,出現了很多行業性的個人網站,突然就像發現了新大陸一樣。于是開始了自己的網站建設與營銷推廣之路...查看完整版>>個人網站建設與優化推廣經驗總結
 
個人經驗總結:Oracle數據庫SCN號詳解
Oracle數據庫SCN號詳解: 系統檢查點scn(v$database(checkpoint_change#)) 數據文件檢查點(v$datafile(checkpoint_change#)) 數據文件終止scn(v$datafile(last_change#)) 數據文件中存放的檢查點 啓動scn (v$datafi...查看完整版>>個人經驗總結:Oracle數據庫SCN號詳解
 
Sybase數據庫的性能優化(-)
Sybase數據庫的性能優化(-) Sybase數據庫的性能優化(-) Sybase數據庫的性能優化(-) 在一個大型的數據庫中,性能成爲人們關注的焦點之一,如何讓數據庫高效有效的運行成爲廣大數據庫管理人員和開...查看完整版>>Sybase數據庫的性能優化(-)
 
Sybase數據庫的性能優化(-)
Sybase數據庫的性能優化(-) 在一個大型的數據庫中,性能成爲人們關注的焦點之一,如何讓數據庫高效有效的運行成爲廣大數據庫管理人員和開發人員必須要考慮的問題。何謂“好性能”性能是一個應用或多個應用在相...查看完整版>>Sybase數據庫的性能優化(-)
 
個人經驗總結:MySQL數據庫優化技巧集錦
一、我們可以且應該優化什麽? 硬件 操作系統/軟件庫 SQL服務器(設置和查詢) 應用編程接口(API) 應用程序 二、優化硬件 如果你需要龐大的數據庫表(>2G),你應該考慮使用64位的硬件結構,像Alpha、Sparc或即將推出...查看完整版>>個人經驗總結:MySQL數據庫優化技巧集錦
 
個人經驗總結:如何限制sa登錄Sybase ASE
需求:必須限制 sa 登錄sybase ASE 解決思路如下: 利用sybase ASE的特性提供的存儲過程 sp_modifylogin,對登錄的合法性進行驗證。 -- 先新創建一個權限足夠高的用戶,將來當服務器有問題時,好用來恢複sa -- 接下來...查看完整版>>個人經驗總結:如何限制sa登錄Sybase ASE
 
經驗總結個人網站優化的九大禁忌
  也談網站優化九大禁忌對網站進行優化,子南覺得還是有許多值得我們注意的地方,避開這些禁忌,我們的網站才能更快的提升權重,網站關鍵詞排名才能更快的上升,網站才能更健康的發展,那麽,子南來一  一介紹這...查看完整版>>經驗總結個人網站優化的九大禁忌
 
 
回到王朝網路移動版首頁