Slow database, easy problem?

meph · #1 (**permalink**) 12-11-09, 18:09

Hello!

Found this forum via google and thought i give it a try....

I'm working on a company that uses Informix in a product they have developed. I'm very beginner level at databases but i have a problem... Hopefully some kind soul can point my beginner ass in the right direction

Optimizing the database is not usally needed for this product i have installed, since it optimizes it self according to the server hardware via a script for this "product"..

The problem is, i have been importing new data to this application for 2 days now.. And have been waiting and waiting since it takes very long time..

The server I'm running this "application" on is a quad core intel xeon machine (16 virtual cpu's) server is running with 16GB memory.. Operating system is SLES 10.

The problem is that when i check "top" i see just one process (oninit) using 100% CPU all the time (for several hours for one import). Couldnt Informix use several CPU's for the work? ...

I managed to find out how to get stats from informix. And here is some, i made a onstat -p and onstat -g glo ... Onstat -p is from a period of one minute and the other one is for a couple of minutes (see uptime down below)..

Can anyone see from this stats what config setting could be wrong? Or maybe something else? .. Just tell me if you need anything more to be able to see whats wrong... Why is only vp #4 doing all the job? :-/

Code:

xxx:/x # onstat -z

IBM Informix Dynamic Server Version 10.00.UC9     -- On-Line -- Up 08:50:03 -- 1179416 Kbytes

x:/x # onstat -p

IBM Informix Dynamic Server Version 10.00.UC9     -- On-Line -- Up 08:51:18 -- 1179416 Kbytes

Profile
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
11         11         59899655   100.00  2          32         1299       99.85

isamtot    open       start      read       write      rewrite    delete     commit     rollbk
17146      1622       2554       4891       15         87         0          152        0

gp_read    gp_write   gp_rewrt   gp_del     gp_alloc   gp_free    gp_curs
0          0          0          0          0          0          0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes
0          0            0          75.05    0.02     0          0

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans
9          0          3443       0          0          0          78         297

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits
11         0          0          11         0

x:/x # onstat -g glo

IBM Informix Dynamic Server Version 10.00.UC9     -- On-Line -- Up 08:54:59 -- 1179416 Kbytes

MT global info:
sessions threads  vps      lngspins
36       60       22       0

          sched calls     thread switches yield 0   yield n   yield forever
total:    540218          18068           523924    2958      137
per sec:  0               0               0         0         0

Virtual processor summary:
 class       vps       usercpu   syscpu    total
 cpu         15        294.05    0.09      294.14
 aio         2         0.00      0.00      0.00
 lio         1         0.00      0.00      0.00
 pio         1         0.00      0.00      0.00
 adm         1         0.00      0.00      0.00
 soc         1         0.00      0.00      0.00
 msc         1         0.00      0.00      0.00
 total       22        294.05    0.09      294.14

Individual virtual processors:
 vp    pid       class       usercpu   syscpu    total
 1     4339      cpu         0.13      0.04      0.17
 2     5203      adm         0.00      0.00      0.00
 3     5204      cpu         0.00      0.00      0.00
 4     5212      cpu         293.89    0.05      293.94
 5     5214      cpu         0.03      0.00      0.03
 6     5215      cpu         0.00      0.00      0.00
 7     5227      cpu         0.00      0.00      0.00
 8     5261      cpu         0.00      0.00      0.00
 9     5280      cpu         0.00      0.00      0.00
 10    5330      cpu         0.00      0.00      0.00
 11    5418      cpu         0.00      0.00      0.00
 12    5475      cpu         0.00      0.00      0.00
 13    5530      cpu         0.00      0.00      0.00
 14    5588      cpu         0.00      0.00      0.00
 15    5656      cpu         0.00      0.00      0.00
 16    5662      cpu         0.00      0.00      0.00
 17    5664      lio         0.00      0.00      0.00
 18    5733      pio         0.00      0.00      0.00
 19    5763      aio         0.00      0.00      0.00
 20    5790      msc         0.00      0.00      0.00
 21    5793      aio         0.00      0.00      0.00
 22    5801      soc         0.00      0.00      0.00
                 tot         294.05    0.09      294.14

meph · #2 (**permalink**) 12-11-09, 18:19

Here is stats after 30 minutes since i ran onstat -z

This application that uses informix is importing a lot of data and processes this data and then put it in the database. The imported data could be a textfile with 10-15MB text that gets imported into the database.. The server has been running for maybe 5 hours to import maybe 50MB text....

Code:

IBM Informix Dynamic Server Version 10.00.UC9     -- On-Line -- Up 09:22:15 -- 1179416 Kbytes

Profile
dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
91626      98123      1496027446 99.99   18152      44614      87509      79.26

isamtot    open       start      read       write      rewrite    delete     commit     rollbk
19996760   142703     2134915    12271684   808        33483      650        3610       0

gp_read    gp_write   gp_rewrt   gp_del     gp_alloc   gp_free    gp_curs
0          0          0          0          0          0          0

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes
0          0            0          1907.07  4.52     11         22

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans
4971       0          315934     0          0          4          2725       16530

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits
22104      2724       1140       25967      420

I

Code:

BM Informix Dynamic Server Version 10.00.UC9     -- On-Line -- Up 09:22:35 -- 1179416 Kbytes

MT global info:
sessions threads  vps      lngspins
40       64       22       0

          sched calls     thread switches yield 0   yield n   yield forever
total:    11540276        763691          10796062  19591     150555
per sec:  178             10              0         6         0

Virtual processor summary:
 class       vps       usercpu   syscpu    total
 cpu         15        1926.59   3.52      1930.11
 aio         2         0.62      0.95      1.57
 lio         1         0.00      0.02      0.02
 pio         1         0.01      0.15      0.16
 adm         1         0.00      0.00      0.00
 soc         1         0.00      0.01      0.01
 msc         1         0.00      0.02      0.02
 total       22        1927.22   4.67      1931.89

Individual virtual processors:
 vp    pid       class       usercpu   syscpu    total
 1     4339      cpu         12.12     1.50      13.62
 2     5203      adm         0.00      0.00      0.00
 3     5204      cpu         30.96     0.37      31.33
 4     5212      cpu         1873.84   1.30      1875.14
 5     5214      cpu         6.49      0.16      6.65
 6     5215      cpu         2.95      0.19      3.14
 7     5227      cpu         0.15      0.00      0.15
 8     5261      cpu         0.08      0.00      0.08
 9     5280      cpu         0.00      0.00      0.00
 10    5330      cpu         0.00      0.00      0.00
 11    5418      cpu         0.00      0.00      0.00
 12    5475      cpu         0.00      0.00      0.00
 13    5530      cpu         0.00      0.00      0.00
 14    5588      cpu         0.00      0.00      0.00
 15    5656      cpu         0.00      0.00      0.00
 16    5662      cpu         0.00      0.00      0.00
 17    5664      lio         0.00      0.02      0.02
 18    5733      pio         0.01      0.15      0.16
 19    5763      aio         0.61      0.92      1.53
 20    5790      msc         0.00      0.02      0.02
 21    5793      aio         0.01      0.03      0.04
 22    5801      soc         0.00      0.01      0.01
                 tot         1927.22   4.67      1931.89

Tyveleyn · #3 (**permalink**) 12-12-09, 03:40

Hi.

First: use the UPDATE STATISTICS sql-statement in between the import of files to recreate the indexes.
Second: with which tool or statement the data is imported? Is it also possible to convert the data to load-files and import it through the LOAD statement? Or can you use the dbload utility, these are the fastest Informix tools to stuff a database.
Third: are there other routines executed with the import of tablerows, like stored procedures through a trigger?

Regards,
Hans

meph · #4 (**permalink**) 12-12-09, 11:23

hello! thanks for your reply! I got a reply from my company that this works the way it supposed to. Since importing data is only a small part of what this application does so it uses the other processes for other jobs. I noticed this yesterday ... The applications queues up job in a list and executes the imports one by one..

The only time when i import this much data is right after the installation of the application.. And this big data import is not so common... This customer im working with now has around 35000 phone extensions so there is much data to handle..

This application, i can not really change the way it work but i could change database settings to optimize it without destoroying stuff i think... But now the application is working better again...

So thank you for your support!

ashsraqui · #5 (**permalink**) 12-24-09, 11:56

can anyone pls let me know whether there will be any difference in the DB temp space usage when we use the order by clause and with out using order by clause( taking into acccount i do not need the ordering of the particular column).

maskanh · #6 (**permalink**) 01-06-10, 05:42

The problem is that when i check "top" i see just one process (oninit) using 100% CPU all the time (for several hours for one import). Couldnt Informix use several CPU's for the work? ...

I managed to find out how to get stats from informix. And here is some, i made a onstat -p and onstat -g glo ... Onstat -p is from a period of one minute and the other one is for a couple of minutes (see uptime down below)..

theeiledon · #7 (**permalink**) 01-06-10, 07:38

Can you post your onconfig? The database engine will only use the processors it's told to so the onconfig will show this.

Internet Services

Web Development

Digital Marketing

Consumer Tech