Thursday, July 24, 2008

Inspect the Query Cache using MySQL Information Schema Plug-ins

A while ago I wrote about MySQL 5.1 information schema plug-ins.

At the time, I wrote a plug-in to report the contents of the query cache, but for all kinds of reasons, I never found the time to write a decent article about it, nor to release the code.

I am not sure if I'll ever find the time to write that article, but I just tidied up the code, and installed it in a new MySQL 5.1.26-rc server. It seems to work there, so I put the code up on the web.

Inside the source file, there's instructions to build and deploy it. If all goes well, you can do things like:

mysql> select * from information_schema.mysql_cached_queries\G
*************************** 1. row ***************************
STATEMENT_ID: 1
SCHEMA_NAME: test
STATEMENT_TEXT: select count(*) from world.city
RESULT_BLOCKS_COUNT: 1
RESULT_BLOCKS_SIZE: 512
RESULT_BLOCKS_SIZE_USED: 106
1 row in set (0.00 sec)

Are you interested in this plugin-in? Do you have any suggestions? Download it, and put your comments here.

6 comments:

Baron said...

In a word, yes. I'm interested on behalf of my clients. It's a great idea.

rpbouman said...

Baron,

Thanks for the comment ;)

It would be great if you could give it a spin. Ping or pm me if you have any problems getting it to run, and I'll do my best to help out.

I have a few ideas to improve it but I figured it would make most sense to wait for response from people that have the opportunity to provide feedback from the trenches.

Looking forward to your feedback,

Cheers,

Roland

pabloj said...

Looks very interesting, why don't you provide Linux and Windows binaries of patched server? It's less geeky but it should help wider testing ;)

rpbouman said...

Hi Pabloj,

thanks for commentig ;)

"why don't you provide Linux and Windows binaries of patched server?"

The truth is, I'm not that geeky myself, this plugin stuff was kind of an exercise for myself (I do not have a C background) to see if I can read and understand the server code.

If you are interested, I can create a binary plugin for you. Unfortunately this wil only work for *nix systems, not windows.

Send me a private mail, and we can work so you get a plugin to test and comment on.

Thanks again,

Roland

Matthew B said...

Source code link broken. Can you re-post somewhere?

Unknown said...

Nice :) i found you finally

https://mariadb.atlassian.net/browse/MDEV-4682
http://rspadim.blogspot.com.br/2014/09/more-about-query-cache.html
https://github.com/MariaDB/server/pull/6


i included some new features :) could you help to check :)?

DuckDB bag of tricks: Processing PGN chess games with DuckDB - Rolling up each game's lines into a single game row (6/6)

DuckDB bag of tricks is the banner I use on this blog to post my tips and tricks about DuckDB . This post is the sixth installment of a s...