Insert into a table is slow

Problem(Abstract)

You are inserting data into a table but the insert speed is slower than expected.

Cause

These are the common causes, there could be others:

• Slow I/O ( Informix or disk )
• Too many indexes
• Indexes need to be reorganized
  

Here are the basic steps involved in an insert into a table with an index:

1. Find free space in the table for the row.
2. Insert row into table.
3. Read through index keys to find the slot for the new index key.
4. Insert new key into index. This leads to index page splits which 
   

Steps 3 and 4 are repeated for each index. This process is slowed when:

• indexes grow larger
• index has many extents
• duplicate values exist in the index

Diagnosing the problem

Slow I/O 

Slow I/O can typically be seen by examining onstat -g ioq output or by using the dd command on UNIX/Linux.

onstat -g ioq:

Look at the maxlen column of onstat -g ioq. Generically speaking, if you use aio and you see maxlen at 25 or greater that indicates you need more aio vps.
dd ( UNIX/Linux ):


Determine the potential speed of the disk. Copy a file that is at least 1 GB in size to the disk using dd. If the dd copy is significantly slower than what is expected from the disk specifications then that could easily cause inserts ( and any other operations ) to run slowly. Run man dd for the syntax. Here is an example:

dd if=/path/to/1GB/file.test of=/path/to/slow/disk/file.test


Too many indexes

Are all the indexes used? 
Could indexes be consolidated and queries rewritten in a way to use 1 index instead of 3?


Indexes need to be reorganized

If the index has many extents which are physically sitting all over on a disk there will be more time associated with I/O as the disk controller moves all over the disk. Use this query to return the number of extents a specific named index has:

SELECT a.dbsname,a.tabname,count(*) num_of_extents,sum(b.pe_size)
total_size 
FROM systabnames a,sysptnext b 
WHERE a.partnum = b.pe_partnum 
AND a.tabname[1,3]!='sys' 
AND a.tabname!='TBLSpace' 
AND a.dbsname[1,3]!='sys' 

and a.tabname ="index_name" 

GROUP BY 1, 2 
ORDER BY 3 desc,4 desc

More than 10 extents could indicate performance gains would be seen after a rebuild of an index.

Resolving the problem

Slow I/O

Add more aio vps ( see onmode -p, VPCLASS, NUMAIOVPS, AUTO_AIOVPS ) or troubleshoot slow disk I/O problems with the help of your system administrator.


Too many indexes

Drop and consolidate as many indexes as possible. Make sure you rewrite queries to take advantage of the new indexes. Here is an example:

Some of these indexes should be consolidated to reduce the number of indexes. Look at these 4 indexes on table slowio:

create index index1 on slowio (col4) using btree;
create index index2 on slowio (col1,col2,col4) using btree;
create index index3 on slowio (col1,col4) using btree;
create index index4 on slowio (col4,col2,col3) using btree;

Considerations for data types and uniqueness must be made. Assuming those were made the 4 indexes could be recreated as 1 index like this: 

create index consolidate_idx on slowio (col4, col1, col2, col3) using btree;

You would likely have to rewrite some existing queries changing the order in the where clause to align with this index so it would be used. Here are the scenarios that would allow a query to use this index: 

select ... where col4 ...;
select ... where col4 ... and/or col1 ...;
select ... where col4 ... and/or col1 ... and/or col2 ...;
select ... where col4 ... and/or col1 ... and/or col2 ... and/or col3 ...;

Not all of the columns in an index have to be queried to utilize an index but they do have to queried in the order of the index and you cannot skip a column to use a following column. Here are some scenarios 
where the index would not be used: 

select ... where col1 ...;
select ... where col3 ...;
select ... where col4 ... and/or col2 ...;
select ... where col4 ... and/or col3 ... and/or col2 ... and/or col1 ...;

Indexes need to be reorganized

Drop and recreate the index.

http://www-01.ibm.com/support/docview.wss?uid=swg21578714