INFORMATION_SCHEMA.PROCESSLIST

MySQL-dump

March 15

INFORMATION_SCHEMA.PROCESSLIST

I requested this urgently for 5.1 and Brian made it happen: An information_schema table called processlist. This is the table version of the "SHOW FULL PROCESSLIST" output, and it is very cool. Using this table, you can create SQL to dig the processlist, and write stored procedures that spot, log and handle long running queries.

Even better: With the new log formats in 5.1, you can access the logs as CSV engine based SQL tables as well, and join the processlist table against the general query log or other logs. This allows you plenty of easy session tracing and easy profiling.

Even better: With the processlist table and 5.1 new feature "events" you can create new threads, monitor their execution, and in case of problems forcibly terminate them using other events or procedures. Or you can write a stored procedure using DROP USER to delete a user, and KILL this users connections afterwards to make sure that guy has been kicked.

The possibilities are endless.
Posted by Kristian Köhntopp in Features at 15:40 | Comments (5) | Trackbacks (0)

Trackbacks
Trackback specific URI for this entry

No Trackbacks

Comments
Display comments as (Linear | Threaded)

Woohoo! I've been wanting this for ages! The possiblities are indeed endless, not only for profiling and naughty-query killing, but also general debugging and logging.

Now, just to code up an event-based hook system... now that would be seriously wicked. That way developers can hook into events (not EVENTs, but actual server-internal events) by registering a listener which can be notified upon event execution. That way, developers can build profilers that are simple plugin listeners into the main server execution engine...

Cheers,

Jay
#1 Jay Pipes (Homepage) on 2006-03-15 18:42 (Reply)
I noticed on the pgsql hackers ml that their pondering if they should or should not add non standard stuff to their information schema or not. They also have their own catalog which replicates some aspects.
#2 Lukas (Homepage) on 2006-03-15 21:24 (Reply)
It's a great start. However what other show information could also become INFORMATION_SCHEMA based, making it much easier for software to use (especially via a JDBC connection)

Oracle has it's V$ Views, several hundred of them that allow you to view everything.
#3 Ronald Bradford (Homepage) on 2006-03-16 02:30 (Reply)
Ronald,

Agreed. Brian's doing a hackfest at MySQL Users Conference on moving SHOW commands to INFORMATION_SCHEMA tables. See here: http://krow.livejournal.com/410764.html. Looks pretty promising :)

Cheers!
#3.1 Jay Pipes (Homepage) on 2006-03-16 16:17 (Reply)
Wonderfull!!! You are on the right track.
#4 pabloj (Homepage) on 2006-03-18 12:04 (Reply)

Add Comment

E-Mail addresses will not be displayed and will only be used for E-Mail notifications

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

BBCode format allowed
 
 

Quicksearch

Comments

santosh about An InnoDB tutorial
Mon, 21.07.2008 11:13
I have a problem in deleting t he child table row from my app lication using unique id. when I am using the command [...]


Azundris about Nermalisation
Sat, 05.07.2008 16:08
The point is that you have the option to not do, but then yo u'll be in a world of pain. Th e point is also to show [...]


fdask about Nermalisation
Fri, 04.07.2008 15:40
Nice little article, and I lik e how you've used cats in your examples :) I am a little unclear what the point h [...]


Salmiakki about Salmiakki - the official MySQL Drink
Sun, 08.06.2008 11:34
150g of liquorice are enough t o get the original Salmiakki t aste.... you can add some more , anyway


mohammad about DB Designer becomes MySQL Workbench
Wed, 28.05.2008 05:41
It's very good


delbertino about phpvikinger.org: Things that have no name
Sun, 11.05.2008 06:34
In reply to "stuff with no nam es":very informative and succi nct. I am retired and need to learn to build a website [...]


ashok about What is the difference between MySQL and Postgres?
Wed, 30.04.2008 14:08
what is the difference between MySql and PostgreSql?


fortunecookienotes about Fortune Cookie
Wed, 09.04.2008 21:46
What a random fortune, who kno ws what it means. My favorite random fortune cookie note rea ds "you will make a good [...]


Categories


All categories

Authors


All authors

Syndicate This Blog

Blog Administration

Open login screen

Disclaimer

All examples, code-snippets, advice, trivia, etc. are supplied in the hope that they are useful, but no warranty of any kind is given. Use of trademarks is not intended as a challenge to them.

Impressum

Copyright 2005-2007 bei den jeweiligen Autorinnen der Beiträge. Die Infrastruktur von MySQL-dump wird betrieben von: Azundris, Immanuelkirchstraße 18, 710405 Berlin, Germany