Hi there - Just purchased a license for Jet Profiler - great app!
Couple questions/comments right off the bat:
1. All of our queries begin with comments that identify the page / function that is making the SQL query. These comments prevent me from clicking on "EXPLAIN" with Jet Profiler, so I haven't been able to use this feature yet.
Example: 1 user UserShard /* foo_public: _fetch_first_page_go() via data_db_fetch_paginated_shard 0 */ SELECT COUNT(*) FROM Widget USE INDEX(latest_user) WHERE owner_id='xxx' AND perms IN (0,1) AND weridness=0 null 0.40609378719352535 426500
2. What exactly is the Threads column in the lower section? And Total Time - is that the sum of the Time column for all matches of the normalized query from the "SHOW PROCESSLIST"?
3. Would be great to have additional columns, such as count, %, etc.
4. A long shot - but a separate backend data collection tool would be great. Could leave it running, gathering data, and fire up the front-end to view the collected/realtime data. On top of that, a "movers-and-shakers" page - to see which queries in a highlighted range are out-of-whack with the average distribution. Whenever I'm troubleshooting an issue, I'm frequently asking "are there any queries running now which weren't running 24 hours ago - or are taking up more resources than 24 hours ago?"
Thanks for hearing me out - feedback on #1 would be especially appreciated since it's a bit of a blocking bug for me.
1. Sorry about that, it is a bug in the query parser. Comments are supposed to be removed in the normalization step. It will be fixed in the next release (due September).
2. Threads - average number of processlist threads busy with the query in any given moment. Anywhere near (or above) 1 means "pretty busy". Total time - The sum of all observations of the normalized query during the time frame. Not same as the Time column from SHOW PROCESSLIST though. As an example, say that we poll the database every 100ms for a 10 second period. If a query is observed 5 times, it is approximated to have a total time of 500 ms.
3. I agree, should be a quick fix. The data is there. Its just a matter of presenting it in the table.
4. Maybe we'll build a separate collector; there is support for it in the code already, it just needs to be packaged properly. Would it work if the collector produced logs which could be imported into the gui app? Or do you require more of an agent setup were you can tap into each stream as you want in near-real time?
Movers and shakers - that's a great idea! I've added it to our todo list so we can do some elaborations.
Thanks for the quick respsonse, I'll stay tuned! All-in-all - a great handy app.
Couple more thoughts after playing around with it some more...
* Highlight the table name in the query list to make it easier to see. * Is this crazy? - Allow an explain on a DELETE/UPDATE query by explaining a select with the same WHERE clause? * Allow setting a limit on dataset size? Would be nice to be able to just leave it running without worrying about memory bloat. When I've left it running for 3-4 hours it started getting pretty sluggish or even crashed. Perhaps decreasing sample resolution as you went back in time to save memory?
As for #4, I was thinking more the latter - especially if it was able to manage a (relatively) large data archive. (Thinking RRD-style data storage here..) Would be great to run the monitor on all servers, and be able to connect to any one on demand to see what's going on / what was going on over time. (Perhaps the whole thing could run behind a web interface?)
* Highlight table name: Sounds good. We'll see what we can do.
* Explaining DELETE/UPDATE as SELECTS: well... I wouldn't call it crazy. Somewhat limited, but you will help find problems where an update-statement is scanning a lot of rows because it's not using indexes efficiently. Agreed, it does not show information about updated rows and other write information, which is often the real cost behind an (efficient) update.
* Dataset size limiting: it is definitely something we're going to add. Probably a fixed window, so you can leave it running as you wish. But it is not supposed to crash :) We thought we had fixed all memory problems, but there must still be some leaks left.
* Multi-server monitoring: This would be quite a big feature since JP uses a lot of resources for providing detailed statistics. But it is also a very powerful feature. We'll work on it.