Problem with dbexport
A user running IDS 7.31.TD6 on Windows 2000 Advanced Server found his nightly dbexport (41 GB of flat files) suddenly taking far longer, with one checkpoint lasting from 03:02 to 05:33; oncheck showed no table corruption. Respondents asked for version/platform details and suggested checking disk health, SAN/NAS outages and the online log. Art Kagel explained that long blocking checkpoints are inherent to 7.x (addressed by non-blocking checkpoints in 11.10+), recommended tuning or a health check, hardware/version upgrade, and using ontape/onbar instead of dbexport for backups. No confirmed fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello People of the Forum! First, I want to be grateful for his answers to Art
Kagel to my previous posts, which I did not answer not for discourtesy but for
excessive work. My problem is the following one: Every night I do a dbexport
of my database, which occupies in flat file 41 GB. Last two nights he has
delayed more of the used thing and I have had to abort it because it was
beginning the rest of the employees to work. I traversed the oncheck with the
options "I" and "D" for the tables in which it was exporting and the same ones
did not have any problem. What was called me the attention, is that one of the
checkpoints began to them 03:02 am and finished to them 05:33 am. Two times
the same thing happened. It knows someone porqué can delay so much the
checkpoint or how it is possible to avoid this? From already, I am grateful
PLEASE post your engine type (SE, Online, or IDS), version and platform
details when you post!
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Oct 3, 2012 at 9:12 AM, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> Hello People of the Forum! First, I want to be grateful for his answers to
> Art
> Kagel to my previous posts, which I did not answer not for discourtesy but
> for
> excessive work. My problem is the following one: Every night I do a
> dbexport> of my database, which occupies in flat file 41 GB. Last two nights he has
> delayed more of the used thing and I have had to abort it because it was
> beginning the rest of the employees to work. I traversed the oncheck with
> the
> options "I" and "D" for the tables in which it was exporting and the same
> ones
> did not have any problem. What was called me the attention, is that one of
> the
> checkpoints began to them 03:02 am and finished to them 05:33 am. Two times
> the same thing happened. It knows someone porqué can delay so much the
> checkpoint or how it is possible to avoid this? From already, I am grateful
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8ff1c94cfaf27d04cb27ce12
I'm Sorry Art, I forgot this information. The Informix is an IDS 7.31 TD6, and the platform is a Windows 2000 Advanced Server.
On Wed, Oct 3, 2012 at 6:12 AM, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> [...] My problem is the following one: Every night I do a dbexport
> of my database, which occupies in flat file 41 GB. Last two nights he has
> delayed more of the used thing and I have had to abort it because it was
> beginning the rest of the employees to work. I traversed the oncheck with
> the
> options "I" and "D" for the tables in which it was exporting and the same
> ones
> did not have any problem. What was called me the attention, is that one of
> the
> checkpoints began to them 03:02 am and finished to them 05:33 am. Two times
> the same thing happened. It knows someone porqué can delay so much the
> checkpoint or how it is possible to avoid this?
>
In most respects, a better subject line for this would be something like:
Why would it suddenly take 2.5 hours to complete a checkpoint?
As Art already suggested, it is going to be necessary to identify the
platform on which this is running (o/s and version) and the version of IDS
that is in use (or XPS, or OnLine). The version should be precise down to
the last available digit if it is something like 11.10.UC4W2X3.
Have you run a physical health check on the disk drives that contain the
dbspaces? Is there any chance that there could be a network outage at the
time that affect NAS/SAN storage?
For a checkpoint to take 2.5 hours to complete, there must be so much data
to write that it takes 2.5 hours to do so (incredibly implausible if the
whole DB is 41 GiB when unloaded), or the disk drives must be responding
incredibly slowly, or some thread in the server must be hanging around in a
critical section that blocks the checkpoint, or something similar.
Is there any information in the online log file indicating any problems
while (or just before, or just after) the checkpoint is supposed to be
occurring?
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--047d7bdc8d0eef872504cb285f45
On Wed, Oct 3, 2012 at 7:13 AM, GUSTAVO ECHENIQUE < gustavo.echenique@cemdo.com.ar> wrote: > I'm Sorry Art, I forgot this information. The Informix is an IDS 7.31 TD6, > and > the platform is a Windows 2000 Advanced Server. > It's trying to tell you "I'm old and decrepit and want to be retired"... -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --20cf306f7e38cb0f2104cb28631b
Gustavo:
I'm going to have to agree with Jonathan. Unless you have recently
replaced all of your drives, then #1 you are at severe risk of a hard disk
crash as the drive(s) must be long past their MTBF rating, #2 the
filesystem you are using for the server chunks is probably being shared
with Windows which is slowing things down, #3 the Informix version you are
running is from circa 1999 or maybe earlier.
I would a) Get a new server machine, say an x86/64 machine running Linux
and b) Upgrade your server on the new machine to the latest release
(11.70.FC5W2 IB). Talk to an IBM salesperson or a competent IBM partner
(like me ;-) who is familiar with the Informix product line about licensing
options. Today's systems are so much more powerful than what you probably
bought in 1999 that relicensing with a current release may be very
reasonable.
Direct to your checkpoint problem: Long checkpoints are a problem in
Informix 7.xx that was finally solved in v11.10 and later releases with
non-blocking checkpoint technology. There are ways to tune your instance
to reduce the impact and duration of checkpoints. I would suggest that you
get a server HealthCheck from an experienced Informix consultancy. A
couple of choices are: Advanced DataTools Corp. for which I work, Oninit
US, XTivia, Oninit UK, and Ardenta in the UK come quickly to mind though
there are others out there.
I have to wonder why you are performing (or attempting to anyway) a
dbexport every week. For backup purposes you should be using ontape or
onbar instead neither of which will lock the databases like dbexport does
and will allow you to make archives even daily without affecting users in
any way.
Art
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Oct 3, 2012 at 10:24 AM, Jonathan Leffler <
jonathan.leffler@gmail.com> wrote:
> On Wed, Oct 3, 2012 at 7:13 AM, GUSTAVO ECHENIQUE <
> gustavo.echenique@cemdo.com.ar> wrote:
>
> > I'm Sorry Art, I forgot this information. The Informix is an IDS 7.31
> TD6,
> > and
> > the platform is a Windows 2000 Advanced Server.
> >
>
> It's trying to tell you "I'm old and decrepit and want to be retired"...
>
> --
> Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
> "Blessed are we who can laugh at ourselves, for we shall never cease to be
> amused."
>
> --20cf306f7e38cb0f2104cb28631b
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f23585fc62cfc04cb28f1b1
Amigo Gustavo, If its any consolation, here in Puerto Rico and in many other Latin-American countries, we're still running SE 4.10 on MS-DOS machines! I have been tinkering with 11.70 on Windows/64-bit machines and performance is ~ 25 times faster! I'm aware that most Latin-American countries favor Windows over Linux. However, I suggest you upgrade to 11.70 on Linux since the Win version is still somewhat shaky!