Existing monitoring tools for databases can be hard to use in real
life situations. They are often based on either logs of easy to define
problems, like crashes, slow queries or security issues. Or they are
counter based saying the database is completing a number of queries
per second or the amount of used memory is not too high. The problem
with this approach is that it limits the user, especially situations
that are specific for the application using the database, e.g a
connection running thousands of small fast queries, causing high
unexpected load on the machine, or connections holding write locks too
long causing other connections to work too slow. On a development
server the overall load may be irrelevant with several developers
working at the same time and each only being interested in their own
workload. Therefore I aim to implement a tool I think is better for
users in general and that used in both development and production
systems.
This project aims to implement a tracker of events that occur inside
the database which can pick out events matching certain criteria
determined by the user, like which user is accessing the database,
which database is in use, which machine does the request originate
from etc. Events may be trivial things like queries taking too long,
or it may be more complex things that are only seen in conjunction
with other workloads, like queries that use a hash join, but the
resulting hash was too big for memory and it needed to be flushed to
disc, causing the machine to slow to a crawl. All this will help
database administrators, database program developers and others track
down problems in their own code.
An additional goal is to implement this tracker in a manor that
minimized the performance impact on a otherwise perfectly optimized
system, thus making it also usable to have enabled in a production
environment.
- How does this compare to existing solutions (or other project in
design or at the implementation level) in MySQL?
http://forge.mysql.com/worklog/task.php?id=3771 The rationale
describes a system very similar to my suggestion, however the
implementation describes a simplified
system that is thightly integrated into MySQL, and does not seem to
offer a way of limiting the speed impact upon activation, nor does it
seem to care much about speed at activation. The "What does SELECT
1 do?"-type end-user also seem to have been left out of the design.
Last years "MySQL Auditing Software" is aimed at the network-level. I
couldn't easily find an overview of what was actually implemented, but
to me it seems like just a packet analyzer. It has its uses and is
great in that it is totally disconnected from the mysql
source. However, since it is that, there is no way of accessing
internal information, making it useable only for the simplest of
tracking.
My solution contains a custom made WORM-style (Write once, read many)
table handler for high speed and low space storage facility. Designed
to allow access both from within MySQL and as a seperate file. Also
designed to allow the data to be streamed to another server for
storage there. As a seperate file it is designed so that the normal
access patterns from a graphical user interface is optimized in speed,
even for super high event counts. From within MySQL scans over the
full dataset is optimized, and there is easy support for adding
indexes on-top.
My solution also support for each at-the-source filtration of subsets
of events to limit the time spent generating events that noone cares
about. My solution also maintains its own internal state for custom
event information, limiting the impact on central MySQL code.
The GUI (which I wont make as part of the gsoc program) will make it
easy for both simple users to get an overview of what is happening,
but also make it possible for the complex user to see deeply
specialized things like hash-spills.
Above all my implementation will be backed by a high quality
documentation (I hope :) ) about design choices and implementation
details since it is done as part of my thesis.
All in all I hope this project will make it trivial for the end-user
to understand their usage of the database and improve upon it, thus
adding value to their own software and increasing the view of MySQL as
the best Database vendor in the world :)