RE: Comments please on performance - table to table copy

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 >

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