Hello,
We quite recently moved our production database from 8.4 to 9.3. Its been just 2 months and the features which it allows for parallel backup and recovery has been a great use for us.
But with 9.3 we have also run into a lot of troubles.
Some background:
We run loaders to load data into the DB. These loaders are perl programs which run on a different server than the db server. The amount of data loaded each day is pretty high.
Problem:
Some of the loaders are performing in a bad way. they get stuck or EOF happens on client connection and things just dies. There is one piece which gets stuck daily ... but once killed runs normal.
One of the episode of today one of the loader got stuck for 2hr 15 min. I killed the whole process, restarted it and things ran within 10 mins. What I observed was the DB load hadnt changed or shifted significantly between the killing and the end of the program. At first we were thinking it was loaders but now it looks as if its the postgres database.
The piece where it was stuck for 2hrs was it was trying to insert 37K rows in a 500K row table, which is pretty small considering PG potential. But the bottleneck here we cant solve it.
Kindly help me find the solution, or the right place to dig into to find the exact problem, its been troubling some of our loaders for quite some time now.
Please let me know if you need further information regarding the settings on our DB server.
--
Regards
Dhruv