Hi,
It is great to see that some DTrace probes finally goes into the MySQL 6.0 src
tree...
I'm wondering if there is any plan to insert more probes into the storage
engine layer as well? so that we can measure the time spending on the defined
events in addtion to the count of the events which we can get from the other
monitor tools, such as:
- wait on innodb flush logs(flush_log_start/done)
- innodb dirty buffer flush(innodb_dirty_buffer_flush/done)
- MyISAM table locks (myisam_wrlock_start/done)
- row locks(row_lock_start/done)
- innodb buffer wait(reading page synchronous from disk)
- innodb spin wait(innodb_spin_wait_start/done)
- innodb os wait(innodb_os_wait_start/done)
- rollback (rollback_start/done)
and also:
- index/table scan(index_scan_start/done, table_scan_start/done)
- binlog cache spill to disk(binlog_cache_flush/done)
Thanks,
Luojia(Jenny) Chen
Software Engineer
ISV Engineering
v-mail:(510) 550-2394
SUN Microsystems
----- Original Message -----
From: Martin MC Brown <[EMAIL PROTECTED]>
Date: Saturday, January 26, 2008 12:17 pm
Subject: Re: [dtrace-discuss] DTrace for MySQL?
To: Brendan Gregg - Sun Microsystems <[EMAIL PROTECTED]>
Cc: [email protected], Alex Peng <[EMAIL PROTECTED]>
> Hi Brendan,
>
> > G'Day Martin, Folks,
> >
> > On Fri, Jan 25, 2008 at 08:50:33AM +0000, Martin MC Brown wrote:
> > [...]
> >> You should find, however, that the latest version on mysql.bkbits.net
> >> of the 6.0 tree has the code. You'll need to use --enable-dtrace
> >> during configure to enable it during build.
> >
> > Thanks for the link, and I can see the 26 probes listed in sql/
> > probes.h.
> >
> > I'm glad this provider is now public and we can discuss it - it's
>
> > going
> > to be of great use, and I'm really looking forward to writing more
>
> > scripts
> > for it. I tested a prototype about six months ago and emailed
> demos
> > of
> > scripts and suggestions for probe and argument additions; by the
> > looks of
> > the current probe list, these demos and suggestions may still be
> > relevant,
> > so I've attached them below.
>
> Thanks - interesting :)
>
> > Note, the demos that follow are not possible with the probes I see
> in
> > the 6.0 tree, since they require arguments other than just the
> > thread id.
> >
> > The 6.0 tree does look like it has a well thought out set of basic
>
> > probes,
> > but not interesting arguments such as query strings and database
> > names.
> > Adding these arguments shouldn't be that difficult (I still have the
> > mysql patch from when I tried it earlier), the difficult part is
> > choosing
> > consistent arguments across probes (something worth discussing here).
> > Writing DTrace scripts will help confirm that the argument choice is
> > working.
>
> Please feel free to suggest anything/everything - I'll pass it back
> to
> the team working on this :)
>
> I've already requested my on enhancement, many of which you already
>
> cover (I would like, for example, the thread ID (as exposed
> internally
> through SHOW PROCESSLIST or query at a minimum when examining the
> output, otherwise we merely get counters of events, rather than the
>
> ability to track individual queries through the system.
>
> To be fair, remember that at the moment we are merely examining where
>
> best to put these things. Particularly in the 6.0 server there are
> some changes that might affect some areas of this. We are also, as I
>
> mentioned originally, trying to be compatible with OS X, but I know
>
> there are some limitations with the current DTrace implementation on
>
> OS X that might make that process more complex than we would like.
>
> > *** Demos ***
> >
> > The following are fairly simple but useful. The scripts are in the
>
> > tar
> > file attached.
>
> It should go without saying that these are pretty cool :)
>
> >
> > *** Suggestions ***
> >
> > here are my suggestions:
> >
> > 1) Probes
> >
> > There are many probes to pick from, which is good, but some are
> > missing -
> > such as probes for the query cache (I have added query cache probes
> in
> > my patch). A quick way to check what might be useful, is to search
>
> > for
> > the DBUG_PRINT calls in the code - if it is instrumented for
> > debugging,
> > it may be useful to instrument it for DTrace.
> >
> > I'd suggest probes to observe:
> >
> > - query cache hits / misses
> > - connection logins (successful and failed)
> > - database startup / shutdown
> > - sorts
> > - replication events
> >
> > Of course, we need to focus on probes that are actually useful at
> > solving real issues, and not create too many unneeded probes.
> > Having a
> > small sufficient set of probes will make it easier to keep the
> > provider
> > as a stable interface.
> >
> > Also, the "-end" probes I think would be better renamed "-done"
> > probes,
> > as is the convention in other DTrace providers (such as "io").
>
> I will pass all of this on. As I said earlier, we are still in the
> early stages of the process and are trying to work out where best to
>
> add the probes, both in terms of the information that can be tracked/
>
> returned and in terms of selecting the most relevant location.
>
> > 2) Probe Arguments
> >
> > The provider really needs many more arguments, and there are plenty
> to
> > pick from in the THD class and what it inherits. Here is what I got
> > going in my suggested patch:
> >
> > Probes,
> > query-parse-start(thr, database, user, host, query)
> > query-parse-done(thr, database, user, host, query, result)
> > query-execute-start(thr, database, user, host, query)
> > query-execute-done(thr, database, user, host, query, result)
> > query-cache-hit(thr, database, user, host, query)
> > query-cache-miss(thr, database, user, host, query)
> >
> > Arguments,
> > (void *)thr THR Class pointer (for raw debugging)
> > (char *)database Database name string
> > (char *)user Username string
> > (char *)host Hostname or IP address string
> > (char *)query SQL Query string
> > (int)result Result (0 == success, -1 == failure)
> >
> > I didn't find thread_id useful (DTrace already has thread IDs), but
>
> > the
> > thr pointer may be useful (both as a unique ID and for raw
> debugging
> > - where
> > pretty much any data can be fetch if you know the offset).
> >
> > The order of arguments is from most to least common. Most probes
>
> > should
> > have thr and database, so they go first, followed by user and host,
> > then specific arguments - such as query string and result.
> >
> > The result argument is invaluable. I picked variables that seemed
>
> > reasonable
> > for that patch - but please check them more carefully (they look fine,
> > but I didn't read all the code).
>
> I'll check this out. It would be nice if we could get some, all or
> more of these into the code.
>
> > There is a lot of very cool info in the THD class, but we need to
>
> > consider
> > stability (and not expose private implementation details).
>
> > For connection/login probes, the Security_context class has some very
> > interesting fields - check them out. Although, they aren't always
>
> > populated
> > (I used "user" and "host_or_ip" in my suggested patch - as they
> seem
> > most
> > frequently populated).
>
>
> There's lots all over the place that we could if we wanted to. I
> think
> the key thing is possibly not what we add, but what we don't add.
> There's a temptation here to add lots and track everything, but we
> have to be sensible...
>
> MC
>
> --
> Martin 'MC' Brown, [EMAIL PROTECTED]
> Everything MCslp: http://planet.mcslp.com
>
>
>
> _______________________________________________
> dtrace-discuss mailing list
> [email protected]
>
_______________________________________________
dtrace-discuss mailing list
[email protected]
