Question:
Why bother with another schema called PERFORMANCE_SCHEMA when we already
have INFORMATION_SCHEMA and the standard allows for custom tables to be
added to INFORMATION_SCHEMA? I understand the performance reasons for
Marc Alff to have not used I_S in MySQL, but I don't see a valid reason
to do this in Drizzle...
I would think it would be better to just have Drizzle I_S plugins for
performance tables, like:
INFORMATION_SCHEMA.PERFORMANCE_XXX
Thoughts?
-jay
Mark Leith wrote:
Hi!
On 4 Aug 2009, at 19:16, Brian Aker wrote:
Hi!
Just put up a blog looking for feedback on performance schema info:
http://krow.livejournal.com/643607.html
Some random thoughts about this:
o Check out Marc Alff's work on PERFORMANCE_SCHEMA in MySQL - I think
this is great strides forwards
o They are proper tables, and hence optimized via the normal optimizer
o Non-blocking/locking is essential
o It doesn't hurt to throw away some data, as long as you can show
that you are doing it
o There is still major instrumentation needed from the storage
engines in able to provide proper IO tracking
o It pretty much covers "contention information, time model, IO
stats") from your blueprint from the server perspective
o http://forge.mysql.com/worklog/task.php?id=2360
o Track and aggregate stats around SQL *within the server* (I know you
have this, but this is a biggie and needs reiterating :))
o QUAN is great, but it's a work around with the limitation of not
being able to get it from the server
o PERFORMANCE_SCHEMA does not have something like this either yet
o You need it to be able to track back all of the PERFORMANCE_SCHEMA
info to a statement and user to go and beat over the head
o Look at V$SQL from Oracle for inspiration
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2113.htm (though
it seems most of the current plan is copied from Oracle :))
o Global transaction ID's and statement ID's surfaced too
o
https://blueprints.launchpad.net/drizzle/+spec/create-session-level-sql-execution-objects has
FIRST_ISSUED, LAST_ISSUED, FIRST_EXECUTED, LAST_EXECUTED - I don't get
the difference. It also splits out physical IO to read/write, but leaves
logical IO lumped together, which is odd - and that happens in a number
of places, such as sysobjectio as well.
o Track object / user level statistics - IO, size, usage, current
optimizer statistics
o Certainly by database / table, would be nice to have some stats
available about index usage too
o Or "The other Mark" is going to hunt you down
o When tracking CPU etc, do not use the SHOW PROFILE methods (getrusage
is about is helpful with threads as a chocolate teapot is with tea)
o More fine grained session statuses - there's plenty of statuses that
mean way too much in MySQL already ("Sending data" anybody?)
o You get a lot of this from the likes of PERFORMANCE_SCHEMA wait
information
o A simple SHOW PROCESSLIST sure is helpful though, and knowing more
exactly which area of the code you are currently executing is sometimes
helpful
o Track temporary table usages (max size used etc.)
I'm know I've probably got a million other things to say on this topic,
but I'm just gonna stop here.. :) The overall plan looks great. I think
there's some clean up to be done in the table structures proposed however.
Cheers,
Mark
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
