Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#13 2015-07-16 12:54:51

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

Re: Option prefetch_forms() - preload all forms in a single query

makss wrote #293330:

Or did you mean to write a plugin that performs preload forms? I also can not do it without changing the core code, because I can not access the static variable $forms.

Ah yes, it’s not global, sorry.

Offline

#14 2015-07-16 17:10:13

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,134
GitHub

Re: Option prefetch_forms() - preload all forms in a single query

makss wrote #293270:

There are forks MySQL, such as Percona or MarinaDB. They are fully compatible with MySQL, but more stable and they added some features. I use Percona.

+1 for Percona, it’s very good. It’s become part of my standard LAMP build deployment.

Offline

#15 2015-08-18 09:27:02

wet
Developer Emeritus
From: Schoerfling, Austria
Registered: 2005-06-06
Posts: 3,323
Website Mastodon

Re: Option prefetch_forms() - preload all forms in a single query

Bloke wrote #284611:

Faced with this restriction we must either:

1) move the static variable out of the function to a global

-1. I’d rather refrain from exposing the private parts of the single function that should load forms to the public.

2) modify fetch_form() such that it retains backwards compatibility but enables multiple forms to be supplied as its argument.

+1. Testable, no side effects, a move in the right direction.

Would you propose a new tag?

I’d decide along that line: Tags are for designers, preferences are for administrators. This is not a decision the designer but the site administrator should have to make.

Offline

#16 2015-08-18 11:03:57

makss
Plugin Author
From: Ukraine
Registered: 2008-10-21
Posts: 355
Website

Re: Option prefetch_forms() - preload all forms in a single query

Bloke wrote #284611:

2) modify fetch_form() such that it retains backwards compatibility but enables multiple forms to be supplied as its argument.

Unfortunately, the additional arguments do not get to do it. But thank you for the right direction to find a solution.
It seems to me that with callback can be solved this problem by fully preserving compatibility with previous versions.

Example callback code has not yet been tested.

function fetch_form($name)
{
    static $forms = array();
    if (empty($forms) && has_handler('forms.prefetch')) {
        $return_forms = callback_event('forms.prefetch');
        if (is_array($return_forms)) {
            $forms = $return_forms;
        }
    }
... skip ...

What do you think about this decision?


aks_cron : Cron inside Textpattern | aks_article : extended article_custom tag
aks_cache : cache for TxP | aks_dragdrop : Drag&Drop categories (article, link, image, file)

Offline

#17 2015-08-18 11:11:13

wet
Developer Emeritus
From: Schoerfling, Austria
Registered: 2005-06-06
Posts: 3,323
Website Mastodon

Re: Option prefetch_forms() - preload all forms in a single query

makss wrote #294188:

Unfortunately, the additional arguments do not get to do it.

You might want to try this:

$name could be an array of names, or even a wildcard like '*' (this would eventually affect people who chose to name a form *, though – we would have to protect them from shooting themselves in the foot).

These are conditions we could detect and act accordingly, couldn’t we?

Combined with a preference setting to enable form prefetches, the core can easily preload forms before the parsing phase even starts.

Offline

#18 2015-08-18 12:33:03

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

Re: Option prefetch_forms() - preload all forms in a single query

On a website which loads 7 or 8 forms on typical pages, I see each form takes around 0.1ms query time. You’d gain at most 0.7ms. However, total query time on those pages is 60-90ms. Why bother for such a small gain?

Offline

#19 2015-08-18 14:14:09

makss
Plugin Author
From: Ukraine
Registered: 2008-10-21
Posts: 355
Website

Re: Option prefetch_forms() - preload all forms in a single query

ruud wrote #294196:

On a website which loads 7 or 8 forms on typical pages, I see each form takes around 0.1ms query time. You’d gain at most 0.7ms. However, total query time on those pages is 60-90ms.

Time request form I have the same: 0.1-0.2 ms
Total query time on those pages is 2.3 – 11.5 ms

Sites are located on the VDS, in MySQL query_cache set.
Results for the second page request, ie, If possible, use query_cache.
Textpattern version 4.5.1. Unfortunately I have no working sites Textpattern 4.6, there is only a test site.

site 1
<!-- Runtime: 0,0237 --><!-- Query time: 0,004335 --><!-- Queries: 23 --><!-- Memory: 5995Kb, -->
site 2
<!-- Runtime: 0,0289 --><!-- Query time: 0,002322 --><!-- Queries: 19 --><!-- Memory: 3718Kb,  -->
<!-- Runtime: 0,0367 --><!-- Query time: 0,003123 --><!-- Queries: 20 --><!-- Memory: 3714Kb,  -->
site 3
<!-- Runtime: 0,0366 --><!-- Query time: 0,009450 --><!-- Queries: 17 --><!-- Memory: 3683Kb, -->
<!-- Runtime: 0,0352 --><!-- Query time: 0,005717 --><!-- Queries: 19 --><!-- Memory: 3691Kb, -->
<!-- Runtime: 0,0983 --><!-- Query time: 0,011531 --><!-- Queries: 14 --><!-- Memory: 3729Kb, -->
site 4
<!-- Runtime: 0,0302 --><!-- Query time: 0,003434 --><!-- Queries: 23 --><!-- Memory: 3985Kb, -->
<!-- Runtime: 0,0269 --><!-- Query time: 0,002985 --><!-- Queries: 21 --><!-- Memory: 3888Kb, -->

BTW, it may be necessary to create several types of sites(sql dump) with content “Lorem ipsum” to test and optimize performance?


aks_cron : Cron inside Textpattern | aks_article : extended article_custom tag
aks_cache : cache for TxP | aks_dragdrop : Drag&Drop categories (article, link, image, file)

Offline

#20 2015-08-18 14:35:27

makss
Plugin Author
From: Ukraine
Registered: 2008-10-21
Posts: 355
Website

Re: Option prefetch_forms() - preload all forms in a single query

wet wrote #294189:

$name could be an array of names, or even a wildcard like '*'
Combined with a preference setting to enable form prefetches, the core can easily preload forms before the parsing phase even starts.

As I understand it, in preference to need to add two fields:

1. Drop down list Enable preload form

  • None
  • All
  • Include only
  • Exclude only

2. Text field with form names or mask.
form1, my*, some*, form2


aks_cron : Cron inside Textpattern | aks_article : extended article_custom tag
aks_cache : cache for TxP | aks_dragdrop : Drag&Drop categories (article, link, image, file)

Offline

#21 2015-08-18 18:09:15

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

Re: Option prefetch_forms() - preload all forms in a single query

makss wrote #294199:

Time request form I have the same: 0.1-0.2 ms
Total query time on those pages is 2.3 – 11.5 ms

In your examples, query time is only around 10% of total runtime. If you manage to reduce it from 3 to 2ms (ideally), you only reduce total runtime by 3%.

Sites are located on the VDS, in MySQL query_cache set.
Results for the second page request, ie, If possible, use query_cache.

I have:

query_cache_limit       = 1M
query_cache_size        = 16M

This concerns a website (undented dot com) with 47 queries on the front page, where 75% of the runtime is spent in queries.

I’m thinking either the query time is already so big that shaving off 1ms doesn’t matter or the query time is so small compared to the total runtime that 1ms more or less doesn’t have much impact. Once you start optimizing in those small margins, it’s probably better to cache the entire page (or parts of it), which has a much bigger effect.

Offline

#22 2015-08-18 18:18:35

wet
Developer Emeritus
From: Schoerfling, Austria
Registered: 2005-06-06
Posts: 3,323
Website Mastodon

Re: Option prefetch_forms() - preload all forms in a single query

makss wrote #294200:

As I understand it, in preference to need to add two fields:

I think this is much to granular for the average user, this needs a Yes/No choice at most. But first of all, the performance gains would have to justify the additional code.

Offline

#23 2015-08-19 07:49:42

makss
Plugin Author
From: Ukraine
Registered: 2008-10-21
Posts: 355
Website

Re: Option prefetch_forms() - preload all forms in a single query

ruud wrote #294205:

In your examples, query time is only around 10% of total runtime. If you manage to reduce it from 3 to 2ms (ideally), you only reduce total runtime by 3%.

Get the overall performance increase of 3% with only one sql query – this is a good result.
Optimization was only one place.
Miracles happen ;-) , but very rarely, and wait an instant performance boost in the 30-50-90% not worth it.

This concerns a website (undented dot com) with 47 queries on the front page, where 75% of the runtime is spent in queries.

Not seeing the sql query is difficult to answer, but I’ll guess: maybe you sql queries do not fall into query_cache. SQL statements tags article / article_custom do not fall into the cache (in most cases).

It will be possible to try to optimize tags article, article_custom, recent_articles, recent_comments, related_articles, getNeighbour() performance gains may be good.

There are two ways:
  • A small loss of accuracy when displaying a future / expired articles. Rounding up the day. This will work quickly and always.
  • A more gentle approach without loss of accuracy, but it increases the performance is not always the case. By event site updated we check whether we have a future / expired articles. The result is stored in the prefs, ie we can use a dynamically optimal queries. In most cases people do not use a future / expired articles. This is our prize. The user does not need any additional settings. Overhead: a few sql queries on the event site updated.

I’m thinking either the query time is already so big that shaving off 1ms doesn’t matter or the query time is so small compared to the total runtime that 1ms more or less doesn’t have much impact. Once you start optimizing in those small margins, it’s probably better to cache the entire page (or parts of it), which has a much bigger effect.

Yes, I often use aks_cache for caching block first page and sidebar. More effective and complete caches can be done using nginx, but unfortunately I do not have so much traffic to the site, and all so fast. ;-)


aks_cron : Cron inside Textpattern | aks_article : extended article_custom tag
aks_cache : cache for TxP | aks_dragdrop : Drag&Drop categories (article, link, image, file)

Offline

#24 2015-08-19 11:11:41

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

Re: Option prefetch_forms() - preload all forms in a single query

makss wrote #294217:

Not seeing the sql query is difficult to answer, but I’ll guess: maybe you sql queries do not fall into query_cache. SQL statements tags article / article_custom do not fall into the cache (in most cases).

Indeed. Almost all those pages use one or more article(_custom) tags.

It will be possible to try to optimize tags article, article_custom, recent_articles, recent_comments, related_articles, getNeighbour() performance gains may be good.

Not just good. I managed to drop query times from 90ms to 4ms.

A small loss of accuracy when displaying a future / expired articles. Rounding up the day. This will work quickly and always.

That’s what I tested yesterday. I’ll prepare a patch.

Yes, I often use aks_cache for caching block first page and sidebar. More effective and complete caches can be done using nginx, but unfortunately I do not have so much traffic to the site, and all so fast. ;-)

Back to prefetching… wouldn’t a aks_prefetch plugin be the optimal solution? That would give the configurability you propose and even allow users to do this per page instead of site-wide.

Offline

Board footer

Powered by FluxBB