Data reorganization makes mainframe database performance more powerful

Many examples of performance management have taught us that slow system performance is no less damaging than a single day, but it is relatively easy to resize a database on a mainframe, and it usually takes a little time to get a larger performance boost.

As time goes on, manufacturers have added many ways to perform database reorganization without user intervention, making the reorganization easier for users to ignore, until the database response is too slow to accept, administrators want to know why the new database response speed did not meet their expectations, this article will introduce the considerations of reorganization, In order to reduce the trouble that makes your head ache.

About Database optimization

Database reorganization repair Data storage secondary selection-a problem as inevitable as death and taxes, from the moment you started the database, the data began to accumulate, performance from the first optimal state gradually decline, how this happened? There are several ways to do this:

Database storage and some data types are proportional, and others are disproportionately growing:

• We add new data types and apply old (inappropriate) optimization techniques;

• We use the database in a different way, especially when using the new query pattern, which does not match the old data storage design;

• Other data stores competing for the same storage on the same disk cause the database to select suboptimal optimized storage.

Over time, these trends have reduced the performance of the application, and in an ever-updated online transaction processing (OLTP) database, one year is enough to cause 5% to 15% performance declines, and subsequent annual performance will gradually fall in a similar proportion, based on past experience, This does not have much to do with whether to run the database on a mainframe, other environments are similar to this, in fact, in a platform such as prime, the low version of the virtual storage access method (VSAM) means that it may take 10 minutes for each additional 100 users to start the office Automation suite, Because user data is fragmented on disk, unless you restructure them, users know where to reorganize their data.

Now we perform a mainframe database reorganization repair data storage suboptimal selection problem, reorganization is mainly to optimize its current state of storage, reorganization can change the index format, so that the index less access to the disk, faster loading data from the disk, reorganization can consolidate the data on the disk, so that the disk cylinder read/ The header does not go back and forth from one end of the disk's cylinder to the other, as is the case with Disk Defragmenter in Windows, in addition, the reorganization can change the cache size so that the database does not frequently perform unnecessary data exchange between disk and memory, but reorganization is only a point-in-time improvement, Time and utilization will eventually result in degraded database performance.

Since I was 30 years ago as a CCA MODEL 204 mainframe database administrator for the first time since the reorganization, it has been a huge improvement, now, most mainstream mainframe database manufacturers have automated online reorganization, which means that most of your mainframe database, depending on your version, and whether you need to enable reorganization, they all provide automated reorganization, cycle approximately 1 years, including the need for 24/7 available mainframe database reorganization support.

But in the implementation of the database reorganization also need to pay attention to some things, first of all, although many modern database vendors have provided automated reorganization function, but can not guarantee that your database just have this function, even if you can use automated reorganization, there is no manufacturer willing to provide special optimization for your system, and secondly, Your reorganization will not necessarily run frequently, and performance will gradually decline, and if you run the reorganization on the line frequently, the performance overhead is very high, and if the vendor does not remind you, it is very likely that the reorganization function is not enabled.

Thinking about the reorganization of database

It is best to schedule a yearly review of your database reorganization functions, including information management Systems (IMS) and VSAM, and make the following decisions:

First, make sure you are running the reorganization online or offline, offline reorganization may require database and application downtime for a long time, when you have enough downtime, or downtime does not cause business damage, offline reorganization is desirable, as well, online reorganization will also slow down other processes, when the database performance is at a peak when the implementation of online reorganization is not recommended.

Next, determine how often you perform a reorganization, and you should have a clear understanding of how many different types of indexes have changed, how many are updated, how many new and modified catalog metadata definitions, and carefully examine each factor. Then decide the overall trend of performance after each storage 1GB data, if the database changes faster, you should accelerate the reorganization frequency, if the change is slow, as far as possible to carry out the reorganization.

According to past experience, every 1% of growth has occurred, means a 1% drop, if the online reorganization, to determine the best day of the year, what time to execute, most mainframe database administrators know when the database is busiest, when the most leisurely, if you reorganized when the port encountered the peak of database transactions, You should stop the reorganization immediately and reschedule another time.

For example, suppose you add a foreign subsidiary in the UK, the new peak will arrive 5 hours ahead of the United States, as well as the year, for example, other countries ' year-end holidays and the United States are different, so if you serve a global company, you need to pay attention to countries ' year-end snapping time.

Finally, determine if you need to modify the spatial allocation of the database on disk. While this is not a reorganization decision, it has a lot to do with reorganization, especially since your database performance is falling because other data stores compete for the same space, and you can make a simple mapping of your disk's cylinders to determine- Especially when you're using a storage area network-showing how much other data is in your database, such as the gaps between IMS records, you should prevent this by redefining the physical disk allocations for your database.