how to check performance?

[dsspen]

Hi,

Plafform:
SUN solaris 7 512MB, 400Mhz x 2
Progress 8.3d enterprise

I do not have the knowledge if my database is performing well, we did experience some slowness but I do not know where/how to tune it.
below is my sample:
ctivity - Sampled at 04/16/01 09:40 for 0:35:12.

Event Total Per Sec Event Total Per Sec
Commits 98470 46.6 Undos 11 0.0
Record Updates 109030 51.6 Record Reads 1694336 802.2
Record Creates 113165 53.5 Record Deletes 112972 53.4
DB Writes 74189 35.1 DB Reads 123102 58.2
BI Writes 16089 7.6 BI Reads 748 0.3
AI Writes 0 0.0
Record Locks 1863260 882.2 Record Waits 1 0.0
Checkpoints 231 0.1 Buffers Flushed 73796 34.9

Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %
Writes by APW 0 % Writes by BIW 0 % Writes by AIW 0 %
Buffer Hits 97 %
DB Size 1081216 K BI Size 10752 K AI Size 0 K
FR chain 0 blocks RM chain 292 blocks
Shared Memory 42000 K Segments 3

I believe my -B which is set to -B 5000 (8k blksize) is quite ok as my Buffer hits is often 95 - 97 %.
However I feel that my checkpoints is quite high and my commits is low. I also measure my cpu rate using "sar" and the cpu idle time is high.

Does anyone has comments on the above?
thanks in advance.

 

[Gabor Topor]

Hi,

Before furher studying I recommend to start 1 BIW and n APW processes (where n is the number of disks used for you DB + 1)
Have you set -spin start-up parameter?
What is you BI block and cluster size?

Strange thing is your Record Reads are closely to Record Locks, so it seems no-lock option is not used in you application !

Regards,
Gabor

 

[shawnw]

What is the command to get this info?

 

[Gabor Topor]

Hi,

Yo can find it

- in your database log file (.lg) after multi-session begin message or
- in promon go to Database Status (7)

Regards,
Gabor

 

[dsspen]

Thanks Gabor for your reply...
I have changed and added 2APWs and set my spin to 1000.
My bi cluster has been changed from 512 to 1024 and my bi blocksize is 8192.

After doing the above, it is true that my Buffers flushed has gone down to zero which is good.
My cpu idle time has also reduced.
However my users cannot feel the speed at all.
I tho. if my database has been fairly tuned, then the problem could have been the applications.

What do you think?



Activity - Sampled at 04/25/01 09:21 for 0:48:34.

Event Total Per Sec Event Total Per Sec
Commits 971 0.3 Undos 22 0.0
Record Updates 1590 0.5 Record Reads 3914741 1343.4
Record Creates 706 0.2 Record Deletes 177 0.0
DB Writes 1339 0.4 DB Reads 20345 6.9
BI Writes 434 0.1 BI Reads 4 0.0
AI Writes 0 0.0
Record Locks 87137 29.9 Record Waits 0 0.0
Checkpoints 1 0.0 Buffers Flushed 0 0.0

Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %
Writes by APW 99 % Writes by BIW 0 % Writes by AIW 0 %
Buffer Hits 99 %
DB Size 1100416 K BI Size 13312 K AI Size 0 K
FR chain 6 blocks RM chain 79 blocks
Shared Memory 42172 K Segments 3

4 Servers, 38 Users (1 Local, 37 Remote, 1 Batch),2 Apws


Thanks in advance.

 

[Gabor Topor]

Hi,

The DB server looks nice (except I could not see BIW process) so
we can examine op. system and client side.

op.system
- what is # vmstat 5 output (or similar sar command)?
- what is your block size of your file system/DB?
- are you using multi-volume DB?


clients
- as I can see you have remote clients. Are those Win32 clients?
- what are the startup parameters?
- what about your network?

Regards,
Gabor

 

[Unregistered]

Hi,

This is my vmstat ...seems like cpu is idle again...perhaps there's not much activities going on

procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s6 s7 -- in sy cs us sy id
0 0 0 10672 11488 0 2 56 25 54 0 5 5 0 0 0 257 915 353 2 2 96
0 0 0 1717368 7824 0 1 0 3 40 0 5 0 0 0 0 227 719 456 0 1 98
0 0 0 1717368 7848 0 0 0 8 8 0 0 1 0 0 0 231 707 448 0 1 99
0 0 0 1717352 7840 0 0 28 1 22 0 3 14 0 0 0 383 1104 627 0 2 97

My blocksize of my db is 8192 and actually, how to check file system?..(i believe it shld be 8192 as well) and i m not using multi-vol. disks.
For the clients, we are using WIN32 client and my startup param is as follows:
# PF-TYPE = R "mydb Run" no
#
# SESSION,Session-related parameters
-D 100
-TB 29
-TM 15
-inp 5000
-mmax 4096
-Mm 4096
-nb 200
-q
-pls
-rand 2
-s 31
-yy 1930
-d dmy
#
# MAIN,cei
-db mydb
-ld symix

# -cache x:\temp\symix.csh
-B 2000
-H dbsever
-S mydb
-N TCP


Appreciate ur help again.

 

[Gabor Topor]

Hi,

I could not see serious problem, so I can recommend only the followings:

1. Check you PROPATH on you client side
2. Check you network traffic
3. You may use/test smaller network buffers (-Mm)
4. -B has no effect on clients side
5. Check/set you temporary directory (-T should point to a local drive)
6. You may limit the number of clients per server process (-Ma 3)
7. If your application use a lot of shared variables using -noshvarfix client parameter can improve overall performance (details in http://www.progress.com/services/support/cgi-bin/techweb-kbase.cgi/webkb.html?kbid=20235)


Hope this helps


Regards,
Gabor

 

[dsspen]

Hi Gabor,

thanks for ur analysis and help . I appreciate it.

Rdgs,