Thread: [SQLObject] load balancing and SQLObject cache

I have a web application with two load-balanced webservers and a separate postgres database server.

When SQLObject instance is updated, the update is processed randomly through either of the servers. 
Sometimes user does the update on one server, and immediately opens object for reading on another. 
So, if caching is on, it looks like database is not getting updated.

If I turn caching off by setting sqlmeta.cacheValues to False, then call to each and every property issues a separate SQL select, and this slows everything down a lot.

What would be the right approach to minimize number of selects on a single webpage? 
Keeping cache on, but calling object.expire(), object.another.expire() after referencing any properties? Trying to lower cullFrequency parameter of cache?

Thank you.

On Tue, Jul 19, 2011 at 12:21:32AM -0300, Егор Следов wrote:
> I have a web application with two load-balanced webservers and a separate postgres database server.
> 
> When SQLObject instance is updated, the update is processed randomly through either of the servers. 
> Sometimes user does the update on one server, and immediately opens object for reading on another. 
> So, if caching is on, it looks like database is not getting updated.
> 
> If I turn caching off by setting sqlmeta.cacheValues to False, then call to each and every property issues a separate SQL select, and this slows everything down a lot.
> 
> What would be the right approach to minimize number of selects on a single webpage? 
> Keeping cache on, but calling object.expire(), object.another.expire() after referencing any properties? Trying to lower cullFrequency parameter of cache?

   In SQLObject there are three ways of caching - attribute caching
(governed by sqlmeta.cacheValues), row caching (governed by
connection.cache), and update caching (governed by sqlmeta.lazyUpdates).
What you probably want is to stop row caching - every time the programs
fetches a row it asks the database, not the cache. So leave
sqlmeta.cacheValues and sqlmeta.lazyUpdates alone (their default values
are good for you) and set connection.cache.doCache=False. You can do it
from DB URI ('postgres://host/db?cache=0'); if the program opens
connection without an URI the parameter for PostgresConnection is
cache=False.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

It does not seem to work. When I set cache to 0 and debug to 1 in uri

DB URI ('postgres://host/db?cache=0&debug=1')

f = db.Person.get(5)
print f.name
f1 = db.Person.get(5)
print f1.name

I don't see second select to the database on second get.
Apparently, the value is fetched from expiredCache.

Any suggestions?

Thank you.

On 7/19/2011 7:34 AM, Oleg Broytman wrote:
> On Tue, Jul 19, 2011 at 12:21:32AM -0300, Егор Следов wrote:
>> I have a web application with two load-balanced webservers and a separate postgres database server.
>> 
>> When SQLObject instance is updated, the update is processed randomly through either of the servers. 
>> Sometimes user does the update on one server, and immediately opens object for reading on another. 
>> So, if caching is on, it looks like database is not getting updated.
>> 
>> If I turn caching off by setting sqlmeta.cacheValues to False, then call to each and every property issues a separate SQL select, and this slows everything down a lot.
>> 
>> What would be the right approach to minimize number of selects on a single webpage? 
>> Keeping cache on, but calling object.expire(), object.another.expire() after referencing any properties? Trying to lower cullFrequency parameter of cache?
> 
>    In SQLObject there are three ways of caching - attribute caching
> (governed by sqlmeta.cacheValues), row caching (governed by
> connection.cache), and update caching (governed by sqlmeta.lazyUpdates).
> What you probably want is to stop row caching - every time the programs
> fetches a row it asks the database, not the cache. So leave
> sqlmeta.cacheValues and sqlmeta.lazyUpdates alone (their default values
> are good for you) and set connection.cache.doCache=False. You can do it
> from DB URI ('postgres://host/db?cache=0'); if the program opens
> connection without an URI the parameter for PostgresConnection is
> cache=False.
> 
> Oleg.

On Tue, Jul 19, 2011 at 09:39:55AM -0300, Егор Следов wrote:
> It does not seem to work. When I set cache to 0 and debug to 1 in uri
> 
> DB URI ('postgres://host/db?cache=0&debug=1')
> 
> f = db.Person.get(5)
> print f.name
> f1 = db.Person.get(5)
> print f1.name
> 
> I don't see second select to the database on second get.
> Apparently, the value is fetched from expiredCache.
> 
> Any suggestions?
> 
> Thank you.

   Expired cache stores weak references. I.e. while the program is
holding a (non-weak) reference to a row the cache will return the same
row. The moment you free the last reference to the row the expired cache
is cleared and next time you will get a new row.
   Get rid of f before getting f1:
del f
   or
f = None
   will do.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

On Tue, Jul 19, 2011 at 05:30:43PM +0400, Oleg Broytman wrote:
>    Expired cache stores weak references. I.e. while the program is
> holding a (non-weak) reference to a row the cache will return the same
> row.

   This is to prevent having two different (unsynchronized) objects to
represent the same row.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.