Hi Nishani, Any progress on your module for including the SQL query in the UI?
Thanks, Nick On Wed, Jun 24, 2015 at 11:46 AM, Ayola Jayamaha <raphaelan...@gmail.com> wrote: > Hi All, > I'll create a javascript module in angular to solve this issue and share. > > Thanks, > Nishani. > > On Thu, Jun 25, 2015 at 12:09 AM, James Taylor <jamestay...@apache.org> > wrote: > >> Yes, exactly right. >> >> On Wed, Jun 24, 2015 at 11:35 AM, Ayola Jayamaha <raphaelan...@gmail.com> >> wrote: >> > 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/ >> > > > > -- > Best Regards, > Nishani Jayamaha > http://ayolajayamaha.blogspot.com/ > > >
