View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000730 | Main CAcert Website | website content | public | 2009-05-03 03:23 | 2013-01-15 14:10 |
| Reporter | Daniel Black | Assigned To | Sourcerer | ||
| Priority | normal | Severity | minor | Reproducibility | sometimes |
| Status | closed | Resolution | fixed | ||
| Fixed in Version | 2009 Q4 | ||||
| Summary | 0000730: statistics pages are slow | ||||
| Description | moved from http://wiki.cacert.org/wiki/InfrastructureEnhancementsSystemTasks == Database optimisation == Investigate how we can tune our database and the database queries. * Contact for this task: philipp@cacert.org * Working on it: * Comments: * Currently http://cacert.org/stats.php 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). | ||||
| Tags | No tags attached. | ||||
| Reviewed by | |||||
| Test Instructions | |||||
|
|
http://wiki.cacert.org/wiki/InfrastructureEnhancementsSystemTasks?action=diff&rev2=13&rev1=12 |
|
|
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 :) |
|
|
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: https://aphexer.ulyssis.org/cacert/ |
|
|
@see https://bugs.cacert.org/view.php?id=793 |
|
|
Caching has been implemented. Thanks a lot to all who helped! |
| 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 |
