Re: Optimizing write performance

Many, many thanks to everyone who has followed up to my original posting. Several points have been raised so I will address them here. The hardware is 1 x PII-300, 128MB, 5 x 9GB Wide SCSI-III drives. Bandwidth on one drive is about 7-8MB/sec so I should be doing quite well to saturate 40MB/sec of SCSI bandwidth. Shared memory is currently about 28MB in size but RAM is now so cheap that I intend to upgrade to 256MB and increase the number of buffers considerably. There is no logging on the database. Logical logs are several hundred MB for index builds (I've been bitten hard by the long transaction problem in the past). The basic idea of the code is a bit like this, for table x where x is one of the main storage tables. Typically, x will be about 5GB with 6m rows and the update will have between 100k and 800k rows. sh -c createupdatefiles CREATE primarykeytable LOAD FROM "primarykeys.update.unload" INSERT INTO primarykeytable DELETE FROM x WHERE x.primarykey = primarykeytable.primarykey DROP INDICES ON x LOAD FROM "x.update.unload" INSERT INTO x CREATE INDICES ON x DROP primarykeytable This method seems to be the fastest available to me for very large updates since, inevitably, the INSERT statement runs much, much faster without the indices. For smaller updates it takes less time to leave the indices in place. The fact that the database is either inaccessible or inconsistent while the update is in progress is not a problem. DB networking doesn't exist. All the data are local and all the processes accessing them are local. There is only one tbpgcl process at the moment. How much of an improvement should I get by adjusting LRU_*_DIRTY? Now, my idea was to run these processes for different tables in parallel; having carefully forced certain tables to be on certain disks so that it would be possible to keep several of the disks busy simultaneously. Mirroring is something I'd like to avoid due to cost issues and also since there isn't physical room in the case for very many more disks. As a matter of interest, though, what type of mirroring would be best for 4.10.UC2? Was the internal mirroring any good in that release or is it something that has matured only recently? I did experiment briefly with it once and didn't notice any difference, but it was hardly a thorough evaluation. Will it really manage the two writes to two drives in parallel? I can't see how that could happen without spindle synchronisation. Okay then, next question. We use Online 4.10.UC2, ISQL 4.10 and I4GL RDS 4.10. Suppose I am able to convince my superiors that to sort these big updates out what they really need is a shiny new 7.30 upgrade to escape from the horrors of an unsupported version of Online and also from SCO 3.2.4.2. What is the best platform upgrade path? Given my experience of SCO I am, to put it mildly, not at all keen to deploy *any* product from that company again but that narrows down the available options somewhat. I'm rather happy with the way Linux performs amazingly well with almost any X86 hardware you throw at it; this makes for great flexibility and cost-effectiveness in upgrades/expansions. But of course there isn't a port to it. Looking at the sales pitch for Solaris X86, it seems that this is pretty flexible when it comes to hardware (i.e. it works with commodity PC parts) so what do experienced hands make of this as an Online platform? I could probably just install this straight onto the current hardware. Or is there anything else (i.e. non-Intel) that I should be looking at? The prices for Online and the tools look pretty steep so I suppose they might dwarf the cost of the hardware and O/S anyway, making my superiors' leaning towards commodity PC bits look a bit spurious. Stephen