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:
(https://i.imgur.com/PAdQjmK.jpg)
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).
Try 2.0, that's what I'm using. 1.1 went eol long time ago, it hasn't been supported for years.
So, what's MySQL doing? Is caching turned on, if so what caching are you using?
Arantor, SQLite3. Alternatively, there is file caching.
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.
1.1.21 has Memcached (second level, not recommended).
Thanks, I'll take a look at SHOW PROCESSLIST.
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)
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.
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:
(https://i.imgur.com/NIBPaWg.jpg)
Why are you using SQLite? Use memcache!
As I said, SQLite is literally the slowest caching option in most configurations.
So far, only file caching and SQLite are available. After installing Memcached I will switch to it.
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...
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.
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.
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.
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 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:
(https://i.imgur.com/SIL3ewL.jpg)
What is the source file & line # of that query? Where did you find it in the SMF code?
What mods do you have installed?
This code lists the latest topics. I guess that the load is due to sorting and select out categories.
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.
That's not core SMF code - there's nowhere that IDs are hard coded in core (at least, not like that!) and most sites don't have that many categories.
It was sort of why I'd asked earlier about what the processlist was reporting because I suspected a custom query.
Quote from: shawnb61 on March 25, 2023, 04:22:11 AMPlease answer the questions.
This is a homemade code that displays the latest topics in the selected categories. What else have I not answered? :)
Quote from: Daretary on March 25, 2023, 05:33:34 AMQuote from: shawnb61 on March 25, 2023, 04:22:11 AMPlease answer the questions.
This is a homemade code that displays the latest topics in the selected categories. What else have I not answered? :)
Well, you blamed SMF but if it's your own code, then how is SMF at fault?
Dear Aleksi "Lex" Kilpinen.
I've already apologized twice in this thread.
Forgive me again!
And don't think I offended SMF. On the contrary, I exalted SMF 1.1.21. I will remember her with nostalgia. :'(
Quote from: Daretary on March 25, 2023, 06:42:41 AMAnd don't think I offended SMF.
On the contrary, you did:
Quote from: Daretary on March 24, 2023, 01:23:43 PMI really regretted that I spent a lot of time on the transition to SMF 2.1.3.
DeadMan..., excuse me!
You just need to remember, that sometimes, you have to calm down and seek assistance instead of judging something due to issues. One never knows when something that works on one version may be an issue on another. The different ways of coding and database calls can cause issues with edits and mods. That's why it's very important to post about things, as not only can it help you, but it may help others.
I commend you on the posting the issue in the first place. It helped you, and also taught you a valuable lesson: Never judge something due to a problem until you make sure of what the issue is.
Glad you got it sorted out!
Quote from: Daretary on March 25, 2023, 01:25:34 AMI 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'
);
All three of those tables store the board id so you could simplify that query by replacing category ids with board ids and dropping the join on boards.
live627, I don't quite understand. Should I list all board IDs?
Your predicate would include all board ids that have those categories assigned to them
t.id_board IN (list_of_ids)
Every day I go to this topic, I read what live627 wrote. I hoped that it would enlighten me.
But to no avail. :'(
Marking unsolved then as you still seem to have an issue that needs resolved.