Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    262

    Rootdbs gets filled

    Dear All,

    I need ur help. I have IDS 9.3 on sun solaris 8. My Informix server is going for a toss. I am attaching my onconfig file for ur ref. My users are connecting thru java. They are opening a lot of connections, when i do onstat -g ses, i get around 2000 sessions as a result of which the rootdbs gets filled, i have added 4 new chunks, but still it gets filled. We cannot continue with the work. I am stuck up, not sure what size to increase. What shared memory parameters need to be set. By default informix allocates additional shared memory. Please advice.

    Best Regards,

    lloyd

    My IDS server is DB1

    SQLHOSTS File

    DB1 onipcshm 10.1.32.66 sqlexec1
    DB1_test ontlitcp 10.1.32.66 sqlexec1

    Attached Onconfig file
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Hi,

    I have a doubt that you may have your database created in the rootdbs space. That's why the users are chewing up the the space that being allocated. To see the particulars, use:

    onmonitor - Status - Databases
    or
    select dbinfo('dbspace',partnum) dbspace, name, owner
    from sysmaster:sysdatabases ;

    If it is the case, you may think of relocating the database into a independent dbspace area.

    Please post onstat -d output.

    $ONCONFIG file says:
    ROOTSIZE 30000 # Size of root dbspace (Kbytes)

    I think, you need to increase it.

    The following param need to be configured to improve performance

    NUMAIOVPS # Number of IO vps
    CLEANERS 1 # Number of buffer cleaner processes
    LOCKS 200000 # Maximum number of locks
    BUFFERS 5000 # Maximum number of shared buffers
    SHMVIRTSIZE 8192 # initial virtual shared memory segment size

    My suggestion:

    NUMAIOVPS no_of_spaces # Number of IO vps
    CLEANERS no_of_spaces # Number of buffer cleaner processes
    LOCKS 150000 # Maximum number of locks
    BUFFERS 10000 # Maximum number of shared buffers
    SHMVIRTSIZE 10240 # initial virtual shared memory segment size

    Regards,
    Shriyan

  3. #3
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Originally posted by vpshriyan
    Hi,

    I have a doubt that you may have your database created in the rootdbs space. That's why the users are chewing up the the space that being allocated. To see the particulars, use:

    onmonitor - Status - Databases
    or
    select dbinfo('dbspace',partnum) dbspace, name, owner
    from sysmaster:sysdatabases ;

    If it is the case, you may think of relocating the database into a independent dbspace area.

    Please post onstat -d output.

    $ONCONFIG file says:
    ROOTSIZE 30000 # Size of root dbspace (Kbytes)

    I think, you need to increase it.

    The following param need to be configured to improve performance

    NUMAIOVPS # Number of IO vps
    CLEANERS 1 # Number of buffer cleaner processes
    LOCKS 200000 # Maximum number of locks
    BUFFERS 5000 # Maximum number of shared buffers
    SHMVIRTSIZE 8192 # initial virtual shared memory segment size

    My suggestion:

    NUMAIOVPS no_of_spaces # Number of IO vps
    CLEANERS no_of_spaces # Number of buffer cleaner processes
    LOCKS 150000 # Maximum number of locks
    BUFFERS 10000 # Maximum number of shared buffers
    SHMVIRTSIZE 10240 # initial virtual shared memory segment size

    Regards,
    Shriyan
    Hi Shriyan,

    Thanks for your prompt reply. My database is created in a new dbspace.
    My users are connecting thru java & they have opened a lot of connections. They are using DB Webservices facade to connect. This results in lot connections to the DB. To overcome that i created 4 new chunks and added it to the rootdbs, my problem could resolve for the moment, but i need to take precautions for future. The output of
    onstat -d is given below. You have suggested few changes to configuration parameters which i will do it. Just want to check on the below 2 parameters.

    NUMAIOVPS no_of_spaces # Number of IO vps
    CLEANERS no_of_spaces

    what is "no_of_spaces"
    I will increase the size of BUFFERS & SHMVIRTSIZE

    Can you draw some light on this...

    Best Regards,

    Lloyd



    Informix Dynamic Server Version 9.30.UC3XB -- On-Line -- Up 03:33:28 -- 153600 Kbytes

    Dbspaces
    address number flags fchunk nchunks flags owner name
    be027d8 1 0x1 1 5 N informix rootdbs
    be49310 2 0x1 2 1 N informix nb4_dev
    be49460 3 0x1 3 1 N informix nb4_idx
    be495b0 4 0x2001 4 1 N T informix tempdbs
    be49700 5 0x8001 5 2 N S informix nb4_sbspace
    be49850 6 0x11 6 1 N B informix nb4_blobspace
    6 active, 2047 maximum

    Note: For BLOB chunks, the number of free pages shown is out of date.
    Run 'onstat -d update' for current stats.

    Chunks
    address chk/dbs offset size free bpages flags pathname
    be02928 1 1 0 15000 6000 PO- /export/home/informix/nb4/nb4_rootdbs
    be58910 2 2 0 25000 15046 PO- /export/home/informix/nb4/nb4_devdbs
    be58a80 3 3 0 7500 6611 PO- /export/home/informix/nb4/nb4_idxdbs
    be58bf0 4 4 0 10000 9939 PO- /export/home/informix/nb4/nb4_tempdbs
    be58d60 5 5 0 20000 16368 18600 POS /export/home/informix/nb4/nb4_sbspace
    Metadata 1347 35 1347
    be02ad0 6 6 0 20000 ~20000 20000 POB /export/home/informix/nb4/nb4_blobspace
    be02c40 7 5 0 10000 8796 9308 POS /export/home/informix/nb4/nb4_sbspace_1
    Metadata 689 689 689
    be02db0 8 1 0 15000 14997 PO- /export/home/informix/nb4/nb4_rootdbs_1
    be49030 9 1 0 15000 14997 PO- /export/home/informix/nb4/nb4_rootdbs_2
    be491a0 10 1 0 20000 0 PO- /export/home/informix/nb4/nb4_rootdbs_3
    e3f99b0 11 1 0 15000 14997 PO- /export/home/informix/nb4/nb4_rootdbs_4
    11 active, 2047 maximum

  4. #4
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Hi Llyod,

    Well, what i mean by "no_of_spaces" is Number of dbspaces!

    Your instance have 6 active dbspaces. So as a thumb rule, you may set the parms as:

    NUMAIOVPS 6 # Number of IO vps
    CLEANERS 6 # Number of buffer cleaner processes

    As far as the rootdbs is going out of space is concerned, I see a lot of free space available is that dbspace!

    __size__free_ST__Path
    15000__6000 PO- /export/home/informix/nb4/nb4_rootdbs
    15000_14997 PO- /export/home/informix/nb4/nb4_rootdbs_1
    15000_14997 PO- /export/home/informix/nb4/nb4_rootdbs_2
    15000_14997 PO- /export/home/informix/nb4/nb4_rootdbs_4

    Problem seems to here.
    ROOTSIZE 30000 # Size of root dbspace (Kbytes)

    Regards,
    Shriyan

  5. #5
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Originally posted by vpshriyan
    Hi Llyod,

    Well, what i mean by "no_of_spaces" is Number of dbspaces!

    Your instance have 6 active dbspaces. So as a thumb rule, you may set the parms as:

    NUMAIOVPS 6 # Number of IO vps
    CLEANERS 6 # Number of buffer cleaner processes

    As far as the rootdbs is going out of space is concerned, I see a lot of free space available is that dbspace!

    __size__free_ST__Path
    15000__6000 PO- /export/home/informix/nb4/nb4_rootdbs
    15000_14997 PO- /export/home/informix/nb4/nb4_rootdbs_1
    15000_14997 PO- /export/home/informix/nb4/nb4_rootdbs_2
    15000_14997 PO- /export/home/informix/nb4/nb4_rootdbs_4

    Problem seems to here.
    ROOTSIZE 30000 # Size of root dbspace (Kbytes)

    Regards,
    Shriyan
    Hi Shriyan,

    Thanks once again, i will update the onconfig file for the above changes.
    For rootdbs its now showing the spaces as free, earlier it was full, maybe the users have closed some of their sessions.
    As for the rootsize, can i change it now in the onconfig file, will my data be altered. Or since i have added additional chunks to the rootdbs it will take care. Please advice.

    Best Regards,

    Lloyd

  6. #6
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Hi Lloyd,

    >For rootdbs its now showing the spaces as free, earlier it was full

    Are you sure about this? You have around 100 MB (assuming 2K is your page size) of free space available at this moment. Have you ever noticed that all of them completely filled up? By the way, according to the $ONCONFIG the rootdbs can only stretch up to 30 MB max. Your statement lacks credibility.

    >maybe the users have closed some of their sessions.

    Doubtfull that the users taking up 100 MB for session related informations.

    >As for the rootsize, can i change it now in the onconfig file

    Yes. You may edit the file or use onparams. Make sure that the instance is free of users before doing it. Hope you have latest & error-free 0 level backup ready in your cupboard.

    >will my data be altered.

    Yes.

    >Or since i have added additional chunks to the rootdbs it will take care

    No, it is not automatic. %ONCONFIG not aware of added chunks, ROOTSIZE para remained unchanged, hence need to be augmented.

    Regards,
    Shriyan

  7. #7
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Originally posted by vpshriyan
    Hi Lloyd,

    >For rootdbs its now showing the spaces as free, earlier it was full

    Are you sure about this? You have around 100 MB (assuming 2K is your page size) of free space available at this moment. Have you ever noticed that all of them completely filled up? By the way, according to the $ONCONFIG the rootdbs can only stretch up to 30 MB max. Your statement lacks credibility.

    >maybe the users have closed some of their sessions.

    Doubtfull that the users taking up 100 MB for session related informations.

    >As for the rootsize, can i change it now in the onconfig file

    Yes. You may edit the file or use onparams. Make sure that the instance is free of users before doing it. Hope you have latest & error-free 0 level backup ready in your cupboard.

    >will my data be altered.

    Yes.

    >Or since i have added additional chunks to the rootdbs it will take care

    No, it is not automatic. %ONCONFIG not aware of added chunks, ROOTSIZE para remained unchanged, hence need to be augmented.

    Regards,
    Shriyan
    Hi Shriayn,

    Thanks once again for your feedback, Yes i am sure the entire rootdbs was full, thats the reason i added 4 additional chunks to it, inspite of that it kept on filling, so i had to kill some sessions which was taking more memory. But its not the case today. The rootdbs is completely free now, as you can see from the output below. Not sure what had happend. I also increased the BUFFER & SHMVIRTSIZE param to size you specified by you & NUMAVIOVPS & CLEANERS to the no_of_space. I am not sure whether this solved the problem. Also i am not too comfortable of increasing the size of rootdbs as i may loose my data. Will the adding up of additional chunks not resolve my issue, as i have 4 new chunks for rootdbs. Also is there a case that i my data gets lost if i increase the size of rootdbs. There is a demo of my application this weekend. Will the current changes as suggested by you take care of it. Also is there a way the application can terminate or kill sessions that are not active. Can i make use of INFORMIXCONTIME & INFORMIXCONRETRY params to tackle this issue, if yes can i do it on the database side or i will have to do at the client place. Please advice. onstat -d output

    Best Regards,

    Lloyd



    Informix Dynamic Server Version 9.30.UC3XB -- On-Line -- Up 06:12:56 -- 149504
    Kbytes

    Dbspaces
    address number flags fchunk nchunks flags owner name
    c8027d8 1 0x1 1 5 N informix rootdbs
    c84d5f0 2 0x1 2 1 N informix nb4_dev
    c84d740 3 0x1 3 1 N informix nb4_idx
    c84d890 4 0x2001 4 1 N T informix tempdbs
    c84d9e0 5 0x8001 5 2 N S informix nb4_sbspace
    c84db30 6 0x11 6 1 N B informix nb4_blobspace
    6 active, 2047 maximum

    Note: For BLOB chunks, the number of free pages shown is out of date.
    Run 'onstat -d update' for current stats.

    Chunks
    address chk/dbs offset size free bpages flags pathname
    c802928 1 1 0 15000 7000 PO- /export/home/informix/nb4/nb4_rootdbs
    c85cb68 2 2 0 25000 15046 PO- /export/home/informix/nb4/nb4_devdbs
    c85ccd8 3 3 0 7500 6611 PO- /export/home/informix/nb4/nb4_idxdbs
    c85ce48 4 4 0 10000 9947 PO- /export/home/informix/nb4/nb4_tempdbs
    c802ad0 5 5 0 20000 16368 18600 POS /export/home/informix/nb4/nb4_sbspace
    Metadata 1347 35 1347
    c802c40 6 6 0 20000 ~20000 20000 POB /export/home/informix/nb4/nb4_blobspace
    c802db0 7 5 0 10000 8796 9308 POS /export/home/informix/nb4/nb4_sbspace_1
    Metadata 689 689 689
    c84d030 8 1 0 15000 12997 PO- /export/home/informix/nb4/nb4_rootdbs_1
    c84d1a0 9 1 0 15000 14997 PO- /export/home/informix/nb4/nb4_rootdbs_2
    c84d310 10 1 0 20000 19997 PO- /export/home/informix/nb4/nb4_rootdbs_3
    c84d480 11 1 0 15000 14997 PO- /export/home/informix/nb4/nb4_rootdbs_4
    11 active, 2047 maximum

  8. #8
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Hi Lloyd,

    I leave the option of increasing the ROOTSIZE in $ONCONFIG file to you. By augmenting ROOTSIZE param you are not going to lose your data. conversely, by not doing so, the added free chunks can not used by the instance.

    As for the idle sessions are concerned, it would be diffcult to judge them for the criterion for idleness. If you go by time factor, it could happen that a process taking up long for certain processing job like monthend analysis reports etc.

    INFORMIXCONTIME and INFORMIXCONRETRY are server-side environment variables related to session connection issues, and are not useful for detecting idle process.

    Regards,
    Shriyan

  9. #9
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Originally posted by vpshriyan
    Hi Lloyd,

    I leave the option of increasing the ROOTSIZE in $ONCONFIG file to you. By augmenting ROOTSIZE param you are not going to lose your data. conversely, by not doing so, the added free chunks can not used by the instance.

    As for the idle sessions are concerned, it would be diffcult to judge them for the criterion for idleness. If you go by time factor, it could happen that a process taking up long for certain processing job like monthend analysis reports etc.

    INFORMIXCONTIME and INFORMIXCONRETRY are server-side environment variables related to session connection issues, and are not useful for detecting idle process.

    Regards,
    Shriyan
    Hi Shriyan,

    Thanks once again, i worked on your steps - i first took a level 0 backup of my server & then increased the ROOTSIZE param & initialized the server inorder to incorporate the changes. Now when i run ontape -r it gives me an error - Physical restore failed - onconfig ROOTSIZE differs from the archive. I am not able to restore my old data.

    Best Regards,

    Lloyd

  10. #10
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Hi Lloyd,

    Oh... my, my...

    >increased the ROOTSIZE param & initialized the server inorder to incorporate the changes.

    What that you mean by "initialized the server"? You had to just intialize the shared memory, that it.

    >Now when i run ontape -r it gives me an error - Physical restore failed - onconfig ROOTSIZE differs from the archive. I am not able to restore my old data.

    Why you need to do the restore job? You need not do it.

    Regards,
    Shriyan

  11. #11
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Hi Shriyan,

    When i increased my ROOTSIZE & shutdown - startup my server
    the effect does not take place. Foll. is entry of my online log.

    20:06:20 Onconfig parameter ROOTSIZE modified from 90000 to 100000.
    20:06:20 Can NOT change ROOTSIZE without reinitializing, leaving 90000.

    I initialized the serer through onmonitor then only the changes were invoked, however it initializes the server, so all data is lost. Then i tried to restore but that too did not work.

    Regards,

    lloyd


    Originally posted by vpshriyan
    Hi Lloyd,

    Oh... my, my...

    >increased the ROOTSIZE param & initialized the server inorder to incorporate the changes.

    What that you mean by "initialized the server"? You had to just intialize the shared memory, that it.

    >Now when i run ontape -r it gives me an error - Physical restore failed - onconfig ROOTSIZE differs from the archive. I am not able to restore my old data.

    Why you need to do the restore job? You need not do it.

    Regards,
    Shriyan

  12. #12
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    Two things. First, it looks like you have no defined space for physical logs or logical logs - which puts them both in rootdbs (bad, bad!, bad!!). With 9.3 you can have automatic allocation of logical logs. I can see that filling up root in a hurry if for some reason you are filling log space without backing them up.


    Check where your temp space is defined. I see a temp dbspace, but that doesn't mean things actually go there unless the onconfig tells them to.
    Fred Prose

  13. #13
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Originally posted by fprose
    Two things. First, it looks like you have no defined space for physical logs or logical logs - which puts them both in rootdbs (bad, bad!, bad!!). With 9.3 you can have automatic allocation of logical logs. I can see that filling up root in a hurry if for some reason you are filling log space without backing them up.


    Check where your temp space is defined. I see a temp dbspace, but that doesn't mean things actually go there unless the onconfig tells them to.
    Hi Fred,

    First of all thanks for your feedback.
    I did move the physical logs from my rootdbs to a new dbspace & increase the rootsize to 25000, but there are around 2000 connections to my DB & i get a warning that physical log size is small, but when i tried to increase the size and add a chunk to it, i got a shared memory initializing error saying all segment are not removed.
    " mt_shm_remove: WARNING: may not have removed all/correct
    segments"
    My users connect thru thier java applications and open a lot of connections to the db, at a point of time there are over 2000 connections, they do not close the same. Is there a way that we can terminate all idle connections. As for logical logs since 9.3 has automatic allocation i haven't moved them. I have a tempdbs too & in onconfig file its poinitng to the same.
    A copy of my onconfig & a listing of my dbspace is attached for your reference. Please advice...

    Best Regards,

    Lloyd
    Attached Files Attached Files

  14. #14
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    Yes, I think it's your logs. To confirm, after your system has been running a while, do a "onstat -l" (that's a lowercase "L") and see how many logs you have. You start with 11 small ones (way too small) and I think you probibly have several. Do you back them up (which free's them for re-use) with ontape or onbar?


    Your SHMVIRTSIZE amd SHMADD look way too smal to support that many users. Do you get log messages stating virtual segments have been added? Do a "onstat -g seg" and count the segments with a "V" - you should have one, maybe two.

    Are you truely a single processor server? That's what your onconfig says.

    As for getting rid of idle connections, you can script something that looks at the output of "onstat -g ntt". Compare the "open" time with the latest of the "read" or "write" and determine if the session has been idle too long (what ever too long means to you) and kill it with an "onmode -z".
    Fred Prose

  15. #15
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Originally posted by fprose
    Yes, I think it's your logs. To confirm, after your system has been running a while, do a "onstat -l" (that's a lowercase "L") and see how many logs you have. You start with 11 small ones (way too small) and I think you probibly have several. Do you back them up (which free's them for re-use) with ontape or onbar?


    Your SHMVIRTSIZE amd SHMADD look way too smal to support that many users. Do you get log messages stating virtual segments have been added? Do a "onstat -g seg" and count the segments with a "V" - you should have one, maybe two.

    Are you truely a single processor server? That's what your onconfig says.

    As for getting rid of idle connections, you can script something that looks at the output of "onstat -g ntt". Compare the "open" time with the latest of the "read" or "write" and determine if the session has been idle too long (what ever too long means to you) and kill it with an "onmode -z".
    Hi Fred,

    Thanks once again for your prompt reply. Yes you are right i have 11 logical logs, i don't back them to tape but to /dev/null. As for SHMVIRTSIZE i always see a log messages that virtual segments have been added, so i keep on increasing the size daily & monitor it. What would be the ideal size for both SHMVIRTSIZE and SHMADD to support around 2000 connections. As for the server whether its single or multi processor i am not to sure about it, i have to check with my stystem amdinistrator about it, he is not been around for a week. Is there any command thru which we can find out about the processors.
    Thanks in advance & have a nice day.

    Best Regards,

    Lloyd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •