On Sun, Feb 15, 2009 at 6:31 PM, Mark Atwood <[email protected]> wrote: > > On Feb 12, 2009, at 7:00 AM, Stewart Smith wrote: > >> On Thu, Feb 12, 2009 at 06:25:59AM -0800, MARK CALLAGHAN wrote: >>> >>> Examples of the compromises (and mistakes?) we made: >>> * We count row activity (rows read, rows changed) per table and per >>> user. An easier way to do that is to modify handler.{h,cc} so that >>> activity for any handler is counted. We modified a few storage engines >>> instead. Lots of interesting things might be possible when you can >>> insert code between a storage engine and the code that calls it. >> >> I think this would be great to be able to have logging hooks here.... >> (I now wait for Mark Atwood to have it done in 3 minutes with some magic >> example statistics thing). > > That's easy enough to do. > > There are two ways to do it. > > I could write a logging plugin that gathers statistics, since that already > has a pair of hooks that fire before and after each event. > > Or, if those hookpoints aren't quite right, I could create yet another > plugin interface that does hook into the correct places. Or add those > hookpoints to the logging plugin API... > > Where *exactly* in handler should such a thing hook? > > > Then there is the issue of displaying the statistics as they are gathered. > Rebuilding INFORMATION_SCHEMA is still pending, I think. But in the I_S is > the Right Place to do it, for the most common case, and thus the example > plugin, when I write it. > > > Gathering statistics doesn't have to be lock contentional at all. The > design would be to use a scoreboard. >
We counted per-table: rows read, rows changed. If you don't want to modify storage engines, then the handler methods are convenient places to count these as long all calls are implemented by handler.cc and then it calls the storage engine method. The methods for which this should be done include: update_row, write_row, delete_row and the row read methods. Initially we counted per index: row read, rows changed. I have since removed that code as it was too complex to make fast on SMP given our implementation. And we rarely used the output. It is useful to find unused indexes. More of the instrumentation for monitoring is above the level of a storage engine. I am not sure what a scoreboard is. Our implementation updates global per-user and per-table structures at statement completion. There was a user HASH and a table HASH each protected by a mutex. At update time, the caller locked the mutex, did a search to find the HASH entry, updated the entry and released the mutex. For short-running statements (CPU bound sysbench for example) these mutexes became hot. I changed the code to save a pointer to the HASH entry and know when the saved pointer was invalid. If you don't aggregate by user or table, then you won't have this problem but aggregation is where the value is. What I have described doesn't provide per-session stats. We don't provide them in SHOW USER_STATS, but others might want them. I don't get to debug at the session level as there are too many users/apps/sessions and only one of me. Another problem we encountered were apps that used SHOW USER_STATS LIKE 'foo' too often. As the output has a Concurrent_connections column apps would use this to know when they had too many connections on a server. So I had to make sure that this would not create load problems. Finally, some users requested aggregation during statement execution rather than at statement end. For long running statements it would be nice to update the global data structures before the multi-hour statement finished. We did not provide that. It would also be a good thing to update the global data structures for killed statements. I am not sure if we provided that. > > -- > Mark Atwood <http://mark.atwood.name> > > > > -- Mark Callaghan [email protected] _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
