mem_postmaster - Postmaster Revamp

colak · 2015-09-28 19:04:00

I have this error and – as usual – I have no idea how to solve it: we had to kill one of your MySql queries because it had been running for over 1 minute and it was impacting other users.

Query: SELECT COUNT(*) FROM (
SELECT S.subscriberID, S.subscriberFirstName, S.subscriberLastName, S.subscriberEmail,
GROUP_CONCAT(L.listName ORDER BY L.listName SEPARATOR ', ') as subscriberLists
FROM `bab_pm_subscribers` as S left join `bab_pm_subscribers_list` as MAP ON S.subscriberID=MAP.subscriber_id
left join `bab_pm_list_prefs` as L ON MAP.list_id=L.listID
WHERE 1
GROUP BY S.subscriberID) as T
WHERE 1

many thanks in advance.

ps. A hacked txp4.4.1 is used, because of this plugin, so any help would be greatly appreciated.

Last edited by colak (2015-09-28 19:20:43)

colak · 2015-09-29 09:35:13

anyone?

ruud · 2015-09-29 09:53:56

Try executing that query from the command line or via PHPmyadmin and see how long it takes. If it’s far below 1 minute (and it probably is) and if you’re using webfaction, then see this topic

colak · 2015-09-29 16:38:16

There are indeed many rows in the bab_pm_subscribers table. But it is very slow indeed even in phpmyadmin I got no results after 3 minutes of waiting. I’m wondering if there is something wrong with the table. I’ll check it out. I am managing 2 sites with this plugin. One with just over 1000 subscribers and another with over 2500. The first seems just fine even though it is using a slightly older version of the plugin. The second, is just not working. Ruud, would it be OK if I send you a l/p for the site for you to check?

ruud · 2015-09-29 16:52:46

Sure, go ahead.

colak · 2015-09-29 16:59:51

Thanks so much ruud. You have mail!

colak · 2015-09-29 17:26:57

ps. Would you also like the server and mysql logins?

ruud · 2015-09-29 18:49:21

Interesting. That query sometimes takes a few seconds to complete, but at other times it just keeps going and results in an error. This is not normal and probably means that the SQL server is not performing consistently.

I wonder why this plugin used a huge query:

SELECT COUNT(*) FROM (
  SELECT S.subscriberID, S.subscriberFirstName, S.subscriberLastName, S.subscriberEmail,
    GROUP_CONCAT(L.listName ORDER BY L.listName SEPARATOR ', ') as subscriberLists
  FROM `bab_pm_subscribers` as S
  left join `bab_pm_subscribers_list` as MAP ON S.subscriberID=MAP.subscriber_id
  left join `bab_pm_list_prefs` as L ON MAP.list_id=L.listID
  WHERE 1
  GROUP BY S.subscriberID
) as T
WHERE 1

… because this gives the exact same result, but is much much faster:

SELECT COUNT(*) FROM `bab_pm_subscribers`

Ah, I see. The “WHERE” part is not always simply “1”.

You can optimize the queries when the WHERE part is 1, but that doesn’t solve the problem, because I see there’s a very similar, but heavier query (because it doesn’t just count, but returns a large table) just below that in the plugin code and that one cannot be simplified, so the question remains: why does that querie sometimes execute within seconds (or less) and at other times take more than a minute. That’s something your webhost should answer.

colak · 2015-09-30 06:08:07

Thanks so much Ruud. I wrote to the host. Hopefully they will respond soon.

colak · 2015-09-30 17:35:18

Would it break postmaster, txp or the internet if I use indexes on the bab_pm_subscribers table? Namely on S.subscriberID, S.subscriberFirstName, S.subscriberLastName, S.subscriberEmail columns (or is it rows?)

ruud · 2015-09-30 18:31:22

Adding indexes never breaks anything. You add indexes on columns (can be more than one per index).
It makes most sense to add them on columns that are used to select data and combine tables:

bab_pm_subscribers => subscriberID
bab_pm_subscribers_list => subscriber_id and list_id (one index for each).
bab_pm_list_prefs => listID

I don’t think this will solve your problem, but it can’t hurt to try.

colak · 2015-10-01 05:23:09

Thanks so much Ruud, I added them and the speed has multiplied although it might be the time of day.

Textpattern CMS

Textpattern CMS support forum

#301 2015-09-28 19:04:00

Re: mem_postmaster - Postmaster Revamp

#302 2015-09-29 09:35:13

Re: mem_postmaster - Postmaster Revamp

#303 2015-09-29 09:53:56

Re: mem_postmaster - Postmaster Revamp

#304 2015-09-29 16:38:16

Re: mem_postmaster - Postmaster Revamp

#305 2015-09-29 16:52:46

Re: mem_postmaster - Postmaster Revamp

#306 2015-09-29 16:59:51

Re: mem_postmaster - Postmaster Revamp

#307 2015-09-29 17:26:57

Re: mem_postmaster - Postmaster Revamp

#308 2015-09-29 18:49:21

Re: mem_postmaster - Postmaster Revamp

#309 2015-09-30 06:08:07

Re: mem_postmaster - Postmaster Revamp

#310 2015-09-30 17:35:18

Re: mem_postmaster - Postmaster Revamp

#311 2015-09-30 18:31:22

Re: mem_postmaster - Postmaster Revamp

#312 2015-10-01 05:23:09

Re: mem_postmaster - Postmaster Revamp

Board footer