re: HPL performance tuning

For those that are interested, a great deal of time is spent discussing HPL performance in the Advanced Performance Tuning Masters class..... Of course, express is much faster than deluxe mode for loads, as it bypasses much of the inner workings of the engine. I'm not sure how it works with unloads, however. To optimize, get your flat files across as many physical devices as possible - either a striped file system, or (preferably) use a device array. This makes a HUGE difference. PLCONFIG parameters: CONVERTVPS - 1 / cpu CONVERTTHREADS - 2 / device (output devices) AIOBUFFSIZE - make it relatively big STRMBUFFSIZE - 1 or 2 *AIOBUFFSIZE STRMBUFFERS - CONVERTTHREADS +4 AIOBUFFERS - max (CONVERTTHREADS+4, 2*CONVERTTHREADS) for loads, make sure that SHMVIRTSIZE is big in onconfig, to allow the express mode to use light appends. Turn logging off, if possible, during the load. Force a checkpoint before beginning the load. Drop indices before loading. Make sure the first extent is sized appropriately. For any load operation, change the LRU_MAX_DIRTY, and LRU_MIN_DIRTY to high values. Really high. This is one case where chunk write are very good. Carlson@WHSmith wrote: > I'm doing some benchmarking with HPL. Just out-of-the-box, I've noticed > that there is a noticeable difference in unloading data, and it seems to > carry over in the data loads. One thing: From a practical point of > view, how can I tune it to go faster, whether unloads or loads? I've > toyed with the express vs. deluxe; I'm looking for PLCONFIG variables > that (probably) need to be tuned. Any ideas? > > Thanks in advance > John Carlson > Informix DBA > WHSmith USA

In article <381059CC.239E5BE@cornell.edu>, Vic Goldberg <vg19@cornell.edu> writes >For those that are interested, a great deal of time is spent discussing HPL >performance in the Advanced Performance Tuning Masters class..... > I'm doing large data conversions at the moment. What are the group recommendations. These are unloads from an SE 4.x database on another machine so no select from oldb:oldserver:tablename! The table sizes are:- 5 tables (>1 million rows) 15 table (>100,000 rows) It is worth using the high performance loader in Deluxe mode to allow loading? E.g. for the biggest 5 tables? Can someone give me a quick 10 steps guide to loading SQL unload files (unload to myfile.unl select * from myfile)? What ONCONFIG parameters would you set? >For any load operation, change the LRU_MAX_DIRTY, and LRU_MIN_DIRTY to high >values. Really high. This is one case where chunk write are very good. > OK, 95/99? How far to go? I'll have a) no logging/empty tables with no indexes. b) load data c) index d) dostats -d <database name> I'll have PSORT_NPROCS=max(2,<number of cpus in machine>) PSORT_MAXALLOC=10240 PDQPRIORITY=100 Would you increase buffers or SHMVIRTSIZE since this is both data loading and indes building with one ONCONFIG? -- David Williams