topic Re: sys$update service works very slow sometimes. in Operating System - OpenVMS

sys$update service works very slow sometimes.

Sergejus Zabinskis — Tue, 03 Jan 2006 09:18:51 GMT

Hello,
Sorry for very long story - I couldn't describe my situation briefly.
We have client/server system working on 3 nodes cluster.
(On each node we have the group of processes that makes server side of system)
Server side use one one indexed variable length file to store results of queries.
The primary key of this file is:
xab.xab$b_ref = 0;
xab.xab$b_dtp = XAB$C_STG;
xab.xab$w_pos0 = 0;
xab.xab$b_siz0 = sizeof(__int64)+sizeof(int)+sizeof(short int);

where the first key member __int64 represents query ID, int - record number, short int - field number.
There are no alternate keys for this file.

We can divide server side processes to 2 groups:
1. One process (let call it server) that has GET access to above mentioned file.
2. About 5 processes running the same image (let call them servants)
that have GET+PUT+UPDATE access.

So we have about 18 processes totaly on cluster that have access to file.

Now, I'll try to describe how system works from two points of view:
server and client.

Note: Queries are distributed between nodes with load balancer.

From serving side:
==================
external client issues query to server,
server supplies query with clusterwide unique ID
and says servant to create "master" record
for this query and dispatches query to some processes
that do something (for example execute some "select" type statement).
After query is completed server receives result of query:
one or more records. Server sends the result of query to servant
and servant puts result to file (here field record number starts from 1 and field number too).

For example, if result of query contains 3 records with 2 fields each servant will put
to file something like:

ID recordno fieldno value
1 1 1 aaa
1 1 2 bbbb
1 2 1 ccccc
1 2 2 dddddd
1 3 1 eeeeeee
1 3 2 ffffffff

The first three fields make primary key.

After the last record of query result is put to file server says servant to update "master" record for
completed query in order to inform external client that query is completed and it may get result record(s).

From client side:
=================
After client issued query and got it's ID it permanently (with some timeout) polls server
to know what is status of the query (completed or not yet) - server (one of its threads)
gets query "master" record and decides whether query is completed or not.
(Server's threads that look for "master" record connect to file with
rab.rab$v_rrl = 1; rab.rab$v_nlk = 1; rab.rab$v_nql = 1;)
Clients are written by our company developers or in 3rd party companies - so sometimes they
set polling timeout very small to 20 times per second.

Problem:
========
Sometimes we see that update works very very slow (>1 min).
Primary key and record length are not changed.
Some additional information: we have approximately 10000 queries/day and 30-50 very slow updates.

Thanks in advance.

Kind regards
Sergejus

Re: sys$update service works very slow sometimes.

Wim Van den Wyngaert — Tue, 03 Jan 2006 09:49:59 GMT

Can't read the fdl (not .txt).
But try reading this
http://groups.google.be/group/comp.os.vms/browse_frm/thread/256cc269ad20d255/2437d086254f304a?lnk=st&q=index+slow+vms+hein&rnum=5&hl=en#2437d086254f304a

If you Google for help on RMS files, always include HEIN to find the good articles.

Wim

Re: sys$update service works very slow sometimes.

Sergejus Zabinskis — Tue, 03 Jan 2006 10:00:51 GMT

IDENT "21-DEC-2005 11:08:53 OpenVMS FDL Editor"

SYSTEM
SOURCE "OpenVMS"

FILE
CONTIGUOUS no
FILE_MONITORING no
GLOBAL_BUFFER_COUNT 200
ORGANIZATION indexed

RECORD
BLOCK_SPAN yes
CARRIAGE_CONTROL none
FORMAT variable
SIZE 1024

AREA 0
ALLOCATION 253265
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 7
EXTENSION 63294

AREA 1
ALLOCATION 2877
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 7
EXTENSION 2877

KEY 0
CHANGES no
DATA_AREA 0
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DATA_RECORD_COMPRESSION yes
DUPLICATES no
INDEX_AREA 1
INDEX_COMPRESSION yes
INDEX_FILL 100
LEVEL1_INDEX_AREA 1
NAME "Index TaskID+recno+fieldno"
NULL_KEY no
PROLOG 3
SEG0_LENGTH 14
SEG0_POSITION 0
TYPE string

Re: sys$update service works very slow sometimes.

Hein van den Heuvel — Tue, 03 Jan 2006 12:04:19 GMT

Wim>> Can't read the fdl (not .txt).

I could read it, but then I have associated .FDL files with a text exditor on my windoze box.
Still, in general it seems nice to label textual attachments as .TXT, whether they are .FDL, .C, .DAT, .LOG or whatever, and most notably for .COM files.

Sergejus... how about the ANALYZE section from the FDL for a production file? That will give a few more important details.

Wim>> If you Google for help on RMS files, always include HEIN to find the good articles

Thanks :-).

Sergejus>> xab.xab$b_siz0 = sizeof(__int64)+sizeof(int)+sizeof(short int);

In other questions we already established that this is 'risky business' for sequential access as the key will not honor the intended sorting, but that is probably not relevant here.

>> rab.rab$v_rrl = 1; rab.rab$v_nlk = 1; rab.rab$v_nql = 1;)

Good! Someone figured out teh newish NQL bit. Minor comment... while it makes not difference in the execution i would not set RRL nor NLK when NQL is active as the extra, ignored, bits only confuse the readers. Again, probably not relevant here.

>> set polling timeout very small to 20 times per second.

That is very rapid and could cause race conditions. You may want to read up on, and experiment with SET RMS/CONTENTION_POLICY

http://h71000.www7.hp.com/doc/82FINAL/9996/9996pro_215.html#brass_86

If the application has nothign better to do than the hard poll you may want to look at keeping the record locked while not ready and using a soft loop with rms record locking using ROP=TMO+WAT, RAB$B_TMO=10

Do you know for sure it is the SYS$UPDATE that is slow, and not the $FIND or $GET leading up to it to lock the record?

Have you analyze RMS STATS for the file while active?

SET FILE/STAT [/SHARE] xxx
MONI RMS/FILE=xxx/ITEM....
or ANAL/SYS... SET PROC ... SHOW PROC/RMS=FSB
or my RMS_STATS tool from teh VMS Freeware.

Hope this helps,
Hein.

Re: sys$update service works very slow sometimes.

Wim Van den Wyngaert — Tue, 03 Jan 2006 13:27:13 GMT

Hein,

Shouldn't you get rid of the HP label now ?
Or would that put your points back to zero ?

If the problem is really as descrived, I never seen a reaction time of 1 minuut for 1 RMS operation unless you have a locking problem.

Once had a "migrating secondary" problem on HP3000 that read a million records. In a few seconds.

Wim

Re: sys$update service works very slow sometimes.

Sergejus Zabinskis — Wed, 04 Jan 2006 02:48:20 GMT

ANALYSIS_OF_AREA 0
RECLAIMED_SPACE 0

ANALYSIS_OF_AREA 1
RECLAIMED_SPACE 0
Here is analysis section:
ANALYSIS_OF_KEY 0
DATA_FILL 59
DATA_KEY_COMPRESSION 63
DATA_RECORD_COMPRESSION 38
DATA_RECORD_COUNT 3655567
DATA_SPACE_OCCUPIED 582316
DEPTH 3
INDEX_COMPRESSION 36
INDEX_FILL 69
INDEX_SPACE_OCCUPIED 2541
LEVEL1_RECORD_COUNT 83188
MEAN_DATA_LENGTH 55
MEAN_INDEX_LENGTH 17

Re: sys$update service works very slow sometimes.

Hein van den Heuvel — Wed, 04 Jan 2006 08:00:44 GMT

How frequently is this file converted?
Maybe not often enough...

It's a bit of a stretch, but those slow updates could be causing bucket splits.

The first part of the strech is that the update would have to grow effective record size after compression for example due to chaning a field full of nulls or spaces to a result value.

The second part of the stretch is that you would have needed to accumulate a good few "RRV" = Record Retriavel Vectors in the buckets, pointing to many different buckets, to make this noticable.

The full story it too long to write up here, but as a bucket splits to make room for new or expanded records then those records that are being moved will leave behind a 7 byte RRV pointing to their new address. If such moved record gets moved again, then old RRV gets updated to the new-new locations.
This normally happens to a bunch of records at the same time, but over time many RRV buckets could be needed.
Analysis tools:
1) VMS FREEWARE, like my RMS_TUNE_CHECK to count RRVs
2) Instrument your SYS$UPDATES with LIB$SHOW_TIMER to count IOs (and time)
3) ANAL/RMS/INT drilling down to buckets holding the record that was slow to update.
4) SET FILE/STAT/SHARE ... monitor splits

Here is what I would:

- convert the file more frequently
- edit the FDL to match the actuall space need
- increase bucket size from 7 to 12 to lower root level to 2 and make the split more bulk liek when they happen. And they will happen due to the random new record distribution. (Or chage data area to 8 and index to 16)
- change index fill from 100 to 80
- probably leave data fill at 100%... it is going to hurt no matter what
- possibley disable index compression.
- disable data record compression (leave data key compression)

be happy.

Cheers,
Hein.

Re: sys$update service works very slow sometimes.

Sergejus Zabinskis — Wed, 04 Jan 2006 09:03:25 GMT

Hein,
thanks a lot for your help. It will take some time to investigate and apply your advices because we can stop system till weekend. I leave this thread open and will come back as soon as something new will happen.

Many thanks
Sergejus

Re: sys$update service works very slow sometimes.

Hein van den Heuvel — Wed, 04 Jan 2006 10:45:57 GMT

>> I leave this thread open and will come back as soon as something new will happen.

Feedback will be much appreciated as my reply had a lot of speculation to be confirmed or denied.

Be sure to keep (a opy of) the old file around after the convert for detail analysis and/or a dedicated test.

For example, if you find buckets with lots of RRVs from many originating buckets, then you may want to measure (lib$show_timer and/or SDA> SHOW PROC/RMS=FSB) a single $PUT with a key in the middle of that bucket to see how fast/slow such split might be. Mind, you, active bucket lock contention, even from readers with no record locks, may seriously impact the bucket split times and you would not have that based just on a test file.

Good luck,
Hein.

Re: sys$update service works very slow sometimes.

Sergejus Zabinskis — Wed, 04 Jan 2006 16:39:27 GMT

Dear Hein,
I and my admins highly appreciate your help. Believe me - I will come back with my story and results. This night our admins will restart application with new datafile created upon your recomendations (i believe). I am sorry - I can't now discuss your advices - I feel they are right ....

Going forward I think I'll do something like messaging exchange between servers operating on cluster nodes.

Many thanks
Sergejus

Re: sys$update service works very slow sometimes.

Sergejus Zabinskis — Thu, 05 Jan 2006 10:34:47 GMT

Hi,
this night system was restarted. We created new empty data file with fdl as follows:

++++++++++++++++++++++++++++++++++++++++++
IDENT " 4-JAN-2006 18:10:24 OpenVMS FDL Editor"

SYSTEM
SOURCE "OpenVMS"

FILE
CONTIGUOUS no
FILE_MONITORING no
GLOBAL_BUFFER_COUNT 200
ORGANIZATION indexed

RECORD
BLOCK_SPAN yes
CARRIAGE_CONTROL none
FORMAT variable
SIZE 1024

AREA 0
ALLOCATION 346320
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 12
EXTENSION 65520

AREA 1
ALLOCATION 1512
BEST_TRY_CONTIGUOUS yes
BUCKET_SIZE 12
EXTENSION 504

KEY 0
CHANGES no
DATA_AREA 0
DATA_FILL 100
DATA_KEY_COMPRESSION yes
DATA_RECORD_COMPRESSION no
DUPLICATES no
INDEX_AREA 1
INDEX_COMPRESSION yes
INDEX_FILL 80
LEVEL1_INDEX_AREA 1
NAME "Index TaskID+recno+fieldno"
NULL_KEY no
PROLOG 3
SEG0_LENGTH 14
SEG0_POSITION 0
TYPE string

-----------------------------------------

Timeouts didn't disappear.

Here is rms_tune_check output:
$ rms_tune_check -t -s=-1 jup_diskw1:[dmlnew]dml_server.tbl_task
- RRVS: 181951 moved records in 237830 record sample. 76.0%. Convert! Smaller fill?

and monitoring summary for three nodes OMNITL, OMNITM, OMNITE:

OpenVMS Monitor Utility
RMS FILE OPERATIONS
on node OMNITL From: 5-JAN-2006 10:16:54
SUMMARY To: 5-JAN-2006 17:00:01
(Index) $1$DGA8478:[DMLNEW]DML_SERVER.TBL_TASK;4
Active Streams: 30 CUR AVE MIN MAX

$GET Call Rate (Seq) 0.00 0.33 0.00 5.00
(Key) 0.00 0.30 0.00 4.66
(RFA) 0.00 0.00 0.00 0.00
$FIND Call Rate (Seq) 0.00 0.00 0.00 0.00
(Key) 0.00 0.00 0.00 0.00
(RFA) 0.00 0.00 0.00 0.00
$PUT Call Rate (Seq) 0.00 0.00 0.00 0.00
(Key) 0.00 0.19 0.00 5.00
$READ Call Rate 0.00 0.18 0.00 639.66
$WRITE Call Rate 0.00 0.00 0.00 0.00
$UPDATE Call Rate 0.00 0.25 0.00 4.66
$DELETE Call Rate 0.00 0.00 0.00 0.00
$TRUNCATE Call Rate 0.00 0.00 0.00 0.00
$EXTEND Call Rate 0.00 0.00 0.00 0.00
$FLUSH Call Rate 0.00 0.00 0.00 0.00

SUMMARIZING

OpenVMS Monitor Utility
RMS DATA RATE STATISTICS
on node OMNITL From: 5-JAN-2006 10:16:54
SUMMARY To: 5-JAN-2006 17:00:01
(Index) $1$DGA8478:[DMLNEW]DML_SERVER.TBL_TASK;4
Active Streams: 30 CUR AVE MIN MAX

Total $GET Call Rate 0.00 0.63 0.00 9.00
Bytes Per $GET 0.00 63.29 0.00 84.00
Total $PUT Call Rate 0.00 0.19 0.00 5.00
Bytes Per $PUT 0.00 57.98 0.00 90.00
$UPDATE Call Rate 0.00 0.25 0.00 4.66
Bytes Per $UPDATE 0.00 61.34 0.00 84.00

$READ Call Rate 0.00 0.18 0.00 639.66
Bytes Per $READ 0.00 6144.00 0.00 6144.00
$WRITE Call Rate 0.00 0.00 0.00 0.00
Bytes Per $WRITE 0.00 0.00 0.00 0.00
$TRUNCATE Call Rate 0.00 0.00 0.00 0.00
Blocks Per $TRUNCATE 0.00 0.00 0.00 0.00
$EXTEND Call Rate 0.00 0.00 0.00 0.00
Blocks Per $EXTEND 0.00 0.00 0.00 0.00

SUMMARIZING

OpenVMS Monitor Utility
RMS FILE LOCKING STATISTICS
on node OMNITL From: 5-JAN-2006 10:16:54
SUMMARY To: 5-JAN-2006 17:00:01
(Index) $1$DGA8478:[DMLNEW]DML_SERVER.TBL_TASK;4
Active Streams: 30 CUR AVE MIN MAX

New ENQ Rate 0.00 1.48 0.00 27.33

DEQ Rate 0.00 1.45 0.00 27.00

Converted ENQ Rate 0.00 0.53 0.00 10.33

Blocking AST Rate 0.00 0.00 0.00 0.00

Bucket Split Rate 0.00 0.00 0.00 0.33

Multi Bucket Split Rate 0.00 0.00 0.00 0.00

SUMMARIZING

OpenVMS Monitor Utility
RMS CACHE STATISTICS
on node OMNITL From: 5-JAN-2006 10:16:54
SUMMARY To: 5-JAN-2006 17:00:01
(Index) $1$DGA8478:[DMLNEW]DML_SERVER.TBL_TASK;4
Active Streams: 30 CUR AVE MIN MAX

Local Cache Hit Percent 0.00 1.15 0.00 80.00

Local Cache Attempt Rate 0.00 2.12 0.00 40.33

Global Cache Hit Percent 0.00 90.01 0.00 100.00

Global Cache Attempt Rate 0.00 2.09 0.00 39.33

Global Buf Read I/O Rate 0.00 0.18 0.00 4.66

Global Buf Write I/O Rate 0.00 0.43 0.00 9.00

Local Buf Read I/O Rate 0.00 0.02 0.00 2.33

Local Buf Write I/O Rate 0.00 0.04 0.00 3.00

SUMMARIZING

OpenVMS Monitor Utility
RMS FILE OPERATIONS
on node OMNITM From: 5-JAN-2006 10:16:54
SUMMARY To: 5-JAN-2006 17:00:01
(Index) $1$DGA8478:[DMLNEW]DML_SERVER.TBL_TASK;4
Active Streams: 30 CUR AVE MIN MAX

$GET Call Rate (Seq) 0.00 0.32 0.00 5.33
(Key) 0.00 0.29 0.00 4.33
(RFA) 0.00 0.00 0.00 0.00
$FIND Call Rate (Seq) 0.00 0.00 0.00 0.00
(Key) 0.00 0.00 0.00 0.00
(RFA) 0.00 0.00 0.00 0.00
$PUT Call Rate (Seq) 0.00 0.00 0.00 0.00
(Key) 0.00 0.20 0.00 4.00
$READ Call Rate 0.00 0.17 0.00 607.00
$WRITE Call Rate 0.00 0.00 0.00 0.00
$UPDATE Call Rate 0.00 0.25 0.00 5.00
$DELETE Call Rate 0.00 0.00 0.00 0.00
$TRUNCATE Call Rate 0.00 0.00 0.00 0.00
$EXTEND Call Rate 0.00 0.00 0.00 0.00
$FLUSH Call Rate 0.00 0.00 0.00 0.00

SUMMARIZING

OpenVMS Monitor Utility
RMS DATA RATE STATISTICS
on node OMNITM From: 5-JAN-2006 10:16:54
SUMMARY To: 5-JAN-2006 17:00:01
(Index) $1$DGA8478:[DMLNEW]DML_SERVER.TBL_TASK;4
Active Streams: 30 CUR AVE MIN MAX

Total $GET Call Rate 0.00 0.62 0.00 9.66
Bytes Per $GET 0.00 63.30 0.00 84.00
Total $PUT Call Rate 0.00 0.20 0.00 4.00
Bytes Per $PUT 0.00 57.97 0.00 90.00
$UPDATE Call Rate 0.00 0.25 0.00 5.00
Bytes Per $UPDATE 0.00 61.51 0.00 84.00

$READ Call Rate 0.00 0.17 0.00 607.00
Bytes Per $READ 0.00 6144.00 0.00 6144.00
$WRITE Call Rate 0.00 0.00 0.00 0.00
Bytes Per $WRITE 0.00 0.00 0.00 0.00
$TRUNCATE Call Rate 0.00 0.00 0.00 0.00
Blocks Per $TRUNCATE 0.00 0.00 0.00 0.00
$EXTEND Call Rate 0.00 0.00 0.00 0.00
Blocks Per $EXTEND 0.00 0.00 0.00 0.00

SUMMARIZING

OpenVMS Monitor Utility
RMS FILE LOCKING STATISTICS
on node OMNITM From: 5-JAN-2006 10:16:54
SUMMARY To: 5-JAN-2006 17:00:01
(Index) $1$DGA8478:[DMLNEW]DML_SERVER.TBL_TASK;4
Active Streams: 30 CUR AVE MIN MAX

New ENQ Rate 0.00 1.46 0.00 22.00

DEQ Rate 0.00 1.43 0.00 22.00

Converted ENQ Rate 0.00 0.52 0.00 16.33

Blocking AST Rate 0.00 0.00 0.00 0.00

Bucket Split Rate 0.00 0.00 0.00 0.33

Multi Bucket Split Rate 0.00 0.00 0.00 0.00

SUMMARIZING

OpenVMS Monitor Utility
RMS CACHE STATISTICS
on node OMNITM From: 5-JAN-2006 10:16:54
SUMMARY To: 5-JAN-2006 17:00:01
(Index) $1$DGA8478:[DMLNEW]DML_SERVER.TBL_TASK;4
Active Streams: 30 CUR AVE MIN MAX

Local Cache Hit Percent 0.00 1.19 0.00 70.00

Local Cache Attempt Rate 0.00 2.10 0.00 31.66

Global Cache Hit Percent 0.00 90.07 0.00 200.00

Global Cache Attempt Rate 0.00 2.07 0.00 28.33

Global Buf Read I/O Rate 0.00 0.18 0.00 3.66

Global Buf Write I/O Rate 0.00 0.42 0.00 7.00

Local Buf Read I/O Rate 0.00 0.02 0.00 3.00

Local Buf Write I/O Rate 0.00 0.04 0.00 3.00

SUMMARIZING

OpenVMS Monitor Utility
RMS FILE OPERATIONS
on node OMNITE From: 5-JAN-2006 10:16:54
SUMMARY To: 5-JAN-2006 17:00:01
(Index) $1$DGA8478:[DMLNEW]DML_SERVER.TBL_TASK;4
Active Streams: 30 CUR AVE MIN MAX

$GET Call Rate (Seq) 0.00 0.33 0.00 5.33
(Key) 0.00 0.29 0.00 4.00
(RFA) 0.00 0.00 0.00 0.00
$FIND Call Rate (Seq) 0.00 0.00 0.00 0.00
(Key) 0.00 0.00 0.00 0.00
(RFA) 0.00 0.00 0.00 0.00
$PUT Call Rate (Seq) 0.00 0.00 0.00 0.00
(Key) 0.00 0.18 0.00 4.00
$READ Call Rate 0.00 0.00 0.00 0.00
$WRITE Call Rate 0.00 0.00 0.00 0.00
$UPDATE Call Rate 0.00 0.25 0.00 5.00
$DELETE Call Rate 0.00 0.00 0.00 0.00
$TRUNCATE Call Rate 0.00 0.00 0.00 0.00
$EXTEND Call Rate 0.00 0.00 0.00 0.00
$FLUSH Call Rate 0.00 0.00 0.00 0.00

SUMMARIZING

OpenVMS Monitor Utility
RMS DATA RATE STATISTICS
on node OMNITE From: 5-JAN-2006 10:16:54
SUMMARY To: 5-JAN-2006 17:00:01
(Index) $1$DGA8478:[DMLNEW]DML_SERVER.TBL_TASK;4
Active Streams: 30 CUR AVE MIN MAX

Total $GET Call Rate 0.00 0.62 0.00 9.33
Bytes Per $GET 0.00 63.26 0.00 84.00
Total $PUT Call Rate 0.00 0.18 0.00 4.00
Bytes Per $PUT 0.00 58.00 0.00 90.00
$UPDATE Call Rate 0.00 0.25 0.00 5.00
Bytes Per $UPDATE 0.00 61.13 0.00 84.00

$READ Call Rate 0.00 0.00 0.00 0.00
Bytes Per $READ 0.00 0.00 0.00 0.00
$WRITE Call Rate 0.00 0.00 0.00 0.00
Bytes Per $WRITE 0.00 0.00 0.00 0.00
$TRUNCATE Call Rate 0.00 0.00 0.00 0.00
Blocks Per $TRUNCATE 0.00 0.00 0.00 0.00
$EXTEND Call Rate 0.00 0.00 0.00 0.00
Blocks Per $EXTEND 0.00 0.00 0.00 0.00

SUMMARIZING

OpenVMS Monitor Utility
RMS FILE LOCKING STATISTICS
on node OMNITE From: 5-JAN-2006 10:16:54
SUMMARY To: 5-JAN-2006 17:00:01
(Index) $1$DGA8478:[DMLNEW]DML_SERVER.TBL_TASK;4
Active Streams: 30 CUR AVE MIN MAX

New ENQ Rate 0.00 1.46 0.00 25.33

DEQ Rate 0.00 1.44 0.00 25.00

Converted ENQ Rate 0.00 0.60 0.00 15.00

Blocking AST Rate 0.00 0.00 0.00 0.33

Bucket Split Rate 0.00 0.00 0.00 0.33

Multi Bucket Split Rate 0.00 0.00 0.00 0.00

SUMMARIZING

OpenVMS Monitor Utility
RMS CACHE STATISTICS
on node OMNITE From: 5-JAN-2006 10:16:54
SUMMARY To: 5-JAN-2006 17:00:01
(Index) $1$DGA8478:[DMLNEW]DML_SERVER.TBL_TASK;4
Active Streams: 30 CUR AVE MIN MAX

Local Cache Hit Percent 0.00 2.76 0.00 66.00

Local Cache Attempt Rate 0.00 2.07 0.00 30.66

Global Cache Hit Percent 0.00 89.78 0.00 120.00

Global Cache Attempt Rate 0.00 2.01 0.00 30.00

Global Buf Read I/O Rate 0.00 0.18 0.00 4.33

Global Buf Write I/O Rate 0.00 0.42 0.00 8.00

Local Buf Read I/O Rate 0.00 0.02 0.00 2.33

Local Buf Write I/O Rate 0.00 0.03 0.00 3.00

SUMMARIZING

Re: sys$update service works very slow sometimes.

Hein van den Heuvel — Thu, 05 Jan 2006 12:05:04 GMT

Well, too bad, no easy fix.

The rms stats number are helpful, but very diluted due to the time window.
The READs in there are probably from tune_check.

If that tune_check output is after just one day, having started with a fresh file then it would seem safe to say there is a bucket split problem.

But I'm not sure how this could be on the update, if teh record is no longer growing. Maybe contention still plays a role, but the lock stats do not suggest that.

You may not be able to avoid splits due to the random insert behaviour.
You MIGHT be able to play tricks like for a new task to insert a large (2000 bytes?) dummy record first to create lots of room for the adjacent 60-ish bytes sub-records and then delete the helper or update it to become the normal size.

Or you may need to re-organize the file to get a primary key with a slowly increasing number and make what is now the primary key become an alternate. The primary key coudl be a binary record creation time stamp, with dups allowed.

How many local buffer to you use? Maybe you need many more than normal (try 20 or 40)? Easy enough to change with SET RMS/IND/BUF...

Be sure to try ANAL/SYS .. SHOW PROC/RMS=FSB for futher understanding.

And you may need a detail file analysis, with respect to the internal structures (ANAL/RMS/INTERACTIVE).

I'm afraid this is starting get beyond the scope of quick advice in the Forum. Send me an Email if serious help is needed.

Cheers,
Hein.

Re: sys$update service works very slow sometimes.

Sergejus Zabinskis — Wed, 11 Jan 2006 10:52:57 GMT

Hi,
You were right Hein - I was wrong when I said that sys$update is slow. It was $GET! And when I got that I very quickly found the bug in my program - missed lock. So now it works fine - no timeouts. Sorry for taking your time. As for us - we gained a lot from this discussion.

Thanks a lot
Sergejus

Re: sys$update service works very slow sometimes.

Sergejus Zabinskis — Wed, 11 Jan 2006 10:54:43 GMT

locking problem