mysql-proxy-discuss team mailing list archive

["Boehm, Matthew" <mboehm@xxxxxxxxxxxxx>]

Per Edwin's request, I am reposting
http://forums.mysql.com/read.php?146,249707,249715 here:

 

 

Is there a way in mysql-proxy to initiate a custom, internal,
connection? I'd like to be able to login to mysqld via mysql-proxy with
an "admin" account so that this connection can insert into a different
db.table certain performance metrics that m-p is gathering for the
other, user-level connections. 

For example: 
1. user query comes in to m-p 
2. m-p analyzes query and passes it on 
3. m-p logs in, or uses existing, admin connection to insert analysis of
user query into private db.table that user doesn't have access to. 

Possible?

 

Reasoning: The slow_log doesn't do a good job these days of indicating
performance issues. I've had slow_log's filled with thousands of queries
from bulletin boards and CRM's where the top 200 by highest CPU usage
(or lock time) were the exact same query, just with a different Y value
in "WHERE col = Y"

 

I want to use m-p to create a "better" slow_log by normalizing (via
tokenizing) all queries, creating a hash of that normalized query, and
saving the hash and query to a table inside mysql db. That way, each
time the same normalize query comes across, I can simply increment a
counter instead of saving the entire query. (INSERT..ON DUPLICATE KEY
UPDATE count=count+1) where the PK is the hash column.

 

Now I've turned a constantly growing slow_log table into a (almost)
static table that just increments a counter.

 

Then at a later date, I can fire off a script that goes through this
table and runs 'EXPLAIN' on each to gather info and I can present that
to a customer in a nice report and tell them things like "well, this
query examines 5,000,000 rows. We should look at adding some indexes."
Or "this query is the worst performer. Lets rewrite it."

 

With all that said, for security, I'd like to create an internal
connection to m-p that has INSERT only access to this "stats table"
instead of having to create this permission for each user account.

 

Matthew Boehm

Senior MySQL DBA, The Planet - Northstar Managed Hosting

Certified MySQL 5.0 DBA

Certified MySQL 5.1 Cluster DBA

Office: 281-714-4018

Mobile: 832-253-8258

Email: mboehm@xxxxxxxxxxxxx