*It's fixed!* road.cc website issues — an apology and some updates

dave atkinson wrote:

yup, post count has gone. it will return. but it was an unreasonably heavy query in its current guise and needs a bit of finessing

This is something I've always wondered - if I was going to implement a post-count, I'd just iterate an INT in whatever database table most closely resembled our user profiles for each comment, and just leave it at that. If a post is added, it goes up by one, but if a post is deleted, it stays the same.

But I've noticed that our post-counts drop when trolls get banned (I've hit 400 posts at least three times now). Surely, it's not querying the database to calculate how many posts we've made, every time it our user-information is displayed?

Don't feed the trolls, and definitely don't quote them, then when they go too far, and they always do, and they are erased from history, your stuff will not vanish with them

BalladOfStruth wrote:

This is something I've always wondered - if I was going to implement a post-count, I'd just iterate an INT in whatever database table most closely resembled our user profiles for each comment, and just leave it at that. If a post is added, it goes up by one, but if a post is deleted, it stays the same.

But I've noticed that our post-counts drop when trolls get banned (I've hit 400 posts at least three times now). Surely, it's not querying the database to calculate how many posts we've made, every time it our user-information is displayed?

That requires extra logic though, so it's more usual to just do a sum of the comments that match a particular commenter. If you set up the relevant indexes (assuming a typical relational dB) then it should be a quick operation without having to worry about updating a count. When you don't have the relevant index or the query optimiser chooses the wrong plan, then you can start having full table scans which doesn't scale well. However, that's doesn't usually cause a noticeable problem until you start having millions of records.

Again, front-end guy, so I'll defer to the knowledge of people who know better, but that "logic" (one line of code) can be handled by (for example) whatever PHP script receives AJAX request for the commment. When it generates the SQL query to add the comment itself to database, it also generates one to increment the post count in the "user_info" table. Then, when that page is rendered, and the query is produced to retrieve the comment, it just grabs the value of "postCount" from "user_info". No need to calculate anything.

 Not only is calculating the post count from every comment still on the database an unnecessary overhead, it's also "wrong". If I've made 500 posts, then I've made 500 posts. It doesn't matter if 100 of them were responses to someone who's been banned, I still made the posts. I've been members of a lot of forums for various hobbies, and not an single one of them has reduced a users post-counts when threads/posts have been removed.  

hawkinspeter wrote:

BalladOfStruth wrote:

This is something I've always wondered - if I was going to implement a post-count, I'd just iterate an INT in whatever database table most closely resembled our user profiles for each comment, and just leave it at that. If a post is added, it goes up by one, but if a post is deleted, it stays the same.

But I've noticed that our post-counts drop when trolls get banned (I've hit 400 posts at least three times now). Surely, it's not querying the database to calculate how many posts we've made, every time it our user-information is displayed?

That requires extra logic though, so it's more usual to just do a sum of the comments that match a particular commenter. If you set up the relevant indexes (assuming a typical relational dB) then it should be a quick operation without having to worry about updating a count. When you don't have the relevant index or the query optimiser chooses the wrong plan, then you can start having full table scans which doesn't scale well. However, that's doesn't usually cause a noticeable problem until you start having millions of records.

The extra logic is just a database trigger, surely?

belugabob wrote:

The extra logic is just a database trigger, surely?

It's totally doable and personally, I'd slap in an extra field to keep track of number of comments. I do dislike database triggers though as once developers start using them, you end up with a mess of logic distributed between code and database triggers. You can also run into some issues with trying to update a row when you're also executing a query - often known as a mutating table issue.

dave atkinson wrote:

ktache wrote:

Well done.

Though we seem to have lost post count.

yup, post count has gone. it will return. but it was an unreasonably heavy query in its current guise and needs a bit of finessing

But without post count, how will we know to ignore someone as a one-post wonder...?

Good point.

Or a troll who changes their username.

Or the knowledge of a 10,000 post poster...

ktache wrote:

Or the knowledge of a 10,000 post poster...

Is there such a thing?

brooksby wrote:

But without post count, how will we know to ignore someone as a one-post wonder...?

If someone posts rubbish, then does it matter if they're a one-post wonder or a regular?

Will the "latest comments" section return to the front page as well? And will we ever get the capacity to click on someone's name and see all of their posts instead of a "404 no bikes allowed here" page? Please?

I didnt think you would need it with a photographic memory. 

And we're missing the new comments at the bottom of the home page.

My favourite place to find them.

Yep it's fixed now, it was a server issue after all! Apologies again. 

Thanks folks. Like a goalie your web support person is now either a hero (for sorting it) or leaving (for it happening in the first place).

chrisonatrike wrote:

Thanks folks. Like a goalie your web support person is now either a hero (for sorting it) or leaving (for it happening in the first place).

It's not usually worth firing someone over computer issues as they've now got experience on what not to do. The exception would be if someone repeats the same kind of mistake, but this doesn't sound like that case.

Jack Sexty wrote:

Yep it's fixed now, it was a server issue after all! Apologies again. 

I'm kinda curious now. Was it failing hardware or some software config issue?

mark1a wrote:

Site seems to be behaving currently. Is it too soon to say? 

Possibly moved? I briefly saw an error from Apache rather than nginx.

I saw an occasional Apache error a week or so ago, but I assumed that the backend (Drupal?) was using Apache with NgINX as the load balancer/reverse proxy. If it is Drupal, then it's not too difficuly to run that directly with NgINX (php-fpm IIRC).

boardmanrider wrote:

I am not surprises at all the errors; there are loads of CORS issues which will slow down a Wordpress based website down to mollases. Plus a bunch of 404s on files that really don't need to be loaded.

Just my two inflations on the ole track pump.

I thought road.cc ran on Drupal rather than Wordpress.

My money is either on database snags or hosting config issue. The former maybe based on the fact it works better when browsing as a non-authenticated user. 

You are indeed correct, it does appear to be Drupal. That said CORS will slow down a site which would as you suggest a hosting config issue.

Been told off for being too quick....

Came here for this, as they say...

Would the site not have to be renamed road10.cc (if one takes Godley and Creme's rather optimistic estimation...)