News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

SMF 2.1.3 and crazy CPU load

Started by Daretary, March 24, 2023, 01:23:43 PM

Previous topic - Next topic

Daretary

I really regretted that I spent a lot of time on the transition to SMF 2.1.3.

MySQL is tormenting the CPU madly here:

Everything works slowly, constantly error 504. And this

I returned to my dear native SMF 1.1.21, which easily holds the load even with 5000 visitors in the last 15 minutes (oh, long time no such attendance).

Sir Osis of Liver

Try 2.0, that's what I'm using.  1.1 went eol long time ago, it hasn't been supported for years.
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Arantor

So, what's MySQL doing? Is caching turned on, if so what caching are you using?

Daretary

Arantor, SQLite3. Alternatively, there is file caching.

Arantor

You had 5000 concurrent users with no cache on the 1.1?

SQLite is the slowest choice, file caching the second slowest choice. Neither was an option on 1.1, so if you had that level of activity one assumes you had an actual cache solution for it...

But you didn't answer my question, what is MySQL doing? SHOW PROCESSLIST is your friend.

Daretary

1.1.21 has Memcached (second level, not recommended).

Thanks, I'll take a look at SHOW PROCESSLIST.

Kindred



1.1.x is completely end of life. There are known security issues that will never be patched.

2.0.x is close to end of life.
Despite what sir Osis might suggest, in this case,  he's wrong (unless you want to do another upgrade sooner rather than later)

Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Arantor

So why don't you use that same option in 2.1? Still supported, same things are cached, it's as "safe" as it ever was.

Daretary

Arantor, the fact is that I ran 2.1.3 on another server with a simultaneous change of DNS

Too bad PROCESSLIST doesn't show in real time like top does.
This is after installing the third level of SQLIte3:

Arantor

Why are you using SQLite? Use memcache!

As I said, SQLite is literally the slowest caching option in most configurations.

Daretary

So far, only file caching and SQLite are available. After installing Memcached I will switch to it.

Arantor

Well, install memcache and then see if the CPU load is 'less crazy'. Rushing to judge SMF 2.1's performance when you tied one hand behind its back isn't exactly fair...

Daretary

I apologize!

It seems that he understood what exactly loads the database heavily - the output of topics in which the latest messages. This output is on every page.
I disabled is_read (new) in the query to the database, but it doesn't help much.

I'll try to port the module from 1.1.21, it did not load the processor.

Arantor

Latest messages is not called every page, it's really not in a stock 2.1.3 so if it is, that's something modded.

It's also something that in stock SMF is explicitly cached even at level 1, not even level 2, but this is why for your scale you absolutely need memcache.

But yeah, I'm sorry, I just get unreasonably annoyed when people complain about how much they hate 2.1 (given that I put a lot of time into it), only for the problem to not really be 2.1 in the first place.

Sir Osis of Liver

Quote from: Kindred on March 24, 2023, 02:54:42 PMDespite what sir Osis might suggest, in this case,  he's wrong (unless you want to do another upgrade sooner rather than later)

Not wrong, we just disagree.  Upgrading to a supported version is not a bad temporary solution to keep the forum running securely, and to possibly provide some insight into what's gone wrong with 2.1.  But Arantor seems to have the matter in hand, so we needn't quibble over it.
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

DeadMan...

Quote from: Sir Osis of Liver on March 24, 2023, 08:41:01 PMso we needn't quibble over it.

But, that takes all the fun out of it for @Kindred;)
I tell it how I see it... Don't like it? Hit Alt+F4!

Daretary

I apologize again, the problem turned out to be in this query:

    $request = $smcFunc['db_query']('', '
SELECT m.subject, m.id_topic, m.icon
FROM {db_prefix}topics AS t
INNER JOIN {db_prefix}messages AS m ON (m.id_msg = t.id_first_msg)
INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
WHERE b.id_cat IN (7,18,21,23,25,5,24,14,15)
ORDER BY id_topic DESC
LIMIT 15'
    );

I did caching for 1 hour and the problem disappeared:

shawnb61

What is the source file & line # of that query?  Where did you find it in the SMF code?

What mods do you have installed?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Daretary

This code lists the latest topics. I guess that the load is due to sorting and select out categories.

shawnb61

Quote from: shawnb61 on March 25, 2023, 02:36:34 AMWhat is the source file & line # of that query?  Where did you find it in the SMF code?

What mods do you have installed?

Please answer the questions.  At first glance, that doesn't look like SMF code.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Advertisement: