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?
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
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.
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?
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.
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.
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.
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.
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
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?
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 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.