Performance Analysis and Troubleshooting

This is a page to assist with performance analysis and troubleshooting.

Monitoring Deadlocks in PostgreSQL

You can monitor any database locks using the Entity SQL Processor in Webtools -> Entity SQL Processor with the following query,

select pg_class.relname, pg_locks.mode, pg_locks.relation, pg_locks.transaction, pg_locks.pid
    from pg_class, pg_locks 
    where pg_class.relfilenode = pg_locks.relation 
    order by pid

This will show what kinds of locks are active on what entities. If there is an exclusive lock on a table followed by a bunch of pids that are waiting for it, then you have a deadlock.

Suspending Runaway Threads

Suppose you start a process that you realize will take forever and need to stop it. However, it can't be stopped because it was activated by an HTTP request and killing the browser session doesn't work. First, check the log to see if you can identify the thread that is running this process. For instance, suppose you have the following line in your log that corresponds to what your process is doing,

2008-01-23 18:55:47,585 (TP-Processor10) [       Something.java:1015:WARN ] Something that identifies your process

This thread is TP-Processor10. You can use the Java Thread API to suspend it by hand. The easiest way to do this is to use a bsh script or the bsh terminal. First, you will want to know the number of threads in the system. Load up Webtools -> Jobs -> Thread List and count the rough number of threads displayed. Suppose you have about 50 threads.

Once you know the rough size, run the following script, either via the bsh terminal or by hooking it up to a controller.xml request,

threads = new Thread[50];
size = Thread.enumerate(threads);
for (i = 0; i < size; i++) {
    print(i + ": " + thread);
}

This will print out the index and name of each thread. Find the index of TP-Processor 10. Suppose it is index 37. You can then suspend the thread by doing this,

t = threads[37];
t.suspend();