Defragmentation of Sybase database

For large DBMS systems like Sybase, the cornerstone of an OLTP (online transaction processing) application is that it needs to run uninterrupted 365 days a day, 24 hours a year. Because of the large number of inserts, updates, deletes and so on of the database on a daily basis, the storage fragmentation is generated on the physical storage media of the database, which affects the efficiency of the storage and the speed at which the database application runs. Can you defragment these fragments, just like the Windows operating system's defragmentation program, to optimize database storage and improve the speed of your database? The answer is yes. This article describes the types of fragmentation and defragmentation methods for Sybase databases.

Fragment type

Since Sybase manages data through OAM pages, allocation units, and extension pages, database server for OLTP applications can perform data deletion, insert, and update operations in a very frequent manner, with the following situations occurring for a long time:

1. Page fragments

That is, data that could have been stored on one page is distributed across multiple pages. If these pages are stored on different expansion units, the Database server will have access to multiple expansion units, thereby reducing system performance.

2. Expansion unit Fragmentation

In the heap table, an empty page appears when you delete a row in the middle of the data chain. As an empty page accumulates, the utilization of the expansion unit decreases, resulting in fragmentation of the expansion unit. A table with cluster index may also have extended cell fragmentation.

When an expansion unit fragment exists, the following problems occur:

When the table is processed, deadlocks often occur;

It is not possible to change the slower I/O speed with larger I/O operations or by increasing the size of the I/O buffer;

Contention for row operations.

3. Extended Unit Traversal

A table with cluster index causes the page to split because the record is inserted, but when the record is deleted, the page is freed to form data across several expansion units and allocation units, and access to the data must traverse several expansion units and allocation units. This will result in a significant increase in the time to access/query logging, while the database performance at the start is higher, but performance declines after a period of time.

In fact, the closer the data is to the storage space, the faster the database server can access it, and eliminating fragmentation helps improve system performance and make more efficient use of data storage space.

Fragmentation optimization methods

There are several ways to handle fragmentation, such as redefining the fill factor for a table, deleting and recreating indexes, rebuilding tables, and so on, based on the definition of a table.

The method presented in this article is to export the data of the user database in text form through the bcp utility, then completely empty and truncate the user database, then import the text data into the database, so as to eliminate the debris and have universality.

The following is an example of Sybase adaptive Server Enterprise 11.5 under the Sun Solaris 7 operating system to illustrate specific ways to organize database data.

1. Backing Up the database

To prevent unforeseen problems in the database defragmentation process, it is necessary to back up the database first.

2. Create a bcp out script and export data

Create a file that contains the following SQL statements:

Cre_bcp_out.sql

Select "bcp" + name + "out./" + name + "_out.txt-udboname-pdbopwd-ssys_name-c"

From sysobjects where type = ' U '

Order BY name

Go

Isql-udboname-pdbopwd-ssystemname < Cre_bcp_out. SQL > B_out

Edit output file, remove the first and last two lines of the file independent characters: VI b_out

Executes the script to export the data from the database to a text file: Sh b_out

3. Create the TRUNCATE TABLE script and truncate the database

Create a file that contains the following SQL statements:

Cre_trunc_out.sql

Select "TRUNCATE TABLE" + name from sysobjects where type = ' U '

Order BY name

Go

Isql-udboname-pdbopwd-ssystemname < Cre_ trunc_out. SQL > Trunc_out. Sql

Edit the output file, remove the first and last two lines of the file, and add the go to the last line to form the complete SQL statement: VI trunc_out

Clear the data for the database by executing the following statement:

Isql-udboname-pdbopwd < Trunc_out. Sql

4. Create a bcp in script and import data

Create a file that contains the following SQL statements:

cre_bcp_in. sql

Select "bcp" + name + "in./" + name + "_out.txt-udboname-pdbopwd-ssys_name-c" from sysobjects where type = ' U '

Order BY name

Go

Isql-udboname-pdbopwd-ssystemname < cre_ bcp_in. sql > B_in

Edit output file, remove the first and last two lines of the file independent characters: VI b_in

Import data from text: SH b_in

5. Update Database status

Sybase does not automatically maintain index statistics, and when the database is truncated with the TRUNCATE TABLE, the index does not change, so you must use the update statistics to ensure that the statistics for the index correspond to the statistics for the current table data.

Create a file that contains the following SQL statements:

Cre_upd_st. sql

Select ' UPDATE STATISTICS ' + name from sysobjects where type = ' U ' ORDER by name

Go

Isql-udboname-pdbopasswd-ssystemname < Cre_upd_st. sql > Upd_st. sql

Edit the output file, remove the first and last two lines of the file, and add the go to the last line to form the complete SQL statement:

VI upd_st. SQL

Update Database Status:

Isql-udboname-pdbopasswd-ssystemname < Upd_st. sql

At this point, basically completed the database user table defragmentation work.

Summary

In the finishing process, there are the following two points to note:

1. The size of tempdb

When Sybase executes the bcp in script, it consumes the tempdb space twice times the imported data, so carefully estimate the maximum size of the table before executing and ensure that there is enough tempdb space. When there is not enough space, consider reducing the size of the table by dividing the table or deleting stale data, or consider increasing the size of tempdb.

2. Settings for database configuration options

When a database executes a bcp in script that produces a large number of logs, the option "Truncate Log on chkpt" for the DB should be set to "true" to ensure that the bcp in process is not interrupted by a log overflow.

Although the Sybase database is self optimized, database fragmentation occurs as long as the database is dynamic. In the case of OLTP applications, as the data increases, the system becomes more and more slow, and often deadlocks, you should check the fragmentation of the database, and use the above methods to optimize.

In fact, database defragmentation should be done on a regular basis to ensure that the physical storage of the database is always in the optimal state, which is a better way to keep the database performance low cost than to increase the hardware.