Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2012
    Posts
    35

    Unanswered: dbexports are taking too long

    Hello everybody,

    I am using Informix 11.50 on AIX 6.1 machine.
    For backups purposes i am performing daily db exports of the Informix Databases. There are 103 Databases.
    I created 12 input files to split the 103 databases and all of these files are doing dbexport <db_name> -ss. I run these 12 files in parallel on my server and i noticed that i need 8 hours for all exports to complete. Note that all dbexports are writing on the same file system - same directory.
    I tried to split dbexports in 3 separate file systems just to see if i can improve the performance of dbexports. So now i have around 4 input files in each file system and again i start all 12 input files in parallel.
    But it seems that the issue is not fixed because i still need 8 hours until all dbexports are complete.

    Is there any parameter that i should use in Informix instance or other action that i can take in order to reduce dbexports time?

    Thank you in advance.

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi,

    dbexport is not the right way to perform backup with informix. It may be useful to recover data at table level, but it is more a migration tool than a backup tool.
    Main inconvenient is that it locks database in exclusive mode while running, stopping any user to work at the same time.

    Informix has 2 utilities to perform backups: ontape and onbar.
    Both perform backup at instance level, can run while users work and above all are must faster that dbexport because they write physical Informix pages instead or rows into filesystem files.

    ontape is extremely simple to run. The drawback is that there is no lower granularity below instance level. Netherveless, it can handle incremental levels( level 0 full, level 1, level 2)

    onbar is a bit more complex to install, but is way richer in functionality: parallel backup and restore, restore to point in time, dbspace level backup and restore, can be interfaced to TSM...

    You choice: ontape is simple and does not need much admin time, onbar is more complete but will need more efforts initially.

    I would not insist on dbexport as a backup tool. It is not intended for that

    E.

  3. #3
    Join Date
    Jul 2012
    Posts
    35
    Hello,

    Yes i am aware of all the tools that you mentioned but for the purpose that i am using it dbexport is just perfect.
    You see i dont perform these backups on the "LIVE" server. I am making a clone of the "LIVE" Informix environment using RPA image combining with VNX feature called "clone" and with this way i can have my "LIVE" Informix environment daily at the Disaster Recovery at the time that i choose. So the backup is performed in a server in DR Site.
    Also, the reason that i make dbexports is not for Informix instance backup. For the Informix instance backup i do daily an ontape like you said before to keep a full backup of the environment.
    Its my fault i should clarify this from before. I am talking about DB backup. I am doing dbexports because i want to keep the backup of a single DB and import it in the Development server when requested by the programmers of the company.
    But the main point here is to reduce the dbexports time so i could keep more than 1 backup of a single DB each day.
    I hope that i explain the situation better now.

    Thank you anyway for your response.

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi,

    I think you could resolve our DR question by implementing Informix HDR, which is way more simple than the solution you are implementing.

    There is no parameter that will accelerate dbexport. Maybe increasingwith the RA_PAGES parameter may help to enhance the read ahead performance at this time.

    How long does ontape -s take ?
    Are you sure that the filesystem performance is generally acceptable?
    Did you measure the disk thruput ?
    Also how does dbexport behave in the original / primary environment ?
    are you on RAID5 disk implementation?

    Else, those articles may also be of interest for you:
    Data archiving with Informix Dynamic Server table-level restore

    and from my iiug mate superstar Andrew Ford:
    Table Level Restore - Pretty Useful Stuff - Informix DBA

  5. #5
    Join Date
    Jul 2012
    Posts
    35
    Hello,

    The filesystem performance and generally disk throughput are tested and they seem fine.
    Yes i am using RAID5 disk implementation but not traditional RAID GROUP. I am using RAID5 through a Storage Pool of 20-25 disks.
    I didnt know about the RA_PAGES so i think that i am going to test this.

    Thank you

  6. #6
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi,

    Disk vendors always say that their disks are fast, and RDBMS specialists always recommend not to use RAID5, specially when performance is needed.

    Do you look in detail how IFMX is behaving ?
    did you run a continuous onstat -g iof to see the real thruput with your chunks?
    did you run a continuous iostat to compare with onstat g iof results ?
    did you compare disk and system behaviour when you run one dbexport at a time, and when run several of them ?
    Is this raw device or cooked files/chukns ?
    in this case, do you use directio ?

    I the best way to respond to your needs is to use onbar with separate dbspaces for each database. Many customers do this and it is way easier and faster.

    Again, with such a number of databases,

  7. #7
    Join Date
    Jul 2012
    Posts
    35
    Hello again,

    You really wrote some very helpful comments for me.
    However, onbar is not what i need because in this case i will need to restore using Informix logs and when i do that it means that the whole instance will be at the same point in time.
    This is not what i want, i just want to have a specific database separately so if a programmer ask for a database in a specific time i can import it in the Development server without affecting the rest of the databases on that instance.
    I use raw devices since the Informix environment in DR Site is the exact clone of the "LIVE" one.

    Thank you

  8. #8
    Join Date
    Sep 2012
    Posts
    12
    I think it's don't you try with dbexport help guide because i'm also using dbexport utility think bit diffrent and try this way also

    create on or two directory like (/usr/export, /usr/export2
    change the permission to root:informix
    and then

    create script "sh_export' and copy it to /usr/bin

    sh_export as like follow

    #bin/ksh
    INFORMIXDIR=
    export INFORMIXDIR
    PATH=$PATH:$INFORMIXDIR/bin
    export PATH
    ONCONFIG=
    export ONCONFIG
    INFORMIXSERVER=
    export INFORMIXSERVER
    TERMCAP=$INFORMIXDIR/etc/termcap
    export TERMCAP
    DBDATE=dmy4/
    export DBDATE (as like set ens's)

    cd /usr/export

    dbexport dbname -o .
    dbexport dbname2 -o . etc.. some dbnames

    cd /usr/export2

    dbexport dbname -o .
    dbexport dbname2 -o . etc.. some dbs

    and then you can create cron jobs with editing crontab file

  9. #9
    Join Date
    Jul 2012
    Posts
    35
    hello,
    interesting idea but i already run it using scripts like you mention here.
    i tried it in 2 ways:
    1) i create 13 scripts that each one contains the command "dbexport dbname -ss" ....etc.
    then i create another scripts that calls all 13 scripts together and run it in the background so that all of them start at the same time.
    2) i split the 13 scripts to 4 different directories like you mentioned above. for example scripts 1-3 located to dir /exports1, 4-6 to /exports2, 7-9 to /exports3 and 10-13 to /exports4.
    then i use again another script to run all 13 scripts at the same time.

    in both ways dbexports are taking the same time to finish.
    basically i think that i tried what you mentioned above just i dont use it in cronjobs because i dont have any fixed time that i need to run it. i can run this procedure anytime during the day thats why i let it start manually.
    is there something in your post that i am not getting? because i think that it will have the same effect.

    Thank you for your reply.

  10. #10
    Join Date
    Sep 2012
    Posts
    12
    and anther thing have you use update statistic command for your databases
    and also is it a live server if not or if you can get it in to single user mode even it late night I think it will be much faster than present i'm using it for my sever it's faster than my others try it and let me know how it's

  11. #11
    Join Date
    Jul 2012
    Posts
    35
    yes update statistics are done once a week.
    the procedure that i described before is NOT DONE on LIVE server because there is no time window and the resources available to do this.
    so i have a clone server and i synchronize both of them using RPA going back to a specific image in time. then i bring the INFORMIX instance in my clone server On-Line and i do this procedure.
    single user mode? how can i do this setting?

  12. #12
    Join Date
    Sep 2012
    Posts
    12
    it's like bellow you should do it on live server (primary server)

    before you run the dbexport command

    run "onmode -c" command

    as soon you run it

    run "onmode -j" it'll change your database server from online to administration mode in the point only informix user can access the database and other user can not after you finished it

    run "onmode -m" to online server

  13. #13
    Join Date
    Jul 2012
    Posts
    35
    ok thank you very much for the tip.
    i will use it

  14. #14
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    One more stupid question:
    are you running dbexport thru tcp protocol , or thru shm protocol ? For this check in your sqlhosts file whether your $INFORMIXSERVER value is sitting on soctcp or ipcshm.

    If ipcshm, this is the fastest way. But if you use soctcp, you can play with the FET_BUF_SIZE env variable to maximize the thruput thru this channel, and test with different values : 32 , 64, 128 .... until you stop noticing progress.

    Also: are you sure that no forgotten sessions are putting locks ?

    I would also give a try to Art Kagel's myexport utility which has very interesting fonctionnality.

  15. #15
    Join Date
    Jul 2012
    Posts
    35
    i use tcp protocol. i will try what you said cause i wasnt aware of this variable.
    im sure that there are no lock tables because every time i do the procedure i bring the informix instance down and then online and start the dbexports.

    thank you

Posting Permissions

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