Login | Register
My pages Projects Community openCollabNet

Discussions > dev > Re: [propel-dev] Query counting

propel
Discussion topic

Back to topic list

Re: [propel-dev] Query counting

Reply

Author Cameron Brunner <cameron dot brunner at gmail dot com>
Full name Cameron Brunner <cameron dot brunner at gmail dot com>
Date 2007-07-14 04:36:04 PDT
Message Just a note...

TODO: add debug_backtrace() into it to have a what called this method
in the first place to cause this query to run
count the total transactions ran in the query (ideally should stay at 1)
make a nice interface for displaying them both on console and in html
(automatically colorcode queries based on performance, automatic
gradient upto red for the slowest queries)
actually test all the different ways to query pdo
... any requests? auto EXPLAIN ANALYZE and stuff it into the debug
output as well?


oh, found a bug in query() where it wasn't using the new phase stuff i
wrote just now, propel doesn't use that normally tho so this patch is
still fine with a standard propel setup, feel free to try it and see
what you think so far...

On 7/14/07, Cameron Brunner <cameron.brunner@​gmail.com> wrote:
> Try this one on for size :) Tested code even!
>
> On 7/14/07, Cameron Brunner <cameron.brunner@​gmail.com> wrote:
> > On 7/14/07, Oliver Schonrock <oliver at realtsp dot com> wrote:
> > > Cameron Brunner wrote:
> > > > A - We need to store the entire query in memory if we are to do
> > > > duplicate query detection.
> > >
> > > true, however (connected with what you say in D) the
> > > PDOStatement->queryString property only gives us the prepared statement
> > > sql not the final, "all params substituted" sql. This is a limitation in
> > > PDO and probably for good reason since the param substitution is
> > > supposed to happen in the DB server for some backends (not mysql due to
> > > query cache limitations).
> > >
> > > This means that we do not, and will not for some time, have access to
> > > the final sql string. Does "duplicate detection" make sense in this
> > > case? Is it a valid feature if we are only looking at prepared
> > > statements? There will be many "duplicated" queiries for
> > > retrieveByPk(1|2|3) for example....
> >
> > Good point, time to tear the prepared statement parser out of Creole
> > then. Duplicate query detection is a big thing in my books.
> >
> > > > B - If we dont, we incur quite an overhead to do func_get_args and
> > > > call_user_func etc so if we dont we pay a penalty even in production
> > > > hence the way i did that
> > >
> > > hmm..good point. i did some simple tests over 1000 calls:
> > >
> > > plain call a(1,2,3,4): 1.5ms
> > > plain call $a->a(1,2,3,4): 1.6ms
> > > call_user_func_array('a', array(1,2,3,4)): 3.6ms
> > > call_user_func_array(array($a, 'a'), array(1,2,3,4)): 4.6ms
> > >
> > > big percentage increase, still low given that every db query takes ~1ms
> > > no matter how trivial. But worth considering when we think about how to
> > > implement these calls. I will revisit this and rewrite as a native call.
> > >
> > > > C - true, specially if you have unbuffered queries enabled, its just a
> > > > bit of a guide on what returns a lot of rows. We could also add a
> > > > memory usage check onto the dtor, that would be about when all rows
> > > > are hydrated... we could also add the true flag to the memory usage
> > > > functions, i dont know the difference tho
> > >
> > > i am not sure the memory_usage thing is very useful due to these
> > > limitations. Should perhaps be dealt with at a profiling level further
> > > out than PDO or even outside Propel.
> >
> > Agreed that its better to do it with real profiling but a quick
> > snapshot while a system is still in development without having to jump
> > through hoops is certainly a bonus in my books. To get a better memory
> > usage we can add a memory check in the dtor for the statement and that
> > should kick in when the objects are done with hydration... ?
> >
> > > > D - We can still add these into PropelPDO and since DebugPropelPDO
> > > > extends it that will run automatically still... nice and seamless and
> > > > easy... i have had propel's name cursed at work since we couldnt see
> > > > the final sql that was being ran easily (having to replace the ?'s by
> > > > hand)
> > >
> > > I am trying to clarify what features we can realistically hope to
> > > achieve and where they should sit. In my opinion, while duplicate query
> > > detection and memory consumption are desirable, they are not really
> > > possible given where we are measuring. That then leaves query timing and
> > > counting.
> > >
> > > As far as I can see these can be easily added to the existing
> > > PropelPDO.php while adding a PropelPDOStatement.php. Making sure that we
> > > use efficient call mechanisms for the extensions.
> > >
> > > Counting could be driven by a $debug switch (although I would prefer a
> > > setter rather than a public property).
> >
> > Granted, static method would be preferable but it was a quick patch
> > (half hr before work)
> >
> > > Timing could be switched on if logging is enabled and $debug=true. The
> > > time written to the log with the sql string since these belong together
> > > and there doesn't seem to be a case for keeping the sql in memory.
> > >
> > > Moving logging to exec time rather than prepare poses several problems
> > > with backward compat and persistent connections. I will prepare a new
> > > patch which considers these options and suggests a solution, bearing
> > > performance in mind.
> >
> > Persistent connections are something that I personally discourage in
> > any situation, is there any databases that really need this these days
> > to get optimal performance? The overhead for mysql and postgres is
> > quite minimal... sqlite is the only real db that i know gains
> > something from persistence.
> >
> > > > Always plenty of options, i have been wanting a way to automatically
> > > > profile an application in development and when a 'slow' or 'expensive'
> > > > (memory wise) query is ran then to display it. I was considering a
> > > > Smarty debug style popup to be able to be enabled...
> > >
> > > we have profiling information automatically at bottom of every
> > > non-production page, just in the html. it works well.
> > >
> > > hope we are getting closer.
> > >
> > > Oliver
> > >
> > >
> > >
> >
> > You're not the only one who hopes we are getting closer to the right
> > solution here. I'm bored at the moment so i'm about to play around
> > with this and maybe more work on the PropelDateTime stuff.
> >
> > --
> > Cameron Brunner
> >
> > Want a better web browser?
> > http://www.spreadfir​efox.com/?q=affiliat​es&id=182780​&t=1
> >
>
>
> --
> Cameron Brunner
>
> Want a better web browser?
> http://www.spreadfir​efox.com/?q=affiliat​es&id=182780​&t=1
>
>


--
Cameron Brunner

Want a better web browser?
http://www.spreadfir​efox.com/?q=affiliat​es&id=182780​&t=1

« Previous message in topic | 21 of 36 | Next message in topic »

Messages

Show all messages in topic

translation cysio Krzysio 2007-07-10 09:28:18 PDT
     Re: [propel-dev] translation hlellelid Hans Lellelid 2007-07-10 09:52:07 PDT
         Query counting Oliver Schonrock <oliver at realtsp dot com> Oliver Schonrock <oliver at realtsp dot com> 2007-07-12 05:38:54 PDT
             Re: [propel-dev] Query counting hlellelid Hans Lellelid 2007-07-12 05:43:28 PDT
                 Re: [propel-dev] Query counting Oliver Schonrock <oliver at realtsp dot com> Oliver Schonrock <oliver at realtsp dot com> 2007-07-12 06:40:03 PDT
                 Re: [propel-dev] Query counting Oliver Schonrock <oliver at realtsp dot com> Oliver Schonrock <oliver at realtsp dot com> 2007-07-12 11:25:28 PDT
                     Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-12 14:27:16 PDT
                         Re: [propel-dev] Query counting hlellelid Hans Lellelid 2007-07-12 14:29:11 PDT
                             Re: [propel-dev] Query counting hlellelid Hans Lellelid 2007-07-12 14:32:11 PDT
                                 Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-12 14:34:25 PDT
                                     Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-12 15:02:17 PDT
                                         Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-12 15:04:01 PDT
                         Re: [propel-dev] Query counting Oliver Schonrock <oliver at realtsp dot com> Oliver Schonrock <oliver at realtsp dot com> 2007-07-13 01:47:24 PDT
                             Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-13 04:03:01 PDT
                                 Re: [propel-dev] Query counting Pieter Nobels <pieter at opengate dot be> Pieter Nobels <pieter at opengate dot be> 2007-07-13 04:10:27 PDT
                                 Re: [propel-dev] Query counting hlellelid Hans Lellelid 2007-07-13 06:55:53 PDT
                                     Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-14 01:26:36 PDT
                                 Re: [propel-dev] Query counting Oliver Schonrock <oliver at realtsp dot com> Oliver Schonrock <oliver at realtsp dot com> 2007-07-13 09:32:08 PDT
                                     Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-14 01:40:29 PDT
                                         Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-14 04:22:35 PDT
                                             Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-14 04:36:04 PDT
                                                 Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-16 15:48:32 PDT
                                                     Re: [propel-dev] Query counting Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-07-17 18:53:07 PDT
                                                     Re: [propel-dev] Query counting Oliver Schonrock <oliver at realtsp dot com> Oliver Schonrock <oliver at realtsp dot com> 2007-07-19 04:40:28 PDT
                                                         Re: [propel-dev] Query counting hlellelid Hans Lellelid 2007-07-19 06:42:12 PDT
Page: of 2 « Previous | Next »
Messages per page: