CODEWITHSUNDEEP
ruby-on-railsdatabaseperformancepostgresqlheroku
MarceloJ
MarceloJ

Reputation: 135

Rails DB Performance with several workers

I'm working on an application that works like a search engine, and all the time it has workers in the background searching the web and adding results to the Results table.

While everything works perfectly, lately I started getting huge response times while trying to browse, edit or delete the results. My guess is that the Results table is being constantly locked by the workers who keep adding new data, which means web requests must wait until the table is freed.

However, I can't figure out a way to lower that load on the Results table and get faster respose times for my web requests. Has anyone had to deal with something like that?

The search bots are constantly reading and adding new stuff, it adds new results as it finds them. I was wondering if maybe by only adding the bulk of the results to the database after the search would help, or if it would make things worse since it would take longer.

Anyway, I'm at a loss here and would appreciate any help or ideas.

I'm using RoR 2.3.8 and hosting my app on Heroku with PostgreSQL

Upvotes: 0

Views: 207

Answers (3)

Frank Heikens
Frank Heikens

Reputation: 127126

PostgreSQL doesn't lock tables for reads nor writes. Start logging your queries and try to find out what is going on. Guessing doesn't help, you have to dig into it.

To check the current activity:

SELECT * FROM pg_stat_activity;

Upvotes: 2

markquezada
markquezada

Reputation: 8535

You might want to put a cache in front of the database. On Heroku you can use memcached as a cache store very easily.

This'll take some load off your db reads. You could even have your search bots update the cache when they add new stuff so that you can use a very long expiration time and your frontend Rails app will very rarely (if ever) hit the database directly for simple reads.

Upvotes: 1

Chuck Callebs
Chuck Callebs

Reputation: 16441

Try the NOWAIT command. Since you're only adding new stuff with your background workers, I'd assume there would be no lock conflicts when browsing/editing/deleting.

Upvotes: 1

Related Questions