Hi Nishani, I think this is a good start. One important part is tying this back to something to which the user can relate - namely the SQL statement that was executed. Would it be possible to include the string of the statement?
Another interesting angle would be to group by the statement and aggregate the overall time spent to get an idea of the "top N queries" over a given time range. Then drilling into those to see the traces. Thanks, James On Fri, Jun 19, 2015 at 11:01 AM, Ayola Jayamaha <[email protected]> wrote: > Hi All, > > Milestone-1 can be found in my git repo[1]. > Features : > > - Adding tracing to a timeline using sample json > - Comparing two or more traces on the timeline > - Visualizing the trace distribution across the time axis > - Removing a trace from the list of traces represented on the chart > - Listing the tracing information on a table > > Any feedback will be appreciated. > Thanks. > > [1] https://github.com/AyolaJayamaha/TracingWebApp/tree/milestone-1 > > On Wed, Jun 17, 2015 at 11:35 PM, Ayola Jayamaha <[email protected]> > wrote: > >> >> Hi All, >> >> You can find milestone-1 in my git repo. This is the working branch[1]. >> It has not been binded to backend yet. But the visualization of traces can >> be seen from the code. >> Traces can be selected from table/time period and shown on the timeline >> as [2]. The parameters could be entered as TableName, StartTime, EndTime >> and the traces would be listed down. The user can select the traces as his >> preference and view their timelines. Is the procedure ok? >> The start time of different traces could be visualized by bringing them >> up to a same time reference for comparison. >> >> >> [1] https://github.com/AyolaJayamaha/TracingWebApp/tree/milestone-1 >> [2] >> https://issues.apache.org/jira/secure/attachment/12740161/timeline.png >> >> >> On Tue, Jun 16, 2015 at 11:10 AM, Ayola Jayamaha <[email protected]> >> wrote: >> >>> Hi All, >>> >>> Attached here are the table schema and data for the join query I >>> executed. >>> >>> ./psql.py localhost:2181/hbase ../examples/school/school.sql >>> ../examples/school/STUDENTS.csv ../examples/school/SUBJECTS.csv >>> ../examples/school/MARKS.csv ../examples/school/school_queries.sql >>> >>> Above is the command I executed. But the last query >>> >>> SELECT M.GRADE >>> FROM MARKS AS M >>> INNER JOIN SUBJECTS AS S >>> ON M.SUBJECT_ID = S.SUBJECT_ID; >>> >>> doesn't give any results and when I check for the traces corresponding >>> the inner join query I couldn't find them. >>> >>> What might be the issue? >>> >>> Thanks. >>> >>> >>> school.zip >>> <https://drive.google.com/file/d/0Bxpj3lSPvr6WdW15bUc0YkdYemc/edit?usp=drive_web> >>> >>> >>> On Sun, Jun 14, 2015 at 9:58 PM, Ayola Jayamaha <[email protected]> >>> wrote: >>> >>>> Hi All, >>>> >>>> On the explain plan to show which part of the code is run where a graph >>>> is shown[1]. Default chart will be a Pie chart and I'm planing to use few >>>> more chat types so user can pick his choice. If any node responding slowly. >>>> Phoenix database administrator can exam the node and examin what are >>>> queries run on a particular time. >>>> >>>> I have run few examples on secondary indexes[4] and I got sample data >>>> and it can be used for the milestone1(end of this week). It is shown with >>>> timesliding capabilities. Trace segments are shown in a timeline.[2] >>>> >>>> Does filters mean 'where' like logic statements? The database admin can >>>> track the duration for a particular trace from timeline visualization so he >>>> can use the filters effectively (best order of the filters) in a query to >>>> get a quick respond. >>>> >>>> I tried the join query and it didn't give any results or corresponding >>>> traces. This is the reference I followed [3]. Is there any more steps to >>>> follow? >>>> >>>> To visualize the tracing details I looked through few charting >>>> libraries and I will give the comparison details over them. >>>> Please feel free to give the feedback on the mock uis. >>>> >>>> Thanks. >>>> >>>> [1] >>>> https://issues.apache.org/jira/secure/attachment/12739498/m1-mockUI-tracedistribution.png >>>> [2] >>>> https://issues.apache.org/jira/secure/attachment/12739499/m1-mockUI-tracetimeline.png >>>> [3] https://phoenix.apache.org/joins.html >>>> [4] >>>> http://ayolajayamaha.blogspot.com/2015/06/tracing-data-secondary-indixes.html >>>> >>>> On Thu, Jun 11, 2015 at 11:39 AM, Ayola Jayamaha < >>>> [email protected]> wrote: >>>> >>>>> Yes. It was a bit confusing :-). But it was useful to get a good idea >>>>> on the use cases. >>>>> Thanks. >>>>> >>>>> On Wed, Jun 10, 2015 at 11:57 PM, James Taylor <[email protected] >>>>> > wrote: >>>>> >>>>>> Excellent, Nishani (and you forgot to say "rambling" :-), but I'm glad >>>>>> it helped). >>>>>> >>>>>> On Wed, Jun 10, 2015 at 11:16 AM, Ayola Jayamaha < >>>>>> [email protected]> wrote: >>>>>> > Hi James, >>>>>> > >>>>>> > Thanks a lot for the lengthy and descriptive reply. I am currently >>>>>> looking >>>>>> > through UI components and charting libraries that can be used for >>>>>> the UI. I >>>>>> > refered [1] with regard to your explaination and came up with some >>>>>> mock ups >>>>>> > which I will share soon. >>>>>> > >>>>>> > Thanks, >>>>>> > Nishani >>>>>> > >>>>>> > [1] https://phoenix.apache.org/language/#index_hint >>>>>> > [2] >>>>>> > >>>>>> https://phoenix.apache.org/faq.html#How_do_I_create_Secondary_Index_on_a_table >>>>>> > >>>>>> > On Tue, Jun 9, 2015 at 11:39 PM, James Taylor < >>>>>> [email protected]> >>>>>> > wrote: >>>>>> > >>>>>> >> Hi Nishani, >>>>>> >> I'd recommend focusing on higher level use cases. From the user's >>>>>> >> point of view, they're executing a query and for some reason it's >>>>>> >> slower than they expect. How do they figure out why? >>>>>> >> >>>>>> >> They might first do an EXPLAIN on their query to see how Phoenix is >>>>>> >> executing it. Which parts are run where? Are secondary indexes >>>>>> being >>>>>> >> used as expected? Are filters being pushed down as expected? A >>>>>> better >>>>>> >> way to visualize the explain plan might be a good thing for you to >>>>>> >> start with. >>>>>> >> >>>>>> >> Second, assuming the explain plan looks good, they'll want to turn >>>>>> on >>>>>> >> tracing so that they can get runtime information on which parts of >>>>>> >> their query are taking the longest. >>>>>> >> >>>>>> >> Maybe more than one Phoenix table is involved - how will you >>>>>> display >>>>>> >> the tracing information across multiple tables for a query that >>>>>> does a >>>>>> >> join? Maybe you can punt on this first pass, and focus on single >>>>>> table >>>>>> >> queries. A related use case would be a DML statement that's >>>>>> executed >>>>>> >> and taking longer than expected. Let's say that the table being >>>>>> >> updated has one or more secondary indexes that are also updating >>>>>> the >>>>>> >> index tables. Seeing the entire picture of both the table writes >>>>>> plus >>>>>> >> the index writes on the same graph would be great. >>>>>> >> >>>>>> >> For the single-table query user case, what does the distribution of >>>>>> >> time look like across all the region servers participating in the >>>>>> >> query? Maybe some kind of graph that shows quickly if one region >>>>>> >> server is taking much more time than the others. Perhaps that's an >>>>>> >> indication that the table statistics need to be re-run, as there >>>>>> may >>>>>> >> be skew that's developed such that one of the threads is handling >>>>>> more >>>>>> >> data than it should. Or perhaps there's an issue with that >>>>>> particular >>>>>> >> region server. Was there something else going on at the same time >>>>>> on >>>>>> >> that region server, like a background compaction/split process? If >>>>>> >> that information is available in the trace table (not sure), it >>>>>> would >>>>>> >> be very cool to be able to superimpose that on top of the query >>>>>> trace >>>>>> >> graph. >>>>>> >> >>>>>> >> Another test might be to run a query over a different table and >>>>>> see if >>>>>> >> the same region server shows up again as being slow. So >>>>>> superimposing >>>>>> >> the query trace graphs of multiple queries might give the user some >>>>>> >> insight. >>>>>> >> >>>>>> >> IMHO, this is the kind of angle you should come at this from. >>>>>> >> >>>>>> >> Thanks, >>>>>> >> James >>>>>> >> >>>>>> >> On Mon, Jun 8, 2015 at 4:12 AM, Ayola Jayamaha < >>>>>> [email protected]> >>>>>> >> wrote: >>>>>> >> > Hi All, >>>>>> >> > >>>>>> >> > Basically what type of use cases are you expecting or performing >>>>>> at the >>>>>> >> > moment with regard to tracing? For example these are the use >>>>>> cases I'm >>>>>> >> > planing. >>>>>> >> > 1. Searching by parent id / trace id / description (regx search) >>>>>> >> > 2. Grouping and ordering the tracing information by time period. >>>>>> >> > 3. Counting the trace count per day / hour. >>>>>> >> > 4. Comparing and distinguishing two sets of tracing. >>>>>> >> > Thanks. >>>>>> >> > >>>>>> >> > >>>>>> >> > On Mon, Jun 8, 2015 at 4:00 PM, Nishani (JIRA) <[email protected]> >>>>>> wrote: >>>>>> >> > >>>>>> >> >> >>>>>> >> >> [ >>>>>> >> >> >>>>>> >> >>>>>> https://issues.apache.org/jira/browse/PHOENIX-1118?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel >>>>>> >> >> ] >>>>>> >> >> >>>>>> >> >> Nishani updated PHOENIX-1118: >>>>>> >> >> ------------------------------ >>>>>> >> >> Attachment: Screenshot of dependency tree.png >>>>>> >> >> >>>>>> >> >> Attaching the dependency tree on tracing. >>>>>> >> >> Pull request can be found here. >>>>>> >> >> https://github.com/AyolaJayamaha/TracingWebApp/pull/1 >>>>>> >> >> >>>>>> >> >> > Provide a tool for visualizing Phoenix tracing information >>>>>> >> >> > ---------------------------------------------------------- >>>>>> >> >> > >>>>>> >> >> > Key: PHOENIX-1118 >>>>>> >> >> > URL: >>>>>> >> https://issues.apache.org/jira/browse/PHOENIX-1118 >>>>>> >> >> > Project: Phoenix >>>>>> >> >> > Issue Type: Sub-task >>>>>> >> >> > Reporter: James Taylor >>>>>> >> >> > Assignee: Nishani >>>>>> >> >> > Labels: Java, SQL, Visualization, gsoc2015, >>>>>> mentor >>>>>> >> >> > Attachments: MockUp1-TimeSlider.png, >>>>>> >> MockUp2-AdvanceSearch.png, >>>>>> >> >> MockUp3-PatternDetector.png, MockUp4-FlameGraph.png, Screenshot >>>>>> of >>>>>> >> >> dependency tree.png, screenshot of tracing web app.png >>>>>> >> >> > >>>>>> >> >> > >>>>>> >> >> > Currently there's no means of visualizing the trace >>>>>> information >>>>>> >> provided >>>>>> >> >> by Phoenix. We should provide some simple charting over our >>>>>> metrics >>>>>> >> tables. >>>>>> >> >> Take a look at the following JIRA for sample queries: >>>>>> >> >> >>>>>> >> >>>>>> https://issues.apache.org/jira/browse/PHOENIX-1115?focusedCommentId=14323151&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14323151 >>>>>> >> >> >>>>>> >> >> >>>>>> >> >> >>>>>> >> >> -- >>>>>> >> >> This message was sent by Atlassian JIRA >>>>>> >> >> (v6.3.4#6332) >>>>>> >> >> >>>>>> >> > >>>>>> >> > >>>>>> >> > >>>>>> >> > -- >>>>>> >> > Best Regards, >>>>>> >> > Nishani Jayamaha >>>>>> >> > http://ayolajayamaha.blogspot.com/ >>>>>> >> >>>>>> > >>>>>> > >>>>>> > >>>>>> > -- >>>>>> > Best Regards, >>>>>> > Nishani Jayamaha >>>>>> > http://ayolajayamaha.blogspot.com/ >>>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> Best Regards, >>>>> Nishani Jayamaha >>>>> http://ayolajayamaha.blogspot.com/ >>>>> >>>>> >>>>> >>>> >>>> >>>> -- >>>> Best Regards, >>>> Nishani Jayamaha >>>> http://ayolajayamaha.blogspot.com/ >>>> >>>> >>>> >>> >>> >>> -- >>> Best Regards, >>> Nishani Jayamaha >>> http://ayolajayamaha.blogspot.com/ >>> >>> >>> >> >> >> -- >> Best Regards, >> Nishani Jayamaha >> http://ayolajayamaha.blogspot.com/ >> >> >> > > > -- > Best Regards, > Nishani Jayamaha > http://ayolajayamaha.blogspot.com/ > > >
