Anego CMS Modules - Report 5

In the last 2 Weeks I've been busy implementing a small image editor that allows the user to crop and resize images for the gallery. It turned out to be unexpectedly much work to create a 3-in-1 Image resizer, Cropper and Dragging tool that has some usability. Hence, I wasn't able to write the server code for it yet, which is however the easiest part. For the next report there's a number of functionalities that I have to build:

• Store and restore the image size and cropped area so a user doesn't need to select it from anew if he wanted the picture just a bit bigger.
• Server code for cropping and resizing
• Allow image editing of both thumbnail and full size picture. For this I have already refactored the image editing code into a jquery plugin for it to be modular.

For this project I will probably drop my plans for improving the blog and focus entirely on the gallery. I realize it's a better choice to have a feature-complete, versatile gallery module, rather than having 2 not very finished modules.

Anego CMS Modules - Report 4

Since last update I've added a settings dialog for each image so users can change their properties such as description and thumbnail. This information is also saved and stored in database now.

Also I've found a great image cropping tool named Jcrop which I probably will be implementing until next time. I'm also considering to add a thumbnail resize feature like in this demo.

And finally I've been fixing a few bugs in the dialog code.

Anego CMS Modules - Report 3

In this 2 weeks I've had to do some major refactoring of Anegos dialog code so that it allows multiple dialogs. This will be needed so e.g. one can open a gallery settings dialog while being in the edit mode.

In the process I had to touch a lot of code, so I also cleaned up much of the code style. I've also found a number of bugs that I fixed.

The progress on the Gallery itself though hasn't gone much further. I've made only small additions and until the last day I spent a lot of time getting jQuery UI Sortable working on the images so one can Drag&Drop them but I failed. There must be something bugged with that widget.

Anego CMS Modules - Report 2

Unfortunately I couldn't find much time to invest on this Project so I will have to catch up within the following 2 weeks.

Anego CMS Modules - Report 1

Week 1
I've spent most time contemplating on how to build a good UI for the Gallery module and researching on HTML5 File upload. Since there is already existing libraries for Drag&Drop file upload I don't have to reinvent the wheel. However as this technology is fairly new, I guess not to many solutions exists for it yet. At least not as jQuery plugin, which for me is a must for better code quality. In particular I've been weighing the pros and cons of:

• jQuery filedrop: A basic do-it-yourself Drag&Drop file upload that only covers the actual uploading and lets the user implement all the UI related stuff.
• jQuery File Upload: A fully featured Drag&Drop file upload with GUI that has it all, but requires jQuery UI and the jQuery Templates plugin.

In the end I decided to go for jQuery filedrop because I have a strong desire to directly integrate the uploading part into the gallery view, which is not possible with the already integrated UI of jQuery File Upload. 


Week 2
This week I mostly invested in a massive refactoring of the module system of Anego and improve the code style in many areas to increase the readability and extensibility. Towards the end of the week I was able to implement the basic Drag&Drop file upload and thumbnail generation. The most important infrastructure for the gallery module is now ready. This basically means the most important design decisions are made and it's mostly just fleshing out each functionality now.

Also I'm am happy to report that Anego also has been accepted as part of a project for my Lecture 'Advanced Software Engineering' where a Team of 6 Students (including me) will do some great improvements on the core  system. The blog and gallery module stays untouched by this though - they are treated as a different project.

A new Project: 2 Anego CMS Modules

Starting from last week, I have begun a new project that will focus on 2 modules of my experimental content management system Anego CMS. This project will end not later than January 2011 due to the fact that this project will be part of my bachelor studies in software engineering.

Anego is a new, basic and still pretty unstable (in terms of backwards compatibility) cms that I have been working on for the last few years and is powering not only my website (tyron.at) but also my wife's (saraty.com), my brother's (simpleit.at) and any small business websites that I can make my customer (I've got 3 so far!) ;-)

My CMS is using a new model for representing a website page - it is broken down into elements that can be dragged and organized to the users preference. Each of these elements is seen as a "Content Element" plugin/module that users may add and remove to Anego.

There already exists a very basic blog module that allows the creation, editing and deleting of blog entries and a simple comment feature to each blog entry. My Job is it now to improve the blog module, that include the following points:
- Module Interface - The current implementation of module support within Anego is rather unstable, it may have to change quite substantially, thus any already written modules will need to be adjusted accordingly
- Pagination - Currently all blog entries are displayed in one page. Proper pagination needs to be added.
- Archive and Navigation - There currently no overview over the written blog entries, an intelligent way of displaying a list of blog entries needs to be found, as well as implementing archivation features (navigation by year/month or similar)
- RSS Feeds - A blog without RSS support is quite useless.
- Settings: A settings dialog for changing some basic properties such as commenting, entries per page, etc. should be added.
- Comment Anti-Spam and maybe signup by google/facebook/openid/etc: The current implementation of comments may be vulnerable to spam
- Ideally possibility of writing hidden drafts and preview

Also I will implement a Gallery module that should have at least following features:
- HTML5 Multi-Upload-Support and ideally batch-resizing of Pictures
- Display gallery as grid with fancy zooming
- Drag&Drop reordering of pictures
And depending on the required time investment I would also add:
- Gallery Settings: Allowing some display options such as thumbnail size, picture descriptions, pre-loading of images, image zoom type, maybe watermark
- Different gallery display types: classic gallery, image slider, etc.

So altogether its going to be an exciting winter!

Web Application Security Scanner

Yesterday I stumbled upon a whole range of software I didn't know it exists. I've found Netsparker, that can automatically scan a website for vulnerabilities such as SQL Injection and Cross Site Scripting threats. All it needs is the URL to your website and it searches and exploits every possible interaction with server side scripts.

There is a Community Edition of Netsparker, which is free but has limited features. Nonetheless it helped me find *a lot* of SQL Injection and XSS threats on my websites. If you run any type of non-static website, I'd strongly suggest to try out this tool. I am damn glad to have used it.

You might also want to check out the comparison of 60 similar tools in this area.

Week 13 report

Since my project was pretty much completed already I didn't have much left to do other than to keep testing and build some additional features.

I originally had only a few regex's to pretty print the sql queries which of course doesn't work for recursing patterns of subqueries. So I've used the tokenizer from CodeMirror and the MySQL Mode to iterate through each token and pretty print it.

What I made this week:

- Rewrote the SQL Pretty printer for the Query Analyzer dialog.

- Easy editing of byte values on the Server variables page

- Lots of fixes and small adjustments

Here's a screenshot that shows the majority of what I've been building this summer:

Week 12 report

My Project is completed. The only thing that is left to do is to improve the code style, write some documentation on the phpMyAdmin wiki, fix any occurring bugs and tweak some things here and there.

I still added a new feature this week which took a lot of rewriting in the monitor code. It is now very easy to add preset charts over calculated values. I've already added two, which is for Query cache efficiency and Query cache usage (%). The reason why I had to rewrite a lot is because in case of the query cache usage, it also required a server variable (query cache size). So I've adjusted the chart configuration code to be more generic about the required data.

What I did this week:

- One new feature: Preset charts over calculated values. Newly added: Query cache efficiency and query cache usage (%).

- Improved page load performance by loading the monitor code on demand (+ other speed improvements)

- Implemented and improved the new tooltip system made by Aris

- Added and adjusted some rules for the Advisor system

- Made lots of code style improvements

- Lots of bugfixing

What I will do next week:

- Documentation

- Keep tweaking and scrubbing the code :-)

- Build stuff from my week 10 ideas list again with the leftover time

Week 11 report

This week I have been coding hard on the Advisor system that I planned for a long time. It uses the mysqltuner 2.0 config as a base, though I have added much more information about each rule/recommendation.

Here is an example rule in mysqltuner2.0

Query cache efficiency (%)|||<20|||Qcache_hits/(Com_select+Qcache_hits)*100|||query cache not efficient, consider increasing query_cache_limit.

This is how it looks in the pma version:

rule 'Query cache efficiency (%)' [Com_select + Qcache_hits > 0 && !fired('Query cache disabled')]

Qcache_hits / (Com_select + Qcache_hits) * 100

value < 20

Query cache not running efficiently, it has a low hit rate.

Consider increasing {query_cache_limit}.

The current query cache hit rate of %s% is below 20% | round(value,1)

As you can see, there is now 3 text lines. They are displayed title with 'issue','recommendation' and 'justification'. This way the user can make a more wise decision on how to improve the performance (or ignore the recommendation altogether)

This rule syntax is being parsed by a simple rules engine I have coded this week.

What I made this week:

- Advisor system:

  - Built rules file and rules parser

  - User interface that displays the recommendations

- New status variables "filter" that allows one to display the unformatted values (so one can copy and calculate own perfomance measures)

- Fixed a security issues with last weeks changes and other small improvements

What I will do next week:

- I will need to find a good way of adding i10n to the rules file

- Improve code style, write documentation etc.

- With leftover time, build some of the features from last weeks idea list (week 10 blog report)

Week 10 report

This was a really interesting weekend. I've been digging through my newly discovered MySQL tuning tools most of the time, and discovered 2 great ideas that I could include to my monitor.

First is the mysqltuner 2.0 ruleset that I already mentioned, which I plan to add this week.

And the other is mk-query-profiler from the maatkit command line tools. It uses 'SHOW SESSION STATUS' to read lots of very interesting data about single executed queries. This would be a wonderful addition to my 'Query Analyzer Dialog'.

What I made this week:

I started out realizing that the slow query log loading didn't work at all, so I had to do some bug fixing there. Having the slow log working again I noticed that I forgot to implement grouping/summarization of the slow query columns such as examined_rows, sent_rows, lock_time etc. It took quite some code rearrangement to fix this.

And I kept finding new bugs that I had to fix first. One for example was that the query profiling information in the 'Query Analyzer Dialog' was recorded for the wrong query.

In the remaining time of the week I implemented

- chart config import and export using json as data format

- chart title and series label editing dialog

- a small look into drizzle support. With a small patch most of the status page now works under drizzle (some values don't exist and return php notices though)

- a fix for cpu charts not displaying anything

- error handling when the pma session runs out. It now displays a dialog asking the user to reload the page

- in the 'query analyzer dialog' it now displays all explain outputs (there are multiple explain rows when multiple tables are used)

- added a check for mysql versions that don't support table logging (< 5.1.6)

So lots of small needed fixes and adjustments, plus a unexpected power outage on friday that lasted most of the day kept me from writing actual code for the advisory system.

What I will do next week:

- Advisory system using mysqltuner 2.0 ruleset

What I still would like to do for phpMyAdmin, though not mission critical for my project:

- Display chart from display table page: Handle big amounts of data with zoomable charts

- Debug server variables table width auto-adjustment (doesn't always adjust properly)

- Fix UI of create table dialog / enum editor

- Status Monitor: Add disable general_log + truncate table link to setup dialog

- rewrite charts page on the wiki

[Edit 01.08.11]

- One more great idea: For the Monitor charts: Allow charting over calculated variables, like the query cache hitrate (Hit rate = Qcache_hits / (Qcache_hits + Com_select))

[Edit 02.08.11] I never run out of ideas -_-

- Fix the query analyzer sql formatter to not fail on subqueries or on occurring strings like "this is where the formatter fails"

- Add a 'Search' - link to the monitor tab allowing one to make searches over slow log / general log and their EXPLAIN's (e.g. find all queries using temporary tables)

The extinction of the MySQL Tuning tools

On my journey of implementing tuning/optimizing tools for phpMyAdmin I've came across many already existing open source tools in that area.

I'm starting to realize though, to my surprise, that most of these tools haven't been updated or gained new features for a year or more. You rarely get to read recent blog postings about variable tuning either.

The most well known tools are:

• mytop - Like the *nix top but for mysql threads. Last release is from Feb 2007
• mysqlreport - '...makes a friendly report of important MySQL status values'. Last release on April 2008 and official statement of no longer being maintained.
• mysqltuner - makes tuning recommendations. Last release from April 2011 but from what I could see only bug fixes and cosmetic changes since it's first release in 2008
• mysqltuner 2.0 - a misleadingly named mysqltuner fork by Sheeri K. Cabral. Last release in April 2010. Was only active for 5 months.

So I'm quite baffled that I seem to be the only one who's currently working on a open source MySQL tuning tool, despite the fact that MySQL is getting quite popular nowadays. I really wonder why so little progress is being made in this direction. Though, I'd love to hear that this is not the case and there's is some recent tools I just haven't found.

Either way, while looking at the tools, I saw that mysqltuner 2.0 uses a comprehensive set of rules to build the recommendations. Since it has been build by a database professional, actually quite a celebrity in the db world, from what I could read, I will probably use it as the basis for my suggestions/advisory system. Gladly the GPL-License allows me to do so :)

This is very useful since it will save me a lot of work "inventing" the recommendation rules, for which you need a lot of MySQL experience.

Also I'm the only pma gsoc student that posts project related stuff which is not a weekly report! :(

[Edit 31.07.11] Ok, I just ran into all the more recent tools that I failed to find, the most extensive one being maatkit. Definitely gonna check out that one!

Week 9 report

This week did quite turn out as I planned to but ended with good results nonetheless.

An unusually big amount of time I had to invest in tracking down a series of bugs.

For the CSS Sprites I built last week, I had to rewrite PMA_linkOrButton() which is a core function of phpMyAdmin, so that took quite a while as well. But in the progress I was able to fix a bug and make the code cleaner.

I still didn't find the time to improve the settings import/export and IE compatibility.

But apart from that I have built a 'Query analyzer' Dialog that is reachable through clicking 'SELECT' - Statements in the Log Table. It allows one to get profiling and 'EXPLAIN' information about that query. I have also built a very simple Javascript MySQL Formatter do display the query nicely in an editable area, so one can also tweak the query and check for execution time changes.

And the last addition was another possibility to filter the log data. If enabled, it removes variable data in WHERE statements with regexp magic, then regroups the queries. So for example if you have a user table that has a name and user id column and your code does hundreds of queries to get the name of different user ids, these queries will be grouped together into one.

And finally I have now a concrete idea on how to build the Advisory/Suggestion system.

- I will add a few advisories inside the query analyzer dialog, using the profiling and explain information.

- On the 'Status/Server variables' Tab I will add a button that allows the user to start a variable analyzer that throws out suggestions for the server variables based on the values of the status variables.

Once that is done, my GSoC project is completed :-)

What I made this week

- 'Query Analyzer' - Dialog. Pretty prints the query, shows profiling information, shows EXPLAIN information

- Filter: Group queries, ignoring the variable data

- Lots of bug fixing

- More work on CSS Sprites, also now documented on the wiki

What I will do next week

- Hopefully get to the import/export part

- Advisory system

- [Edit 26.07] I forgot that I still need to implement the chart editing feature. It should allow one to rename the chart title and series.

- [Edit 28.07] I also forgot that I still need to fix the appearing empty spaces in the chart grid when changing columns count

Week 8 report

This week I've done a good portion on finalizing the monitor, such as chart ordering and i10n. After that I've invested a considerable amount of time in replacing the big amounts of icon images in phpMyAdmin with CSS Sprites, since they improve page loading speed and allow for better style customization.

Since the Monitor is now not far from finished, I can start focusing on some type of Advisory/Suggestion System that can give the user hints for optimizing their configuration and server queries. I will need to put together a plan on how to infer those advisories and a proper visualization of them. For that I've already done some research of whether I can solve that with a business rule engine - as it would be just wonderful to have all these advisories/suggestions getting inferred from a set of well organized rules. To my disappointment there seems very little available for PHP. But I will keep looking.

What I made this week:

- Disable CPU and Memory graph for database server that are not local(-host)

- i10n

- Wrote a jQuery Plugin for convenient element reordering in tables, since I couldn't find any good solution for that: http://tyron.at/tmp/ts.html

This allows for easy reordering of charts now.

- Replaced chart size settings with amount of columns. The charts automatically size to the dimensions of the viewable area.

- The chart ordering is now saved as well. It will keep its order after a reload/restart of the browser.

- Improved usability of the returned log data table by adding a 'total sum' row at the bottom and adding a way of filtering the query texts. So now one can just type a table name and you get to see how many queries have been executed on that table (not pushed to my repo yet).

- I've spent a lot of time to add CSS Sprites to all of phpMyAdmin to replace the big amounts of single icon files currently being used. In a few tests I noticed a reduction of 20-30% of GET requests on a page load, resulting in the page being loaded faster. But it will also make it easier to customize themes and will make the pma code more readable.

What I will do next week:

- Still need to work on that settings import/export & IE compatibility

- Some feature that allows you get profiling information from the queries in the returned log data

- Check out what the possibilities are for displaying statistics or list over server queries that failed with an error and whether it would be of any use to read from the log files instead of the log table.

- Flesh out a concept for the Advisory/Suggestion system.

Under attack

Sigh, I'm currently trying to reduce the damage caused by a DDoS attack on my server.

A hacker has found a weak spot in one of my oldest websites (the PHP code I wrote for it is like 5-8 years old). I've taken the site offline now, nonetheless the flood of requests still seems to slow down the server. But at least it responds again.

The hacker has abused the fact that the comments list on that site has no limit in how much shall be displayed, so the hacker completely overloads the MySQL Server by flooding requests to load all 51000 Comments. Most of you probably gonna slap your forehead when you read this, I do too ;) But this website was made when I started with PHP and serves only a really small gaming community. I have left that community years ago, but left the site online. And the code remained mostly untouched since then. I will need to take it down permanently some day, and let that collection of really bad PHP code rest in peace.

By the way the first indication of a DDoS Attack I found through my newly programmed Monitor Tab in phpMyAdmin which showed me that some queries have been executed several thousand times. So thats an epic win right there :D

Looking at the logs, the attack is truly distributed. I checked some of the IPs with reverse DNS -the results: Saudi Arabia, Brazil, one ip from Amazon Web Services, China, Romania, ....

Might generate some statistics over all these ips once the attack is over, that would be awesome to have.

[Edit:]

Mini-Statistics: (counting from like 1 hour after the attack has started)

Amount of DDoS Attacks: 202.299

Amount of IPs from where the attacks come from: 1.106

GSoC Project LoC

Since I love making statistics, I checked very roughly how much lines of code I've added to the phpMyAdmin codebase so far. This does not include a bunch of smaller patches and small code changes, and of course a lot of it is newlines and comments:

functions.js : ~100 LoC

server_status.php: ~1000 LoC

server_status.js: 1380 LoC

server_variables.js: 160 LoC

tbl_chart.php: 140 LoC

tbl_chart.js: 240 LoC

Total: 3020 LoC

Week 7 report

I've been making good progress on the 'Monitor' Tab. Now the user has the ability to select a time span in any chart, which will load intelligently grouped data from the logs.

Also the current configuration is now saved in the browsers local storage (however, currently it doesn't save how the charts are ordered).

I'm quite happy how the monitor turned out so far, though there's still a lot of rough edges that I will have to even out ;-)

Regarding intelligently grouped data:

Currently, selecting log data does the following (more will come):

- Returns only INSERT, UPDATE, DELETE, SELECT

- Groups together queries that are identical and therefore adds a 'count' column to the result table.

- Truncates long INSERTs and UPDATEs (above 300 chars)

- If selected, groups together INSERTs into the same table.

- Also creates some statistics what query types have been found in this time range

What I made this week:

- Linux CPU / Swap Usage are now correct

- Lots of UI work on for selecting data from the logs. The user can now select a time span in any chart that will load the logs from the general_log or slow_log in that given time span.

- Play/Pause button

- An Instructions/Setup dialog that contains useful information on how to use the Monitor, as well as a bunch of links that allow the user to configure the server to start logging.

- A first implementation of saving the settings to the browsers storage (localStorage)

- A lot of other small UI improvements (e.g. edit mode, better chart tooltip)

What I will do next week:

I will have a lot of small things to do for the Monitor:

- Take into consideration that not every server is on localhost

- Test Monitor without super user rights

- Proper l10n

- A better "chart grid". I think one should be able to select how many 'columns' of charts he wants to have. Maybe some autosize to window width would be useful too.

- More ways to save the monitor settings

- IE compatibility
- Import/Export config
- Save how the charts are ordered

- More ways to extract the data you need for optimizing server/queries

- Generally improve the Monitor to make it more usable for query optimization. Need to research on this a bit more.

If some time is left, I will improve some other parts of pma on which I have wrote a lot of suggestions on the mailing list (i.e. create table dialog).

Week 6 report

This week I made some more progress on the new 'Monitor' tab. But I was also looking up on other query/server optimizer tools, to see how they solved it.

There's is 2 very good presentations from Oracle and the Pythian Group that I recommend watching to anyone who is interested in this topic.

What I made this week:

- A bit of more research how other query optimizer tools are built

- In the 'Monitor' Tab the charts can now be removed again and the UI is a bit better

- For all the charts, the chart size and refresh rate is changeable (this included building a tiny popup box)

- I've implemented cpu, memory and swap usage for windows and linux, however cpu and swap for linux still seem to display incorrect values. Some calcuation must be wrong or a name mistyped, I couldn't figure it out yet.

What I'm going to do next week:

- Fix cpu and swap chart for linux

- Below the charts will be starting to build a UI for selecting data from the slow query log and general log

- Some sort of play/pause button

- When there's time left: Safe the charts to the browser localstorage (next version will add saving to db)

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.

Week 1 report

As mentioned in my previous blog I spent the last days learning for a really difficult (and probably last) exam that I had yesterday (and gladly turned out well), so I had not so much time to work on pma.
What I did accomplish however is a bigger reorganization of the code, so that the single tabs of the status page can be refreshed via ajax. Also I fixed some bugs that I introduced with the changes I made in the week before as well as some fixes in other parts of pma. And finally I have fixed the reported bug 'Query Statistics add to well over 250% - ID: 3242060'.

Altogether I would say my project goal 'Restructure the page, add more interactivity to it' has been completed, and I have come up with a good idea for server status charting which is what I'm going to work on next week. Also I want to merge together some pages, which I already wanted to do last week, but lacked the time.

My initial idea of using a cronjob to record server status variables over time in order to chart them has the big disadvantage that additional setup is required which not every user can do as well as a low data density since cronjobs can not run more often than once every minute.

So I'm thinking to first implemented a windows task-manager-like charting that does an AJAX request every x seconds and thus slowly filling the chart. That allows for accurate real-time information which does not require any setup. For long term server status charting, a cronjob method could still be implemented as alternative.

Looking forward to build more cool stuff! :)

First day of work

So today is the first official working day for the google summer of code. Gladly I could finish quite some work already last week so I can spend some more time learning for my last exam on friday.

What I've done so far for the status page:
- Reorganized the layout and separated the statistics into tabs
- JavaScript filtering by name, category and alarm values.
- Query chart loads asynchronously to speed up page loading

I also added the JavaScript filtering by name to the variables page.

What I'm going to do this week:
- i18n for the added Texts
- some CSS fixes, that will also improve other parts of phpMyAdmin
- Putting together Charsets, Engines, Processes and Status page into one page
- From what time is left: Further research on a proper history recording for server load and query history.

Prepared to code

Since I have a fairly difficult exam (statistics & probability theory - how fitting ;), on may 27 I thought I start doing some coding a week earlier so I have enough time to learn in the evenings.

So tomorrow, I am going to restructure the status variables into one big table and then add filtering options that filter values with javascript. This can happen without AJAX, so it should turn out really fast.
I'm thinking of a simple textarea that filters the values as you type, as well as a checkbox to filter for bad values (the ones marked red) and a drop-down list for filtering by category.

Once that is done, I will start my work on some proper server status recording & charts.

Some better colors for charts

I've decided to browse through some of the phpMyAdmin code in order to get more familiar with it.

So I started my day by looking through all the include files of server_status.php (finding all kinds of curiosities like /* We must do hard way... but almost no chance to execute this */) and somehow ended up with recoding the chart classes to make them use theme-specific colors, since I found those green colors awfully unfitting from the day I saw them. Also, when I'm going to rebuild the Status page, I don't want to have green graphs there ;)

So this is the result in the themes original and pmahomme (before/after pics):

Since there was such a nice 'layout.inc.php' in each theme folder defining some colors, I thought it'd be best place to put the color definitions.

I will be posting the patch to the mailing list and push the changes to my repos once I figured out how it works :|

GSoC 2011: More Statistics for the status page

I am very delighted to hear that my proposal for 'Query statistics & charts for the status page' has been accepted to the Google summer of Code Program.

In the course of the next few months I will turn the phpMyAdmin Status page from a plain collection of server variables into a sophisticated query statistics and analytics tool, as well as server health statistics. But I also really would like to add some completely unplanned improvements since I always get countless ideas what could be done better while I'm coding. However, I will make sure that it's actually useful for everyone by communicating actively about my progress and ideas on the pma mailing list.

Here is a short summary of what I've planned, for now:

• Restructure the status page. Organize status variables into jQuery Tabs.
  
  
• Add some code that allows for recording of server status data, and use those in return to generate graphs about the general server health. In the process of this I would also like to make sure that the configuration of the database (where the data will be stored in) can be done easily through the phpMyAdmin interface, and not through the config.inc.php. Unfortunately this feature will be limited to users that have the possibility to set up cronjobs.
  
  
• With the help of MySQLs slow_query_log we can extract a bunch of very useful graphs such as:
• Amount of queries by user
• Amount of queries vs time chart, to help identify at what times the server is more busy
• List of particularly slow queries
• Common queries that use non-indexed fields in the WHERE Statement, which is a well known performance pitfall.
  
  
• To some degree, I'd like to develop a tool that helps with optimizing queries. I would do this by looking at well known optimization techniques and trying to identify programmatically where they could be applied to.

I am very excited in implementing these features, since I regularly use phpMyAdmin myself and always enjoy looking at the statistics. So these changes will benefit me directly when working on future Website projects.

Lastly I would like to thank the phpMyAdmin Mentors for choosing my project and Google for this amazing program!