RESOLVED: Issue with Hosting Environment

progre55 · 2011-09-14 14:21:01

I received this message for one of my sites from our host:

The following query is a poor fit for the Cloud Sites environment:

select *, id as thisid, unix_timestamp(Posted) as posted from textpattern where Status = 4   and Posted <= now() and (now() <= Expires or Expires = '0000-00-00 00:00:00')  order by rand() limit 0,6

They state the problem is that the unix_timestamp function cannot be cached in any way on the MySQL server, making the queries more CPU intensive than necessary when a PHP or other web-facing function could be used to determine the date. This is causing high load as the queries stack up in the queue. Additionally, the rand() function can also be scripted outside of the MySQL server, and is affecting performance as well.Because of this, they have limited the number of connections.

Issue was with:

<txp:recent_articles limit="6" break="li" wraptag="ul" label="" sort="rand()" />

progre55

Last edited by progre55 (2011-09-14 15:23:32)

Bloke · 2011-09-14 15:41:50

progre55 wrote:

Can someone please let me know how I can fix or disable the code.

Looks like you’re using <txp:recent_articles /> with sort="rand()". EDIT: good guess! I see you’ve updated your post while I was writing.

While it’s true that doing the random sort in MySQL is not a very good idea — you can get round that by not using rand() of course — there’s currently no plugin that’ll shuffle the results in code after the event (hmmm, though I’m working on a plugin with a possible fit here right now). There is not much we can currently do to prohibit people using rand() as a sort option, since it’s passed directly into the query from the tag.

There are a few ways round this in MySQL, but nothing foolproof, and not a lot you can do without hacking the core. I can think of two non-hacky ways. Replace <txp:recent_articles>with:

<txp:smd_query>. Perhaps you can generate a similar query but seed it with, say, 10 times the number of rows you actually want. So you pick 60 random numbers between 1 and MAX articles, fetch each of those records by doing WHERE ID IN(n1, n2, n3, n4, n5...) iterate over the results and output any row that contains data, then stop after the 6th row has been displayed.
<txp:article_custom>. Since you’re not using any particularly special features of recent_articles, just do something similar to the approach outlined in the smd_query solution and feed the list of IDs into the tag’s id="" attribute.

Bottom line is that you don’t need to be using recent_articles because you’re pulling them out randomly (i.e. destroying recentness) so choosing a better tag would help widen your options. But even if you choose either of the above query approaches, they’re still not cacheable (although they’d be much more efficient) because you’re asking them for a list of random rows from the database each time. Plus you’d probably have to do a query up-front to determine the min and max ID values.

As for the unix_timestamp(), meh. Yeah it should be done in PHP. Long term it will be done that way wherever possible. I put a function in 4.3.0 to help, it’s just that the rest of the core doesn’t use it yet — including txp:article_custom, so while it may be attractive to use the article_custom tag because it’s simpler, using smd_query might be more beneficial because it only (currently) relies on the unix_timestamp if you want to populate article data. If you’re happy with using the {replacement} vars then it’s more cacheable at the moment. The next version of the plugin will use the new core function so this issue will go away whether you choose to populate or not.

Not sure if that helps at all. If you need a hand putting this togther we can probably hammer out a PHP solution.

EDIT2: A variation on one of the proposed solutions — if your article pool is big enough — is to choose a random category and a random sort column/direction then feed those two values into article_custom or smd_query. Sure, it’ll be less random than rand(), but it’d make your host happier and stuff will still ‘change’ for the user each page visit. Plus if the article count is going up all the time as new articles are written, the articles picked will differ.

Last edited by Bloke (2011-09-14 15:55:06)

maruchan · 2011-09-14 16:06:29

Unless I misunderstand, you could also use a jQuery (or similar) script to randomly reorder all children of a selected element: One example

Gocom · 2011-09-14 16:18:32

Bloke wrote:

Perhaps you can generate a similar query but seed it with, say, 10 times the number of rows you actually want. So you pick 60 random numbers between 1 and MAX articles, fetch each of those records by doing WHERE ID IN(n1, n2, n3, n4, n5…) iterate over the results and output any row that contains data, then stop after the 6th row has been displayed.

IN comparison is indeed very fast, especially as long as the list isn’t too long, values use correct type and the values are sorted correctly.

But what happens when you delete articles. Selecting 60 IDs usually allows getting ten rows at least, but what happens if you remove 60 rows? You might get no articles at all.

It would still require PHP based random generator, and you will also have to make sure you don’t end up with 15 fives out of 60.

Yeah it should be done in PHP

The possibility of having incorrect dates and unnecessary conversion is fun tho ;-)

Bloke · 2011-09-14 17:55:00

Gocom wrote:

But what happens when you delete articles. Selecting 60 IDs usually allows getting ten rows at least, but what happens if you remove 60 rows? You might get no articles at all.

Meh, I didn’t say it was perfect :-D

There doesn’t seem to be a clean way of emulating rand(). Loads of people have workarounds like the ones I mentioned, but they all rely on having sequential records or hoping that when you’ve pulled out more records than you need you have enough good records left.

One other way I thought of would be to select all IDs (safe_column) and cache them at the start of the page in a txp:variable. Then we can create a list out of it, shuffle it and take a subset easily, with a line or two of PHP (or a transform in my upcoming plugin… oh the mystery) and feed that into a tag, safe(ish) in the knowledge that there won’t be any rows missing.

The possibility of having incorrect dates and unnecessary conversion is fun tho ;-)

Ah yeah, good point. There are downsides. Not sure what’s best: feel the wrath of hosters who whine about cacheing and load issues, or thousands of website users who complain that their dates are wrong because the platform doesn’t support a particular stftime() string or is using a wrong timezone.

It’s a bit of a minefiel…. BOOM!

progre55 · 2011-09-14 17:58:44

Thank you guys for your comments and suggestions. So that I could get off the hosts “most wanted list” I have pulled the code/tag in question and will focus on a work around later this week.

progre55

Textpattern CMS

Textpattern CMS support forum

#1 2011-09-14 14:21:01

RESOLVED: Issue with Hosting Environment

#2 2011-09-14 15:41:50

Re: RESOLVED: Issue with Hosting Environment

#3 2011-09-14 16:06:29

Re: RESOLVED: Issue with Hosting Environment

#4 2011-09-14 16:18:32

Re: RESOLVED: Issue with Hosting Environment

#5 2011-09-14 17:55:00

Re: RESOLVED: Issue with Hosting Environment

#6 2011-09-14 17:58:44

Re: RESOLVED: Issue with Hosting Environment

Board footer