So MySQL released there Enterprise monitor system which includes a Query Analyser.. I spent 30 minutes today and hacked together a lua script for MySQL proxy that pushes the query and timing stats into a database table for every query run.
Currently saves a tokenized pretty version of the query, response time and query time to a DB table..
Now granted, since this inserts into a table there is an issue with added load to the system, this can be limited by using a memory/heap table and then having a separate process/cron that archives those records.
The first thing to do would be to setup the ability to log these records to a separate db
Copyright (C) 2008 Richard Thomas PHPJack Inc.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; version 2 of the License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
--]]
local tokenizer = require("proxy.tokenizer");
function read_query( packet )
if packet:byte() == proxy.COM_QUERY then
local query = packet:sub(2)
local tokens = tokenizer.tokenize(query)
local norm_query = tokenizer.normalize(tokens)
proxy.queries:append(1, packet )
proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "SET @statement = '" .. norm_query .. "'")
return proxy.PROXY_SEND_QUERY
end
end
function read_query_result(inj)
if(inj.type == 1) then
local queryt = (inj.query_time / 1000)
local responset = (inj.response_time / 1000)
insert_query(queryt,responset);
return
end
return proxy.PROXY_IGNORE_RESULT
end
function insert_query(queryt, responset)
proxy.queries:append(3, string.char(proxy.COM_QUERY) .. "INSERT INTO ilogs.logged_queries " ..
"VALUES(@statement," .. "\"" ..
queryt .."\"" .. "," .. "\"" ..
responset .. "\"" .. ")")
return proxy.PROXY_SEND_QUERY
end
