Re: Optimizing write performance

On 24 Jun 1998 20:44:01 GMT, newc0571@sable.ox.ac.uk (Stephen Walmsley) wrote: >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. Do you have several dbspaces pr. disk? I assume you do. In that case you have to be very carefull where things are done so that you don't get a lot of head movement between the dbspaces. That could realy kill your performance. Generally it's better to use multiple smaller drives. You should however be able to experiment with the performance benefits of that by running things in parallell as you describe below making sure the data accessed in each process is on one dbspace on one or more disks and each process use data on different disks. With only 5 disks to play with there is of course a significant limit to what you can do. Depending on the disk controller you use there may also be limitations to how much activity you will be able to spread over multiple disks simultaneously. With 7.x you could probably gain significantly in performance by spreading data over more disks and probably also using more controllers and possibly multiple CPU's. >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. This might help a lot. You should monitor the read and in your case the write cashing percentages to get them as high as possible. There are however several other issues with writing that I don't know much about. > 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). If you have set your log tape device to /dev/null there shouldn't be any need for any significant ammount of log space. With no logging all that is logged is the creation of new tables and new extents on existing tables. However with the log tape device set to /dev/null at least our server (using IDS 7.x) free the logs immediately as they get full. There may be a problem with this in 4.x if you have been bitten by it. >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 If you have other indexes than the one on the primary key on the x-table you should drop them here. Instead of creating a table and loading it from the updatefiles have you tried to read the file and do the updates directly. Avoiding the load face *may* make it faster. > 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. Even on 4.10 this should give you parallel execution as you expect. It's well worth trying at least. >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. Mirroring would potentially give you better read performance but no improvment in write performance as you seem to need. > 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. If you do want to do mirroring for some reason I would do it in hardware via an appropriate disk controller that does it all for you. With cach in the diskcontroller you might also get better write performance (without mirroring). However I am not sure it would be any better than more main memory in the machine. >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 can understand this attitude. We now have a very good local support company however that alleviates most of the problems. Also even OpenServer 5.0.4 is a much better platform than 3.2.4.2 was. The new Unixware 7 is even better, but they have to finish up a deasent system management interface before we are particularly interested. You would also have to check with Informix when the 7.3 port will be available. >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. Unfortunately there isn't and trying to run an unsupported SCO port on it wouldn't be a good idea. > 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 have no direct experience with Solaris X86, but have heard good things about it. Again you would have to check with Informix for the 7.3 port. As long as you run no networking, all 4GL applications locally on the Unix server, I don't quite see the big difference however between this and the newer SCO offerings. I wouldn't expect any significant performance difference. In the face of what's happening in the Unix market right now Solaris may however be a better option for the future. Several hardware companies that plan to develop with the new Merced (64 bit) processor from Intel