Technofresh Web-based Graphical Statistics

Project Scope

The brief for this project was simple - daily average prices from the country's Fresh produce markets had to be stored indefinitely to build up a history and be queryied with answers displayed in graphical format via a web-based interface.

Neither software or hardware acquisition was possible - there was no budget. The solution had to share space on an existing webserver until it had covered ROI (consulting fees) and could support itself.

Method

  • Each day yields around 5500 records (records are only around 80 bytes each).
  • The project would take a year to bring in enough funds to allow for expansion - so the solution would have to cater for a record set of at least 1 320 000 and table size of around 100Mb
  • MySQL was chosen due to interoperability with other software on the existing webserver and there being no upfront license fee payable
  • PHPLOT was chosen as the renderer again due to no upfront license fee payable

A table was built and filled with 2 000 000 random records. After some initial tweaking of the table type and indexes used a query yielded a return in around 5 seconds. This included the failry complex grouping and where clause necessary to render a meaningful graph. This was deemed to be adequate and development proceeded.

Unfortunately one query wasn't sufficient to produce a graph that was sufficiently meaningful for the end user - at least 3 were required and had to run sequentially, so the turn-around-time was now 15 seconds (+ rendering time and transmission time - although these were neligible). - In fact it was determined that 3 was a bare minimum when during beta testing the need for even more was identified

The need for greater performance was obvious and at least limited funding was now available.

Outcomes

The system now runs from 2 tables, one mounted on a RAM drive (for queries only) and one on disk (to persist between reboots). The import software has been ammended to update both tables. The 5 second query time is now a fraction of a second.

Thanks must go to Andrew Ilgner, Dennis Bartlett and Ryan Kayser who have provided ad-hoc assistance on various phases of the project