CODEWITHSUNDEEP
pythondatabasequeue
Bite code
Bite code

Reputation: 596723

How to ensure several Python processes access the data base one by one?

I got a lot scripts running: scrappers, checkers, cleaners, etc. They have some things in common:

Accumulating them, it's starting to slow down the website, which runs on the same system, but depends on these scripts.

I can use queues with Kombu to inline all writtings.

But do you know a way to make the same with reading ?

E.G: if one script need to read from the DB, his request is sent to a blocking queue, et it resumes when it got the answer ? This way everybody is making request to one process, and the process is the only one talking to the DB, making one request at the time.

I have no idea how to do this.

Of course, in the end I may have to add more servers to the mix, but before that, is there something I can do at the software level ?

Upvotes: 0

Views: 194

Answers (3)

tback
tback

Reputation: 11561

You say that your dataset is <1GB, the problem is CPU bound.

Now start analyzing what is eating CPU cycles:

  • Which queries are really slow and executed often. MySQL can log those queries.
  • What about the slow queries? Can they be accelerated by using an index?
  • Are there unused indices? Drop them!
  • Nothing helps? Can you solve it by denormalizing/precomputing stuff?

Upvotes: 1

01100110
01100110

Reputation: 2344

You could create a function that each process must call in order to talk to the DB. You could re-write the scripts so that they must call that function rather than talk directly to the DB. Within that function, you could have a scope-based lock so that only one process would be talking to the DB at a time.

Upvotes: 0

kgr
kgr

Reputation: 9948

You could use a connection pooler and make the connections from the scripts go through it. It would limit the number of real connections hitting your DB while being transparent to your scripts (their connections would be held in a "wait" state until a real connections is freed).

I don't know what DB you use, but for Postgres I'm using PGBouncer for similiar reasons, see http://pgfoundry.org/projects/pgbouncer/

Upvotes: 1

Related Questions