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)