XF 1.2 No rgistration statistics since we moved to Xenforo

[Stuart Wright]

The daily stats report for the last month shows no registrations since two days before we migrated to Xenforo.
Is something not set up right?

 

[MattW]

What does this SQL query return?

SELECT * 
FROM xf_stats_daily
WHERE stats_type =  'user_registration'
AND stats_date > '1382659200'

EDITED with date for 25th

 

[Chris D]

If you do that query ordered by date descending, the most recent date it has stats for is 24th October.

I just did a cache rebuild for daily stats and it didn't appear to report back any status until it eventually finished successfully.

Just running the Daily Stats cron now...

Here's the problem:

Request Timeout
This request takes too long to process, it is timed out by the server. If it should not be timed out, please contact administrator of this web site to increase 'Connection Timeout'.

I'm going to look at what stats handlers are extending that, because of course the cron is building add-on stats as well.

 

[Stuart Wright]

What does this SQL query return?

SELECT *
FROM xf_stats_daily
WHERE stats_type =  'user_registration'
AND stats_date > '1382659200'

EDITED with date for 25th

Empty result set.

 

[Chris D]

Does disabling an add-on actually prevent its stats from being rebuilt? As far as I can tell, because it's defined as a content_type in the database, all that does is check if the stats handler class exists, in which case even for a disabled add-on, the class exists.

Stuart, rather than disabling these add-ons, I might edit the add-ons stats handler classes and get them to return without processing anything. If it magically rebuilds the default content no problem then we can investigate which add-on caused the issue.

 

[Mike]

They should be disabled, as the content type cache removes content types for disabled/non-existent add-ons.

 

[Chris D]

Forgot to update this earlier.

So the issue was that the daily stats Cron entry was timing out.

@Mike very kindly gave us some advice and the solution was to add an index to the xf_post table on the post_date column. Ideally you should only do this if you're having issues, and preferably only via shell and while your board is either closed or very quiet. If you have a lot of posts it will take a considerable amount of time.

The Rebuild Statistics cron now runs within seconds, though.

Thanks again, Mike.

 

[Alpha1]

Is this something that other big boards will likely encounter as well? I have about 300-500 new users per day. (Not really sure because we deny a lot)

 

[Chris D]

Potentially. It's actually nothing to do with the number of registrations. But the rebuild stats cron of course rebuilds all of the content types, including posts. In the case of AVForums and its nearly 20 million posts, the lack of that index grinds the process to a halt.

The easy way to know whether the issue affects you or not is to just run the Cron manually in the Admin CP. If it takes longer than around 5-10 seconds, you're likely to want to add this index.

There are no other detrimental effects to not having that index.

 

[Alpha1]

It seems fine on my development installation. Mind that there are no active users on it. I only have 1.2 million posts.

 

[Chris D]

If you wanted to time it before and after the index, to add the index on your development installation, the query is:

ALTER TABLE `xf_post`
ADD INDEX `post_date` (`post_date` ASC);

It will take some time to run. You could of course run the same query on your live installation (preferably during a quite time). It doesn't hurt having the index there.

 

[Brent W]

Curious if it doesn't hurt having it there why it isn't there by default (ELI5) lol

 

[Chris D]

The issue hasn't arisen before now and the index would only ever be used while that cron runs and nowhere else.

 

[Stuart Wright]

And we have statistics. Many thanks for your help.

 

[HWS]

@Mike, may I suggest this index to be included into the core package?
It does not hurt and may help boards that grow beyond a certain size.

 

[Chris D]

I think it should be done as required.

Even if it was done on a smaller board that doesn't require the index, adding that index is going to take a considerable amount of time. Not appropriate for upgrades and maybe not appropriate while the board is active due to the load of the index being built.

 

[HWS]

I think it should be done as required.

Even if it was done on a smaller board that doesn't require the index, adding that index is going to take a considerable amount of time. Not appropriate for upgrades and maybe not appropriate while the board is active due to the load of the index being built.

I know that this takes some time. All changes and handling of xf_post does at large boards. This is another reason why it should be included into the core product. It is easier to be changed as long as you do not have too many posts.

 

[Stuart Wright]

It's easy to change if you have a massive board, you just have to manage it more.

 

[HWS]

It's easy to change if you have a massive board, you just have to manage it more.

Being an admin of a very large board myself, we've changed this (and some other) indexes some time ago.

My philosophy is that if there is something that does not scale well and can be prevented easily, you should to this before it breaks.

 

[CTXMedia]

I've added the index to CycleChat's post table and re-run the cron manually, but my stats for 4th Nov are showing as zero - is this likely to right itself after tonights scheduled run do you think or would that be related to something else altogether?