High SQL server CPU usage · YAF.NET Support Forum

Welcome Guest! To enable all features please Login or Register

BWG
100% (Exalted)
YAF Lover Topic Starter

6 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]

Wanna join the discussion?! Login to your YAF.NET Support Forum forum account or Register a new forum account

Sponsor

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

6 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
100% (Exalted)
YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2

6 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
100% (Exalted)
YAF Lover Topic Starter

6 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
100% (Exalted)
YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2

6 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
100% (Exalted)
YAF Lover Topic Starter

6 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
100% (Exalted)
YAF Lover Topic Starter

6 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
100% (Exalted)
YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2

6 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
100% (Exalted)
YAF Lover Topic Starter

6 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
100% (Exalted)
YAF Lover Topic Starter

6 months ago

#10

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
100% (Exalted)
YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2

6 months ago

#11

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
100% (Exalted)
YAF Lover Topic Starter

6 months ago

#12

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
100% (Exalted)
YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2

6 months ago

#13

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
100% (Exalted)
YAF Lover Topic Starter

5 months ago

#14

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
100% (Exalted)
YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2

5 months ago

#15

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.

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.

Important Information: