RE:Performance Advice

Firstly may I thank you for reading all of my e-mail and taking an interest, any advice you may give is welcome. The problem mentioned reared its ugly head again today. I had 18 users on the system and each user seemed to have ground to a stand still. The jobs were starting off OK and then appeared to go into a state of sleeping forever (as reported by the status of the onstat -g sees <sess_id>). The system appeared to be context switching between each user. i.e. client A would process 100 inserts then go to sleep again whilst client B awoke from its sleep and carried on. Lock waits on the server which has been up and running for 4 days now since a system reboot on Saturday shows 13. Bear in mind though that these jobs although running concurrently are all reading a separate source and writing the output to newly created target databases. The jobs are written in Informix ESQL/C. Another point that is worth consideration is that these jobs can be rerun to populate only a portion of the created target database and the result is a large transaction, as a hole is made in the population by deleting 100s of old records, prior to repopulating. Latch waits however are 34,000+ which probably indicates that memory may be a problem. However when I follow this with an onstat -b to show active buffers the wait list shows 0 on nearly all buffers. Buffer waits is also quite high at about 26,000. Maybe it is memory but surely the initial flag of an onstat -u command would be B and not Y. The percentage reads and writes to buffers is quite high approx. 90% each. It is probably also worth mentioning that over the last 4 days an additional 2 segments(8192) for the virtual portion of share memory have been claimed by the server. I will check the session pool size to see how much shared memory is being used by these population jobs, but is it not the case that additional memory will be claimed automatically if this proves to be a problem to the server provided that the operating system doe not have a limit on the amount of segments that can be allocated. Interestingly enough when the amount of users dropped to approx. 9 still suffering from the performance problem I issued an onmode -F to free any unused pools and none where freed. Maybe this is the bottleneck? I'll check ipcs and Kernel Shared Memory parameters again tomorrow. The server profile(onstat -p) reports ovtbls, ovlock, ovuserthread , ovbuff as 0 As for the checkpoint interval, I have not analysed this yet but I expect it to be quite high so maybe the picture we are painting here is a memory bottleneck where repeated checkpoints are slowing down the system an the solution presumably is to increase the physical log buffer and the logical log buffer. Any suggestions. But why does an onstat -u not show a C as the initial flag if the checkpoints are causing a problem. OPTCOMPIND is set to 2 which regardless of the isolation level should give best performance based on costs. If this is not the case please let me know your thoughts. I can try timing the programs by setting the Environment variable prior to running if you think its worth a shot. INDEXING I agree with you point about indexing and I had considered removing them and then adding after the inserts had finished but the problem is that these jobs add so much data the tables then will reread these tables in order to construct another record for another table so the selects and inserts are interleaved throughout the program. Maybe setting a lower fill factor on these indexes may help? Any thoughts about the PSORT_NPROCS, PSORT_DBTEMP environment variables. PDQ No we do not use PDQ as we only have a single processor machine and therefore I felt that we would not gain any additional benefit. SHARED MEMORY AND LOCAL DISKS Yes the data is being written to local disk but as I said we cannot used shared memory as the client applications use concurrent transactions which can only be used across a TCP/IP connection. Thanks for listening Bill As I say any comments suggestions are welcome, if you need any more info let me know. I'll be plugging away at it again tomorrow. -----Original Message----- From: Bill Ennis [SMTP:ennis@ssax.com] Sent: Tuesday, June 17, 1997 10:15 AM To: "L.Shaw" Subject: Re: Performance advice Hi, Looks like you've done alot of reseach but from reading I'm unable to determine where the bottleneck is. Can you categorize it as: 1) Memory 2) I/O 3) CPU What is OPTCOMPIND set to? Are you using PDQ? Running shared memory and local disks will help; but you said yu can't run through shared mem. How many users did you have previously? Just for the record: Indexes will slow down data loads. What is your checkpoint interval? (see online.log) Bill