Hi Matt, Thanks for the cross-post - I'm sure the folks here will like to see what we are doing!
If anybody has any comments on the DTrace/MySQL stuff, please either post here, the DTrace-Discuss list, or send them direct to me (using dev at mcslp.com or mc at mysql.com) and I will pass on to the team working on the DTrace integration. MC On 28 Jan 2008, at 03:20, Matt Ingenthron wrote: > FYI to folks on this list, MySQL/DTrace integration has been > discussed over on the DTrace OpenSOlaris list. > > - Matt > > From: Luojia Chen <Luojia.Chen at Sun.COM> > Date: 27 January 2008 02:36:57 GMT > To: Martin MC Brown <dev at mcslp.com> > Cc: dtrace-discuss at opensolaris.org, Alex Peng <pengjx73 at yahoo.com>, > Brendan Gregg - Sun Microsystems <brendan at sun.com> > Subject: Re: [dtrace-discuss] DTrace for MySQL? > > > 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 <dev at mcslp.com> > Date: Saturday, January 26, 2008 12:17 pm > Subject: Re: [dtrace-discuss] DTrace for MySQL? > To: Brendan Gregg - Sun Microsystems <brendan at sun.com> > Cc: dtrace-discuss at opensolaris.org, Alex Peng <pengjx73 at yahoo.com> > > >> 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, mc at mcslp.com >> Everything MCslp: http://planet.mcslp.com >> >> >> >> _______________________________________________ >> dtrace-discuss mailing list >> dtrace-discuss at opensolaris.org >> > _______________________________________________ > dtrace-discuss mailing list > dtrace-discuss at opensolaris.org > > > _______________________________________________ > > > webstack-discuss mailing list > webstack-discuss at opensolaris.org > http://mail.opensolaris.org/mailman/listinfo/webstack-discuss -- Martin 'MC' Brown, mc at mcslp.com Everything MCslp: http://planet.mcslp.com
