YAFLogo

BWG
  • BWG
  • 100% (Exalted)
  • YAF Lover Topic Starter
11 months ago
Hi,

I'm using v3.1.13 CP and the CPU usage has suddenly surged. On investigation, it is SQL server which is consuming about 70% of the available resources, so I've had to take the forum offline temporarily. This has not happened before and usage at the time the problem started was light. The event log shows multiple entries like the one below. (I've blanked out the full url path). 

Any ideas? 

No posts were found for topic 54.36.149.20 **************/default.aspx?g=posts&m=838147Mozilla/5.0 (compatible; AhrefsBot/7.0; +http://ahrefs.com/robot/)No posts were found for topic [
                            topicId:16393, userId:1, updateViewCount:False, 
                            showDeleted:False, sincePostedDate:1/1/1903 12:00:00 AM, toPostedDate:12/13/2023 6:34:41 AM, 
                            pageIndex:0, pageSize:20, messagePosition:0]
                         
Sponsor
tha_watcha
  • tha_watcha
  • 100% (Exalted)
  • YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2
11 months ago
I had similar issues with such useless bots. Thats why since YAF 3.1.15 its possible to block user agents like that AhrefsBot
tha_watcha
  • tha_watcha
  • 100% (Exalted)
  • YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2
11 months ago
Regarding to the event log entries those come from dead topics which contains no posts or only deleted posts. You should find and delete the topics under Admin > Maintenance > Restore & Delete Topics.
BWG
  • BWG
  • 100% (Exalted)
  • YAF Lover Topic Starter
11 months ago
I've taken a backup of the database and restored to my local development server. When trying Admin > Maintenance > Restore & Delete Topics I eventually get the following timed out message. There are about 80,000 pages because the database has existed a long time! Is it possible to just delete these topics from SQL sever directly?



[HttpException (0x80004005): Request timed out.]
   System.Web.HttpContext.InvokeCancellableCallback(WaitCallback callback, Object state) +372
   System.Web.UI.<ProcessRequestAsync>d__554.MoveNext() +454
   System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +32
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
   System.Web.TaskAsyncHelper.EndTask(IAsyncResult ar) +65
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +426
   System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +172
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +158
 
tha_watcha
  • tha_watcha
  • 100% (Exalted)
  • YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2
11 months ago
The page loads all topics the paging is not server side paging. I didn't expect someone with so many topics and messages. I put this on my todo list to implement sql paging.

You could manually delete all topics and messages from the Topic/Message Table with the Isdeleted flag set to true.

BWG
  • BWG
  • 100% (Exalted)
  • YAF Lover Topic Starter
11 months ago
If I run:

Delete FROM [YAF].[dbo].[yaf_Message] where IsDeleted = 1

The query fails because of a foreign key constraint:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_yaf_Message_yaf_Message". The conflict occurred in database "YAF", table "dbo.yaf_Message", column 'ReplyTo'.
The statement has been terminated.
 
BWG
  • BWG
  • 100% (Exalted)
  • YAF Lover Topic Starter
11 months ago
I've had chance to investigate further and what I'm seeing is:

1) Consistently very high CPU usage on the worker process - YAF is the only application running under this particular application pool. SO I think the earlier ahrefs bot may have been a non-issue

2) I've run DebugDiag 2 (Microsoft diagnostic tool) on the server and it is

i)  Notifying "High CPU usage between dump files was detected on one or more threads."

ii) Showing the following in the Top 11 threads by CPU

Thread 4344 - System_Web_ni!System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr, IntPtr, In
tPtr, Int32)  
Thread 6116 - System_Web_ni!System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr, IntPtr, In
tPtr, Int32)  
Thread 4412 - System_Web_ni!DomainNeutralILStubClass.IL_STUB_PInvoke(IntPtr, System.Web.RequestNotificationStatus 
ByRef)  
Thread 6112 - [[InlinedCallFrame] (.SNIReadSyncOverAsync)] .SNIReadSyncOverAsync(SNI_ConnWrapper*,
 SNI_Packet**, Int32  
Thread 3452 - YAF.Core.dll!Unknown  
Thread 116 - System_Web_ni!System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr, IntPtr, In
tPtr, Int32)  
Thread 6108 - [[InlinedCallFrame] (.SNIReadSyncOverAsync)] .SNIReadSyncOverAsync(SNI_ConnWrapper*,
 SNI_Packet**, Int32  
Thread 5408 - System_Web_ni!System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr, IntPtr, In
tPtr, Int32)  
Thread 4328 - System_Web_ni!DomainNeutralILStubClass.IL_STUB_PInvoke(IntPtr, System.Web.RequestNotificationStatus 
ByRef)  
Thread 6140 - [[InlinedCallFrame] (.SNIReadSyncOverAsync)] .SNIReadSyncOverAsync(SNI_ConnWrapper*,
 SNI_Packet**, Int32  
Thread 4500 - System_Web_ni!DomainNeutralILStubClass.IL_STUB_PInvoke(IntPtr, System.Web.RequestNotificationStatus 
ByRef)  

iv) In the top thread above, the top function stats are:

System_Data_ni!SNINativeMethodWrapper.SNIReadSyncOverAsync(System.Runtime.InteropServices.SafeHandle
, IntPtr ByRef, Int32) 
ServiceStack.OrmLite.dll!Unknown 
YAF.Core.dll!Unknown 
YAF.Web.dll!Unknown 
YAF.dll!Unknown 
App_Web_45hd2zr0.dll!Unknown 
App_Web_sv0tltxt.dll!Unknown 
YAF.Types.dll!Unknown 
ServiceStack.OrmLite.OrmLiteResultsFilterExtensions.ConvertTo[[System.ValueTuple`2[[System.Int32, ms
corlib],[System.Int32, mscorlib]], mscorlib]](System.Data.IDbCommand, System.String) 
ServiceStack.OrmLite.OrmLiteExecFilter.Exec[[System.ValueTuple`2[[System.Int32, mscorlib],[System.In
t32, mscorlib]], mscorlib]](System.Data.IDbConnection, System.Func`2alueTuple`2> 
YAF.Core.Data.DbAccessBase.Execute[[System.ValueTuple`2[[System.Int32, mscorlib],[System.Int32, msco
rlib]], mscorlib]](System.Func`2>, System.Da
ta.IDbCommand, System.Data.IDbTransaction) 
YAF.Core.Model.MessageRepositoryExtensions.FindUnread(YAF.Types.Interfaces.Data.IRepository`1pes.Models.Message>, Int32, System.Nullable`1, System.DateTime, Boolean) 
YAF.Pages.Posts.GetFindMessageId(Boolean, Int32 ByRef) 
YAF.Pages.Posts.BindData() 
YAF.Pages.Posts.Page_Load(System.Object, System.EventArgs) 

Any ideas or pointers on where to start looking in the source code?  I'm thinking if I can find the function call which is causing the problems I may be able to simply comment out as an interim workaround.

 

tha_watcha
  • tha_watcha
  • 100% (Exalted)
  • YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2
11 months ago
Thank you for the info. Can you try to modify the following code in the file ..\Pages\Posts.ascx.cs

replace the code block

   // find first unread message
 var lastRead = !this.PageBoardContext.IsCrawler
     ? this.Get<IReadTrackCurrentUser>().GetForumTopicRead(
         this.PageBoardContext.PageForumID,
         this.PageBoardContext.PageTopicID,
         null,
         null)
     : DateTime.UtcNow;

 var unreadFirst = this.GetRepository<Message>().FindUnread(
     this.PageBoardContext.PageTopicID,
     null,
     lastRead,
     showDeleted);

 findMessageId = unreadFirst.MessageID;
 messagePosition = unreadFirst.MessagePosition;

 if (this.PageBoardContext.PageUser.Activity)
 {
     this.GetRepository<Activity>().UpdateNotification(this.PageBoardContext.PageUserID, findMessageId);
 }

with 

  if (!this.PageBoardContext.IsCrawler)
{
    // find first unread message
    var lastRead = !this.PageBoardContext.IsCrawler
        ? this.Get<IReadTrackCurrentUser>().GetForumTopicRead(
            this.PageBoardContext.PageForumID,
            this.PageBoardContext.PageTopicID,
            null,
            null)
        : DateTime.UtcNow;

    var unreadFirst = this.GetRepository<Message>().FindUnread(
        this.PageBoardContext.PageTopicID,
        null,
        lastRead,
        showDeleted);

    findMessageId = unreadFirst.MessageID;
    messagePosition = unreadFirst.MessagePosition;

    if (this.PageBoardContext.PageUser.Activity)
    {
        this.GetRepository<Activity>().UpdateNotification(this.PageBoardContext.PageUserID, findMessageId);
    }
}

this is would exclude the bots from that function. If this this not help comment out this code.

 

BWG
  • BWG
  • 100% (Exalted)
  • YAF Lover Topic Starter
11 months ago
Thanks - I modified the code, recompiled and deployed earlier this morning (I'm in the UK). Also, yesterday I deleted the dead topics using SQL and applied a maximum memory limit in SQL Server Management Studio. 

The problems with high SQL / worker process usage have gone away now, but I'm still not sure of the underlying cause (nothing obvious in the server log files). There are two other websites running on the same server, but they run under their own application pools and CPU usage for both remained low, despite relatively high volumes of traffic.

BWG
  • BWG
  • 100% (Exalted)
  • YAF Lover Topic Starter
11 months ago
The problem with the IIS worker process spiking returned yesterday. On investigation, the IIS logfiles are showing a lot of requests from Bytespider.

The code provided earlier in this thread doesn't seem to stop Bytespider, but I have left it in. Therefore, I've put a URL rewrite blocking rule (via the IIS manager user interface) which checks the User-agent Header for Bytespider and aborts the request if found. As I'm still not sure what the underlying cause of the problem is, it is too early to say whether this will help or not. 

tha_watcha
  • tha_watcha
  • 100% (Exalted)
  • YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2
11 months ago
Yes bytespider is one of the most aggressive bots, you can find dozens of topics on the internet about this bot and how to stop it

Can you apply the following fix for the Crawler detection in the file yafsrc\YAF.Core\Helpers\UserAgentHelper.cs

replace 

 else
        {
            isSearchEngine = isCrawler || IsSearchEngineSpider(userAgent);
        }

with

 

            isSearchEngine = isCrawler || IsSearchEngineSpider(userAgent);
BWG
  • BWG
  • 100% (Exalted)
  • YAF Lover Topic Starter
11 months ago
I can't find that code to replace. In the source I have there is this block:

  else
  {
      // check if it's a search engine spider or an ignored UI string...
      var san = SearchEngineSpiderName(userAgent);
      if (san.IsSet())
      {
          browser = san;
      }

      isSearchEngine = isCrawler || san.IsSet();
  }
tha_watcha
  • tha_watcha
  • 100% (Exalted)
  • YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2
11 months ago
You might want to upgrade to the latest version. I already applied the fixes to the version 3.2.2.

Or you make the changes from this commit 

https://github.com/YAFNET/YAFNET/commit/ef5774b4881a73a3b857840e530eddcfe1b1cce6 

BWG
  • BWG
  • 100% (Exalted)
  • YAF Lover Topic Starter
10 months ago

You might want to upgrade to the latest version. I already applied the fixes to the version 3.2.2.

Or you make the changes from this commit 

https://github.com/YAFNET/YAFNET/commit/ef5774b4881a73a3b857840e530eddcfe1b1cce6 

Originally Posted by: tha_watcha 

Thanks and sorry for the delay in acknowledging your suggestions. Christmas got in the way of things, but I have now had a chance to look at v3.2.2 on my development machine.

One thing I noticed is CKEditor has been replaced (since v3.1.15 I think) with BBCode editor. My forum members aren't technical and probably wouldn't like seeing BBcode when they are writing their posts.  Do you know if it possible to have a WYSIWYG view like CKEditor? (I had a look in settings and couldn't find anything to change the default)

tha_watcha
  • tha_watcha
  • 100% (Exalted)
  • YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2
10 months ago

You might want to upgrade to the latest version. I already applied the fixes to the version 3.2.2.

Or you make the changes from this commit 

https://github.com/YAFNET/YAFNET/commit/ef5774b4881a73a3b857840e530eddcfe1b1cce6 

Originally Posted by: BWG 

Thanks and sorry for the delay in acknowledging your suggestions. Christmas got in the way of things, but I have now had a chance to look at v3.2.2 on my development machine.

One thing I noticed is CKEditor has been replaced (since v3.1.15 I think) with BBCode editor. My forum members aren't technical and probably wouldn't like seeing BBcode when they are writing their posts.  Do you know if it possible to have a WYSIWYG view like CKEditor? (I had a look in settings and couldn't find anything to change the default)

Originally Posted by: tha_watcha 

CKEditor was removed because it causes too many problems, also CKEditor 4 is no longer been developed and its a paid product now. At the moment there is no option to switch to an WYSIWYG view.

But im still open for suggestions for implementing an Editor which supports bbcodes, but most available one are no longer maintained or not really usable or causing similar issues like ckeditor.