If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Informix > Rootdbs gets filled

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
File Type: txt netbooker.txt (11.7 KB, 118 views)
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: India
Posts: 262
Quote:
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: India
Posts: 262
Quote:
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: India
Posts: 262
Quote:
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: India
Posts: 262
Quote:
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
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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


Quote:
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
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: India
Posts: 262
Quote:
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
File Type: txt onstat.txt (14.6 KB, 97 views)
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: India
Posts: 262
Quote:
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On