Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    Belgium
    Posts
    3

    Unanswered: Informix Database full

    Hello,
    I have a serious problem on my server.
    Since a few days the informix database is 100% full (userdbs1).
    I deleted a huge amount of data (pm_cdrdatatbl from 170 million records to 5 million records), but the database keeps 100% full.
    Is it possible that the pm_cdrdatatbl for example is fragmented?
    Or is my db maybe filled up with log files or something else?

    Can anyone give me some tips & tricks, I'm an unexperienced user.

    Many Thanks in advance!

    -----------------------------

    output of onstat -d :

    # onstat -d

    Informix Dynamic Server 2000 Version 9.21.UC4X4 -- On-Line -- Up 11:01:56 -- 591240 Kbytes

    Dbspaces
    address number flags fchunk nchunks flags owner name
    2ca6e7d0 1 0x1 1 1 N informix rootdbs
    2ca9e830 2 0x1 2 1 N informix hsddbs
    2ca9e978 3 0x1 3 1 N informix logmdbs
    2ca9eac0 4 0x2001 4 2 N T informix tmpdbs1
    2ca9ec08 5 0x2001 5 1 N T informix tmpdbs2
    2ca9ed50 6 0x2001 6 1 N T informix tmpdbs3
    2ca9ee98 7 0x1 7 5 N informix userdbs1
    2d0fd018 8 0x1 13 1 N informix backupdbs
    8 active, 2047 maximum

    Chunks
    address chk/dbs offset size free bpages flags pathname
    2ca6e918 1 1 0 200000 85220 PO- /dev/rdsk/c0b0t0d1se
    2caad2a8 2 2 200000 5500 5447 PO- /dev/rdsk/c0b0t0d1se
    2caad418 3 3 205500 160000 103229 PO- /dev/rdsk/c0b0t0d1se
    2caad588 4 4 365500 25000 24947 PO- /dev/rdsk/c0b0t0d1se
    2caad6f8 5 5 390500 25000 24947 PO- /dev/rdsk/c0b0t0d1se
    2caad868 6 6 415500 25000 24947 PO- /dev/rdsk/c0b0t0d1se
    2caad9d8 7 7 440500 608075 72 PO- /dev/rdsk/c0b0t0d1se
    2caadb48 8 7 0 1048575 5 PO- /dev/rdsk/c0b0t0d1sf
    2caadcb8 9 4 0 1048575 1048572 PO- /dev/rdsk/c0b0t0d1s10
    2caade28 10 7 0 568248 0 PO- /dev/rdsk/c0b0t0d1s11
    2ca6ea88 11 7 0 1048575 10 PO- /dev/rdsk/c0b0t0d0se
    2ca6ebf8 12 7 0 456076 0 PO- /dev/rdsk/c0b0t0d0sf
    2ca6ed68 13 8 0 262144 257741 PO- /.AS/BACKUP/tmp_cdb/backup_tmp_dbspace
    13 active, 2047 maximum

    #

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Informix Database full

    Originally posted by Brontes
    Hello,
    I have a serious problem on my server.
    Since a few days the informix database is 100% full (userdbs1).
    I deleted a huge amount of data (pm_cdrdatatbl from 170 million records to 5 million records), but the database keeps 100% full.
    Is it possible that the pm_cdrdatatbl for example is fragmented?
    Or is my db maybe filled up with log files or something else?

    Can anyone give me some tips & tricks, I'm an unexperienced user.

    Many Thanks in advance!

    -----------------------------

    output of onstat -d :

    # onstat -d

    Informix Dynamic Server 2000 Version 9.21.UC4X4 -- On-Line -- Up 11:01:56 -- 591240 Kbytes

    Dbspaces
    address number flags fchunk nchunks flags owner name
    2ca6e7d0 1 0x1 1 1 N informix rootdbs
    2ca9e830 2 0x1 2 1 N informix hsddbs
    2ca9e978 3 0x1 3 1 N informix logmdbs
    2ca9eac0 4 0x2001 4 2 N T informix tmpdbs1
    2ca9ec08 5 0x2001 5 1 N T informix tmpdbs2
    2ca9ed50 6 0x2001 6 1 N T informix tmpdbs3
    2ca9ee98 7 0x1 7 5 N informix userdbs1
    2d0fd018 8 0x1 13 1 N informix backupdbs
    8 active, 2047 maximum

    Chunks
    address chk/dbs offset size free bpages flags pathname
    2ca6e918 1 1 0 200000 85220 PO- /dev/rdsk/c0b0t0d1se
    2caad2a8 2 2 200000 5500 5447 PO- /dev/rdsk/c0b0t0d1se
    2caad418 3 3 205500 160000 103229 PO- /dev/rdsk/c0b0t0d1se
    2caad588 4 4 365500 25000 24947 PO- /dev/rdsk/c0b0t0d1se
    2caad6f8 5 5 390500 25000 24947 PO- /dev/rdsk/c0b0t0d1se
    2caad868 6 6 415500 25000 24947 PO- /dev/rdsk/c0b0t0d1se
    2caad9d8 7 7 440500 608075 72 PO- /dev/rdsk/c0b0t0d1se
    2caadb48 8 7 0 1048575 5 PO- /dev/rdsk/c0b0t0d1sf
    2caadcb8 9 4 0 1048575 1048572 PO- /dev/rdsk/c0b0t0d1s10
    2caade28 10 7 0 568248 0 PO- /dev/rdsk/c0b0t0d1s11
    2ca6ea88 11 7 0 1048575 10 PO- /dev/rdsk/c0b0t0d0se
    2ca6ebf8 12 7 0 456076 0 PO- /dev/rdsk/c0b0t0d0sf
    2ca6ed68 13 8 0 262144 257741 PO- /.AS/BACKUP/tmp_cdb/backup_tmp_dbspace
    13 active, 2047 maximum

    #
    Hi,

    In order to release space you must recreate the table.
    A suggestion, uses links simbolicos instead of aiming direct at raw-devices, this facilitates the task if a disc is broken or you recover the on-line one in another servant.

    Gustavo.

  3. #3
    Join Date
    Oct 2003
    Location
    Belgium
    Posts
    3
    Thanx!
    I think I understand it: if a table has got a size of x Gig, it always keeps this size although you delete his records.
    This problem might never have happened because we've set an autodelete on 1 month. This apparently failed and we now had 4.5 months of data in the table.

    We've escalated our problem to Vienna, they should know how to recreate the table.

    One small question: how do I know if I've got space to extend the database (add chunks). I've just seen that another (bigger) customer of us has a database of only 3 Gig...

    greetz,
    B

  4. #4
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    I assume your question is how do I tell if I've got any more physical space on the disk to add a chunk?

    That depends upon the operating system, but must likely your going to have to look at each physical disk and determine how/if each logical partition is allocated.

    From the pathname in your onstat -d output it would appear that you have two physical drives in play: c0b0t0d0 and c0b0t0d1. Most of the space is allocated across c0b0t0d1 using partition "e" with offsets. I'm not sure what someone did with the chunks allocated to s10 and s11.

    At any rate, I'd start with disk c0b0t0d0.

    Point of note: You are running three temp dbspaces of dissimilar sizes. tempdbs1 has a large second chuck. I'm assuming that this was done because of occasional sort or temporary table requirements?

    If this extra space really isn't needed, and since three temp spaces are a waste (considering they are all on the same device) you might rethink the temp space and use most or all of chunk 9 for additional space for userdbs1.
    Fred Prose

  5. #5
    Join Date
    Oct 2003
    Location
    Belgium
    Posts
    3

    Thumbs up

    Thanks to everyone for the useful hints!

  6. #6
    Join Date
    Nov 2003
    Posts
    1

    Wink

    Friend,

    I'm brazilian and I've finished my english course a long time ago, so, sorry if I write something wrong, ok?! :-)
    Well, I have a doubt. I'd like to know if it's possible to increase the number of pages in chunk for a specific temporary dbspace. In other words, I'd like to know if I can change value of chksize.
    If not, the one solution will be add a new chunk?

    Thanks in advance,
    Sílvia Soares.

  7. #7
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Originally posted by Brontes
    Thanx!
    I think I understand it: if a table has got a size of x Gig, it always keeps this size although you delete his records.
    This problem might never have happened because we've set an autodelete on 1 month. This apparently failed and we now had 4.5 months of data in the table.

    We've escalated our problem to Vienna, they should know how to recreate the table.

    One small question: how do I know if I've got space to extend the database (add chunks). I've just seen that another (bigger) customer of us has a database of only 3 Gig...

    greetz,
    B
    Hi,

    Please execute this script, for look free space.

    #!/usr/bin/ksh
    [ `uname -a|grep -c 'AIX'` -eq 1 ] && bk=256 || bk=512
    raya="--------------------------------------------------------------------------
    --"
    echo "Space used in On-Line `echo $INFORMIXSERVER` al `date +%d/%m/%Y-%H:
    %M`"
    echo $raya
    {
    dbaccess sysmaster <<!
    select dbsnum,
    (
    select name[1,18]
    from sysdbspaces
    where syschunks.dbsnum=sysdbspaces.dbsnum
    ) dbspace,
    trunc((sum(chksize))/$bk,0) total,
    trunc(((sum(chksize)-sum(nfree)))/$bk,0) ocupado,
    trunc(sum(nfree)/$bk,0) libre
    from syschunks
    group by 1,2
    order by 1
    !
    } 2>/dev/null | sed '/^$/d'|pg -p "(%d) <Enter>=Continue - <q>uit:"
    echo $raya

    Gustavo.

Posting Permissions

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