statistics are back up for pear.php.net

(30)

Do you develop a website? It is infinitely better to synchronize live and development sites using the PEAR Installer

(25)

doing the PEAR thing

(19)

Using PEAR 1.4.0 to install PEAR packages on a remote host

(19)

phpDocumentor and __get/__set/__call - give us your ideas (RFC)

(17)

Syndicate This Blog

RSS 0.91 feed

RSS 1.0 feed

RSS 2.0 feed

ATOM 0.3 feed

ATOM 1.0 feed

RSS 2.0 Comments

Powered by

Serendipity PHP Weblog

Tuesday, October 17. 2006

After 3 grueling days of work, statistics are finally back up and running for pear.php.net, and are running fast at pecl.php.net. You can see the results in action at http://pear.php.net/package-stats.php.

The saga of converting the stats started with figuring out how to process the 27 million records of the downloads table, and convert them into something more manageable. The solution aggregates downloads into a table of per-release/per-month counters, and pairs with the existing package_stats table that aggregates per-release of all time to deliver blazing performance on download statistics with instantaneous updates as downloads occur.

There were some scary moments, like when I accidentally set all of the download stats prior to october to the same value, and then proceeded to erase my local copy of all stats prior to october in a brilliant stroke of SQL. Fortunately, while re-dumping the downloads table in order to re-create the aggregated stats, I discovered a pre-existing dump of the pre-october aggregated stats data and simply synced it into the mysql server, saving the day.

This would not have been possible were it not for Lukas Smith's excellent database index debugging and suggestions of how to implement the actual conversion, so a big thanks in his direction. I think I'll go sleep now for a few days.

Posted by Greg Beaver in PEAR at 22:34 | Comments (8) | Trackbacks (0)

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

Excellent work! Greatly appreciated.

#1 Torsten Roehr on 2006-10-18 00:16 (Reply)

Yay, thanks!

#2 Lorenzo Alberton (Homepage) on 2006-10-18 00:42 (Reply)

This all sounds good and dandy but wouldn't it be more efficient to store those data bits in a say ... arrays in php files or something along those lines ?

It would save a database query even tho it might mean additional I/O but I'd say that's a fair comprimise since the database has always been the weak part of the whole pear web.

Anyway just my 2 cents, the current approach is tho nifty.

#3 Helgi Þormar (Homepage) on 2006-10-18 14:15 (Reply)

The database used to be the weak part because the queries were insanely inefficient. The downloads table had 27 milllion entries with no key on the most significant search terms. The key would have been impossible to create because insert would have become too slow. In addition a few other slow queries did not have proper keys, something that was fixed yesterday.

The sql dump of the database dropped from 3.2 GB to 180 MB after deleting the downloads table

.

Using a static file might seem better, but then we run into concurrency issues. Anything that isn't a single byte runs the serious risk of colliding writes, something a database handles much better.

In any case, there you have it - we're lean and mean now

#3.1 Greg Beaver (Homepage) on 2006-10-19 07:56 (Reply)

Looking at the per-category statistics.. all download counts seem to be on 0, and there's a bunch of categories that show no packages.

#4 Evert (Homepage) on 2006-10-18 23:14 (Reply)

Sure enough, must be a bug that needs fixing, and so we'll fix it

#4.1 Greg Beaver (Homepage) on 2006-10-19 07:59 (Reply)

Have you looked at the archive table type for all the download data and then using summary tables for counts for project and releases downloads?

#5 Jacques Marneweck (Homepage) on 2006-10-19 12:15 (Reply)

We actually can't consider archive, because the server is running MySQL 4.0. It may be a possibility on upgrade, but I don't think we'll even do that. More likely is to write a log scraper script and store the stats in the database with a cron job.

#5.1 Greg Beaver (Homepage) on 2006-10-19 13:48 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	Standard emoticons like :-) and ;-) are converted to images. E-Mail addresses will not be displayed and will only be used for E-Mail notifications To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly. Enter the string from the spam-prevention image above: BBCode format allowed You can use [geshi lang=lang_name [,ln={y\|n}]][/lang] tags to embed source code snippets
	Remember Information? Subscribe to this entry
Submitted comments will be subject to moderation before being displayed.