Hourly Cleanup #2 - V3.8.4 taking a *long* time to execute. PMs unusable

[Abomination]

Apparently *somehow* the ownership was set to root instead of mysql.

Using this command within PuTTY (lowercase 'L' not '1'):
[email protected] [~]# ls -l /var/lib/mysql
Instead of this:

drwx------  2 mysql mysql   18432 Jul  6 11:10 databasename/

It was this.

drwx------  2 root  root   18432 Jul  6 11:10 databasename/

After this was run:

[email protected] [~]# chown –R mysql:mysql /var/lib/mysqlat

Ownership was set to mysql as it should have been.


Then other things could be done within myphpadmin such as optimize & enable keys.

I am extremely grateful to the person that helped me with this today, hopefully it will help others in the future.

[Abomination]

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'.

[KrON]

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.

[Abomination]

This query will find the ids of your orphan PMs.

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?

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

[KrON]

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.

[Abomination]

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.

[Abomination]

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:

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?

[Abomination]

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?

[Wayne Luke]

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.

[Abomination]

I appreciate you helping Wayne

Here is the output:

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

[Wayne Luke]

Please run this query directly in phpMyAdmin and post the results here:

EXPLAIN SELECT pmtext.pmtextid FROM pmtext AS pmtext
LEFT JOIN pm AS pm USING(pmtextid) WHERE pm.pmid IS NULL;

[Abomination]

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?

[Abomination]

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

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?

[Wayne Luke]

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.

[Abomination]

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.