1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Fixed Inefficient update of thread view counter

Discussion in 'Resolved Bug Reports' started by digitalpoint, Feb 5, 2013.

  1. digitalpoint

    digitalpoint Well-Known Member

    Every hour a cron job kicks off that spins through every thread viewed at least once in the last hour to update it's view counter.

    For us, it makes our DB server queries per second chart look like this:


    The spikes are the counters being updated (1 query per thread viewed in the last hour). When you have 30,000+ unique threads being viewed every hour, it starts to be a little silly with 30,000+ SQL write queries going on.

    You can make it much more efficient by JOINing against a temporary table and updating based on the JOIN and rewriting the XenForo_Model_Thread::updateThreadViews() method to use a query like this:

    UPDATE xf_thread
    		SELECT thread_id, COUNT(*) AS total
    		FROM xf_thread_view
    		GROUP BY thread_id
    	) AS xf_tv ON (xf_tv.thread_id = xf_thread.thread_id)
    SET xf_thread.view_count = xf_thread.view_count + xf_tv.total
    I just tested it and rather than doing 30,000 SQL UPDATEs every hour, it was able to update all threads that needed it with a single query that took 0.06 seconds to run (against a DB with ~2.6M threads).
  2. digitalpoint

    digitalpoint Well-Known Member

    Side note... it probably would be more efficient for xf_thread_view to have 2 columns (thread_id and count) and just do an INSERT ON DUPLICATE KEY UPDATE. On average threads are going to be viewed more than once per hour, so creating 100 records with the thread ID for 100 views doesn't seem terribly efficient vs. thread ID + the number 100. And as a bonus you wouldn't even have to create the temp table that summarizes records like shown above.
  3. Mike

    Mike XenForo Developer Staff Member

    Trying out the sub query method now. No schema change to that table yet. I think it was done that way to allow DELAYED inserts so we didn't have to worry about potentially slowing down the load to write the record.
    TBolley and Slavik like this.
  4. digitalpoint

    digitalpoint Well-Known Member

    Is it actually an issue? You could relegate it to some sort of shutdown query or something if so?
    Adam Howard likes this.

Share This Page