Hi All, Now it is clear. We can create a statement from the user's input query to the format in the description column and filter out the possible root spans of the traces of the query. Then by selecting the traces which have their parent ids equal to span id of the root span we can get all the traces relevant to the query. We can find the total duration for a particular statement. Interesting statements/traces can viewed as a timeline. Is this method alright?
Thanks, Nishani On Wed, Jun 24, 2015 at 11:21 PM, James Taylor <jamestay...@apache.org> wrote: > Yep, Jesse's right - the query is in the description column of the > root span of the trace. We'll need to include this in the trace UI, > otherwise the user won't have the context they need to know what > they're looking at. If there's something missing from the way we're > capturing, we can fix it. > Thanks, > James > > On Wed, Jun 24, 2015 at 9:09 AM, Jesse Yates <jesse.k.ya...@gmail.com> > wrote: > > There was some discussion (maybe internal to salesforce?) around how to > > include the query in the trace. I think the simplest we came up with was > > just adding it to the trace metadata (as an annotation?) and then you can > > pull it out later since you know the key it was stored as > > > > On Wed, Jun 24, 2015 at 9:05 AM Ayola Jayamaha <raphaelan...@gmail.com> > > wrote: > >> > >> Hi James, > >> > >> I find it difficult to come up with a method to include the SQL > statements > >> with the traces. But it is possible to filter out the traces for a > >> particular table for a given time period. > >> > >> Aggregating over the time spent for each SQL statement is possible. With > >> the relationship between parent and span ids it is possible to > >> differentiate between traces belonging to each query. > >> > >> Thanks, > >> Nishani > >> > >> On Wed, Jun 24, 2015 at 12:11 PM, James Taylor <jamestay...@apache.org> > >> wrote: > >> > >> > 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 > >> > <raphaelan...@gmail.com> > >> > 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 > >> >> <raphaelan...@gmail.com> > >> >> 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 > >> >>> <raphaelan...@gmail.com > >> >>> > 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 > >> >>>> <raphaelan...@gmail.com > >> >>>> > 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 < > >> >>>>> raphaelan...@gmail.com> 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 < > >> >>>>>> jamestay...@apache.org> 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 < > >> >>>>>>> raphaelan...@gmail.com> 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 < > >> >>>>>>> jamestay...@apache.org> > >> >>>>>>> > 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 < > >> >>>>>>> raphaelan...@gmail.com> > >> >>>>>>> >> 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) > >> >>>>>>> >> > <j...@apache.org> > >> >>>>>>> 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/ > >> >> > >> >> > >> >> > >> > > >> > >> > >> -- > >> Best Regards, > >> Nishani Jayamaha > >> http://ayolajayamaha.blogspot.com/ > -- Best Regards, Nishani Jayamaha http://ayolajayamaha.blogspot.com/
