lockstat is available in Solaris 9. That can help you to determine if
there are any kernel level locks that are occuring during that time.
Solaris 10 also has plockstat which can be used to identify userland
locks happening in your process.
Since you have Solaris 9, try the following:
You can run (as root)
lockstat sleep 5
and note the output which can be long.
I guess "prstat -am" output, "iostat -xczn 3", "vmstat 3" outputs will
help also.
prstat -am has a column called "LAT", if the value is in double digits,
then you have a locking issue which will probably result in higher "SLP"
value for the process. (Interpretation is data and workload specific
which this email is too small to decode)
Once you have identified a particular process (if any) to be the source
of the problem, get its id and you can look at the outputs of following
command which (quite intrusive)
truss -c -p $pid 2> truss-syscount.txt
(Ctrl-C after a while to stop collecting)
truss -a -e -u":::" -p $pid 2> trussout.txt
(Ctrl-C after a while to stop collecting)
Regards,
Jignesh
Jerry Sievers wrote:
Hello;
I am going through a post mortem analysis of an infrequent but
recurring problem on a Pg 8.0.3 installation. Application code
connects to Pg using J2EE pooled connections.
PostgreSQL 8.0.3 on sparc-sun-solaris2.9, compiled by GCC sparc-sun-solaris2.8-gcc (GCC) 3.3.2
Database is quite large with respect to the number of tables, some of
which have up to 6 million tuples. Typical idle/busy connection ratio
is 3/100 but occationally we'll catch 20 or more busy sessions.
The problem manifests itself and appears like a locking issue. About
weekly throuput slows down and we notice the busy connection count
rising minute by minute. 2, 20, 40... Before long, the app server
detects lack of responsiveness and fails over to another app server
(not Pg) which in turn attempts a bunch of new connections into
Postgres.
Sampling of the snapshots of pg_locks and pg_stat_activity tables
takes place each minute.
I am wishing for a few new ideas as to what to be watching; Here's
some observations that I've made.
1. At no time do any UN-granted locks show in pg_locks
2. The number of exclusive locks is small 1, 4, 8
3. Other locks type/mode are numerous but appear like normal workload.
4. There are at least a few old '<IDLE> In Transaction' cases in
activity view
5. No interesting error messages or warning in Pg logs.
6. No crash of Pg backend
Other goodies includes a bounty of poor performing queries which are
constantly being optimized now for good measure. Aside from the heavy
queries, performance is generallly decent.
Resource related server configs have been boosted substantially but
have not undergone any formal R&D to verify that we're inthe safe
under heavy load.
An max_fsm_relations setting which is *below* our table and index
count was discovered by me today and will be increased this evening
during a maint cycle.
The slowdown and subsequent run-away app server takes place within a
small 2-5 minute window and I have as of yet not been able to get into
Psql during the event for a hands-on look.
Questions;
1. Is there any type of resource lock that can unconditionally block
another session and NOT appear as UN-granted lock?
2. What in particular other runtime info would be most useful to
sample here?
3. What Solaris side runtime stats might give some clues here
(maybe?)( and how often to sample? Assume needs to be aggressive
due to how fast this problem crops up.
Any help appreciated
Thank you
