I had experienced a similar problem with pgsql. You may have some really inefficient statements utilizing all of your resources. A good starting place might be to turn on query logging and then use the app and create a deadlock. Then run all of your queries (that you have logged) manually in psql one at a time, paying close attention to the time they take to execute and the output from EXPLAINing them. In my case I had a handful of queries that had tremendous costs... after some careful rewriting and testing the system works great now.
-r
At 03:42 PM 8/14/01 -0400, Gordon Campbell wrote:
This is my first posting to this site, but it's my last resort. We're running 7.0.3. I have 20 servlets that make use of our postgres database. All of the programs are able to perform their assigned SQL tasks, but not on a consistent basis. Often and unpredictably, different programs performing different functions (add, delete, update) will essentially hang. I will view the process (ps -ef) and postgres will be in an (commit, delete or update) state but does not finish. All others wishing to perform a task either via the browser or at command line (psql) will hang. The only way I can free up the process is having our sysadmin "kill" the process.
The ten tables contain less than 400 rows. We are in test mode and will go into production after this "show stopper" is resolved.
I have run "vacuum" which also hangs on one table that contain 382 rows. I am the sole tester, so I am not clashing with other persons. The servlets each make their own connection using the postgres' jdbc driver. The connections are closed at the end of the program. These programs are very simple insert, update, select statements.
Has anybody else had this problem? What debugging methods/tools/logs will help.
This is a very small development shop, so I'm throwing this out to a larger community for the first time.
