da’s juust

Adriaan asked about handling a large mysql table with regard to a traffic log :
I have a similar problem with a rank tracker, a table that grows fast, and after a while queries become painful slow. I want to show results per domain on screen in less than a second the next two years (generally 5 to 20 urls per domain, with 200.000 urls for 80.000 domains per month).

warehouse

Some information systems offer a snapshot reporting system. Using it means shifting the focus from querying on raw entry tables towards aggregating the entry data into tables and reporting from these tables (technorati cosmos, google analytics, sap business warehouse). Successful systems minimize their costs (resources like database and application server capacity, and admin hours) whilst offering end users a rich data representation with short report generation time.

If I use the traditional “data function form” application model, I can optimise all three areas and avoid querying on large tables.

data

First the data layer :
For raw entries I use one archive table, one table to store entries of the current period,
and besides that, a set of aggregated data tables.

• for raw entries : a small temp-table and basic insert-queries
• for raw entries : an archive table with a periodic insert of the temp-table
• for end user reporting : basic queries on the snapshot/warehouse tables
• a procedure to archive raw entries
• a procedure to aggregate to snapshot tables

The insert instructions are performed on a small table, which works faster. Transforming data into information is done in the aggregation procedure, once a day or once a month. These are the same queries I would use for end-user reports, but now I report to the snapshot ‘warehouse’.

The cycles and time for operations that I would normally perform on the whole set for each report query, are stored in the warehouse aggregates. After aggregating the raw entry data, I archive the records (=dump the temp table into the archive table) and clear the temp tables for new input. With a bit of luck, I don’t have to access the archive anymore.

functions

After bringing down query-time in the data layer, I can diminish the number of times functions are run by caching parts of pages to disk, as html tables or a serialized array. In stead of generating the same table every time a user hits a page, I can store the generated table and retrieve it from a server cache directory. That can also save some connection handles.

forms

In the form layer I can use the apache gzip module and compress data sent to a clients browser which decreases traffic and page loading time.

evaluation
I am testing it with a site, for now it saves up to 80% in data traffic and page loading, next few months I’ll see if it holds up. It seems to work fine for registration systems with a lot of static historic data like a hit log, rank tracker, time sheets, banking statements.

Negative :

• more tables
• requires more programming and planning
• I cannot easily add ‘on the fly’ reports

Positive :

• minimal claim on database/application server resources
• more control over transformation of data
• good basis for a ‘rich’ frontend
• scalable
• fast pages
• no large tables, fast queries

I was playing with Google Trends using another blog. Of the seven or eight posts I tried, five made it to the details page, which gave me some traffic. I am too lazy to follow 100 entries during the day, so I want a simple report on how trends develop the past day : like this :

Now how do I get that done ?

First I make a cronjob that runs a routine to pull the Google trends xml-feed in every hour and store it in a database. I add a string for the period (year-month-day-hour).

That gives me a table like this one :

I want an output

The way to do that is a crosstable query, also called a pivot table .

MySql pivot table

I have to make a routine to turn that base table into a table with the dates as column, and per phrase the position in the correct column. I want a complete row, so I take the highest period from the table and the lowest, and from that intrapolate the other periods.

At some point I have to clean up the table, and I want the data to fit in one page,
so I will use 24 periods

… and delete all older records :

…that keeps the table small enough for quick querying. I have 24 periods to make columns, how do I get the position as value in each column ? I read the ‘wizard’ article on mysql pivot tables and that sounded difficult.

I don’t get it, but I got it running :

• group the records by phrase (”searchid”)
• use the array of periods as columns
• see if the phrase has a position in a specific period,
  • if so, take the position as value (it only has one per period)
  • otherwise take 0 as value
• sum the values
• name the column “T”+period

I take the average position from the grouped records, order it ascending and output that as table, first the two columns phrase (searchid) and average position, then the periods as column names.

I use a javascript library (MooTools), and the Zebra table class of David Walsh’ site. That makes it easier to read.

When I echo $CTB, the crosstable, this comes out :

Seeing rows per phrase means I can check a lot easier which trends are running, rising, dropping, or even returning. And that was what I wanted.

I zipped the files but I would not recommend downloading it. Next week, I’ll put on a better version of this disaster. It was just basically fun doing some cross-table stuff.

A rather obsolete post. I hacked a quick referrer tracking script together because I wanted to see how Googlebot indexed my serp tool pages. I can use Google analytics or phpMyVisites, but that is not as rewarding as keeping my own logs.

I start with a mysql table LOG to store the traffic data

on every page I track, I start with

The function log_hit() relies on the php $_SERVER array that holds all data the web server collects:

the connect_data() function is standard mysql connection

It is far from perfect, but it works. It helps check the crawl-rate of pages. You can filter out the useragent ‘Googlebot’ and run a query grouping useragent by date and see what times Googlebot comes by.

…or exclude all records if the UserAgent indicates it was a Bot, and get a listing of organic beings visiting the pages :

…that shows ‘organic’ visitors.

other resources
tek-tips.com/ Limit Requests per day by IP

phpMyVisites open source free referrer and traffic analysis