2

I have a production web server with postgresql database.

My server gets data from another server every hour on the hour. The other server wakes up and sends many requests to my server, each result in an insert/update to the postgresql database.

In order to avoid overloading my server, the requests are queued and handled one at a time.

So typically my server is doing (not many) reads from the database most of the time, and every hour on the hour it runs many inserts and update, one after the other.

The problem I'm experiencing is that once the updates/inserts start, the performance of the database gets very bad for a couple of minutes and then, even though the updates/inserts continue, the performance gets better and stay at a good level until the updates/inserts are finished.

enter image description here

I asked around and got some good directions that may explain the degradation in the performance of the webserver itself, but since I'm seeing that the database's performance also degrades, there might be more to this.

What can explain this behaviour? How can I fix this?

  • How your setup does look like? Are the two servers on the same machine? VMs involved? What sort of disks are there? And what do you see when you monitor the DB activity during the update/insert time? – dezso May 11 '15 at 14:25
  • These are two totally separate servers, my server is in Heroku and so is the postgresql. I don't know about the disks. Heroku won't let me access the DB logs, so I'm not sure what's going on in the database, what I do see is that simple updates run extremely slowly. – davidrac May 11 '15 at 14:45
3

In order to avoid overloading my server, the requests are queued and handled one at a time.

That's the problem right there. You are not avoiding but causing overloading this way. Single row INSERT / UPDATE is dramatically more expensive than doing the same en bloc. Each statement has to be planned and executed separately. Depending on missing details there may be transaction overhead and even connection overhead, too.

Try to run multi-row INSERT / UPDATE commands instead. Or use COPY, or one of the fastest options would be to define a foreign table and fetch all data from a remote DB directly in a single SQL command (if that's at all possible).

Or depending on details, some other replication method may fit your needs.

This is how you update multiple rows at once:

And here is how to do that from a CSV file:

  • The problem is that I am not able to put all the data in with a single call. The other server has a long running process of querying prices and sending them to my server. Each price should update an existing price in my DB or create a new record. I guess I could have the other server issue batch calls. In such a case how will I update/insert multiple rows, each with its own price? – davidrac May 12 '15 at 7:06
  • @davidrac: I added some more links for that. – Erwin Brandstetter May 12 '15 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.