Question How to find "User Load" on server?

[Mucip]

Hi,
We've Progress 10.1b on Linux (CentOS 6) server. Sometimes we have heavy load on server.

But we can not understan that which user making this load/query?...

Is there way to find out instant user load?... If yes than I can ask to user "What are you doing now?".

Regards,
Mucip

 

[Cringer]

Have a look into http://dbappraise.com/protop.html as it will be very useful to you IMO.

 

[Mucip]

Hi Cringer,
Thanks a lot for your fast replay...
I checked the site but I saw below warning:

You must have Progress OpenEdge 10.2B or better to run ProTop version 3. This release of ProTop has not been tested on any earlier version of Progress. If it works on earlier releases it will be considered a miracle.

But I have 10.1b version... Any problem?

Regards,
Mucip

 

[Mucip]

Hi,
I follow all instruction from the webside. The input screen labeled "ProTop 3 PATHs" shown on screen. I press enter and program stop... Nothing more! I affraid problem comes from my Progress version maybe? And there is no information in /tmp directory!...


┌─────────────────── ProTop 3 PATHs ───────────────────┐
│ │
│ OpenEdge: /usr/dlc │
│ ProTop 3: /mnt/ProTop │
│ Log Files: /tmp │
│ Temp Files: /tmp │
│ │
└──────────────────────────────────────────────────────┘



Regards,
Mucip

 

[Rob Fitzpatrick]

You can download the character client version of ProTop version xxi from DBAppraise, it will run fine on 10.1B.

 

[Rob Fitzpatrick]

You can also try to find out yourself which users are placing this load on the database by querying the _TableStat and _IndexStat VSTs, if you can write some ABL code.

Note that either approach, ProTop code or your own code, requires that you have the -tablerangesize and -indexrangesize database startup parameters set correctly. They should be set equal to or higher than your highest table number and your highest index number respectively. If you don't set them explicitly then you only get stats for the first 50 tables and the first 50 indexes in the database, which likely won't be too useful.

 

[TomBascom]

And, of course, 10.1B is ancient, obsolete and notverysupported. You should upgrade.

ProTop (any release) will also show you who is driving the traffic via the "User IO" screen. The -tablerangesize and -indexrangesize parameters are needed to see which tables and indexes those users are accessing, but the basics of who is behind the activity are available even without that.

 

[Mucip]

Hi,
This is my first time in this forums and I am very glad to see this interest to my question...

Well, I can write ABL no problem. Can I get little bit trick or code samples to get information of users who are behind the database activity?...

And I have houndereds of table in database. Should I change the database parameters to get my goal and do this paramater change negative effect to my database performance?...


Regards,
Mucip

 

[TomBascom]

Adding -tablerangesize and -indexrangesize will use some memory but it will not directly impact performance.

To determine what values to use:

define variable t as integer no-undo.
define variable i as integer no-undo.

for each _file no-lock where _file-num > 0 and _file-num < 10000:
  t = t + 1.
end.

for each _index no-lock:
  i = i + 1.
end.

display t i.
pause.

ProTop really will save you a lot of time though -- putting together a useful monitor isn't a simple task. Just download the ProTop xxi source rather than ProTop 3 -- it says that it is for v9 but it works for everything.

 

[Rob Fitzpatrick]

Depending on how often your schema changes (e.g. adding table and indexes) and how often you are able to restart your database (to change the startup parameter values) you may want to give yourself a bit of a buffer above the values you get from Tom's example code above. I like to set the values high so I still have complete stats even if I add a few tables or indexes after the last DB start.

Note that there is a subtlety you may encounter if you have "churn" in your schema, i.e. it changes often like in a development DB: you could have non-contiguously numbered tables and indexes. So what you really need to ensure you have complete stats is to have the parameter values set equal to or greater than the highest-numbered table and index, rather than the total count of them which may be lower.

I count this way:

/* show the highest application table number and index number */

for each _file no-lock where _file._tbl-type = "T" by _file._file-number descending:
  display _file._file-number with frame a.
  leave.
end.

for each _index no-lock where not _index._index-name begins "_" by _index._idx-num descending:
  display _index._idx-num with frame b.
  leave.
end.

Then I add 20 or 30 to those values, depending on what type of database it is, and use those numbers for -tablerangesize and -indexrangesize. If your numbers are too low you will be missing some stats. If they are too high, it's no big deal.

 

[Mucip]

Hi,
Thanks a lot to all...

I can restart the database anytime I want... No problem...

But as I uderstoot, above code shows table load... I need to see instant load depends on user. Can you give info about this?

And I will try to run ProTop Character version too...

Regards,
Mucip

 

[Mucip]

Hi,
Thanks a lot to all...

I can restart the database anytime I want... No problem...

But as I uderstoot, above code shows table load... I need to see instant databse load depends on user. Can you give info about this?

And I will try to run ProTop Character version too...

Thanks a lot for your kind interest.

Regards,
Mucip

 

[Cringer]

I think you're missing the point a little. That code is to work out what the -tablerangesize and -indexrangesize startup parameters should be so that you can accurately find what you are after.

 

[Mucip]

Hi Cringer,
Ok... Thanks...

Regards,
Mucip

 

[Rob Fitzpatrick]

As Tom said, once you get ProTop running you can use the User IO screen to show a list of connected users and their CRUD stats. That will tell you who the problem is, and you don't need the -tablerangesize and -indexrangesize set for that. But to fix the problem you next need to know which code is being run by the problem user(s).

There are a few ways to approach this, although some of them are not available to you on an older release like 10.1B. You can look at the stats from _TableStat and _IndexStat to see which objects are being accessed most frequently in aggregate across the database, so if you know the application that may give you some clue. But if you now know which user to look at (from the User IO screen in ProTop or from the _UserIO VST) you can determine which tables and indexes that specific user is accessing by querying the _UserTableStat and _UserIndexStat VSTs or by using the screens in ProTop: Table Usage - Specific User and Index Usage - Specific User.

Another simple approach you can take on the client side, knowing who the client it, is to add the -yx client startup parameter to that client's command line. This will log statistics about the programs they run to a file called proc.mon in their working directory, once their client exits. It can help you to narrow down which specific programs they are running. An even simpler approach is to just ask the user what they're doing. And if you can sit by their desk with a laptop and watch ProTop as they run code, it should become pretty obvious to you when they run a program that is a rapid reader.

 

[Mucip]

Hi,
I downloaded ProTop xxi version as you adviced. But I'm not so good on character gui on linux unfortunately.

When I open protop.p in pro editor and try to RUN then error message appear like below:

┌─────────────────────── Error ────────────────────────┐
│ Unknown database name dictdb. (855) │
│ Unknown database name dictdb. (855) │
│ Unknown or ambiguous table _File. (725) │
│ ** lib/vstlib.p Could not understand line 86. (196) │
│ │
│ ──────────────────────────────────────────────────── │
│ <OK> │
└──────────────────────────────────────────────────────┘
What should I do? Where should I write my Database name?

And what happen if I connect 3 databases in same time?...

Regards,
Mucip

 

[TomBascom]

You don't do it that way.

ProTop works with one database at a time. There is a script in the "bin" directory -- bin/protop which takes a single argument: the path to the db that you want to monitor. So, for instance:

bin/protop /db/dbName

would start ProTop and monitor the database found at /db/dbName.

 

[Mucip]

Hi,
But I am using xxi version. not the new one?...

Regards,
Mucip

 

[TomBascom]

Both versions have a bin/protop script and work the same way in so far as startup is concerned.

 

[Mucip]

Hi,
I just could run the xxi version after than your guide. Thanks...

But all I saw on the screen is below. I coldn't see the instant user load?

01:06:33 ProTop xx -- Progress Database Monitor 26/10/13
Sample uyum2005 [/progress/UyumData/uyum2005/uyum2005] Rate
Hit Ratio: 0:0 0:0 Commits: 0 0 Sessions: 6
Miss% : 0,000% 0,000% Latch Waits: 0 0 Local: 1
Hit% : 100,000% 100,000% Tot/Mod Bufs: 64002 0 Remote: 0
Log Reads: 2 3 Evict Bufs: 0 0 Batch: 2
OS Reads: 0 0 Lock Table: 1000000 0 Server: 1
Rec Reads: 0 1 LkHWM|OldTrx: 2 00:00:00 Other: 4
Log/Rec: 4,30 4,33 Old/Curr BI: 0 0 TRX: 0
Area Full: 0 77,38% After Image: Enabled Blocked: 0

Regards,
Mucip