Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,081
Website GitHub Mastodon Twitter

Re: mem_postmaster - Postmaster Revamp

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)


Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

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

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,081
Website GitHub Mastodon Twitter

Re: mem_postmaster - Postmaster Revamp

anyone?


Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

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

ruud
Developer Emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

Re: mem_postmaster - Postmaster Revamp

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

Offline

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

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,081
Website GitHub Mastodon Twitter

Re: mem_postmaster - Postmaster Revamp

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?


Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

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

ruud
Developer Emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

Re: mem_postmaster - Postmaster Revamp

Sure, go ahead.

Offline

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

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,081
Website GitHub Mastodon Twitter

Re: mem_postmaster - Postmaster Revamp

Thanks so much ruud. You have mail!


Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

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

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,081
Website GitHub Mastodon Twitter

Re: mem_postmaster - Postmaster Revamp

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


Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

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

ruud
Developer Emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

Re: mem_postmaster - Postmaster Revamp

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.

Offline

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

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,081
Website GitHub Mastodon Twitter

Re: mem_postmaster - Postmaster Revamp

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


Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

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

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,081
Website GitHub Mastodon Twitter

Re: mem_postmaster - Postmaster Revamp

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?)


Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

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

ruud
Developer Emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

Re: mem_postmaster - Postmaster Revamp

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.

Offline

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

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,081
Website GitHub Mastodon Twitter

Re: mem_postmaster - Postmaster Revamp

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


Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

Board footer

Powered by FluxBB