View Issue Details

IDProjectCategoryView StatusLast Update
0000730Main CAcert Websitewebsite contentpublic2013-01-15 14:10
ReporterDaniel Black Assigned ToSourcerer  
Status closedResolutionfixed 
Fixed in Version2009 Q4 
Summary0000730: statistics pages are slow
Descriptionmoved from

== Database optimisation ==
Investigate how we can tune our database and the database queries.

 * Contact for this task:
 * Working on it:
 * Comments:
  * Currently seems to be generated for each hit (it takes ages). This info should be cached, even if just by using curl/wget from cron to spit out the page every hour/day/etc. There may be other long running queries hurting us. (samj)
  * or have a stats table where changes that effect it are caught by a database trigger to update it (daniel).
TagsNo tags attached.
Reviewed by
Test Instructions


parent of 0000793 closedSourcerer stats.php consumes big time to finish - add caching feature 


Daniel Black

2009-05-03 03:24

reporter   ~0001394


2009-06-27 09:36

reporter   ~0001454

I'll try to fix this and implement it on the test system.

My current plan is to add a stats table. The stats.php checks if all the statistics it wants to show are in the stats table. If so it just displays that.

If not, it'll run the "heavy" queries to fill the stats table. If someone visits the stats.php daily, then this query won't be all that heavy as it only needs the changes from the last day.

This way the stats system is very confined. It doesn't depend on a specific RDBS, you can clean the stats table and the script will just fill it again, and you need no extra infrastructure like a cron/wget'ing solution. If nobody would be interested in the stats, it also wouldn't consume any power :)


2009-11-13 12:49

reporter   ~0001513

I believe I solved this issue. I was discussing my patches with Dirk Astrath but I had no response back in my latest mail to him, so I'll just put it here:

If you take a look at the stats.php file, around line 116, there's this query:

$assurers = mysql_num_rows(mysql_query("select `to` from `notary` where `when` like '$date%' and `method`!='Administrative Increase' group by `to` having sum(`points`) >= 100"));

This query is supposed to get the amount of new assurers (growth) in a certain month (specified by $date). However I think it's wrong.

I believe this query only counts those people that got 100 points in one month. Alot of people got assurer this way, going to a big event and getting assured by alot of people, but not everyone. So I think the results on the current stats.php are lower than what they should be.

Do you agree with me? Or is my brain malfunctioning? :)

I found this bug while comparing results from the current stats.php with my new stats.php script. So that also means it's almost finished ;) The speedup is near infinite, I'm just making sure it's results are correct for the moment (in the little time I got for it... :)).

My patches are located here:


2009-11-25 12:20

developer   ~0001516



2009-12-17 11:03

administrator   ~0001539

Caching has been implemented. Thanks a lot to all who helped!

Issue History

Date Modified Username Field Change
2009-05-03 03:23 Daniel Black New Issue
2009-05-03 03:24 Daniel Black Note Added: 0001394
2009-06-27 09:36 aphexer Note Added: 0001454
2009-11-13 12:49 aphexer Note Added: 0001513
2009-11-25 12:19 edgarwahn Relationship added parent of 0000793
2009-11-25 12:20 edgarwahn Note Added: 0001516
2009-12-17 11:03 Sourcerer Note Added: 0001539
2009-12-17 11:03 Sourcerer Status new => solved?
2010-07-27 15:20 Sourcerer Status solved? => closed
2010-07-27 15:20 Sourcerer Resolution open => fixed
2010-07-27 15:20 Sourcerer Assigned To => Sourcerer
2013-01-15 14:10 Werner Dworak Fixed in Version => 2009 Q4