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.
Re: [sqlalchemy] filter vs get question
On Thu, Feb 20, 2014 at 5:22 PM, Jonathan Vanasco jonat...@findmeon.com wrote: this seems to work, but I want to just make sure this is the intended behavior. a = dbSession.query( Something ).filter( Something.primary_key == 1 ).first() b = dbSession.query( Something ).get( 1 ) c = dbSession.query( Something ).get( 1 ) d = dbSession.query( Something ).get( 1 ) e = dbSession.query( Something ).get( 1 ) in the above example , we will only hit the database once , because the 'filter' populates the local session map with the primary key. right ? Only as long as you keep a reference to the object returned by the first query, since the identity map is a weak map. -- 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.
Re: [sqlalchemy] filter vs get question
that's fine. this is for a webapp where we have in a single request : begin; user = .filter().first() DO LOTS OF STUFF, all over the place DO EVEN MORE STUFF , in more places user = .get(user_id) commit; if this behavior is intended, then we can just rely on it for now. otherwise we need to refactor a lot of code to explicitly pass around the User object. until today, we only needed to operate on user_ids , now we need to check a value on the user object in 5 places (only on a particular url). i don't want to hit the db 5x. -- 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.
Re: [sqlalchemy] filter vs get question
On Feb 20, 2014, at 3:22 PM, Jonathan Vanasco jonat...@findmeon.com wrote: this seems to work, but I want to just make sure this is the intended behavior. a = dbSession.query( Something ).filter( Something.primary_key == 1 ).first() b = dbSession.query( Something ).get( 1 ) c = dbSession.query( Something ).get( 1 ) d = dbSession.query( Something ).get( 1 ) e = dbSession.query( Something ).get( 1 ) in the above example , we will only hit the database once , because the 'filter' populates the local session map with the primary key. right ? yes. As long as you maintain a reference to the object outside of the session. I use this pattern when I am dealing with lots of data that has a bunch of many to ones. Suppose Player objects have a many-to-one to a Sport. We have large N number of players and just a handful of Sports. So i do this: sports = set(sess.query(Sport)) # one SELECT. hold onto “sports” for the duration for player in sess.query(Player): # one SELECT # … player.sport # uses get(), no SELECT this is not too different from using “subquery eager loading” except the queries are straight SELECT with no joins or subqueries. signature.asc Description: Message signed with OpenPGP using GPGMail
