vBulletin 3 is end of life and will not be receiving future development. Warning: vBulletin 3.8.11 is not compatible with PHP 7.2.0 or higher.
Welcome to the vBulletin support forums! In our community forums you can receive professional support and assistance with any issues you might have with your vBulletin Products.
If you are having problems posting in the relevant areas for your software, please see this topic.
Upgrade to vBulletin 5
We're pleased to announce a special promotion for upgrading your vBulletin 3/4 sites to vBulletin 5. From now until December 31st, we are offering vBulletin 5 license upgrades at $169 each. This promotion is available to all vBulletin 3 (owned) and vBulletin 4 license holders, entitling you to the latest version of vBulletin 5.
If you would like to purchase this upgrade, please log into the vBulletin Members Area and use Promo Code: vB5UPGRADE during checkout to apply the discount.
Announcement
Collapse
No announcement yet.
Hourly Cleanup #2 - V3.8.4 taking a *long* time to execute. PMs unusable
I don't think dumping and reimporting your database fixed it, I think that what probably happened is that the cronjob responsible for Orphan PM cleanup was able to complete successfully at some point.
While the query was not running, with the forum off: Once I created a new database, and imported a backup, that same query only took a few milliseconds. More thoughts?
You are correct, the PM system is useless while that query is running. And the loads go up 100x. Question: what would happen if that query never ran? The old PMs would still be there taking up space?
Our forum is really small, way smaller than a million post 'big board'.
That does indeed mean you have no orphan PMs. I don't think dumping and reimporting your database fixed it, I think that what probably happened is that the cronjob responsible for Orphan PM cleanup was able to complete successfully at some point.
The issue is though, that even though that query returned no orphan PMs, it still took 372 seconds to run, and during that time it's going to be locking your pm and pmtext tables, rendering the PM portion of your site useless for a period of time.
Sadly, short of doing what I had to do, I don't know of a good solution to prevent this from being a problem on a regular basis given your setup.
You have *no* idea how grateful I am for your clearly worded reply!
I have been getting other input that was not nearly as easy to understand.
Here are the results, it looks to me that there are no orphaned PMs, true?
Code:
MySQL returned an empty result set (i.e. zero rows). ( Query took 372.3083 sec )
What does fix the problem is to create a new database and import a backup of our existing database into it. I've no idea why that fixes it.
For the record I know almost nothing about these types of things but have been learning just enough to be dangerous these last few weeks. We only have 20k PMs
SELECT pmtext.pmtextid
FROM pmtext AS pmtext
LEFT JOIN pm AS pm USING(pmtextid)
WHERE pm.pmid IS NULL;
This query will find the ids of your orphan PMs. You could write a query to delete these PMs, but I'm wary of posting it because it could be dangerous in the hands of someone who isn't totally aware of what they are doing.
What we have done on our site, which has 8-10 million pms (we prune unread PMs after 6 months), is to disable this periodic cron job (which is a nasty one due to the way the PM system is designed in 3.x), and write a script that we run once a week to find and delete the orphan PMs.
I would post the code we use to do so, but it's sort of proprietary in that we actually slave the query to find the orphan PMs to a MySQL slave server (using query_read_slave()), then batch delete (in 500-1000 PM id increments) the orphan PMs on our master server. We delete about 75,000 orphan PMs a week (on Sunday night) this way.
The query to find the orphan PMs is nasty still, but a) it's not running on a regular basis locking people out of doing PM related stuff and b) it's running on the slave server, so it's not as bad.
Thankfully, I believe that the PM system was overhauled to fix this design flaw in 4.x, but I could be wrong.
Ideally this query should return 0 rows. That is you shouldn't have any orphan PMs in the system. I recommend deleting these manually and then running a repair and optimize on the table. This should allow the cron script to run properly in the future.
I still don't know which ones, if any, are orphaned based on your response.
I 'repaired and optimized' the table, that did not help.
Ideally this query should return 0 rows. That is you shouldn't have any orphan PMs in the system. I recommend deleting these manually and then running a repair and optimize on the table. This should allow the cron script to run properly in the future.
Also, the problem appears to be database related. I took the forums offline, made sure the database was not being used at all by editing the config.php file, and tried running this query:
Code:
SELECT pmtext.pmtextid
FROM pmtext AS pmtext
LEFT JOIN pm AS pm USING(pmtextid)
WHERE pm.pmid IS NULL
;
And it took about 9 minutes to execute. I *thought* it may have been a problem that only occurred when the database was being used online, but the database itself seems to be the problem.
As stated earlier, a recent backup which has this problem was restored into a brand new database using this command: mysql -u dbusername -p databasename < backupname.sql
And it worked great
Is it possible by backing up and restoring into a clean database, the the orphaned PMs will be deleted, and possibly the tables would be repaired and optimized?
Ideally this query should return 0 rows. That is you shouldn't have any orphan PMs in the system. I recommend deleting these manually and then running a repair and optimize on the table. This should allow the cron script to run properly in the future.
Deleting the orphaned PMs sounds great to me, but I've no idea which ones are orphaned.
I've been studying how the different tables interact for many hours now. And I've been looking at the output of that EXPLAIN command. From that output, how can I determine which PMs are orphaned?
Ideally this query should return 0 rows. That is you shouldn't have any orphan PMs in the system. I recommend deleting these manually and then running a repair and optimize on the table. This should allow the cron script to run properly in the future.
Generation Time: Jun 21, 2010 at 03:39 PM
Generated by: phpMyAdmin 3.2.4 / MySQL 5.0.90-community-log
SQL query: EXPLAIN SELECT pmtext.pmtextid FROM pmtext AS pmtext LEFT JOIN pm AS pm USING(pmtextid) WHERE pm.pmid IS NULL;
Rows: 2
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pmtext index NULL PRIMARY 4 NULL 19962 Using index
1 SIMPLE pm ALL NULL NULL NULL NULL 29080 Using where; Not exists
I almost forgot to say, thank you very much for your input Wayne, much appreciated.
More information:
This issue has been going on for a long time, at least for several weeks, possibly for months. The only symptom is slow PMs, and of course higher loads if those are looked at.
I created another database and restored a back up less that 24 hours old.
I ran the test query on that database, and it worked. It only took a few milliseconds.
I am re-running the query on the live database, and it is taking forever again. Probably 10 minutes before it gets done running. EDIT: 337 seconds to make the query.
Can anyone guess what might be going on?
EDIT: is it possible that turning off the forum would help for debug? Is there any way to just freeze it then do the query?
Last edited by Abomination; Mon 21st Jun '10, 2:44pm.
If an Index is disabled, then it won't be used for any queries and those queries will take long to run. According to the MySQL documentation, one can disable an index if they are making schema changes to a large table in order to increase the speed at which those changes are applied. However the index should be enabled again afterwords. You should re-enable the index...
ALTER TABLE ... ENABLE KEYS
Replace ... with the table name.
Regarding this test query
Code:
SELECT pmtext.pmtextid
FROM pmtext AS pmtext
LEFT JOIN pm AS pm USING(pmtextid)
WHERE pm.pmid IS NULL
;
On the database without problems I did this:
ALTER TABLE pmtext DISABLE KEYS
Confirmed that the forumuserid was DISABLED
then ran test query.
Then:
ALTER TABLE pmtext ENABLE KEYS
Confirmed that the forumuserid was ENABLED
then ran test query.
And I did that multiple times. No difference from my perspective, but obviously the time differences might be difficult to measure using a stop watch.
On the database with problems I did this:
ALTER TABLE pmtext DISABLE KEYS
Confirmed that the forumuserid was DISABLED
then ran test query 2 times: 371 & 691 seconds.
Then:
ALTER TABLE pmtext ENABLE KEYS
Confirmed that the forumuserid was ENABLED
then ran test query. 735 seconds.
During those 3 querys the load averages were about 4.
Is it possible to run this command on a subset of the PMs?
Can I make a back up and start testing that, maybe deleting 5K pms at a time and try to find out which ones might be causing the problem?
If an Index is disabled, then it won't be used for any queries and those queries will take long to run. According to the MySQL documentation, one can disable an index if they are making schema changes to a large table in order to increase the speed at which those changes are applied. However the index should be enabled again afterwords. You should re-enable the index...
Someone is trying to help me in the background. Anyway I was asked about the Indexes / structure of the table.
This is the data structure (and I believe index) of the database that has the issue. Notice that 'forumuserid' says 'disabled' in the comment field. Anyone know what that means?
Here is the data structure (and I believe index) of the database that works, it is an old database. Notice that 'forumuserid' does not say disabled in the comment field.
widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Working...
X
We process personal data about users of our site, through the use of cookies and other technologies, to deliver our services, personalize advertising, and to analyze site activity. We may share certain information about our users with our advertising and analytics partners. For additional details, refer to our Privacy Policy.
By clicking "I AGREE" below, you agree to our Privacy Policy and our personal data processing and cookie practices as described therein. You also consent to the transfer of your data to our servers in the United States, where data protection laws may be different from those in your country.
Leave a comment: