Hi,

The size of a few big tables are as follows:
Table No of Records Row Length
1) tfgld106 20 million 287
2) tfgld410 29 million 355
3) tdinv700 13 million 99


We are restoring production server data on our backup server weekly. The time taken to restore tdinv700 is 3 times more than other tables which are much bigger.

How can I check what could be causing the restoration of tdinv700 so slow.

I know it is advisable to archieve the data, but still there is issue in performance of the table tdinv700. How to identify what is the problem and what is the solution.

Hello Vineet,

Please let us know , by what means you are restoring the data back in backup server .
i.e DB cold backup , export-import etc...

Regards,
Sushil Kumar Mudaliar

Hi Sushil,

We use bdbpost command of Baan.

The speed of restoring data doesn't depends only on size of the table but even on numbers of indexes and referenced tables.
There is an option on bdbpost (sorry I don't remember which) that states that index rebuilding of the table must be done after the loading of tha table. Maybe this option could improve the performances...

running parallel bdpost sessions can help, also changing your database driver settings. I don't know if there are any specific ones for informix, there are several for oracle that can speed the process.

The option "create rows before index" does not work for large tables, hence we don't use it.

In our case the table def is standard. i.e nothing has been customized. Hence, if it is slow because of references to other tables then the same would be the case for everybody ?

One more thing, - when we restore the data we ignore the domain constraints and referential integrity.

and tfgld106 has 8 indexes and more records as compared to tdinv700 which has 3 indexes.

:confused:

there is a solution on baan support site for improving the speed of the import

Hints to speed table and sequential dump creation in Informix solution 3107

Hi Darren,

Yes the inf is useful as it will help in improving restoration time.

I would still like to understand why only tdinv700 is much much slower than other tables. What about tdinv700 is causing this ?

;)

Hi Guys,

The mystery remains unsolved. Table tdinv700 takes hell lot of time as compared to any other tables. There are tables which are 10 times the size of tdinv700 which gets restored much much faster.


I need to know why only tdinv700 is so slow. How can I find out ? :confused:

If u c other tables, all indices would be unique (by additing first key values as last elements of other indices) . However in tdinv700 i think there is a duplicate index . Also have u added any other index to this table.

Hi Hitesh,

Yes there is one duplicate index, but how does that affect the performance of restoring data ?

In all there are only three indexes in the table..

I am attaching table definition for reference :

And I have not added any other index.

If you have an adequate test environment, try modifying index 3 and then do a bdbpre and bdbpost. I am not quite sure why index 3 is setup the way it is. To me, I would think that trdt, trtm, sern, cwar, kost, cmba.

At the very least, add sern as the last part and that should allow index 3 to be nonduplicate. Do a test run before and after and see if there is a difference.

The other option is to utilize database tools instead of Baan tools for your refreshes.

______________
Jason Foster

http://jasonfoster.emurse.com

Hi, if I modify the index some sessions/reports where index3 is used might not work...

I am also looking for other ways i.e. database tools to speedup the process..

But really want to know the reason why only tdinv700 is soo slow..

Take for example tfgld106 it has 8 indices as compared to 3 of tdinv700

Also the row length of tfgld106 is 287 as compared to 99 of tdinv700

and ofcourse the no of records in tfgld106 are 2.5 times more than tdinv700 still tfgld106 it is getting restored in half the time it takes for tdinv700.

Is there a way to do some debug to find this out ?

Hi, if I modify the index some sessions/reports where index3 is used might not work...


IF u add fields at end to make it unique without changing the order of existing key fields , it will work. However I also do not recommend changing the index for this purpose (though there is no harm doing it on test server if u have time , inclination and requirement to do so )

Probably informix driver may have a problem dealing with duplicate key while inserting. I too have seen tdinv700 slow import but not to the extent u experience. Probably unique indices may be buffered substantially by driver whereas that may not be the case for duplicate indices .

At the very least, adding sern on to the end of index 3 for test purposes would give you and idea if the duplicate key is causing the performance problem. I wouldn't suggest permanently modifying a pre-existing Baan index.

____________

Jason Foster
http://jasonfoster.emurse.com

Hi

What does your inf_storage* files look like for that table compared to the others? (specifically on the server you're restoring to).
Also do you have anything strange for that table in your tabledef6.X file?

I'm going to assume that you haven't:
1. Added Foreign Key Constraints at the DB level
2. Added Triggers to the Table at the DB level


What happens when you export/import the table via informix tools?

Dave

Hi,

I have defined a separate db space for tdinv700 data and separate dbspace for index of tdinv700. Also the no of extents have been properly defined.

I think this is the most optimum setup w.r.t. tdinv700. The same is the case with other big tables.

And there are no modifications at db level (w.r.t Foreign Key Constraints or triggers)

I have not yet imported or exported data via informix commands.

so you've got a special line in your inf_storage specifically for inv700 and it's indexes?

that takes you to a special tablespace just for inv700? are there other tables in that tablespace?

i'd look to those two thoughts for why inv700 is acting different than your other tables.

maybe that tablespace is on a bad controller/disk segment/disk
maybe you typoed the inf_storage file
maybe you setup the tablespace differently....

just some ideas.

Dave

Yes a special line in inf_storage for tdinv700 similarly special lines for lot of other big tables like tfgld106, tfgld410, tfgld418...

Hence its not specific to tdinv700.

These are individual dedicated dbspace (tablespace) for the data and indexes of the tables. there are no other tables in these dbspace.
i.e. in dbspace of tdinv700 data of only table tdinv700, in dbspace of tfgld106 data of only table tfgld106 is there.

And having separate dbspace is supposed to improve the performance.

The point is that what setup is done for tdinv700 similar setup is also there for other big tables, so why is tdinv700 so slow ?

Is it on a different area of the disk?

maybe you've got a bad controller, slow disk, degraded raid... something.

or maybe you didn't setup the tablespace correctly.

unfortunately, since you're not attaching any of the requested files or giving any real details. it's unlikely that I'll (or anyone else) would be able to guess what's wrong.

tdinv700 is not on different area of disk.

We have raid5 and all the dbspaces are on same logical partition. Hence if there was some bad controller or slow disk it would affect all dbspaces (and tables).


Below is a part of the inf_storage file.

tfgld410:920:T:group:01:30:INITIAL 1024 NEXT 1024 LOCK row CLUSTERED dbspace bdb_tfgld410
tfgld410:920:I::01:: dbspace bdb_tfgld410_idx

tfgld106:920:T:group:01:30:INITIAL 1024 NEXT 1024 LOCK row CLUSTERED dbspace bdb_tfgld106
tfgld106:920:I::01:: dbspace bdb_tfgld106_idx

tfgld418:920:T:group:01:30:INITIAL 1024 NEXT 1024 LOCK row CLUSTERED dbspace bdb_tfgld418
tfgld418:920:I::01:: dbspace bdb_tfgld418_idx

tfgld417:920:T:group:01:30:INITIAL 1024 NEXT 1024 LOCK row CLUSTERED dbspace bdb_tfgld417
tfgld417:920:I::01:: dbspace bdb_tfgld417_idx

tdinv700:920:T:group:01:30:INITIAL 65536 NEXT 65536 LOCK row CLUSTERED dbspace bdb_tdinv700
tdinv700:920:I::01:: dbspace bdb_tdinv700_idx

So i can see that your initial and next extents for the inv700 are larger than the other tables.

Perhaps Informix doesn't like dealing with the large chunk size, it could be spending time looking for space.

Just a guess from what you've shown.

Dave

Hi Dev,

I will try to reduce the extent sizes for tdinv700 but infact as part of optimization i had increased those. Infact the solution given by infor is to increase the initial size of the extent so such a size that all the data is accomodated in only one extent.

According to it the extent size should be much much bigger.

I agree that normally makes an import faster.

It would have to be something internal to informix causing issues with the larger segment size.

Dave

Hi Dave, we will come to know whether it is an internal informix issue or not,

as I have reduced the extentsize for tdinv700 and am restoring the data.

..

also, what flags are you using for import? and are you setting any special environment variables like RDS_FULL or SSTS_SET_ROWS ?

Dave