论坛 › Sybase › Sybase 15.0.2 "number of locks"数量不断增大

jjdst 发表于 2011-04-26 22:56

Sybase 15.0.2 "number of locks"数量不断增大

请教各位，系统运行以来（操作系统AIX5.3），数据库锁的数量不断增大，已调整多次，目前已调整至20W，过一段时间通过sp_configmonitor “number of locks”观察还是在不断增大，不知道是什么原因引发的，因为系统不能停机，所以非常着急！先谢谢各位大虾了！！！！！！！

jjdst 发表于 2011-04-26 23:26

config文件信息如下：





        recovery interval in minutes = DEFAULT
        print recovery information = DEFAULT
        tape retention in days = DEFAULT
        max concurrently recovered db = DEFAULT
        number of checkpoint tasks = 2


        number of oam trips = DEFAULT
        number of index trips = DEFAULT
        memory alignment boundary = DEFAULT
        global async prefetch limit = DEFAULT
        global cache partition number = DEFAULT
        extended cache size = DEFAULT


        cache size = 2.000G
        cache status = default data cache
        cache replacement policy = relaxed LRU replacement
        local cache partition number = 1


        pool size = DEFAULT
        wash size = 61440 K
        local async prefetch limit = DEFAULT


        pool size = 128.0000m
        wash size = 61440 K
        local async prefetch limit = DEFAULT


        cache size = 200.000M
        cache status = mixed cache
        cache replacement policy = relaxed LRU replacement
        local cache partition number = 1


        pool size = DEFAULT
        wash size = 61440 K
        local async prefetch limit = DEFAULT


        pool size = 100.0000m
        wash size = 2048 K
        local async prefetch limit = DEFAULT


        cache size = 50.000M
        cache status = mixed cache
        cache replacement policy = relaxed LRU replacement
        local cache partition number = 1


        cache size = 50.000M
        cache status = mixed cache
        cache replacement policy = relaxed LRU replacement
        local cache partition number = 1


        cache size = 500.000M
        cache status = mixed cache
        cache replacement policy = relaxed LRU replacement
        local cache partition number = 1


        cache size = 300.000M
        cache status = mixed cache
        cache replacement policy = relaxed LRU replacement
        local cache partition number = 1


        pool size = DEFAULT
        wash size = 61440 K
        local async prefetch limit = DEFAULT


        pool size = 100.0000m
        wash size = 61440 K
        local async prefetch limit = DEFAULT


        number of open databases = DEFAULT
        number of open objects = 50000
        open object spinlock ratio = DEFAULT
        number of open indexes = 30000
        open index hash spinlock ratio = DEFAULT
        open index spinlock ratio = DEFAULT
        partition groups = DEFAULT
        partition spinlock ratio = DEFAULT
        number of open partitions = 2000


        disk i/o structures = DEFAULT
        number of large i/o buffers = DEFAULT
        page utilization percent = DEFAULT
        number of devices = 20
        disable disk mirroring = DEFAULT
        allow sql server async i/o = DEFAULT


        disable character set conversions = DEFAULT


        enable unicode normalization = DEFAULT
        enable surrogate processing = DEFAULT
        enable unicode conversions = DEFAULT
        size of unilib cache = DEFAULT


        default network packet size = DEFAULT
        max network packet size = DEFAULT
        remote server pre-read packets = DEFAULT
        number of remote connections = DEFAULT
        number of remote logins = DEFAULT
        number of remote sites = DEFAULT
        max number network listeners = DEFAULT
        tcp no delay = DEFAULT
        send doneinproc tokens = DEFAULT
        allow sendmsg = DEFAULT
        syb_sendmsg port number = DEFAULT
        allow remote access = DEFAULT


        max async i/os per engine = 4096
        max async i/os per server = DEFAULT


        optimization goal = allrows_oltp
        allow backward scans = DEFAULT
        abstract plan load = DEFAULT
        abstract plan dump = DEFAULT
        abstract plan replace = DEFAULT
        abstract plan cache = DEFAULT
        sampling percent = DEFAULT
        number of histogram steps = DEFAULT
        enable sort-merge join and JTC = DEFAULT
        number of worker processes = 2
        memory per worker process = DEFAULT
        max parallel degree = DEFAULT
        max scan parallel degree = DEFAULT
        max repartition degree = DEFAULT
        max resource granularity = DEFAULT
        enable metrics capture = 1
        optimization timeout limit = DEFAULT
        metrics lio max = DEFAULT
        metrics pio max = DEFAULT
        metrics elap max = DEFAULT
        metrics exec max = DEFAULT
        sproc optimize timeout limit = DEFAULT
        min pages for parallel scan = DEFAULT
        prod-consumer overlap factor = DEFAULT
        enable literal autoparam = DEFAULT
        max query parallel degree = DEFAULT
        cost of a logical io = DEFAULT
        cost of a physical io = DEFAULT
        cost of a cpu unit = DEFAULT
        auto query tuning = DEFAULT
        enable query tuning mem limit = DEFAULT
        query tuning plan executions = DEFAULT
        enable query tuning time limit = DEFAULT
        max buffers per lava operator = DEFAULT




        max memory = 3145728
        additional network memory = DEFAULT
        shared memory starting address = DEFAULT
        allocate max shared memory = 1
        dynamic allocation on demand = DEFAULT
        lock shared memory = DEFAULT
        heap memory per user = DEFAULT
        engine memory log size = DEFAULT
        compression memory size = DEFAULT


        max online engines = 8
        number of engines at startup = 8
        statement cache size = 102400


        procedure cache size = 307200
        default database size = DEFAULT
        identity burning set factor = DEFAULT
        allow nested triggers = DEFAULT
        allow updates to system tables = DEFAULT
        default fill factor percent = DEFAULT
        default exp_row_size percent = DEFAULT
        number of mailboxes = DEFAULT
        number of messages = DEFAULT
        number of alarms = DEFAULT
        number of pre-allocated extents = DEFAULT
        event buffers per engine = 2000
        cpu accounting flush interval = DEFAULT
        i/o accounting flush interval = DEFAULT
        sql server clock tick length = DEFAULT
        runnable process search count = 1500
        i/o polling process count = DEFAULT
        time slice = DEFAULT
        cpu grace time = DEFAULT
        number of sort buffers = DEFAULT
        size of auto identity column = DEFAULT
        identity grab size = DEFAULT
        housekeeper free write percent = 3
        enable housekeeper GC = DEFAULT
        sysstatistics flush interval = DEFAULT
        allow resource limits = DEFAULT
        number of aux scan descriptors = DEFAULT
        SQL Perfmon Integration = DEFAULT
        license information = 300
        text prefetch size = DEFAULT
        enable HA = DEFAULT
        i/o batch size = DEFAULT
        enable semantic partitioning = DEFAULT
        enable xml = DEFAULT
        enable webservices = DEFAULT
        enable job scheduler = DEFAULT
        job scheduler tasks = DEFAULT
        job scheduler interval = DEFAULT
        percent database for history = DEFAULT
        percent history free = DEFAULT
        percent database for output = DEFAULT
        percent output free = DEFAULT
        maximum job output = DEFAULT
        enable sql debugger = DEFAULT


        number of user connections = 250
        stack size = DEFAULT
        stack guard size = DEFAULT
        permission cache entries = DEFAULT
        user log cache size = 4096
        user log cache spinlock ratio = DEFAULT
        session tempdb log cache size = DEFAULT
        max native threads per engine = DEFAULT
        messaging memory = DEFAULT
        enable real time messaging = DEFAULT
        histogram tuning factor = DEFAULT
        rtm thread idle wait period = DEFAULT


        number of locks = 200000
        deadlock checking period = DEFAULT
        lock spinlock ratio = 100
        lock address spinlock ratio = 120
        lock table spinlock ratio = 40
        lock hashtable size = DEFAULT
        lock scheme = DEFAULT
        lock wait period = DEFAULT
        read committed with lock = DEFAULT
        print deadlock information = DEFAULT
        deadlock retries = DEFAULT
        page lock promotion HWM = DEFAULT
        page lock promotion LWM = DEFAULT
        page lock promotion PCT = DEFAULT
        row lock promotion HWM = DEFAULT
        row lock promotion LWM = DEFAULT
        row lock promotion PCT = DEFAULT


        systemwide password expiration = DEFAULT
        audit queue size = DEFAULT
        curread change w/ open cursors = DEFAULT
        allow procedure grouping = DEFAULT
        select on syscomments.text = DEFAULT
        auditing = DEFAULT
        current audit table = DEFAULT
        suspend audit when device full = DEFAULT
        enable row level access = DEFAULT
        check password for digit = DEFAULT
        minimum password length = DEFAULT
        maximum failed logins = DEFAULT
        enable ssl = DEFAULT
        unified login required = DEFAULT
        use security services = DEFAULT
        msg confidentiality reqd = DEFAULT
        msg integrity reqd = DEFAULT
        enable pam user auth = DEFAULT
        enable ldap user auth = DEFAULT
        enable encrypted columns = DEFAULT
        secure default login = DEFAULT
        enable logins during recovery = DEFAULT


        esp unload dll = DEFAULT
        esp execution priority = DEFAULT
        esp execution stacksize = DEFAULT
        xp_cmdshell context = DEFAULT
        start mail session = DEFAULT
        start xp server during boot = DEFAULT


        event logging = DEFAULT
        log audit logon success = DEFAULT
        log audit logon failure = DEFAULT
        event log computer name = DEFAULT


        enable rep agent threads = DEFAULT


        enable cis = DEFAULT
        cis connect timeout = DEFAULT
        cis bulk insert batch size = DEFAULT
        max cis remote connections = DEFAULT
        cis idle connection timeout = DEFAULT
        cis packet size = DEFAULT
        cis cursor rows = DEFAULT
        enable snmp = DEFAULT
        enable file access = DEFAULT
        cis bulk insert array size = DEFAULT
        enable full-text search = DEFAULT
        cis rpc handling = DEFAULT


        enable java = DEFAULT
        size of process object heap = DEFAULT
        size of shared class heap = DEFAULT
        size of global fixed heap = DEFAULT
        number of java sockets = DEFAULT


        enable DTM = DEFAULT
        enable xact coordination = DEFAULT
        xact coordination interval = DEFAULT
        number of dtx participants = DEFAULT
        strict dtm enforcement = DEFAULT
        txn to pss ratio = DEFAULT
        dtm lock timeout period = DEFAULT
        dtm detach timeout period = DEFAULT


        dump on conditions = DEFAULT
        maximum dump conditions = DEFAULT
        number of dump threads = DEFAULT
        number of ccbs = DEFAULT
        caps per ccb = DEFAULT
        average cap size = DEFAULT


        enable monitoring = 1
        sql text pipe active = 1
        sql text pipe max messages = 2000
        plan text pipe active = 1
        plan text pipe max messages = 1000
        statement pipe active = 1
        statement pipe max messages = 5000
        errorlog pipe active = 1
        errorlog pipe max messages = 1000
        deadlock pipe active = 1
        deadlock pipe max messages = 1000
        wait event timing = 1
        process wait events = 1
        object lockwait timing = 1
        SQL batch capture = 1
        statement statistics active = 1
        per object statistics active = 1
        max SQL text monitored = 45497
        performance monitoring option = DEFAULT
        enable stmt cache monitoring = DEFAULT
        identity reservation size = DEFAULT
        max online Q engines = DEFAULT
        number of Q engines at startup = DEFAULT
        net password encryption reqd = DEFAULT
        restricted decrypt permission = DEFAULT
        enable merge join = DEFAULT

jjdst 发表于 2011-04-27 10:58

急！各位大虾求救！

andkylee 发表于 2011-04-27 12:15

过一段时间通过sp_configmonitor “number of locks”观察还是在不断增大，
----------------------------------------------------------------------------------------
应该是sp_monitorconfig 吧！　
sp_monitorconfig "number of locks"显示的是锁的数量的利用率。
你可以看看目前的使用率是多少？

jjdst 发表于 2011-04-27 12:25

不好意思，命令写错了
每次去观察的时候锁的利用率不是很高，一般显示是几百个锁在用，过一段时间观察最大使用锁的数量时就增加很多，因为系统在异地，所以检测时间间隔一般是1周

chuxu 发表于 2011-04-27 13:19

锁是数据库自身的保护机制，有锁是很正常的事情，如果观察中 “利用率不是很高，一般显示是几百个锁在用”，偶发需要大量的锁可能是应用本身的问题了，其实简单的办法就是暂时不调整 number of locks，看社么应用无法获取锁而报错，在进行相应的修改。
另外建议参考一下 page lock promotion HWM、page lock promotion LWM 和 page lock promotion PCT 进行相应的修改，考虑锁升级的问题。

jjdst 发表于 2011-04-27 13:45

十分感谢

andkylee 发表于 2011-04-27 21:13

锁升级需要考虑page lock promotion HWM、page lock promotion LWM 和 page lock promotion PCT以及 row lock promotion HWM、row lock promotion LWM 和 row lock promotion PCT

你用sp_monitorconfig 监控参数 number of locks ，其中used表示最大使用率，如果这个值不大的话，先休息一下。呵呵。

alanzhu 发表于 2011-05-02 18:45

“锁”配到20W也不足为奇，在有复制（replication server）和很多“行级锁”的环境中经常配到更高的数目。
调整“锁升级”水平，或者把“行级锁”改为数据页锁（DOL）或全页锁（APL)都可以改善。
题外话，行级锁对Sybase并不是最好的，DOL/APL往往效率会更高。

alanzhu 发表于 2011-05-02 18:47

另外，建议尽快升级到ase1503，打更新一点的补丁，1502出现过好多问题。

查看完整版本: Sybase 15.0.2 "number of locks"数量不断增大