View Issue Details

IDProjectCategoryView StatusLast Update
0003961Composrcorepublic2019-11-24 02:24
ReporterChris GrahamAssigned ToChris Graham 
Severityfeature 
Status resolvedResolutionfixed 
Product Version 
Fixed in Version 
Summary0003961: Probabilistic and Logarithmic update of view counts
DescriptionComposr maintains various view counts (topic view counts, banner view counts, etc).

Writing to these each view has a performance impact. It should be a very small impact - but on InnoDB they have to do ACID redo logging in ib_logfile0 and ib_logfile1, multiplying the work needed. On a busy site with a stressed disk, this may add up. On MyISAM, there is table-level locking, which has its own performance ramifications.

A solution is to reduce our updating of view counts. With a probabilistic model we can reduce updating counts, knowing that they will be statistically similar to the true value if we increase the view tally by the inverse of the likelihood of writing one. To determine the probability of considering a view into the view count we'd do some formula based on the current view count, making sure that the figure is always within some reasonable order of magnitude in terms of accuracy.
TagsType: Performance
Attach Tags
Time estimation (hours)1.5
Sponsorship open

Activities

Guest

2019-11-20 21:30

viewer   ~0006166

Last edited: 2019-11-20 21:50

View 3 revisions

Lines that this will affect (from v9, as this is initially for a customer)...

$GLOBALS['SITE_DB']->query('UPDATE '.get_table_prefix().'banners SET views_to=(views_to+1) WHERE '.db_string_equal_to('name',$name),1,NULL,false,true);
$GLOBALS['SITE_DB']->query('UPDATE '.get_table_prefix().'banners SET views_from=(views_from+1) WHERE '.db_string_equal_to('name',$name),1,NULL,false,true);
$connection->query_update('attachments',array('a_num_downloads'=>$myrow['a_num_downloads']+1,'a_last_downloaded_time'=>time()),array('id'=>$id),'',1,NULL,false,true);
$GLOBALS['FORUM_DB']->query('UPDATE '.$GLOBALS['FORUM_DB']->get_table_prefix().'f_topics SET t_num_views=(t_num_views+1) WHERE id='.strval((integer)$this->id),1,NULL,true);
$GLOBALS['SITE_DB']->query_update('videos',array('video_views'=>$row['video_views']+1),array('id'=>$row['id']),'',1,NULL,false,true);
$GLOBALS['SITE_DB']->query_update('videos',array('video_views'=>$myrow['video_views']),array('id'=>$id),'',1,NULL,false,true);
$GLOBALS['SITE_DB']->query_update('images',array('image_views'=>$row['image_views']+1),array('id'=>$row['id']),'',1,NULL,false,true);
$GLOBALS['SITE_DB']->query_update('images',array('image_views'=>$myrow['image_views']),array('id'=>$id),'',1,NULL,false,true);
$GLOBALS['SITE_DB']->query_update('iotd',array('iotd_views'=>$myrow['iotd_views']),array('id'=>$id),'',1,NULL,false,true);
$GLOBALS['SITE_DB']->query_update('catalogue_entries',array('ce_views'=>$entry['ce_views']),array('id'=>$id),'',1,NULL,false,true);
$GLOBALS['SITE_DB']->query_update('news',array('news_views'=>$myrow['news_views']),array('id'=>$id),'',1,NULL,false,true);
$GLOBALS['SITE_DB']->query_update('galleries',array('gallery_views'=>$myrow['gallery_views']),array('name'=>$cat),'',1,NULL,false,true);
$GLOBALS['SITE_DB']->query_update('seedy_pages',array('seedy_views'=>$page['seedy_views']),array('id'=>$id),'',1,NULL,false,true);
$GLOBALS['SITE_DB']->query_update('calendar_events',array('e_views'=>$event['e_views']),array('id'=>$id),'',1,NULL,false,true);
$GLOBALS['SITE_DB']->query_update('poll',array('poll_views'=>$myrow['poll_views']),array('id'=>$id),'',1,NULL,false,true);
if (get_db_type()!='xml') $entry['ce_views']++;
$this->connection->query_update('f_members',$change_map+$extra,array('id'=>$id),'',1,NULL,false,true);
set_value('page_views',strval(intval(get_value('page_views'))+1));

Chris Graham

2019-11-21 02:46

administrator   ~0006167

Last edited: 2019-11-21 02:47

View 2 revisions

To stay within an order of magnitude of accuracy (accuracy changing logarithmically), that means a linear relationship between view count and the view count raise step.

I plotted some graphs and this works well...

Raise step = max(1, view count / 20)

Or expressed as probabilities...

Probability = min(1, 20 / view count)

Issue History

Date Modified Username Field Change
2019-11-20 17:34 Chris Graham New Issue
2019-11-20 17:34 Chris Graham Tag Attached: Type: Performance
2019-11-20 21:30 Guest Note Added: 0006166
2019-11-20 21:45 Chris Graham Note Edited: 0006166 View Revisions
2019-11-20 21:50 Chris Graham Note Edited: 0006166 View Revisions
2019-11-21 02:46 Chris Graham Note Added: 0006167
2019-11-21 02:47 Chris Graham Note Edited: 0006167 View Revisions
2019-11-21 02:48 Chris Graham Description Updated View Revisions
2019-11-24 02:24 Chris Graham Assigned To => Chris Graham
2019-11-24 02:24 Chris Graham Status non-assigned => resolved
2019-11-24 02:24 Chris Graham Resolution open => fixed