RE: Comments please on performance - table to table copy
Oh dear - crappy day for long posts from me - too much else going on.
1 - 10 seconds to do a table to table for 1 million rows under Linux ain't
bad.
2 - fragmentation is two way - you get faster access (although not according
to the ICP exam !!!) because you are reading in parallel. You are correct
about the commit to disk from the buffer. If you can use light appends (how
HPL is faster) then you don't go through that buffer and you go back to
writing to multiple disks at once.
3 - how to measure the buffer wall. I don't have a good answer off the top
of my head. I might start with onstat -g ioq. It brings forth a good
question though - your load is not complete until a checkpoint occurs.
Therefore you have to include the time for that checkpoint in your load
time.
4 - If you're trying to improve things - I would start by watching what the
system is doing underneath the process. How much cpu/disk you are going
through (memory won't be that big an issue). If disk is idling until the
end, you might want to pump your background writes - although they are not
as efficient as a foreground (checkpoint) write. But I'd be careful in
tuning the engine to solve one thing unless that thing was thye single
representative thing that your engine was going to do.
cheers
j.
> -----Original Message-----
> From: Brett Randall [mailto:Brett@reply.to.newsgroup]
> Sent: Monday, November 20, 2000 4:59 PM
> To: informix-list@iiug.org
> Subject: Re: Comments please on performance - table to table copy
>
>
> Thanks for your reply Jack.
>
> Fragmentation is only an issue once the database wants to commit the
> buffers to disk - yes? I think I will test the same queries but using
> temp tables to remove any persistent storage concerns.
>
> BTW - I am not particularly fussed by the poor performance from the
> "load from" query, or yes, I would need to look at HPL. It
> is the table
> to table copy that concerns me. And it's not a practical
> exercise, only
> a benchmark.
>
> So,
> How does one reduce/measure contention for the buffer
> pool (AKA "The
> wall")?
> Any clue on which onstat area to inspect?
> Tunable parameter to improve?
> If the system is quiet (one user), no disk accesses (shared memory
> only), then how can I find out what is slowing down the
> database in its
> table to table copy?
>
> Many thanks in advance for anyone with further advice.
>
> Brett Randall
>
> "Parker, Jack" wrote:
> >
> > How did you fragment the tables?
> >
> > Not that I'm going to bother testing this, but I would
> expect this take
> > under a second
> > on our Sun 6500 16 procs/16GB with proper fragmentation and
> XPS. If you
> > don't fragment it at all - well then it might slow down a
> bit, but 1 million
> > records on one disk is about a second around here.
> >
> > The biggest problem you have there is that you have to go
> through the
> > buiffer pool. It's like a wall. If you had HPL you could
> unload to pipe
> > and load into the new table from the same pipe - improve> your performance by
> > 75% or so.
> >
> > cheers
> > j.
> >
> > > -----Original Message-----
> > > From: Brett Randall [mailto:Brett@reply.to.newsgroup]
> > > Sent: Monday, November 20, 2000 10:45 AM
> > > To: informix-list@iiug.org
> > > Subject: Comments please on performance - table to table copy
> > >
> > >
> > > Comments please on this recorded performance for a table
> to table copy
> > > (insert into <> select * from <>).
> > >
> > > System :-
> > > Intel 4 x Xeon 700
> > > 2 Gb Ram
> > > Acceleraid 352 RAID controller
> > > Linux Redhat 7.0
> > > IDS 9.21
> > >
> > > Forget I/O performance for a minute - no I/O involved in
> the bit I am
> > > interested in.
> > >
> > > Onconfig :-
> > >
> > > No KAIO (platform)
> > > NOAGE=1
> > > NO AFFINITY (platform)
> > >
> > > Important (and constant) - BUFFERS 200000
> > >
> > > Everthing else - tried changing just about everything to
> values within
> > > reason (one change at a time), including :-
> > >
> > > MULTIPROCESSOR (0, 1)
> > > NUMCPUVPS (0->4)
> > > NUMAIOVPS (2->32)
> > > ...> > >
> > > Varied just about everything ...
> > >
> > > Benchmark :-
> > >
> > > UNLOGGED DATABASE !!! (don't want to cloud the issue with logging)
> > >
> > > create table test (char(40));
> > > create table test2(char(40));> > >
> > > <create a text file test.unl, 40 columns of char, 1,000,000 rows>
> > >
> > > load from test.unl insert into test;> > >
> > > (Now, during the following operation, only user on the
> > > system, no matter
> > > what config changes, disk drives are absolutely silent ...)
> > >
> > > insert into test2 select * from test;> > >
> > > During that operation, about 22,000 (44,000 kb, ~44 Mb) of
> > > buffers were
> > > modified. What you'd expect.
> > >
> > > Question - how long should it take on a beefy system?
> Takes more than
> > > ten seconds on the system under test. Is this reasonable
> > > given only CPU
> > > and memory (no disk, I/O) are in use?
> > >
> > > Much appreciated if someone can describe their
> hardware/os/system that
> > > performs better.
> > >
> > > Regards
> > >
> > > Brett Randall
> > >
>