Sybase Performance Optimization

Source: Internet
Author: User
Tags sybase database

Sybase command: remove the segment: sp_drow.gment "logsegment", pay2, "dev01"


1. SYBASE system parameter adjustment
1. Memory
/Sbin/sysctl-w kernel. shmmax = 3416386150

Shmmax is the maximum shared memory segment. If there are no other applications on the server and raw device is used, this parameter can be adjusted to 90% of the physical memory. If file system device is used, it can be adjusted to a smaller value, because file system buffer requires overhead memory! After modification, restart the system. Sp_configure "max memory", 1500000 restart takes effect (set to 75% of the shared memory)
Sp_configure "allocate max shared mem", 1 automatically allocates the maximum memory specified by max memory at startup
Sp_cacheconfig "default data cache", "1500 m" sets data cache (set to half of max memory)
Sp_cacheconfig "default data cache", "cache_partition = 2" is a multiple of the number of CPUs, which partitions the data buffer zone.
Sp_poolconfig "default data cache", "64 m", "16 k" sets 16 K data cache
Sp_poolconfig "default data cache", "128 m", "8 k" set 8 K data cache
Sp_configure "procedure cache size", 90000 Stored procedure data cache
Sp_cacheconfig 'tempdb _ cache', '200m', 'mixed' create a named cache
Sp_bindcache 'tempdb _ cache', and tempdb binds a temporary database
Tempdb_cache high-speed cache 2.cpu
Sp_configure "max online engines", 2 sets the number of CPUs used
Sp_configure "number of engines at startup", 2 number of CPUs used at startup 3. Network
Sp_configure "default network packet size", 2048 set the size of the network transfer package (effective after restart)
Sp_configure "max network packet size", 2048 4. Use other resources
Sp_configure "number of locks", 100000 number of locks used
Sp_configure "number of open indexes", 5000 enable Index
Sp_configure "number of open objects", 5000 open an object
Sp_configure "number of user connections", 1000 user connections
Sp_configure "number of device", 100 maximum number of new devices 2. sybase device adjustment data device and log device must be separated, add temporary database device 1. Data device
Sp_deviceattr devname, "dsync", true
2. Log Device
Sp_deviceattr devname, "dsync", false
3. Temporary database Device
Sp_deviceattr devname, "dsync", falsetempdb data and log separation:
USE master
Go disk init name = 'tempdbdev01', physname = '/opt/sybase/data/tempdbdev01.dat', size = '1g ', dsync = 'false'
Go disk init name = 'tempdblogdev01', physname = '/opt/sybase/data/tempdblogdev01.dat', size = '1g ', dsync = 'false'
Go alter database tempdb ON tempdbdev01 = '1gb' log on tempdblogdev01 = '1gb'
Go USE tempdb
Go deletes the master segments used on tempdb: EXEC sp_drow.gment 'logsegment ', 'tempdb', 'master'
Go EXEC sp_dropsegment 'system', 'tempdb', 'master'
Go EXEC sp_dropsegment 'default', 'tempdb', 'master'
Go if you have set up a device for tempdb, you only need to disable dsyncio, but you need to restart Adaptive Server: EXEC sp_deviceattr 'tempdbdev01', 'dsync', 'false'
Go EXEC sp_deviceattr 'tempdblogdev01', 'dsync', 'false'
It is best to set Direct IO for the Go database device. The performance is expected to satisfy you. 3. sybase Data Structure Adjustment. 1. database object tables and indexes ..
(1) create a reasonable index and regular analysis table for the table
Update statistics tabname (no table lock)
(2) Sort database space (Lock table, the remaining space must be 1.2 times of the maximum table)
Reorg rebuild tabname
Recreate clustered index
(3) recompile the stored procedure and trigger
Sp_recompile usertable (stored procedure and trigger associated with the table) iv. sybase database monitoring 1. Database Dead Process
Select * from master. syslogshold 5. sybase Database startup parameters-T3607 master
-T3608 other data
-M single ============================================== ======================================
SYBASE move log files to other devices
========================================================== ==============================
Moving the transaction log to another device
1> alter database dbname log on device19 = 101> sp_logdevice dbname, device19The last-chance threshold for database dbname is now 1232 pages .... SQL inserts, to fill old log segment... 1> dump tran dbname with truncate_only1> sp_helplog dbname2> goIn database 'dbname', the log starts on device 'device19 '. (return status = 0) 1> feimei published at: 2006.04.20: Category: (SYBASE): Read :( 63 times): Comment (0): Reference (0)
========================================================== ==============================
SYBASE sequence exception Jump solution
========================================================== ==============================
Identity burning set factor
The number of jumps from an abnormal power failure is: The length defined in the identity column in the negative 7 power * Table of identity burning set factor * 10, for example, identity burning set factor = 5000, the length defined in the identity column is numeric (10, 0), and the number is equal to: 5000*10 negative to the power of 7*1010 to the power of 10, this number is relatively large. In addition, you can set the jump value when creating a table. The syntax is create table (...) with identity_gap = value. modify the current table step EXEC sp_chgattribute 't_ 61e_tolltype ', 'Identity _ gap', 50 feimei published on: 2006.04.19: Classification: (SYBASE): Read :( 91 times ):: Comment (0): Reference (0)
========================================================== ==============================
How does SYBASE create a proxy table?
========================================================== ==============================
How to Create a proxy table
Add Remote Server entries to the interfaces File
Sp_addserver dblocal, local
Sp_addserver dbremote, null, dbremote
Sp_configure "enable cis", 1
Sp_addexternlogin dbremote, sa, sa, password
Create proxy table
Create existing table zz_proxy (id int not null, name varchar (10) not null)
At "sybase. master... zz"
Create proxy_table zz1
At "sybase. master .. zz "when a proxy table is created, the structure data of the remote table is stored locally and the table structure is not checked for change. Therefore, when the structure of the remote table is changed, I don't know about the local database. I need to re-create the proxy table, which is equivalent to refresh the local storage structure. feimei was published at: 2006.04.19: Category: (SYBASE): Read :( 108 times): Comment (0): Reference (0)
========================================================== ==============================
SYBASE Remote Backup
========================================================== ==============================
Add a remote backup server entry (IP address or physical name of the remote server) to interfaces)
Note that the name must be the same as that of the remote backup server !!! Dump database to... at bs_server_name

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.