Re: [sqlalchemy] Measure time to checkout a connection from the pool
Thanks Michael. For what it's worth, I ended up doing this: # thread local storage tls = threading.local() # Add custom code to measure the time we spend on the current thread waiting # to get a DB connection from the pool. We do this by defining a decorator # and monkey-patching the relevant function. # https://groups.google.com/forum/#!topic/sqlalchemy/jnulkBclSGk def time_pool_get(function): global tls def wrapper(*args, **kwargs): if not hasattr(tls, 'pool_get_stopwatch'): tls.pool_get_stopwatch = Stopwatch() tls.pool_get_stopwatch.start() try: return function(*args, **kwargs) finally: tls.pool_get_stopwatch.stop() return wrapper from sqlalchemy.pool import QueuePool QueuePool._do_get = time_pool_get(QueuePool._do_get) It's a bit of hack in that: 1. I'm monkey-patching, and 2. I'm monkey-patching an internal function (_do_get). But it does measure what I'm after (I think). --S On Wed, Feb 19, 2014 at 8:15 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Feb 19, 2014, at 9:27 PM, Shahaf Abileah sha...@gmail.com wrote: Hello, I'd like to measure how much time my code spends waiting to check out a connection from the pool. Why? Because I suspect that I have too many workers and not enough connections in the pool, and I'd like to gather some hard numbers to prove my theory one way or the other. I see that there are events I can register for: http://docs.sqlalchemy.org/en/rel_0_9/core/events.html#connection-pool-events. However, off hand I don't see a documented way to get the time spent waiting in checkout. I took a peek at pool.py (I'm using sqlalchemy version 0.9.1) . I see _ConnectionRecord doing the following: def __connect(self): try: self.starttime = time.time() connection = self.__pool._creator() self.__pool.logger.debug(Created new connection %r, connection) return connection except Exception as e: self.__pool.logger.debug(Error on connect(): %s, e) raise Is it safe to use this starttime? Does it measure the time-to-checkout? Or time-to-establish-new-connection-in-pool? Or something else? Is there a better way to get the info I'm looking for? for profiling code you should use the Python profiler module. See http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677for an introduction to using Python profiling with specifics regarding SQLAlchemy. -- http://twitter.com/shahafabileah -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Measure time to checkout a connection from the pool
Hello, I'd like to measure how much time my code spends waiting to check out a connection from the pool. Why? Because I suspect that I have too many workers and not enough connections in the pool, and I'd like to gather some hard numbers to prove my theory one way or the other. I see that there are events I can register for: http://docs.sqlalchemy.org/en/rel_0_9/core/events.html#connection-pool-events. However, off hand I don't see a documented way to get the time spent waiting in checkout. I took a peek at pool.py (I'm using sqlalchemy version 0.9.1) . I see _ConnectionRecord doing the following: def __connect(self): try: self.starttime = time.time() connection = self.__pool._creator() self.__pool.logger.debug(Created new connection %r, connection) return connection except Exception as e: self.__pool.logger.debug(Error on connect(): %s, e) raise Is it safe to use this starttime? Does it measure the time-to-checkout? Or time-to-establish-new-connection-in-pool? Or something else? Is there a better way to get the info I'm looking for? thanks! --Shahaf -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: SQLAlchemy / Memcache layer
I'm looking to do the same (automatic invalidation). Vince, were you able to get it to work? Any tips worth sharing? thanks, --S On Monday, December 13, 2010 5:31:17 PM UTC-8, Vince Ng wrote: Hi, I've been attempting to try to add an abstract layer of Memcache on top of the current version of SQLAlchemy, but I've been running into a few different issues and wanted to get a few thoughts on the best approach on loading objects. I've studied the examples at: http://www.sqlalchemy.org/trac/browser/examples/beaker_caching My issue with these methods are they cache simple queries and relationships, but in a fairly simple way and doesn't account for invalidation. They also seem to require more explicit query calls versus some of the sophisticated lazy-loading property relationship mapping of SQLAlchemy. My goal is to add Memcache at the object layer based on primary key (and to control caching of specific object types, versus caching all queries generated). All ColumnProperty's of an object may be stored in cache, and relationships and RelationshipProperty's are ignored for now. The objects that the relationships point to may be stored in cache on their primary key. If an object is updated/deleted, then the object in cache will be invalidated. Basic Example of What I'd Like to Accomplish: # User has property 'image' which has a 'image_id' foreign key relation to Image object # initial request user = Session.query(User).get(1) # checks memcache for user ID 1 # nothing in memcache, queries DB for user ID 1 (image_id for user 1 is 100) # stores user 1 data in memcache image = user.image # checks memcache for image ID 1 # nothing in memcache, queries DB for image ID 100 # stores image 100 data in memcache # separate subsequent request user = Session.query(User).get(1) # checks memcache for user ID 1, found it! # populate User object with data from memcache image = user.image # checks memcache for image ID 100, found it! # populate Image object with data from memcache image.view_count = image.view_count + 1 Session.commit() # invalidate image ID 100 in memcache I suspect the best way to issue invalidate requests to Memcache will be to create a MapperExtension and use before_update() and before_delete(). However, I'm having much more trouble figuring out where and how I should store data in Memcache and when/how to load from it. MapperExtensions only offer hooks after data has been read from the DB. I've looked into subclassing the Query class and overriding the get() function (as well as a few other functions), but the problem I run into is that I can load the regular columns properties, but will run into issues with lazy-loaded RelationshipProperties with mostly the error: DetachedInstanceError: Parent instance User at 0x7fc7503942d0 is not bound to a Session; lazy load operation of attribute 'images' cannot proceed Any ideas or suggestions? Thanks, Vince -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
