My site is "choking"

[LiveG]

Hello,
Every Saturday night, and sometimes in the middle of the week, my site is "choking" and working very slowly and sometimes crashes. When it happens, my server CPU shows 100 %.
What could be the reason for that? How can i debug it the next time it happens?

Edited December 17, 2022 by LiveG

[teraßyte]

Maybe you have a backup cronjob/task that runs on those specific days?

That's something you should ask your hosting usually though. (Unless you're hosted on Cloud.)

[Jim M]

You would need to contact your hosting provider to find out what is running. As the task happens every Saturday night, this would likely indicate a server related task, such as a backup.

[LiveG]

The CPU went down. Whatever query it was executing, it seems to have finished

It's possible this was caused by searching for posts by user. But it's hard to say for certain.

How can i validate it? 

 

[Miss_B]

2 minutes ago, LiveG said:

It's possible this was caused by searching for posts by user. But it's hard to say for certain.

Seeing the fact that it happens on a certain time I highly doubt that that could be the case. To echo @teraßyteabove, do you have a backup cronjob/task that runs on those specific days?

If you check the logs in your admin panel or the server error log, is there any message logged in either place about the same time when the issue is happening?

[LiveG]

 don't have the full query, but here's the partial:
/suite::root::IPS\Content\Search\Mysql\_Query::filterForProfile:253/ SELECT COUNT(*) FROM

It was stuck for about 1400 seconds, in State: Repair with keycache

Edited December 17, 2022 by LiveG

[LiveG]

It happened today as well.

Looking at errors from today, the first error would be
Sat, 17 Dec 2022 15:59:08 +0000
Error: Call to undefined method stdClass::language() (0)
#0 /var/www/html/system/Dispatcher/Front.php(109): IPS\Dispatcher\_Standard->ini
t()
#1 /var/www/html/init.php(927) : eval()'d code(46): IPS\Dispatcher\_Front->init(
)
#2 /var/www/html/system/Dispatcher/Dispatcher.php(110): IPS\Dispatcher\stickynot
es_hook_Outputs->init()
#3 /var/www/html/index.php(13): IPS\_Dispatcher::i()
#4 {main}
#0 /var/www/html/init.php(1029): IPS\_Log::log()
#1 [internal function]: IPS\IPS::exceptionHandler()
#2 {main}
This error is usually generated as a result of a database failure or query timeout

Edited December 17, 2022 by LiveG

[Jim M]

You would need to work with your hosting provider to investigate why your MySQL instance is not able to handle the query. A slow query log of what is happening would help determine what query this is related to. If your hosting provider determines that this is a software fault, we will require that slow query log and any other server errors logs associated.

[LiveG]

Do they have instructions on where to find it ( slow query log) ? I'm not sure if one is generated by default

am seeing a mysql errorlog
2022-12-17T15:59:08.347189Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2022-12-17T15:59:08.354720Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31-0ubuntu0.20.04.1) starting as process 1071
2022-12-17T15:59:08.384870Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-17T15:59:09.506188Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-17T15:59:10.044286Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2022-12-17T15:59:10.065552Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2022-12-17T15:59:10.184684Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-12-17T15:59:10.184719Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-12-17T15:59:10.217439Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-12-17T15:59:10.217489Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31-0ubuntu0.20.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).
2022-12-17T15:59:10.396884Z 8 [ERROR] [MY-013134] [Server] Table './suite/core_sessions' is marked as crashed and should be repaired
2022-12-17T15:59:10.405459Z 8 [Warning] [MY-010756] [Server] Checking table:   './suite/core_sessions'
2022-12-17T15:59:10.405626Z 8 [ERROR] [MY-010239] [Server] Got an error from unknown thread, /build/mysql-8.0-W37JCQ/mysql-8.0-8.0.31/storage/myisam/ha_myisam.cc:893
2022-12-17T15:59:10.405642Z 8 [Warning] [MY-010757] [Server] Recovering table: './suite/core_sessions'
2022-12-17T15:59:19.999341Z 98 [ERROR] [MY-013134] [Server] Table './suite/core_search_index' is marked as crashed and should be repaired
2022-12-17T15:59:20.000110Z 98 [Warning] [MY-010756] [Server] Checking table:   './suite/core_search_index'
2022-12-17T15:59:43.716082Z 98 [ERROR] [MY-010239] [Server] Got an error from unknown thread, /build/mysql-8.0-W37JCQ/mysql-8.0-8.0.31/storage/myisam/ha_myisam.cc:893
2022-12-17T15:59:43.716118Z 98 [Warning] [MY-010757] [Server] Recovering table: './suite/core_search_index'
2022-12-17T16:08:11.802883Z 4515 [ERROR] [MY-013134] [Server] Table './suite/pmviewer_core_message_topics' is marked as crashed and should be repaired
2022-12-17T16:08:11.803277Z 4515 [Warning] [MY-010756] [Server] Checking table:   './suite/pmviewer_core_message_topics'
2022-12-17T16:25:43.214877Z 13191 [ERROR] [MY-013134] [Server] Table './suite/core_tags_perms' is marked as crashed and should be repaired
2022-12-17T16:25:43.215318Z 13191 [Warning] [MY-010756] [Server] Checking table:   './suite/core_tags_perms'
2022-12-17T16:25:43.401098Z 13191 [ERROR] [MY-010239] [Server] Got an error from unknown thread, /build/mysql-8.0-W37JCQ/mysql-8.0-8.0.31/storage/myisam/ha_myisam.cc:893
2022-12-17T16:25:43.401143Z 13191 [Warning] [MY-010757] [Server] Recovering table: './suitel/core_tags_perms'

 

Edited December 17, 2022 by LiveG

[teraßyte]

Looking at those logs you have several crashed tables that need to be repaired.

In addition I also see that you're using MySQL 8.0 and I've seen a few people having issues with it. It would be best to use MySQL 5.7 or an equivalent MariaDB version instead.

[LiveG]

Thanks.

Is this formal ipb recommendation? it's happening again periodically.

My server is fit the requirement, i don't understand why it's happening.  

Edited December 18, 2022 by LiveG

[LiveG]

@Jim M please tell me which log u need?

My Hundred of people forum stop functioning properly.

Edited December 18, 2022 by LiveG

[Jim M]

Did you or your hosting provider fix the missing tables as your logs indicated? That would need to happen before we can assist anything further. 

[Miss_B]

On 12/17/2022 at 6:56 PM, LiveG said:

The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.

Have a look at this link regarding the quoted error.

https://dev.mysql.com/worklog/task/?id=10924

[AlexWebsites]

I just noticed that mysql process on my server is at times peaking over 100% which I find odd. I've killed the process a few times to watch it go back up. I'll have to dig in. Running the latest IPS version and mysql 8. 

Edited December 18, 2022 by AlexWebsites

[LiveG]

I have a warning on my ACP, could it be related?

InnoDB database tables typically perform more efficiently and reliably than other database engines such as MyISAM on most modern hosts when configured correctly. It is strongly encouraged to ensure all of your database tables are using the InnoDB storage engine, and that your hosting provider has configured MySQL for InnoDB use.

Edited December 19, 2022 by LiveG

[Marc Stridgen]

You should indeed have all tables running INNODB, and its likely why you have queries with "Repair with keycache" showing there. While this may well solve the specific issue you are experiencing though, you need to contact your hosting company about the fact you have tables crashing repeatedly, as that is an issue on the mysql instance itself.