Tyrons coding blog

Why the phpMyAdmin Status Monitor is awesome

2012-01-25T17:14:00.001+02:00

So today I checked out my vServer performance using the phpMyAdmin Status Monitor that I have coded last summer, because a user notified me that a certain webpage doesn't load.
I couldn't figure out the problem as the site loaded normally for me and the server wasn't overloaded, however somehow I ended up hammering on the refresh button in the server tab to check what MySQL processes were running because it was fun and interesting.

So then one process did appear that was currently sorting the results of a query. It looked like a candidate for optimization. So I threw it into the query analyzer.

My assumption turned out to be correct. This query is used to determine when the visitor last visited the website, and it took 0.3 seconds to execute. It happens to be run on every page visit on of my websites.

So, being already in phpMyAdmin, I head over to the table in question and add an index for this field.

ALTER TABLE `cc_counter` ADD INDEX ( `ip` )

Now lets run this query again:

Well that sounds much better doesn't it! From 300ms to 0.8ms. Around 375 times faster now. While we're at it, we might as well write this query correctly.

As we only require the newest visit it is enough find MAX(time) of the visitor in question. Another 40% faster, which results in a speedup of around 525 times compared to the original.

So we not only saved ~0.3 seconds of loading time per visit but also reduced the required CPU time allowing greater workload on the server.

If you are running whatever self written code on your servers or webspace, get the new phpMyAdmin 3.5, check out your database load and optimize your queries. It's very gratifying work ;-)

P.S.: I'm aware that this might be an exceptional case, since a well designed database layout would have proper indexes from the very start. Nonetheless the Status Monitor is a great tool for surgical improvements on bad code like this one. Also in projects that slowly grow over time the chances are not too low to miss out on creating an index where it would be needed.

Anego CMS Modules - Report 6

2012-01-03T12:55:00.002+02:00

Due to the holidays this report has been slightly delayed ;-)
However, I'm happy to report that the gallery module is now mostly finished and only a number of smaller features are missing. Since the last report I've completed:
- Store/Restore image size and crop area
- All the server side code
- Default sizes from which the user can select from. This includes default sizes for the original image, the preview image and the crop area.
- Pictures can be deleted.
- There's now default sizes for the original and preview image which is a per-gallery setting.
- Drag&Drop image reordering is finally working too. I've had difficulties getting jQuery UI Sortable to work, the bug being wrong event bubbling.
- Added a very simple pagination for the blog (not related to the gallery module)
- A ton of bug fixing

The gallery module is now also being tested and running the gallery on my wife's website saraty.com. I was happy to hear that she is very pleased with the simplicity of setting up a gallery. Below is a screenshot of an image being edited.

Until the end of January the only things left to do are:
- There is one bug left to fix with image reordering
- Classic style file upload with file selection
- If there is time left, there could be some per-gallery-batch-image-resize so one could easily change all the preview and original image sizes.
- Write documentation.

Anego CMS Modules - Report 5

2011-12-18T00:42:00.001+02:00

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

2011-12-04T19:35:00.001+02:00

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

2011-11-20T10:47:00.001+02:00

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

2011-11-05T16:56:00.004+02:00

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

2011-10-22T22:04:00.000+03:00

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

2011-10-08T21:51:00.000+03:00

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

2011-09-09T23:11:00.001+03:00

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

2011-08-22T10:09:00.004+03:00

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

2011-08-14T22:20:00.003+03:00

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

2011-08-08T09:43:00.003+03:00

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

2011-07-31T16:08:00.009+03:00

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

2011-07-30T16:38:00.011+03:00

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

2011-07-24T22:47:00.005+03:00

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

2011-07-17T23:25:00.003+03:00

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

2011-07-11T16:55:00.009+03:00

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

2011-07-10T21:33:00.004+03:00

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

2011-07-10T20:41:00.006+03:00

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

2011-07-03T00:37:00.000+03:00

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

2011-06-26T23:44:00.002+03:00

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

2011-06-19T23:48:00.002+03:00

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

2011-06-13T00:22:00.003+03:00

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

2011-06-07T22:39:00.013+03:00

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

2011-06-06T01:10:00.002+03:00

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

2011-05-28T23:03:00.000+03:00

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

2011-05-23T18:13:00.000+03:00

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

2011-05-15T22:00:00.000+03:00

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

2011-04-30T14:24:00.009+03:00

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

2011-04-26T11:18:00.002+03:00

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!

10 Great Libraries/Links for Web2.0 Websites

2009-11-24T02:13:00.010+02:00

A few great Javascript Libraries and other Links that I used and which I'd like to share:

1. Animator

Create perfectly smooth animations with this library, you can even use CSS to describe the animation transitions. There's probably no animation idea Animator couldn't handle.

Easy to implement and fairly easy to use.

2. Lytebox

A packed, single file version of the Lightbox 2, which is great for overlaying pictures on your website. Particularly great for galleries, as it let's you navigate through the images.

Very easy to implement and very easy to use (just add a rel parameter to your linked images)

3. Drag&Drop Tree

If you want intuitive file management, then drag&drop in trees is the way to go. The gurus at dhtmlgoodies.com offer you one good implementation of that. Though it took quite some adjustments for me to get it working smoothly, it was a great help to me.

4. Fancy Uploader

The one thing HTML4/XHTML is missing: Multiple file upload within one dialog. A great tool to make your uploads more user friendly. A bit hard to set up though.

5. Really Simple History

I haven't used this one yet but I'm planning to implement this into my website. One problem with making a website fully AJAX is that the functionality of the history button gets lost. This lib is one way how to bring it back.

6. PNGfix

IE6 is not the newest browser, but still being used by a lot of people. And one big frustration about making a website IE6 compatible is that it's lacking alpha transparency. This small script automatically fixes all PNG-Images in your source code through a microsoft filter. All you gotta do is to load the script.

7. Ajaxload.info

A collection for fully customizable Web2.0 loading animations.

8. Tips for Making Websites Run Faster

A great Firefox plug in that gives you many tips on how to optimize your website speed

9. Silk Icons

1000 Free-to-use awesome-looking 16x16 Pixel icons. I use them everywhere

10. The Free Sound Project

A collection of free sounds

Web 2.0 Thoughts

2009-11-22T02:52:00.019+02:00

As a web developer you are confronted with a massive wave of emerging technologies like scripting languages (PHP, Python, Ruby on Rails, Zope,..), data storage systems (MySQL, PostgreeSQL, SQLite, XML, Cloud), protocols (JSON, AJAX/HTTP), web services (twitter, youtube, gmail, facebook,..), content management systems (Typo3, Joomla,...) and ever growing software, addons and libraries.

Nowadays being a web developer is a completely different matter like it was 7 years ago when I started my "Internet-career". Back then, it still was an achievement to be able to create your own file upload and file listing in simple HTML and PHP. Over the years, the server side scripting languages have become less and less important and follow a shadowy existence by now, as the client scripting side, namely JavaScript, was put in the spotlight with it's great promise: AJAX.

If you want to have a modern website it is infeasible not to use JavaScript, which increases the efforts by much more than it was 7 years ago.

We all follow the hidden obligation to re-implement 40 years of user interface design into every common browser, without breaking compatibility to older browsers or technologies (like the Browsers Back button).

That not only requires a big set of expertise and will of learning but also endless nights of experimenting and trying out which configuration works for which browser.

Gladly, many many excellent tools and libraries have been written, available for the greater public on which I stumble upon from time to time.

I'm particularly astonished about all the existing JavaScript Libararies like Prototype, jQuery, DoJo, Mootools, etc. serving Multipurpose functionality to the mass. They seem to have a big establishment in Web2.0 Websites by now.

Frankly, I was never a big fan of bulky libraries when you have the chance to put together or program the things you need yourself.

How to make your PHP Website UTF8 ready

2009-10-17T20:24:00.014+03:00

After many days of frustration I think, I hope, I finally figured out how to configure my server and php code the right way to enable bug free UTF-8 on my website, which certainly took me a day or two to achieve. Lets begin with the...

Browser
From what I have seen is that at least Firefox and IE listen just to the Line 'Content-Type: text/html;' charset=utf-8' in the HTTP header and simply ignore my meta tag on my websites:

<meta http-equiv="Content-Type" content="text/html;charset=utf-8">
So the first thing I did is to add this line of php code before any text is printed out: header('Content-type: text/html; charset=utf-8');

But its also possible to achieve the same using the AddDefaultCharSet directive in the apache config.

MySQL Database
MySQL allows collations to be set on each table and text/char field. As the MySQL manual already tells this setting won't concern you at all until you start searching with unicode characters. So you can leave that to whatever setting you want when you don't search for it.

PHP Code
I've ran into several difficulties when writing user input from a form into my database.

The first thing I had to change is to append the parameter accept-charset="UTF-8" to all my <form>'s otherwise you'll get some ISO encoded chars in your DB.
Secondly, I ran into a bug when using htmlentities() converting umlauts wrongly. I realized you also need to tell htmlentities() the charset in which you want to work on. I solved it with using htmlentities($mytext,ENT_COMPAT,'UTF-8');

AJAX
When I was in the process of getting UTF-8 working I always suspected that my AJAX code / XMLHttpRequest() was some of the cause of the problems. But the only point where you can do something with the charset is by setting the request header (myRequest.setRequestHeader) for the right content-type (just like with the browser http header) before sending your http request. And I guess when you don't explicitly specifiy your charset there the Browser uses the Content-Type charset given from the websites http header.

So no tweaks needed here.

If you got this all your done! Your Site should work for 99.5% of your users.

Why UTF-8 sucks

2009-09-30T05:24:00.001+03:00

Because you can configure it at every goddamn place. Assuming I want to set up an ordinary website with php and mysql. Then you can configure it at:

Apache config
PHP config
PHP MySQL module config
MySQL general config
Each text/char/varchar field in the particular MySQL tables
When you process the Data in your PHP Script and when you receive/transport Data via GET/POST
In the metadata each HTML site
On each AJAX Request you make
On each browser visiting the site

Thats not only plain confusing for the admin but between each program/module you have the possibility to somehow screw up something with the conversion if not all of them is set to UTF-8
It's always been a pain in the ass to make my websites UTF-8; some users always had problems getting it displayer correctly.

Now i got the problem that normally submitted data works fine, but data containing umlauts submitted over AJAX are displayed wrongly. Hail utf-8 -.-