Performance Optimization of Sybase Database (-)
In a large database, performance is one of the focus of people, how to make the database efficiently and efficient operation becomes a problem that the majority of database managers and developers must consider.
What is "good performance"
Performance is an application or multiple applications to measure efficiency at runtime in the same environment. Performance is often represented by response time and work efficiency. The response time refers to the time that the task is expected, and the response time can be reduced from the following three aspects:
· Reduce the number of competition and waiting, especially the disk read and write waiting
· Using faster parts
· Reduce the time required to use resources
Most performance gains from excellent database design, accurate query analysis and appropriate indexes. The best performance can be achieved by establishing excellent database design, learning to use the SQL Server query optimizer when developing.
In order to achieve better database performance, we need to optimize the database, reduce the competition of system resources, such as data Cache, process cache, system resources, and CPU competition.
In SQL Server, there is an optimization level:
· Application layer - most of the performance is from the optimization of query in your SQL application, which must be based on good database design.
• Database Layer - Application Shared resources in the database layer, including hard drives, transaction logs, and data cache.
• Server layer - There are many shared resources in the server layer, including data cache, process cache, lock, CPU, etc.
· Equipment layer - refers to the disk and its controller that stores data. At this layer, you should pay particular attention to the I / O of the disk.
• Network layer - refers to the network connected to the user and SQL Server.
· Hardware layer - refers to the available CPU.
· Operating system layer - ideally, SQL Server is the only primary application of a machine, which must be and other Sybase software such as Backup Server or SQL Server Monitor sharing processors, memory, and other resources.
In most cases, we optimize the application layer. Because the optimization of application performance is the most pleasing function, the result can be observed and inspected, and the performance of queries is a key to the entire performance of SQL applications.
The problem on the application layer includes the following:
· Decision support VS. and online transaction (OLTP) requires different performance strategies
· Transaction design can reduce concurrency, because long transactions keep the lock, reducing other users' access to relevant data
· Associated consistency requires JOIN operations
· Supporting the index of the SELECT operation adds time to modify the data
· The audit set up for security restrictions
Options in application layer optimization include:
· Remote processing or replication processing can separate decision support from the OLTP machine
· Use stored procedures to reduce compilation time and network utilization
· Use the least amount of lock to meet your application needs
Database layer issues include:
· Establish a backup and recovery program
· Distributed storage data on the device
· The audit operation affects performance; only audits you need
· Daily maintenance activities will result in reduced performance and cause users to operate database tables
Optimized in the database layer includes:
· Use the threshold of the transaction log to automatically dump the transaction log to prevent it from using space
· Use the threshold in the data segment to monitor the use of space
· Use partition to accelerate data loading
· Objectivity to avoid hard disk competition
· Put important tables and indexes into cache and ensure that you can get it at any time.
The problem with the server layer is:
· Application type - server is support OLTP or DSS, or both support
· The number of users supported affects the optimization decision - as the number of users increases, the competition for resources will change
· Network load
· Copy server or other distributed processing is an optimized option for a resolved method server layer when the number of users reaches a certain number of users.
· Optimize memory - a key configuration parameter and other parameters
· Decision is the client processing or server-side processing - can some processing can be done at the client?
· Configuring the size of Cache and the size of I / O
· Add multiple CPUs
· Place the batch task and generate reports for idle time
· Workload changes, reconfigure specific parameters
· Decide if it is possible to move DSS to another SQL server
The problem of equipment layer includes:
· Main device, device, user data device, or database log for the user database to be image
· How to distribute system databases, user databases, and database logs
· Does it necessary to partition for the high performance of the plug-in operation
Optimized options on the device layer include:
· Use multiple medium-sized devices and multiple controllers may have better I / O performance than with a small amount of large equipment
· Distributed databases, tables, and indexes for I / O load on different devices
Network layer
In fact, all users of SQL Server have access their data over the network. The main problems on the network layer are:
· Network traffic
· Network bottleneck
· The speed of the network
Optimized options on the network layer include:
· Configure the size of the package to match the needs of the application.
· Configure the subnet
· Separate busy network application
· Create a high-capacity network
· Configure multiple network engines
· Better design application, restrict the network transmission required
Hardware layer
Problems on the hardware layer include
· Efficiency of CPU
· Disk access: controller and disk
· Disk backup
· Memory
Optimized options on the hardware layer include:
· Add CPU to accommodate workload
· Configure the scheduler to improve CPU utilization
· Follow multiprocessor application design guidance to reduce competition
· Configure multiple data Cache operating system layers
The main problems of the operating system layer are:
· File system - whether it is used exclusively by SQL Server
· Memory management - Accurately estimates the memory occupation of operating systems and other programs
· CPU's utilization - How many processors are available throughout the system? How much is allocated to SQL Server
Options in operating system layer optimization include:
· Network Interface
· Select between files and original partitions
· Increase memory
· Move customer operation and batch to other machines
· SQL Server uses multiple CPUs
