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

etc · 2015-07-16 12:54:51

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.

gaekwad · 2015-07-16 17:10:13

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.

wet · 2015-08-18 09:27:02

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.

makss · 2015-08-18 11:03:57

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?

wet · 2015-08-18 11:11:13

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.

ruud · 2015-08-18 12:33:03

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?

makss · 2015-08-18 14:14:09

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?

makss · 2015-08-18 14:35:27

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

ruud · 2015-08-18 18:09:15

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.

wet · 2015-08-18 18:18:35

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.

makss · 2015-08-19 07:49:42

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. ;-)

ruud · 2015-08-19 11:11:41

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.

Textpattern CMS

Textpattern CMS support forum

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

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

makss wrote #293330:

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

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

makss wrote #293270:

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

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

Bloke wrote #284611:

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

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

Bloke wrote #284611:

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

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

makss wrote #294188:

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

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

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

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

ruud wrote #294196:

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

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

wet wrote #294189:

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

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

makss wrote #294199:

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

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

makss wrote #294200:

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

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

ruud wrote #294205:

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

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

makss wrote #294217:

Board footer