On Jul 7, 2008, at 11:36 AM, Wael Ben Moussa wrote:
> i am using ingres 2006 under centos 5.0
>
> i have to migrate from oracle to ingres,
> i have a huge amount of data , about 50 G
> but the default ingres configuration seem not to work for me
> i have first increased maxlocks param from 750 to another arbitrary
> great one 90000
> but (insert ) transactions begins fast then, its speed decrease a
> lot
>
This sounds more like disk issues than Ingres issues. The situation
is more
complex than I can answer in an email right now, but basically there
are a
few things to deal with:
- linux filesystems generally see writes one page at a time. If
there is
any concurrency either from Ingres itself or from other users, it's very
easy to get disk fragmentation which can drastically slow things down.
Using xfs might help, it has tricky code to do delayed allocation and
other
good things. Using direct_io set to ON might help (there are some
kernel versions where this is dangerous; I think 2.6.19 thru .22, but
I am going from unsure memory).
- ext3 filesystems seem to have trouble writing faster than a couple
hundred
meg per second even striped across many drives. (sorry, I don't have
a more
precise statement.) xfs does better. jfs does better too for non-
concurrent
writes, but under concurrency the linux flavor of jfs calls apart
horribly.
- if you have not separated data writes from transaction log writes, you
can get a lot of disk contention.
- if the data area is raid5 you are at the mercy of the controller,
and I've
never yet seen a controller that could write raid5 as fast as straight
stripes/mirrors in real life.
- when the page cache becomes filled with dirty pages, linux has to
throttle writes or it will run out of VM. Unfortunately it does this on
a system-wide basis, not on a per-device basis. In other words if
you clog
up one device, everything slows down, related or not. I keep reading
about
patches that claim to fix this, but I don't know if they are in the
mainline;
and certainly they aren't in the Centos5 kernel (which is what,
2.6.18 or
something?)
Turning on direct IO might help with a couple of these. The downside is
that you don't get any benefit from linux page caching for reads.
(ii.*.dbms.*.direct_io: ON to turn on direct IO, in released Ingres 2006
versions.) Note: Turning on direct IO won't help if you are doing a
table "load",
meaning bulk copy-in to an empty file or a heap table. (insert-
select does
not do a "load" in the public Ingres version.) Table-load into an empty
table will probably do a rebuild load, which builds a new table file
and renames it. There's no way in public Ingres to ask for direct IO on
the rebuild tem****ary file.
I suggest first examining your disk iostats, maybe compare to writing an
ordinary junk file onto the data filesystem with dd. Make sure you have
enough spindles on the data area to handle the write load, and make sure
that the transaction log is somewhere else.
Karl
|
