Shulou(Shulou.com)06/01 Report--

In view of the example analysis of improving the performance of INSERT in DB2 database, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Overview of INSERT process

First, let's take a quick look at the processing steps when inserting a row. Each of these steps has the potential for optimization, which we will discuss later.

Prepare statements on the client. For dynamic SQL, this step is done before the statement is executed, and the performance here is important; for static SQL, the performance of this step does not really matter, because the preparation of the statement is done in advance. On the client, the column values of the row to be inserted are assembled and sent to the DB2 server. The DB2 server determines which page to insert this row into. DB2 reserves a place in the buffer pool used for the page. If DB2 selects an existing page, you need to read the disk; if you use a new page, you need to physically allocate space for that page in the tablespace (if it is SMS, that is, the tablespace of system-managed storage). Each page that inserts a new row is eventually written from the buffer pool to disk. Format the row in the target page and get an X (exclusive) row lock on the line. Writes a record that reflects the insert to the log buffer.

Finally, the transaction containing the insert is committed, and if the records in the log buffer have not been written to the log document, they are written to the log document. In addition, many types of additional processing can occur, depending on the database configuration, such as the existence of indexes or triggers. This additional processing is also significant for performance, which we will discuss later.

An alternative to INSERT

Before discussing the optimization of insert in detail, let's consider two alternatives to insert: load and import. The import utility is actually a front end to SQL INSERT, but some of its features are also useful to you. Load also has some useful additional features, but the main reason we use load instead of insert is to improve performance.

Load formats the data page directly, avoiding most of the overhead of processing each row due to insertion (for example, logging is actually eliminated here). Moreover, load can make better use of parallelism on multiprocessor machines. There are two new features in V8 load that are particularly useful for load as an alternative to insert: loading from cursors and loading from call-layer interface (CLI) applications.

Load from cursor

This method can be used for the application's program code (through db2Load API) or for DB2 scripts. Here is an example of the latter:

Declare staffcursor cursor forselect * from staff

Load from staffcursor of cursor insert into myschema.new_staff

These two lines can be replaced by the following line:

Insert into myschema.new_staff select * from staff

The equivalent INSERT... Loading from cursors can improve performance by almost 20% compared to SELECT statements.

Load from CLI

This approach is obviously limited to call-layer interface (CLI) applications, but it's fast. This technique is very similar to array insertion, and DB2 comes with an example that uses load twice as fast as fully optimized array inserts and almost 10 times faster than unoptimized array inserts.

Anything that INSERT can improve.

Let's take a look at some of the necessary steps for the insertion process, along with the techniques we can use to optimize them.

1. Statement preparation

As a SQL statement, the INSERT statement must be compiled by DB2 before execution. This step can occur automatically (for example, in CLP, or in a CLI SQLExecDirect call) or explicitly (for example, through a SQLPrepare, CLI SQLPrepare, or JDBC prepareStatement statement). This compilation process involves authorization checking, optimization, and other activities required to convert statements into an executable format. When the statement is compiled, the access plan for the statement is stored in the package cache.

If you execute the same INSERT statement repeatedly, the access plan for that statement (usually) goes into the package cache, which eliminates the overhead of compilation. However, if insert statements have different values for each row, each statement will be considered unique and must be compiled separately. Therefore, you will repeat statements like the following:

Insert into mytable values (1, 'abc')

Insert into mytable values (2, 'def')

Wait, wait,

Insert into mytable values (?)

Using parameter markers can make a series of insert run several times faster. (you can get similar benefits from using host variables in static SQL programs.)

two。 Send column values to the server

There are several optimization techniques that can be classified into this category. One of the most important techniques is to include multiple lines in each insert statement, which avoids client-server communication for each line and reduces DB2 overhead. The techniques that can be used for multi-row insertion are:

Contains multiple lines in the VALUES clause. For example, the following statement inserts three lines: INSERT INTO mytable VALUES (1, 'abc'), (2,' def'), and (3, 'ghi') use array insertion (array insert) in CLI. This requires preparing an INSERT statement with parameter markers, defining an array to store the values to insert, binding the array to the parameter markers, and performing an insert for a set of contents in each array. Moreover, the sample program sqllib/samples/cli/tbload.c provides the basic framework for array insertion (but executes CLI LOAD). Switching from an array to an array of 100 rows can shorten the time by about 2.5 times. So you should use an array of at least 100 rows as much as possible.

Use batch operations in JDBC. This is the same as array insertion in CLI, based on the same concept, but with different implementation details. When the insert statement is prepared through the prepareStatement method, the remaining steps are to call the appropriate setXXXX method (for example, setString or setInt) for each column, followed by addBatch. Repeat these steps for each row you want to insert, and then call executeBatch to perform the insert. For examples of this, see JDBC Tutorial in the Resources section.

Use load to quickly load data into an staging table, and then use INSERT. SELECT populates the main table. (the cost saved in this way comes from the high speed of load, coupled with INSERT. SELECT transmits data within the DB2 (on the server), eliminating the cost of communication. We don't normally use this method, except in INSERT. SELECT also needs to do additional processing that cannot be done by load.

If it is not possible to pass multiple lines in a single insert statement, it is best to group multiple insert statements and pass them together from the client to the server. (however, this means that each insert contains a different value and needs to be prepared, so its performance is actually worse than in the case of parameter markers.) Combining multiple statements into a single statement can be achieved through Compound SQL. In SQL, compound statements are created through BEGIN ATOMIC or BEGIN COMPOUND statements. In CLI, compound statements can be established through SQLExecDirect and SQLExecute calls. For DB2 V8 FixPak 4, another way to generate compound statements is to configure the statement attribute SQL_ATTR_CHAINING_BEGIN before making multiple SQLExecute calls (on a preprocessing statement) and configure the statement attribute SQL_ATTR_CHAINING_END after the call.

Here are some other suggestions on this topic:

If possible, have the client and the database you want to access use the same code page to avoid conversion costs on the server. The code page of the database can be determined by running "get db cfg for".

In some cases, CLI performs data type conversions automatically, but this can also cause invisible (small) performance losses. Therefore, try to keep the insert value directly in the format corresponding to the corresponding column.

Minimize the configuration overhead associated with insertion in the application. For example, when using array inserts in CLI, for the entire set of inserts, you should try to ensure that SQLBindParameter is performed only once for each column, not once for each array content. For individuals, the cost of these calls is not high, but these costs are cumulative.

3. Find the place to store the row

DB2 uses one of three algorithms to determine where to insert rows. (if multidimensional clustering (Multi-dimensional Clustering,MDC) is used, it is a different matter and we will not discuss it here.)

The default mode is that DB2 searches for free space control records (Free Space Control Records,FSCR) scattered across pages of the table to find pages with enough free space for new rows. Obviously, if there is less free space on each page, a lot of search time will be wasted. To cope with this, DB2 provides the DB2MAXFSCRSEARCH registry variable to allow the search to be limited to less than the default of five pages.

The second algorithm is used when the table is placed in APPEND mode through ALTER TABLE. This avoids FSCR search entirely, because you simply put the row directly at the end of the table.

The last algorithm is used when the table has a clustered index (clustering index). In this case, DB2 attempts to insert each row into a page with similar key values. If there is no space on that page, DB2 will try a nearby page, and if there is no space on that page, DB2 will do a FSCR search.

If you only consider the optimization of insertion time, then using APPEND mode is the fastest method for bulk insertion, but this method is not nearly as effective as many of the other methods we discuss here. The second best approach would be to use the default algorithm, but if in the best environment, the impact of changing the value of DB2MAXFSCRSEARCH is small, but in an environment with fewer Imax O constraints, the impact of such a change is significant.

If you have a clustered index, it will have a significant negative impact on the performance of insert, which is not surprising, because the purpose of using clustered indexes is to improve query (that is, select) performance by doing extra work at insert time. If you do need a clustered index, you can minimize its impact on inserts by ensuring that there is enough free space: increase PCTFREE using ALTER TABLE, and then reserve free space using REORG. However, if too much free space is allowed, it may cause additional pages to be read when querying, which greatly violates the original intention of using cluster indexes. Another option is to delete the clustered index before bulk insert, and then recreate the clustered index, which may be the best approach (the cost of creating a clustered index is similar to that of a regular index, and it is not very large. It's just extra overhead when inserting.

4. Buffer pools, Icano, and page cleanup

When each insert executes, it first stores the new row in a page, and eventually writes that page to disk. Once a page is specified as discussed earlier, the page must be in the buffer pool before rows can be added to the page. For bulk inserts, most pages are newly assigned to the table, so let's focus on the processing of new pages.

If the table is in the system managed storage (System Managed Storage,SMS) tablespace, when new pages are needed, space for each page is allocated separately from the document system by default. However, if you run the db2empfa command against the database, each SMS tablespace allocates an extent to the new page at once. We recommend running the db2empfa command and using a 32-page section.

For database-managed Database Managed Storage,DMS tablespaces, the space is pre-allocated when the tablespace is created, but the section of the page is assigned to the table during the insert process. DMS's pre-allocation of space can improve performance by about 20% compared to SMS-changing the segment size has no significant effect when using DMS.

If there is an index on the table, an entry is added to each index for each inserted row. This requires the existence of appropriate index pages in the buffer pool. We'll talk about index maintenance later, but for now just keep in mind that the buffer pool and Ibig O considerations at insert time apply similarly to index pages, as well as to data pages.

As the insert progresses, more and more pages will be populated with inserted rows, but DB2 does not need to write any newly inserted or updated data or indexes to disk after insert or Commit. (this is due to DB2's writeahead logging algorithm. There is one exception, which will be discussed in the section on logging.) However, these pages need to be written to disk at some point, which may not happen until the database is shut down.

In general, for bulk inserts, you will want to actively perform asynchronous page cleanup (asynchronous page cleaning) so that there is always free space in the buffer pool for new pages. The page clearance rate, or the total page missing rate, can lead to a big difference in timing, making performance more misleading. For example, if you use a 100000-page buffer pool and there is no page cleanup, no new or changed ("dirty") pages will be written to disk before the bulk insert ends, but subsequent operations (such as selection). Even shutting down the database) will be greatly delayed, because at this time up to 100000 dirty pages generated at the time of insertion will be written to disk. On the other hand, if active page cleanup is performed in the same situation, the bulk insert process may take longer, but since then there will be fewer dirty pages in the buffer pool, making subsequent tasks perform better. We can't always tell which of those results is better, but in general, it's impossible to store any dirty pages in the buffer pool, so for best performance, effective page cleanup is necessary.

To clean up the page as well as possible: reduce the value of the CHNGPGS_THRESH database configuration parameter from the default of 60 to as low as 5. This parameter determines the dirty pages in the buffer pool. Value percentage, when the dirty page reaches this percentage, the page cleanup will be started.

Try to enable the registry variable DB2_USE_ALTERNATE_PAGE_CLEANING (newly available in DB2 V8 FixPak 4). By configuring this variable to ON, you can provide a more active approach to page cleanup than the default method (based on CHNGPGS_THRESH and LSN gap triggers). I haven't evaluated its effect. See FixPak 4 Release Notes for this information. Ensure that the value of the NUM_IOCLEANERS database configuration parameter is at least equal to the number of physical storage devices in the database.

As for the Imap O itself, when indexing is needed, the activity can be minimized by using as large a buffer pool as possible. If there is no index, it is not helpful to use a larger buffer pool, but only to postpone Imax O. That is, he allows any new pages to be temporarily placed in the buffer pool, but eventually they still need to be written to disk.

When an Iripple O that writes pages to disk occurs, you can speed up the process through some regular Imax O tuning steps, such as:

Distribute tablespaces across multiple containers (these containers are mapped to different disks).

Use the fastest hardware and storage management configuration possible, including disk and channel speeds, write caching along with parallel writes, and so on.

Avoid RAID5 (unless used with an efficient storage device like Shark).

5. Lock

By default, there is an X lock on top of each inserted row, which starts when the row is created until the insert is committed. There are two performance issues related to insert and locks:

The CPU overhead incurred to acquire and release locks.

Concurrency problems that may be caused by lock conflicts.

For well-optimized batch inserts, the CPU overhead caused by acquiring an X lock on each line together with later releasing the lock is considerable. The only alternative to a lock on each new row is a table lock (there is no page lock in DB2). When using table locks, the time spent is reduced by 3%. There are three situations that can lead to the use of table locks. Before we discuss the disadvantages of table locks, let's take a moment to look at these three situations:

Run ALTER TABLE LOCKSIZE TABLE. This causes DB2 to use a table lock for any subsequent SQL statements that use the table until the locksize parameter is changed back to ROW.

Run LOCK TABLE IN EXCLUSIVE MODE. This will result in an X lock on the table immediately. Note that on the next commit (or rollback), the table will be released, so if you want to run a test that commits every N rows in the test, you need to repeat the LOCK TABLE after each commit.

Default locks are used, but the values of the LOCKLIST and MAXLOCKS database configuration parameters are small. When a small number of row locks are acquired, row locks are automatically escalated to table locks.

Of course, the disadvantage of any of these is the concurrency effect: if there is an X lock on the table, no other application can access the table unless it uses the isolation level UR (uncommitted reads). If you know that exclusive access will not cause problems, you should try to use table locks. However, even if you insist on using row locks, keep in mind that there may be thousands of new rows with X locks in the table during bulk insert, so it may conflict with other applications that use the table. There are a number of ways to minimize these conflicts:

Make sure that lock upgrades do not happen for no reason. You may need to increase the values of LOCKLIST and / or MAXLOCKS to allow the plug-in application to have enough locks. For other applications, use the isolation level UR.

For V8 FixPak 4, it may also be possible to reduce lock conflicts through the DB2_EVALUNCOMMITTED registry variable: if you configure this variable to YES, in many cases, you can only obtain locks on rows that match a predicate, not locks on any rows being checked.

Issue a COMMIT command to release the lock, so committing more frequently is sufficient to reduce the burden of lock conflicts.

Be careful

In V7, there are concurrency issues involving insert and keylocks, but in V8, these problems are virtually gone due to the provision of type-2 indexes. If you are migrating to V8, be sure to use the REORG INDEXES command with the CONVERT keyword to convert the index from type-1 to type-2.

In V7, W or NW locks may be used during insertion, but in V8 these two locks occur only if type-1 indexes are used or the isolation level is RR. Therefore, these two situations should be avoided as far as possible.

The lock owned by an insert (usually an X lock) is usually not affected by the isolation level. For example, using the isolation level UR does not prevent locks from being acquired from inserted rows. However, if INSERT is used. SELECT, the isolation level affects locks acquired from SELECT.

6. Log record

By default, each insert is recorded for recovery. Log records are first written to the in-memory log buffer pool, and then to the log document, usually when the log buffer pool is full or when a commit occurs. The optimization of bulk inserted logging is actually to minimize the number of log writes, along with making writes as fast as possible.

The first consideration here is the size of the log buffer pool, which is controlled by the database configuration parameter LOGBUFSZ. The default value for this parameter is 8 pages or 32 K, which is smaller than the ideal log buffer pool size required for most bulk inserts. For example, for a bulk insert, assuming that there are 200 bytes of log content for each line, the log buffer pool will be filled after 160 rows have been inserted. If you want to insert 1000 rows, there will be about 6 log writes because the log buffer pool will be filled several times, plus commits. If you increase the value of LOGBUFSZ to 64 pages (256K) or more, the buffer pool will not be filled, so there will be only one log write for the batch insert (at commit time). A performance improvement of about 13% can be achieved by using larger LOGBUFSZ. The downside of the larger log buffer pool is that emergency recovery may take a little longer.

Another possibility to reduce log writes is to use "ALTER TABLE ACTIVATE NOT LOGGED INITIALLY" (NLI) on the table into which the new row is inserted. If you do, no insert actions will be recorded in this unit of work, but there are two important issues related to NLI:

If a statement fails, the table will be marked as inaccessible and need to be deleted. This, along with other recovery issues (see SQL Reference's discussion of Create Table), makes NLI not a viable approach in many cases.

The submission at the end of the unit of work must wait until any dirty pages involved in this unit of work are written to disk. This means that this kind of submission takes a lot of time. If page cleanup is not actively done, the total time taken by Insert plus commit is longer in the case of NLI. When using NLI with positive page cleanup, you can greatly reduce time consumption. If you use NLI, keep an eye on the time it takes to submit.

As for increasing the speed of log writing, there are some possibilities:

Put the tables into which the log and the new row are to be inserted on separate disks.

Divide the logs to multiple disks at the operating system layer.

Consider using raw devices (raw device) for logging, but note that this is more difficult to manage.

Avoid using RAID 5 because it is not suitable for write-intensive (write-intensive) activities.

7. Submit

Commit forces log records to be written to disk to ensure that committed inserts must exist in the database and to release locks on new rows. These are all valuable activities, but because Commit always involves synchronous Imax O (for logging), while insert does not, the cost of Commit can easily be higher than that of insert. Therefore, the practice of committing each row once when doing bulk inserts is bad for performance, so make sure that autocommit is not used (which is the default for CLI and CLP). It is recommended that you commit about every 1000 rows: when committing every 1000 rows instead of one or two, performance can be improved by about 10 times. However, committing more than 1000 rows at a time saves a small amount of time, but in the event of a failure, it takes more time to recover.

A correction to the above approach: if the value of the MINCOMMIT database configuration parameter is greater than 1 (the default), then DB2 does not have to synchronize Imax O for each commit, but instead waits and attempts to share the log Imax O with a set of events. This is good for some environments, but it is often ineffective or even negative for bulk inserts, so if the key task to be performed is bulk inserts, you should keep the value of MINCOMMIT at 1.

Areas where improvements can be selectively made

For an insert, several types of processing will occur automatically. If your main goal is to reduce insertion time, the easiest way is to avoid the overhead of any of these processes, but in general, it may not be worth it. Let's discuss it in turn.

Index maintenance

For each row inserted, an entry must be added to each index on the table (including any primary key index). There are two main costs in this process:

CPU overhead caused by traversing each index tree and searching for a page at each level of the tree to determine where new entries must be stored (index entries are always stored in key order)

Read any searched pages into the buffer pool and eventually write each updated page to disk.

An even worse scenario is that there are a large number of random Icano during index maintenance. Suppose you want to insert 10000 rows, there are 5000 pages in the buffer pool of the index, and the key values of the rows to be inserted are randomly distributed across the entire key range. So, there are 10000 so many leaf pages (and perhaps more non-leaf pages) that need to enter the buffer pool in order to search and / or update them, and there is only a 10% chance that he will be in the buffer pool in advance for a given leaf page. For each insert, the probability of having to read the disk is so high that the performance of this scenario is often poor.

For row-by-row inserts, adding new rows to an existing index is much more expensive than creating a new index. If you are inserting into an empty table, you should always create an index after a column insert. (note that if load is used, the index should be created in advance.) If you are inserting into a populated table, it may be fastest to delete the index before the column is inserted and recreate the index after the column is inserted, but only if you want to insert a considerable number of rows-about 10-20% of the table. If you use a large buffer pool for index tablespaces and sort different insert as much as possible so that key values are ordered rather than random, you can help speed up index maintenance.

This is the answer to the sample analysis question about improving INSERT performance in DB2 database. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.