Re: Optimizing write performance

Stephen Walmsley wrote: > > Further to the recent discussion about the relative merits of mirroring, > RAID etc., what if I had a database which is updated in bulk and queried > in a really trivial, computationally cheap way and therefore for which I > wanted to maximize insert, update and delete speed but really wasn't > bothered about select speed? > > It's SCO 3.2.4.2 / Online 4.10.UC2 so fragmenting is not an option. > Several tables are huge (i.e. of the order of 4G "data bytes" and 1.3 > million rows). Short of upgrading, what's the best thing to do? I thought > of using plain old raw partition allocation and arranging for the chunk > allocation to happen in such a way that each of the big tables reside on > seperate disks and then do the batch inserts, deletes and updates across > multiple tables simultaneously. Would this give me a speed increase > commensurate with keeping two or three disks busy instead of just one? > > I suspect RAID and Mirroring are out because of their degraded write > performance. Any ideas anyone? Hi, first I guess it's a good idea to spread the huge table across several disks and perform the i/o in parallel mode. It's also possible to use modern RAID 5 systems with a large cache buffer onboard. Second, you should set the number of CLEANER processes to the number of disks. Finally, increase the LRU_MAX_DIRTY and LRU_MIN_DIRTY parameters to about 90 / 80 ( optimized checkpoint writes ). Most interesting are the programs that will perform the I/O. -> Use prepared statements to reduce the amount of optimizing time. -> Use Insert Cursors to minimize the network traffic, even if you are using pipe-connections. -> whenever possible prefer buffered logged database over unbuffered databases. -> check whether the I/O is balanced across the disks by using "sar -d 5 1000" during your updates,deletes and inserts. Just a few examples of mine: 1st example - took 2min40secs FOR i = 1 TO 30000 INSERT INTO TABLE VALUES( SOMETHING ) 2nd example - took 1min30secs PREPARE x FROM "INSERT INTO TABLE VALUES( SOMETHING )" FOR i = 1 TO 30000 EXECUTE x USING :valuelist 3rd example - took 29secs PREPARE x FROM "INSERT INTO TABLE VALUES( SOMETHING )" DECLARE xy CURSOR FOR x FOR i = 1 TO 30000 PUT xy FROM :valuelist Without logging it took only 6seconds. This is just an example - but it demonstrates the overhead for optimizing and communication between client/server programs. The test ran with client and server on the same machine, so we didn't use a real network. Bye Stefan Weideneder