I wrote recently about using events and the new processlist table in MySQL 5.1 to keep track of the number of connected processes. Although having the PROCESSLIST available as an INFORMATION SCHEMA table is usefull, it seemed to me that having SHOW GLOBAL STATUS exposed in a similar fashion would be far more useful. So at the MySQL UC last year, I asked Brian Aker if that would be possible. I know how many enhancement requests MySQL has to deal with, so I was really happy to see that table appear in the latest 5.1 build (5.1.14 beta).
This table, together with the EVENT scheduler, lets us keep track of the values of status variables over time without having to have any external deamon running. This won't come anywhere near to matching what MySQL have made avalable in Merlin, but still could be fairly useful. So lets build a simple system using events to keep track of "interesting" status variables....
I also thought I’d take the opportunity of working with the MySQL Workbench to design the tables involved. Unfortunately the product is effectively unusable on my system until at least rc7 (due when?) and b/c it only support windows, I’m not up for a download the source and compile. Oh well. I used our own (Quest Software's) Toad Data Modeller instead.
The idea is to schedule some events that trap (snapshot) the contents of the PROCESSLIST and GLOBAL_STATUS variables into tables that can be used to track trends and monitor performance in a simple way. I created three tables to hold the data:
- GHSNAP_STATUS_VARIABLES lists the variables names we consider "interesting". If the variable_name is listed here and CAPTURE_FLAG=1, then we will capture the value of the variable. VARIABLE_SOURCE indicates the table from which the data is obtained - I only implemented GLOBAL_VARIABLES for now
- GHSNAP_SNAPSHOTS contains one row for each snapshot of the table we take. The UPTIME column is used to detect server restarts so we don't calculate nonsense deltas or rates
- GHSNAP_SNAPSHOT_VALUES contains one row for each variable for each snapshot. We capture the raw value of the variable, its change since the last snapshot and the rate of change per second.
This file installs the tables, stored procedures and events. You may have to run it from the MySQL query browser to avoid errors caused by the command line client not processing DELIMITER statements properly. The script creates a new database GH_SNAPSHOTS. Three stored procedures are created:
- ghsnap_populate_variables creates default entries in the GHSNAP_STATUS_VARIABLES table and is run only during installation. By default I set it up to capture all the INNODB%, COM% and QCACHE% variables but you might want to change the CAPTURE_FLAG for those you do/don't want.
- ghsnap_take_snapshot takes a snapshot of GLOBAL_STATUS and stores it in the snapshot tables. It also works out if the server has been restarted and if not, calculates rates and deltas.
- ghsnap_delete_snapshots deletes all snapshots more than a certain number of days old.
There's two events defined:
- ghsnap_take_snap_event runs (by default) every five minutes and simply executes ghsnap_take_snapshot.
- ghsnap_delete_snap_event runs every 5 hours (by default) and deletes snapshots more than 1 day old (you can edit the installation script to change this default).
There's some obvious additional things that we could do with this (optimze deletes via partitioning, calculate ratios, capture other state, etc) but this probably has some real value already. Now I can issue queries such as this:
select snapshot_id,snapshot_timestamp ,
sum( case variable_name when 'COM_COMMIT' then variable_value_rate end
) commit_ps,
sum( case variable_name when 'COM_SELECT' then variable_value_rate end
) select_ps,
sum( case variable_name when 'COM_INSERT' then variable_value_rate end
) insert_ps,
sum( case variable_name when 'COM_DELETE' then variable_value_rate end
) delete_ps,
sum( case variable_name when 'COM_UPDATE' then variable_value_rate end
) update_ps
from ghsnap_snapshots join ghsnap_snapshot_values using (snapshot_id)
where snapshot_id>70
group by snapshot_id,snapshot_timestamp
To view the activity on my server. Using a reporting or charting tool I can generate usefull charts, etc. For instance, using the BIRT module in eclipse I created the following chart showing my SQL execution rates:
Nothing earth-shattering but still useful and - with the event scheduler, stored procedures and the new INFORMATION_SCHEMA tables we don't need any external infrastructure to capture this information.
We'll probably implement something like this in the next version of our freeware Spotlight on MySQL as well as better diagnostics on replication and exploiting the fact that in 5.1 you can get SELECT access to logs. Feel free to check it out.
P.S. don't forget that to set EVENT_SCHEDULER=1 to enable events on your 5.1 server. And watch out on Windows where the event scheduler in 5.1.14 still seems a bit unstable.
Nice trick, but why aren't those values available from information_schema tables?
Posted by: p | January 04, 2007 at 12:01 AM
The instantaneous values are indeed obtained from INFORMATION_SCHEMA.GLOBAL_STATUS. The "trick" is to create an historical record of selected values so we can do some trending or historical diagnostics.
In the past we could have done this by running some external program (in perl say) that issued SHOW GLOBAL STATUS commands and saved the values and deltas back into tables in the DB. But with the INFORMATION_SCHEMA table we can get the values within a stored procedure, and with the new events mechanism, we can run that stored procedure _inside_ the database.
Posted by: Guy | January 04, 2007 at 09:09 AM
Great post, I really found this useful! As a sidenote there's this new site on the net which is looking at bringing together like-minded database users & professionals. It's mainly a forum site but looks like it's got potential to grow. Here is a blurb from the site: ---- SQLSet.com the place to go for all things database. The Forums are grouped for easy navigation, registration is quick and simple. Once registered you can begin posting threads and exchanging ideas. ---- I'd recommend visiting if you've got an interest in databases: http://www.sqlset.com Cheers
Posted by: Johnny | September 04, 2008 at 08:51 AM
hello guys, I really like this post, the events in general are things that draw little attention, thanks for sharing the information, and answer many questions I would like updates to this article, thanks
Posted by: viagra online | April 07, 2010 at 06:06 AM
Great theory.It took me some time to understand the facts but i must say they are really logical one.If we do things as shown in the article,The system can be improved.
Thanks for sharing.
Posted by: Online Viagra | May 24, 2010 at 07:59 PM
nice article post.I have seen number of posts from you.But this one is something that is really best.Nice analytical concept.
Posted by: Kamagra | May 24, 2010 at 08:00 PM
Really great. It works in my WAMPP- as well in my LAMPP-environment. It contributes to AoMi client/server realtime-monitor as a realible and fast resource. Check my Web Site URL for LINUX (WINDOWS also available)
Posted by: Richard Ganter | July 18, 2010 at 09:33 PM
I love the comfortable tables because i usually like to put some vases with a lot of flower. I love to decorate my tables with vases everywhere, My house look happier and i love when my boyfriend comes to my home. IS sure i am happy too.
Posted by: Buy Viagra | September 22, 2010 at 01:45 PM
http://mikardia.com/porno_pictures/sex-69-4.jpg
My story is youporn adult fly porn tube wesite for porn on itouch lichelle maire porn shaved pornstars video porno hantail de naruto shippuden xxx movies porno xxl torrents tsunami videos asian also mor med sГёn fuck og porno porntube teens torture gratis porno sex video free slut porn video samples italian porn movies.com
And also mature horney women fucking on porn tube transvestite free video porn xxx free porn online free sexy pornotv.lv free mobile porn no cost full frii porn download 3gb porn retro swinger porn movies free movie of tawnee stone sex also free porn in espanol sex porno 3arab fisting on youporn.com anime petite porn free vintage porn video teens porn fun free itouch porn downloads amateur homemade porn mpegs poop porn tubes
No one find preeen boy porn phone porn mpeg free sex porn film gratuit actress bollywood clip porn sex arab fadiha porno in rrussel cigarette porn tube anal colegialas also brazzers free porn download ben 10 gwen porn videos pornos .online para ver desde la web gratis manga xxx porn punjabi
I get site:www.ihackr.com pornhup fuck jerk off porn video teen rusianporn also free porn native pornstars mp3 alexis texas unseen porn lemon porn movies porn vido for the psp
And finnaly porn smileys absolutely free picture porn thumb retro porn pack torrent beastie boys taringa also ileana porn sex videos bst pornstars free porno mature fuck young guy freeyoung porn download free full porn movies already in mp4 format with no cost slut movie wife porn
Posted by: TDBradley | December 31, 2010 at 08:47 PM
Please one more post about that.I wonder how you got so good. This is really a fascinating blog, lots of stuff that I can get into. One thing I just want to say is that your Blog is so perfect
Posted by: xanax online | August 02, 2011 at 01:10 PM
Hello I enjoyed yoiur article. I think you have some good ideas and everytime i learn something new i dont think it will ever stop always new info , Thanks for all of your hard work!.
Posted by: Acheter Kamagra | August 18, 2011 at 08:24 AM
p4cO5, klonopin for sale, x3cI8, yellow klonopin drug, klonopin for sale, o9pH8, difference between klonopin lortab, http://forum.bo2forum.com/showthread.php?t=124794 klonopin, p6wJ5, order klonopin mastercard, d1yY6
Posted by: rleprvlm | September 07, 2011 at 06:09 PM
mjbbjhvzibssjtpo, Registry Cleaner, KsIGDFb, [url=http://www.windows7pcregistrycleaner.com/]Freeware full registry cleaner[/url], uEyxfJA, http://www.windows7pcregistrycleaner.com/ Registry Cleaner, mjQDYQz, Generic cialis viagra, xgtsQly, [url=http://escialis.ucoz.es/]Cialis[/url], dFMHsjV, http://escialis.ucoz.es/ Generic cialis viagra, vOHtGNQ, Free viagra without prescription, LNvBkQu, [url=http://getviagra.ucoz.co.uk/]Adderall viagra combo[/url], AQhDseL, http://getviagra.ucoz.co.uk/ Buy generic viagra, bYbOcDZ, asp.net web hosting, QSzwWXy, [url=http://webhostingreview.org/category/asp-hosting/]asp hosting[/url], OUOrRzt, http://webhostingreview.org/category/asp-hosting/ asp.net hosting, tIXuvyz, African Mango, YfJMDid, [url=http://africanmangodietpill.org/]African Mango Diet[/url], SyXMGlz, http://africanmangodietpill.org/ African mango in pa, inhcAEG, Electronic Cigarette, xStghpP, [url=http://electroniccigarette-advice.net/]Thee electronic cigarette[/url], dQSwBEm, http://electroniccigarette-advice.net/ Electronic cigarette customer reviews, yBPYplp.
Posted by: Which electronic cigarette company is not a scam | October 17, 2011 at 07:00 PM
mjbbjhvzibssjtpo, Registry Cleaner, KsIGDFb, [url=http://www.windows7pcregistrycleaner.com/]Freeware full registry cleaner[/url], uEyxfJA, http://www.windows7pcregistrycleaner.com/ Registry Cleaner, mjQDYQz, Generic cialis viagra, xgtsQly, [url=http://escialis.ucoz.es/]Cialis[/url], dFMHsjV, http://escialis.ucoz.es/ Generic cialis viagra, vOHtGNQ, Free viagra without prescription, LNvBkQu, [url=http://getviagra.ucoz.co.uk/]Adderall viagra combo[/url], AQhDseL, http://getviagra.ucoz.co.uk/ Buy generic viagra, bYbOcDZ, asp.net web hosting, QSzwWXy, [url=http://webhostingreview.org/category/asp-hosting/]asp hosting[/url], OUOrRzt, http://webhostingreview.org/category/asp-hosting/ asp.net hosting, tIXuvyz, African Mango, YfJMDid, [url=http://africanmangodietpill.org/]African Mango Diet[/url], SyXMGlz, http://africanmangodietpill.org/ African mango in pa, inhcAEG, Electronic Cigarette, xStghpP, [url=http://electroniccigarette-advice.net/]Thee electronic cigarette[/url], dQSwBEm, http://electroniccigarette-advice.net/ Electronic cigarette customer reviews, yBPYplp.
Posted by: Which electronic cigarette company is not a scam | October 17, 2011 at 07:00 PM
tlqsmhvzibssjtpo, offshore web hosting, OEmvKEu, [url=http://webhostingreview.org/category/offshore-hosting/]cheap offshore hosting[/url], louGdoU, http://webhostingreview.org/category/offshore-hosting/ offshore host, ihKOMSm, Mortgage Rates, bQZhIGR, [url=http://www.ratelines.com/mortgage-rates/]Refinancing mortgage rates[/url], jPSluPj, http://www.ratelines.com/mortgage-rates/ Mortgage rates refinancing, NDILkCI, Cool math games rotate and roll, bmCnFMo, [url=http://coolmathgameszone.com/]math games[/url], qLhDITY, http://coolmathgameszone.com/ Cool math games gluey, tvjitRN, Free online fico score, mvPDSbf, [url=http://www.freeficoscore.me/]Free FICO Score[/url], YAguiOa, http://www.freeficoscore.me/ What government site can i get my free credit and fico score, sTIEJWt, Where to start canadian penny stocks, Dvvecxu, [url=http://www.onpennystocks.com/]Gorilla glass penny stocks[/url], hBLViKC, http://www.onpennystocks.com/ Buy penny stocks, SEFbdPC, Online fun flash strip poker games, MKGmogf, [url=http://pokeroninebrazil.com/]Free texas holdem poker games online[/url], ogBKUKk, http://pokeroninebrazil.com/ poker online, QBFnAsu.
Posted by: math games | October 19, 2011 at 07:01 PM
tlqsmhvzibssjtpo, offshore web hosting, OEmvKEu, [url=http://webhostingreview.org/category/offshore-hosting/]cheap offshore hosting[/url], louGdoU, http://webhostingreview.org/category/offshore-hosting/ offshore host, ihKOMSm, Mortgage Rates, bQZhIGR, [url=http://www.ratelines.com/mortgage-rates/]Refinancing mortgage rates[/url], jPSluPj, http://www.ratelines.com/mortgage-rates/ Mortgage rates refinancing, NDILkCI, Cool math games rotate and roll, bmCnFMo, [url=http://coolmathgameszone.com/]math games[/url], qLhDITY, http://coolmathgameszone.com/ Cool math games gluey, tvjitRN, Free online fico score, mvPDSbf, [url=http://www.freeficoscore.me/]Free FICO Score[/url], YAguiOa, http://www.freeficoscore.me/ What government site can i get my free credit and fico score, sTIEJWt, Where to start canadian penny stocks, Dvvecxu, [url=http://www.onpennystocks.com/]Gorilla glass penny stocks[/url], hBLViKC, http://www.onpennystocks.com/ Buy penny stocks, SEFbdPC, Online fun flash strip poker games, MKGmogf, [url=http://pokeroninebrazil.com/]Free texas holdem poker games online[/url], ogBKUKk, http://pokeroninebrazil.com/ poker online, QBFnAsu.
Posted by: math games | October 19, 2011 at 07:02 PM
k7n3fp ooghrpkunrnz, [url=http://phuxclwrumix.com/]phuxclwrumix[/url], [link=http://cyxrycvoykhv.com/]cyxrycvoykhv[/link], http://ywqykmbpfzvd.com/
Posted by: zzmghb | October 19, 2011 at 07:20 PM
, http://www.viagraonlinewithoutprescriptionusa.com/ Viagra, iyqb, http://www.genericviagrawq.com/ Generic viagra online, 726492, http://www.genericviagraonlineuk.com/ Online viagra, akqht, http://www.cheapviagraonlinehq.com/ Viagra for sale, 6907, http://www.buyviagrast.com/ Cheap viagra, >:-))), http://www.genericviagraweb.com/ Generic Viagra, 035438, http://www.cheapviagraonlinewww.com/ Cheap viagra online, szxtws, http://www.cialisvsviagrausa.com/ Viagra online, =), http://www.cialisonlinews.com/ Cialis, 27690,
Posted by: Generic viagra prices | October 24, 2011 at 07:00 PM
, http://www.cheapviagrausa.com/ Cheap viagra, 344847, http://www.cheapviagraws.com/ Cheap viagra, :-O, http://www.genericviagraonlinewww.com/ Does generic viagra work, %]], http://www.viagraonlineph.com/ Viagra online without prescription, =-[[, http://www.onlinemedsvvv.com/ Cialis online, 50591, http://www.genericviagraonlinedb.com/ Generic viagra, 98392, http://www.genericviagraonlinehq.com/ Viagra online, 196, http://www.genericviagrasww.com/ Viagra online, >:-PP, http://www.viagraonlinewwv.com/ Viagra online, 817,
Posted by: Viagra | October 24, 2011 at 07:02 PM
, http://www.cheapviagrausa.com/ Cheapest Viagra, 438, http://www.cheapviagraws.com/ Viagra, 585, http://www.genericviagraonlinewww.com/ Generic viagra online, 8[[, http://www.viagraonlineph.com/ Viagra online without prescription, 66588, http://www.onlinemedsvvv.com/ Cialis online, eym, http://www.genericviagraonlinedb.com/ Herbal viagra, szp, http://www.genericviagraonlinehq.com/ Viagra online, qth, http://www.genericviagrasww.com/ Discount viagra, qskbg, http://www.viagraonlinewwv.com/ Viagra online, bba,
Posted by: Cheap cialis | November 02, 2011 at 07:00 PM
, http://www.viagraonlinemeds.co.uk/ Viagra sales, =P, http://www.viagraonlinemedic.com/ Viagra, 40191, http://www.cialisonlinehq.com/ Cialis vs viagra, >:(((, http://www.levitraonlineweb.com/ Levitra online pharmacy, efoqm, http://www.genericviagraonlineuk.co.uk/ Cheap viagra, 0031, http://www.genericviagrast.com/ Viagra, :[[[, http://www.viagragenericfast.com/ Generic viagra, mszol, http://www.viagraonlineuuk.com/ Viagra online, 8P, http://www.cheapviagraweb.com/ Cialis vs viagra, %)),
Posted by: Generic levitra | November 08, 2011 at 08:00 PM
tWhlZepKbgIr http://www.andrewsambell.com mRmaFtqSqsGl moncler outlet sZhlFgjWnkLp
Posted by: Vampairee | November 15, 2011 at 08:01 PM
, http://www.levitraonlinemeds.com/ Levitra online, :[[, http://www.viagraonlineww.com/ Online viagra, 7021, http://www.viagraonlineie.com/ Generic viagra, 4286, http://www.viagraonlinewithoutprescriptionusa.biz/ Buy viagra online, rhdgd, http://www.viagraonline100.com/ Viagra for sale, >:-[[, http://www.genericviagraonlineusa.com/ Generic viagra, iksyh, http://www.onlinepharmacywww.com/ Generic cialis, wzddu, http://www.cheapviagraonlineusa.com/ Cheap viagra, =)), http://www.viagraonlinews.com/ Generic viagra, 876865,
Posted by: Levitra online without prescription | November 27, 2011 at 08:00 PM
, http://www.viagraonlinewithoutprescriptionusa.com/ Viagra for sale, zobmfz, http://www.genericviagrawq.com/ Generic viagra, xyoyin, http://www.genericviagraonlineuk.com/ Viagra, =-]]], http://www.cheapviagraonlinehq.com/ Online viagra, amxsnm, http://www.buyviagrast.com/ Buy viagra online, mducj, http://www.cheapviagraonlinewww.com/ Viagra for sale, 8DDD, http://www.onlineviagrahq.com/ Discount viagra, lrse, http://www.usamedichq.com/ Levitra viagra cialis, 45771, http://www.cialisonlinews.com/ Cialis vs viagra, >:-)),
Posted by: Generic viagra online | November 30, 2011 at 08:13 PM
, http://www.cheapviagrausa.com/ Cheap viagra, 183759, http://www.cheapviagraws.com/ Viagra for sale, :[, http://www.genericviagraonlinewww.com/ Generic viagra online, :-(((, http://www.viagraonlineph.com/ Viagra online canadian pharmacy, %-), http://www.onlinemedsvvv.com/ Cheap cialis, =-[[, http://www.genericviagraonlinedb.com/ Generic viagra online, xgmdp, http://www.usarxhq.com/ Cheap viagra, 8754, http://www.genericviagrasww.com/ Cheap viagra, 8[[, http://www.viagraonlinewwv.com/ Viagra, %-[,
Posted by: Viagra | December 05, 2011 at 07:49 PM