Week 5 report

Little to report this week. I've again fixed a lot of bugs and visual issues on my existing code, such as:

• For live charting there was a 'race condition' bug. When the user changed the chart refresh rate while an ajax request is in progress, it would the ajax requests would start twice each interval. 
• For users where client and server are in a different timezone the chart did not work. I fixed this by always using the server time. 
• And as planned, I have done a considerable amount of research on how to build a query optimizer. I came to the conclusion that I will build a tool similar to the MySQL Query optimizer from Oracle.
  And finally, I have already started coding this tool this week.

So my plan for the next few weeks is to build:

• A page where the user can monitor as many variables as he wants. It will offer a UI to add/remove charts.
• If the db server is local, the user can also select to display cpu usage and memory usage
• I want to have one or several possibilities to display queries for a certain timeframe, extracted from the general_log and/or slow_query log.
• Either some sort of data recording or a play/pause button + zoom feature

Having such functionality should allow one to systematically identify badly performing queries. 

Although it seems so obvious now to build a query optimizer in this way, it took me quite a lot of thinking to figure this out. So I'm glad I've accomplished that this week. 

Week 4 report

Right in the beginning of Week 4 my mentor proposed to merge all the coding I've done so far to the master branch of pma. So I spent most of the time formatting the code, adding localization and tracking down bugs which were plentiful, since I didn't test my code with all configurations.
That and other problems (1. git making problems because my repos is synced over dropbox, 2. my wife's flight on Friday got cancelled due to technical problems, 3. my hurting shoulders force me to make regular breaks from the PC)  kept me from reaching all the goals I planned for this week.

What I did this week:
- Clean, localize and fix my code for merging it to the master branch
- Process/Connection charting on the status page
- Server variable editing on the variables page. This will be really helpful for users tweaking mysql server performance. Also the table width adjusts to the window width now, because long variables cause the table to be over 100% wide, through that you can't see the variable name and it's value without scrolling horizontally.

What I will do next week:
- I probably will need to spend most of the time to flesh out some concrete concepts on how to make good use of the slow query log and general log. Simply displaying those tables will not result in something useful, so it might be a good idea to instead generate charts over the log data. That will require more research on how performance issues can be pinned down with those 2 logs. So in the process I also might have some starting point for a sort of 'performance advisor' system that I planned. Secondly, I guess I will have to build myself some sort of testing environment, that is, simulating heavy load on my local mysql server.

- Also I still hope to implement the data selection for table charting that I planned to build last week.

Week 3 report

I've achieved most of my goals except for the network traffic graph, mostly because I don't know how to aesthetically include it to the current tab. Also by Suggestion of my Mentor I didn't implement JPEG and PDF export as it is not really required.
One really nice thing I've done now is putting awesome looking highcharts everywhere and deleted all pChart related files from my repository.
And while I was replacing the table charting I realized that the previous implementation was veeery basic, so I added some more features like allowing which column to use as x-axis and therefore removed the margin field because they seem pretty useless to me. Here's a comparison (for the old version I had the data in the wrong format):

Also for big amounts of data, the graph (in the old and new version) is completely overwhelmed, so I'm thinking of adding a feature that allows you to select which region of data you want to display. Last friday I was mostly investigating on how to do that. Probably will use some really good feature from Highcharts.


Something I forgot to mention last week: In my original proposal I suggested to use the slow_query_log with a long_query_time set to zero. But in my tests it appeared that not every query is logged although it's supposed to. Unfortunately there is little information available about setting long_query_time to zero; I also asked in the official mysql forums with no real success a long time ago. I probably will have to use the general query log alone and/or make a bug report about this hoping for it to get fixed.
That is a bit unfortunate because the slow_query_log would have provided us with useful information which is not present in the general_log. Either way, I'm still going to build a 'slow queries' tab to help identify slow queries.

And finally I'm thinking of whether I should build some small interface that allows one to edit the variables on the variables page. That probably might also be quite helpful when trying to optimize the server.


What I did this week:
- Processes + Connections chart in the server traffic tab on the server status page
- Chart Export now also works for SVG, whereas JPEG and PDF is now removed
- Replaced pChart charts everywhere with Highcharts
- Quite some bug fixing and code cleanup


What I'm planning for next week:
- Getting an idea how to place the network traffic graph and implement it
- Add above discussed data selection to the table charting, if it doesn't consume too much time.
- Add a 'slow queries' tab to the server status page
- Probably adding some edit feature for server variables.
- With the remaining time: Evaluate some useful information extractions from the general_log

Small progress report

A before/after screenshot after replacing pChart with Highcharts for the data when query profiling is enabled. The Highcharts-chart also shows a more informative, better looking tooltip than pChart does.

Week 2 report

I'm really happy how my second week turned out because I've got the permit from my Mentor to replace pmas current and rather undynamic, server-side charting library pChart with a client-side library called highcharts which gives me much more possibilities and speed improvement. The reason pChart was selected for use in pma is that an image generated chart would allow the user to save/export it. However in the way pChart is currently implemented a export is not possible because the charts that are being sent to the client are split into 20 pieces using Data URI Scheme for compatibility reasons (IE8 limits data urls to 32kb).

What I did this week:

- My initial goal to merge some of the pages turned out that I've currently put the processes list into the status pages, whereas the other pages Charsets, Engines and Variables have been put and the end of the menu bar by Marc, which is also a good solution.

- I've successfully added real-time charting the way I planned it. Currently it's working for queries sent to the server on the query statistics tab, which also gives very useful information on each points tooltip where it displays what type of queries have been sent. And I'm happy that after lots of research I could solve the main concern of client-side charting libraries, which is the exporting of charts. Highcharts does have an export feature, but it's relying on either an external service provided by highcharts.com or uses Batik, a library written in Java to parse and convert SVG. Since the pma team (and me too) prefers to not rely on an external service (and adding java as requirement is out of question) I added a small js library called canvg that can convert SVG-Graphics into a html5 canvas element which is already stored as image internally. With only minimal changes I was able to adjust the already provided export feature by highcharts to make it convert to a html5 canvas and is then sent via POST to a php script that basically just echos it back with a force-download header. I might add the lib Downloadify, that would save us the echo service altogether.
Best of all is that, charting and exporting should both work in all major browsers. Exporting in IE8 currently does not work due to a bug in canvg that I have reported and already is acknowledged by the original author and thus hopefully fixed soon.

What I'm planning for next week:

- Currently there is only one real-time chart for queries. I'd also like to add one for processes and connections  and maybe for network traffic.

- The chart export currently only exports to PNG, I will look into the code to see whether I can also add SVG, PDF and JPEG export.

- But most of all, I will be looking forward to replace pChart with highcharts. Since pChart is used at only 3 places this should be fairly easy (but maybe time consuming). Replacing the charts for query statistics and profiling will be a piece of cake, since the charting data is being sent along anyway. Only the work on replacing pChart for graphical display of query results will be a bit bigger.