I've noticed in the dev branch the pm_index table has ballooned to a ridiculous size. Currently there are 20k rows in pm_messages, but 2 million in pm_index. This has caused a huge slowdown of the site and it takes roughly 2 minutes to pull up a users messages.

If I run:

select mid, count(mid) from pm_index group by mid

many mid's have a count of 200+. I know for a fact that people are not sending the same message around that many times over and over. I am not sure if this is a symptom of the dev branch, an issue with the data on my site, or an issue that will crop up for everyone in the dev branch. It seems there could be a flaw in the updating and inserting of the pm_index table.

For the time being I am going to disable the privatemsg module until I figure out some rhyme or reason to the table and can delete the extraneous data.

I can link to a sql dump of the pm_index table and others if needed. It is rather large so I cannot attach it to this bug report.

Comments

Berdir’s picture

Status: Active » Postponed (maintainer needs more info)

That looks definitly strange.

A few questions/hints...

- Since when are you using privatemsg? Which versions?
- Are you using any additional, privatemsg related modules?
- There was was a bug in an older version of privatemsg which caused exactly this, can you check if the mid's with a count of 200+ are older? and newer messages don't have that many duplicates? (order by mid DESC / Order BY count(mid) DESC your query)
- There is currently a situation where it is possible that there are duplicates, but I'm working on a patch that will eliminate the need for this. #660092: Use tag for inbox handling and #502664: remove duplicate records from pm_index and add primary key. Once the first one has been commited, I can write a update function to get rid of these unessary duplicates but it's quite hard to do without that. I think we can still figure something out to help you getting rid of your duplicates.

PS: A dump of the data would be interesting, not only to get more information about this but also to test upgrade path functions and performance of queries on "real" data. Could you upload it somewhere and send me the link privately? If possible with pm_message content, feel free to delete subject and body (insert dump somewhere, run UPDATE pm_message SET subject = '', body = '' before creating a new dump) for privacy reasons.

NitroPye’s picture

Version: 6.x-1.x-dev »

- Looks like I put in the wrong version for the ticket. I am using the 6.x-2.x-dev branch now. Not sure what version the site started with as it has been over a year that the privatemsg module has been turned on.

- Using the default modules included with privatemsg: email notifications, attachments, block, filter.

- As far as old / new messages having this issue it looks to be sporadic. Messages from the initial release of the site as well as messages from yesterday are having the issue, although I could be mistaken.

- I'll take a look at those two tickets.

Thanks a ton, I've PM'ed you a link to the data. Hopefully it is useful.

NitroPye’s picture

Status: Postponed (maintainer needs more info) » Active
Berdir’s picture

Status: Active » Postponed (maintainer needs more info)

The link you sent me per email doesn't work, I wrote you back but maybe you didn't get my answer. Can't do much without the dump, except working on the two linked issues to have a way to remove these duplicates. And even there, I could really use your dump to test the upgrade path.

NitroPye’s picture

Status: Postponed (maintainer needs more info) » Active

Whoops my bad. Resent a confirmed working link. Sorry about that.

Berdir’s picture

Status: Active » Postponed (maintainer needs more info)

Strange. Can you confirm that in your original pm_index table, almost all thread_id values are empty by running the following query:

SELECT COUNT(*) FROM pm_index WHERE thread_id = 0

From your dump, this returns a value of 2104476. That simply means that 99% of the rows in your pm_index table are totally useless because they don't have a thread_id. thread_id connects multiple messages to threads, and among others, the list messages query groups on that field. That basically means that MySQL tries to group 2,1 million rows into a single one if at least one message that has thread_id = 0 has been sent to you.

You could also check with older backups (if you have those) since when these thread_id's are 0 or if they've been that forever. If I'm not mistaken, it looks like only messages older than 25.2.2009 are affected by this issue. (maybe an update function borked your data)

It is not possible to correctly fix this, but you can basically make each message with thread_id = 0 it's own thread, by assigning the mid value to the thread_id value. The following query will do that:

UPDATE pm_index SET thread_id = mid WHERE thread_id = 0

/messages won't be exactly fast after this, but it should be a nice improvement.

The mentioned issue about duplicates remains, so we still need to work on that. But as I assumed, this seems to affect only older messages as well.

- If it's still too slow for you, you can try to disable privatemsg_filter unless the linked issue has been commited, since that will remove the slowest part of the query. (On my dev site, the query was 50% faster then)

Please test this and report back how it's working for you.

heavy_engineer’s picture

Hi, i have the same problem.

Was running stable 15k users, lots of pms working fine for 12months(?). had a big traffic spike, PMs stopped working, updated to 6.x.2 to see if it make a difference, and it hasnt as i have the following db problems which were caused by 6.x.1:

mysql> SELECT COUNT(*) FROM pm_index WHERE thread_id = 0;
+----------+
| COUNT(*) |
+----------+
| 1351073 |
+----------+

No one has been able to get to the pm page for days (cant even do it in test!) after running UPDATE pm_index SET thread_id = mid WHERE thread_id = 0 its accesible after a wait, but i like to check for messages logged in users on each page load, which is baaaaaaad.

Im thinking i'll read the other threads, on the problem, but atm i cant see why i cant just delete all but one of those duplicate threads?

If i get time, i will anonymise tables and attach them if needed?(if anyone has anonymiser sript would be greatful..)

EDIT: disbabling the filter makes it work nicely again.

Berdir’s picture

I don't think I need the tables, I have enough information with the ones from the thread started.

However, I'd like to find out where these empty threads are coming from. Maybe we do have a bug somewhere. Do you have made regular backups? If so, can you figure out since when you have such a high amount of such rows? And if you did an upgrade of privatemsg during that time? And if so from which version to which?

I'ts not that complicated to provide an upgrade function which handles this for all users (we could even make the thread detection more intelligent based on the subject for that fix) but first, I need to figure out if this is an old bug that doesn't happen anymore or find and fix it.

Berdir’s picture

Another question... did you maybe upgrade from Drupal 5? I'ts possible that there is a bug in the upgrade function....

heavy_engineer’s picture

nope, was Drupal 6 from the start.

No code updates to any modules (i added facebookstyle statuses a few days earlier, and addd this). We were in a code freeze till launch, had a traffic spike, then this.

NitroPye’s picture

Same here no upgrade from 5 to 6, started at 6.

Running

UPDATE pm_index SET thread_id = mid WHERE thread_id = 0

Sped things up a bit but its still taking over 30 seconds to pull up a users PMs and giving considerable CPU spikes on my shared host, so I have to turn them back off.

NaheemSays’s picture

(subscribe)

Do you have any other privatemsg related modules/cusomisations installed?

(are you using messaging.module?)

I think we also need a thread_id check in privatemsg_reply - just expecting evryone to send valid data may be a bad idea.

Berdir’s picture

@nbz What do you mean by thread_id check?

We already do that in http://blog.worldempire.ch/api/function/privatemsg_reply/1 by checking if we can load the thread with that thread_id for that users. If we can't replying isn't possible

heavy_engineer’s picture

hi again

One of my users has just come to me complaining about thousands and thousands of messages in their inbox. This is after all the shiz described above. Do you want a copy of these messages? They havent used the PM function in months.

EDIT: Sorry, what i meant to say was, i can supply you the data, but i dont have any time to help debug for another few days sorry, im already in debt to one project...

cheers

Berdir’s picture

Yeah, that's because of two reasons

- All the threads with thread_id 0 did not show up before, or only as a single thread.
- Each message of these threads is now displayed as a separate thread, if you had 10 threads with 5 messages each then you have 50 threads now.

If you want to get rid of these, you can import the backup you made before the above query (I hope you did ;)) and then execute "DELETE FROM pm_INDEX WHERE thread_id = 0" instead, that will delete them.

robby.smith’s picture

subscribing

oadaeh’s picture

Version: » 6.x-2.x-dev
Issue summary: View changes
Status: Postponed (maintainer needs more info) » Closed (won't fix)

This issue is being closed because it is against a branch for a version of Drupal that is no longer supported.
If you feel that this issue is still valid, feel free to re-open and update it (and any possible patch) to work with the 7.x-1.x branch (bug fixes only) or the 7.x-2.x branch.
Thank you.