It's the yaf_message_findunread which generates about 800 000-1 000 000 reads in the DB....

exec [dbo].[yaf_message_findunread] @TopicID=20370,@MessageID=N'309370',@LastRead='1903-01-01 00:00:00',@ShowDeleted=0,@AuthorUserID=2183

And the yaf_topic_list which generates about 150 000-250 000 reads in the DB....

exec [dbo].[yaf_post_list] @TopicID=20370,@PageUserID=2183,@AuthorUserID=2183,@UpdateViewCount=0,@ShowDeleted=0,@StyledNicks=0,@ShowReputation=0,@SincePostedDate='1902-01-01 00:00:00',@ToPostedDate='2012-04-11 19:21:55.673',@SinceEditedDate='1902-01-01 00:00:00',@ToEditedDate='2012-04-11 19:21:55.673',@PageIndex=0,@PageSize=100,@SortPosted=1,@SortEdited=0,@SortPosition=0,@ShowThanks=1,@MessagePosition=1452,@UTCTIMESTAMP='2012-04-11 19:21:55.673'

Together, these make more than a million reads for the same message... Can't really afford to have that kind of overhead even if the DB serves it within less than a second...

To me it seems like some optimization of indexes vs SP's could maybe help a bit...

However, the yaf_message_findunread is still very inefficient...

Since the option to store the last unread status in the database is the only reliable way to go for visiting user status since it is independent of server restarts and users using the forum from different computer. Maybe some thoughts could be given regarding a better performance for this feature.

I know it is a new feature, but it is very usable indeed and gives YAF a bit of "pro" status when it has been tuned a bit.

Other forums have this feature, so it's quite common, just needs some tuning...

Pleased to hear! 🙂

Yes, it's a known thing. The functionality was introduced because it dropped a limitation for number of posts in a topic. Previously, if number of posts exceded 1000 more this loaded seconds. Currently it loads a last most viewed page very fast from a cold start. Unfortunately there's a problem - bots which travel in all topic pages. I suspect that you've spotted exactly this.First topic pages are handled more slowly then the last ones too by design.

Every solution to the problem is a trade-off between RAM and CPU loads and nothing more.

Nature has its own laws and the more large forum you have the more higher are hardware requirements. YAF here is not a looser at all. I could point to some commercial products which pay a price for simplicity - resource hogging. So the complexity is not always bad.

Simplicity is worse than theft A Russian by-word 🙂

Looks like we need better seo handling to serve bots different links or at least add nofollow to the unread links, tecnically that should solve the problem.

It will not help much, because bots should index our forums anyway and we can't always detect bots which prefer to come incognito.

There's a universal solution. Everytime a bot is detected by IsCrawler value we can use a timer which slows doen it for several seconds or more. It can be ajustable. If we can't stop bots we can slow them down. If you have ideas how to clever inject the functionality it would be a great thing.

And it is indeed the problem, bots.... The guest account is not logged in the Forumreadtracking table either...

I did manually add it there, but that did not help at all...

I also modified the message_findunread sp with

if @AuthorUserId = 2183  set @LastRead = getdate()

I see no major reason for bots to put such a load on the site. It is quite a big problem for me...

I have actually had to ban some incogninto "magpie" bot IP ranges, since those are the worst. The Yahoo, Bing, Baidu, Yandex & Google bots are rather well-behaved.

Well if a bot is detected as bot we need to show different links which goes to. For example to the Print topic view thats how other forum software does it. I think that should solve our problems

Your site is quite fast. Cry wolf? Anyway the problem can be eased but other way. I can't make it right now as recently I've applied some other things and they need tests. I can't fine-tune some things to not overcomplicate an already complex data layer. But I've put it on the back burner for future.

Bots are not idiots and they check that they are not being duped into things like this, because some clever guys are abusing by this, and you can easily be banned for some time. At least by Google.

But it surely should be thinked over.

It's mostly about server load. The site seems fast since the "iron" is upgraded lately.

The server is a 4-core 64-bit Win 2008, SQL 2008 R2, with 12 GB of memory. But the server owner will upgrade it soon with an extra CPU (4 more cores) and up the memory t0 20GB... Mostly since the forum loads the server so much...

The guys running hosted solutions may not have this option to upgrade their "iron" to increase the performance.

Edit - seems like my message_findunread getdate() workaround works now also in the "live" environment....

I don't understand what do you mean by 1 million reads. There's exactly 1 sp call per link after a click.

Could you explain it?

Sorry, but I can't believe in the fantastic load. I visit your forum quite often and never seen something horrible on every server :wink: . But it's quite fine that you so care about perfomance. Because I care too.

The sp yaf_message_findunread makes over a million reads in the db, and consumes a lot of CPU...

The major part comes from one statement.

When 100+ simultaneous anonymous magpie bots "attack", as I had some weeks ago (banned the IP range to solve it), the site was bogged down and was really slow...