|
|
Joined: Oct 2007
Posts: 512 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 512 Likes: 13 |
I enabled the slow query log. After two days, there are over 1 million lines in the log file. I asked Grok to analyze it. This was its response: Slow queries examine ~1M rows each for single POST_ID lookups.
Core issue: USE INDEX (indx_2) forces inefficient index; likely indx_2 does not cover WHERE conditions well or is not optimal for PRIMARY KEY lookup.
Fix:
Remove USE INDEX (indx_2) — let optimizer choose (probably PRIMARY on POST_ID).
Ensure POST_ID is PRIMARY KEY or has unique index.
Check EXPLAIN on query without forced index.
Add composite index on (POST_ID, POST_IS_APPROVED, USER_ID) if needed.
These 2-second single-post fetches cause CPU spikes when many concurrent.
|
|
|
|
|
Joined: Jun 2006
Posts: 16,467 Likes: 147
|
|
Joined: Jun 2006
Posts: 16,467 Likes: 147 |
Logged spikes likely indicate user traffic triggers on the system. Again, we have no plans on implementing any fixes through any AI system that is not trained on the overall system with code that is completely untested.
You're welcome to make any changes to your own system however these changes would be unsupported untested 3rd party code and is not eligible for support.
The overall UBB.treads project is porting old code up to newer systems (PHP versions, MySQL updates, SMARTY and other foundational script updates, etc); as code is gone through during upgrades and rewrites queries are looked at to see if they can be cleaned further; the entire purpose of PHP8 is cleaner strict code otherwise errors pop up everywhere; this is all an ongoing process.
|
|
|
|
|
Joined: Oct 2007
Posts: 512 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 512 Likes: 13 |
I understand that and have no plans on altering code. I thought the suggestion about not using USE INDEX (indx_2) might be worth considering as code development and updating continues.
|
|
|
|
|
Joined: Oct 2007
Posts: 512 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 512 Likes: 13 |
Quick update after testing a targeted fix: There are three files that contain the code USE INDEX (indx_2) - libs/topic_tree.inc.php, scripts/showflat.inc.php, and scripts/domailthread.inc.php. I made backups of the files and then removed that code from all three files. Here are the results. I used [root@ded602 mysqld]# pidstat -u -p $(pgrep mysqld) 5 > mysqld-cpu.log 2>&1 & to monitor CPU usage. Pre-change (from earlier logs): Frequent extreme spikes: 300–800% CPU (e.g., 779.60%, 788.42%, 790.62%, 787.62%) Sustained high usage: many entries 100–500%+ Caused by queries scanning ~1M rows each time due to forced wrong index Post-change (latest tail): CPU usage very low and stable: 0.2–4.6% (typical 0.4–1.8%) No spikes at all in the sample Normal background activity only Difference explanation: Removing USE INDEX (indx_2) lets MySQL use the PRIMARY KEY on POST_ID for direct row lookups instead of scanning ~1 million rows per query, eliminating the CPU-intensive table scans and dropping usage from massive spikes to near-idle levels. It''s up to you, but I would highly recommend you implement this change in your code. I'd be happy to share exact diffs if helpful.
|
|
|
|
|
Joined: Oct 2007
Posts: 512 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 512 Likes: 13 |
After testing on my instance, I saw occasional 1–3 second Lock_time on simple single-row queries against TOPIC_VIEWS (e.g., SELECT … WHERE p.POST_ID = ? AND p.TOPIC_ID = t.TOPIC_ID), even though Rows_examined is always 1. The cause is concurrent INSERTs into {table_prefix}TOPIC_VIEWS (in showflat.inc.php) when multiple users view the same thread simultaneously—each insert queues row-level locks. A low-contention fix is to keep using the existing {table_prefix}TOPIC_VIEWS table but convert it to InnoDB and switch to INSERT … ON DUPLICATE KEY UPDATE. Steps: Convert engine (run once) ALTER TABLE {table_prefix}TOPIC_VIEWS ENGINE=InnoDB;Ensure TOPIC_ID is PRIMARY KEY ALTER TABLE {table_prefix}TOPIC_VIEWS ADD PRIMARY KEY (TOPIC_ID);Replace the INSERT block in showflat.inc.php with: if ($_SESSION['current_topic'] != $topic_info['TOPIC_ID']) {
$query = "
INSERT INTO {$config['TABLE_PREFIX']}TOPIC_VIEWS (TOPIC_ID)
VALUES (?)
ON DUPLICATE KEY UPDATE TOPIC_VIEWS = TOPIC_VIEWS + 1
";
$dbh->do_placeholder_query($query, array($topic_info['TOPIC_ID']), __LINE__, __FILE__);
$_SESSION['current_topic'] = $topic_info['TOPIC_ID'];
}This uses InnoDB row-level locking + upsert to eliminate queueing while keeping view counts accurate. I tested a variant and Lock_time dropped to zero. Happy to provide more detail or diffs if useful—just a performance tweak suggestion. Thanks!
|
|
|
|
|
Joined: Jun 2006
Posts: 16,467 Likes: 147
|
|
Joined: Jun 2006
Posts: 16,467 Likes: 147 |
Posts with potential code updates belong at UBBDev as changes to the base code (what you downloaded from the member area) are completely unsupported by official support channels such as this user to user forum.
|
|
|
|
|
Joined: Oct 2007
Posts: 512 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 512 Likes: 13 |
I posted this and the other one on ubbdev.
|
|
|
|
|
Joined: Oct 2007
Posts: 512 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 512 Likes: 13 |
For some reason, our members' signatures are suddenly not rendering bbcode. Any clues on where to look for the problem would be appreciated. I've already replaced libs/html.inc.php and libs/bbcode.inc.php with original copies from my downloads, just to be certain I have original code. For example: [b]1953 Chevy Panel[/b] [url=http://www.stovebolt.com/gallery/kuiperij%20leo%201953.html]In the Stovebolt Gallery[/url] [url=http://i365.photobucket.com/albums/oo91/1953panel/P1000724.jpg]More pix on Photobucket[/url] This is what is displayed instead of this: 1953 Chevy Panel In the Stovebolt Gallery More pix on Photobucket
|
|
|
|
|
Joined: Apr 2004
Posts: 2,027 Likes: 175
|
|
Joined: Apr 2004
Posts: 2,027 Likes: 175 |
For some reason, our members' signatures are suddenly not rendering bbcode. Any clues on where to look for the problem would be appreciated. I've already replaced libs/html.inc.php and libs/bbcode.inc.php with original copies from my downloads, just to be certain I have original code. There is no way any one of us could know what code or database columns/structures you've changed. Beginning from scratch, I suggest that you grep your modified code for "$Signature" and "_SIGNATURE", then replace those listed files which you've modified with their stock files. Test. Finally, edit your profile to save your signature again. Test again. If you're still having issues with your modified code and you've made several database changes, take note: _USER_PROFILE is the database what holds the signatures Reading your other post, it looks like you may have interchanged table column names while making modifications to your table data https://ubbdev.com/forums/ubbthreads.php/topics/323716/corrupted-characters.htmlBE VERY CAREFUL OF "USER_DEFAULT_SIGNATURE" and "USER_SIGNATURE" USAGE - DO NOT INTERCHANGE THEM• USER_DEFAULT_SIGNATURE - [BBcode] This is the original signature. CONTENT REBUILDER > REBUILD SIGNATURES takes this and converts it to USER_SIGNATURE [HTML] • USER_SIGNATURE - [HTML] This is shown to the user. it is generated from USER_DEFAULT_SIGNATURE
|
|
|
|
|
Joined: Oct 2007
Posts: 512 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 512 Likes: 13 |
# grep -rl "\$Signature" /var/www/html/ubbthreads/*
/var/www/html/ubbthreads/scripts/changebasic.inc.php
/var/www/html/ubbthreads/scripts/editbasic.inc.php
/var/www/html/ubbthreads/scripts/showflat.inc.php
/var/www/html/ubbthreads/scripts/showprofile.inc.php
/var/www/html/ubbthreads/scripts/viewmessage.inc.php
/var/www/html/ubbthreads/scripts/showflat.inc.php.bak
/var/www/html/ubbthreads/scripts/showflat.inc.php.orig
/var/www/html/ubbthreads/templates/default/editbasic.tpl The only one of those files that I've modified is showflat.inc.php, and I'm not replacing that with the original. Here's the diff: # diff -u /home/pauls/ubbthreads-8-0-0-3/scripts/showflat.inc.php ../scripts/showflat.inc.php
--- /home/pauls/ubbthreads-8-0-0-3/scripts/showflat.inc.php 2024-08-26 08:00:00.000000000 -0500
+++ ../scripts/showflat.inc.php 2025-12-29 23:53:45.439485538 -0600
@@ -632,7 +632,7 @@
up.USER_BIRTHDAY, up.USER_PUBLIC_BIRTHDAY, p.POST_ADD_SIGNATURE, up.USER_ACCEPT_PM,
up.USER_HOMEPAGE, up.USER_VISIBLE_ONLINE_STATUS, up.USER_MOOD, p.POST_POSTER_NAME,up.USER_GROUP_IMAGES
FROM
- {$config['TABLE_PREFIX']}POSTS AS p USE INDEX (indx_2),
+ {$config['TABLE_PREFIX']}POSTS AS p,
{$config['TABLE_PREFIX']}USERS AS u,
{$config['TABLE_PREFIX']}USER_PROFILE AS up
WHERE
@@ -1745,4 +1745,4 @@
);
}
-?>
\ No newline at end of file
+?>
As you can see, the only thing done to that file is the removal of USE INDEX (indx_2), and that has nothing to do with signatures but does increase the performance of the forum by a factor of 1000. Speaking of which, have you not noticed that your board is sluggish? Performance grade
Score 73
Page size 1.0 MB
Load time 13.35 s
Requests 29# grep -rl "_SIGNATURE" /var/www/html/ubbthreads/*
/var/www/html/ubbthreads/admin/changeuser.php
/var/www/html/ubbthreads/admin/dorebuildcontent.php
/var/www/html/ubbthreads/admin/membersearch.php
/var/www/html/ubbthreads/admin/reg_settings.php
/var/www/html/ubbthreads/admin/showuser.php
/var/www/html/ubbthreads/languages/english/admin/dorebuildcontent.php
/var/www/html/ubbthreads/languages/english/admin/rebuildcontent.php
/var/www/html/ubbthreads/languages/english/admin/reg_settings.php
/var/www/html/ubbthreads/libs/content_rebuild.inc.php
/var/www/html/ubbthreads/libs/content_rebuild.inc.php.orig
/var/www/html/ubbthreads/scripts/addpost.inc.php
/var/www/html/ubbthreads/scripts/adduser.inc.php
/var/www/html/ubbthreads/scripts/changebasic.inc.php
/var/www/html/ubbthreads/scripts/changedisplay.inc.php
/var/www/html/ubbthreads/scripts/editbasic.inc.php
/var/www/html/ubbthreads/scripts/editdisplay.inc.php
/var/www/html/ubbthreads/scripts/editpost.inc.php
/var/www/html/ubbthreads/scripts/modifypost.inc.php
/var/www/html/ubbthreads/scripts/newuser.inc.php
/var/www/html/ubbthreads/scripts/profile_comment.inc.php
/var/www/html/ubbthreads/scripts/showflat.inc.php
/var/www/html/ubbthreads/scripts/showprofile.inc.php
/var/www/html/ubbthreads/scripts/viewmessage.inc.php
/var/www/html/ubbthreads/scripts/showflat.inc.php.bak
/var/www/html/ubbthreads/scripts/showflat.inc.php.orig None of these files have been altered except for showflat.inc.php which I've already shown that the modification doesn't affect signatures, and libs/content_rebuild.inc.php which has already been returned to the original.. # diff -u content_rebuild.inc.php content_rebuild.inc.php.orig
(The way you can tell is there's not .bak or.orig file with the same name.) Can you at least tell me where in the code the bbcode in signatures is parsed?
|
|
|
|
|
Joined: Oct 2007
Posts: 512 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 512 Likes: 13 |
Whatever it was, rebuilding signatures fixed it.
|
|
|
|
0 members (),
3,205
guests, and
170
robots. |
|
Key:
Admin,
Global Mod,
Mod
|
|
|
|
