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

makss · 2013-02-19 08:31:54

In developing the site, I get 10-15 forms on one page. It’s got separate 10-15 sql queries that return only one form.
I propose to add to Textpattern option that will load all(some?..) the forms in a single query. ( Like load_lang() function )

I looked at my existing sites, the total size of the table txp_form 9kb to 45kb. I think it is permissible to read all the forms in the memory.

mrdale · 2014-10-07 22:37:07

this SOUNDS like a great idea… any of the devs want to chime in?

Bloke · 2014-10-08 22:42:21

makss wrote #270676:

I propose to add to Textpattern option that will load all(some?..) the forms in a single query.

Interesting idea.

As you noted, Forms are loaded on an as-needed basis, and are cached so each one only incurs a single trip to the database. But, yes, using multiple <txp:output_form /> tags or many form attributes to tags can increase the query count somewhat.

Every Form request (at least, I hope this is the case!) is piped through fetch_form() and that maintains the cache using a static variable. If we introduce a new mechanism (tag, option, whatever) for loading multiple images it needs to somehow populate this static variable inside fetch_form(), otherwise it won’t have any effect: the database will still be consulted.

Faced with this restriction we must either:

1) move the static variable out of the function to a global (or some other externally-accessible location) which pollutes the already crowded global namespace. Reducing the impact of this poor programming approach is something we’re slowly addressing.

Or

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

Or

3) something else entirely that I haven’t thought of yet.

The second option is feasible, imo. If you pass an array, for example, we could detect that situation, fetch all the forms at once, populate the static variable with each one and then any subsequent calls will already have the necessary Forms in place. Not quite sure how to handle the situation when a Form doesn’t exist. At the moment it triggers an error and bombs out. In the multiple case, we could keep track of which ones failed and trigger_error() once with a list of all the failed ones. That gTxt() error string in there isn’t very international-friendly anyway, so could probably do with being updated to accept the forms as a parameter.

Would you propose a new tag? Something like <txp:prefetch name="default, banner, gallery, page_head, page_foot" />? It wouldn’t really do anything visible (which might be a problem for some people who prefer tags to always output something) but would be simply a tool for performance improvements. Having to list every form to include might not be ideal if you have a lot of them, so maybe a parameter to exclude would be beneficial too?

looked at my existing sites, the total size of the table txp_form 9kb to 45kb. I think it is permissible to read all the forms in the memory.

Yes, in your case maybe. But I have a site with close to 100, fairly big, Forms in it. The memory overhead could be significant if we assumed that all Forms were to be loaded at startup. So an option/pref might not be a good idea, whereas a tag or some other user-controllable mechanism at least gives admins a say in what is loaded and when.

etc · 2014-10-09 07:18:46

The query time depends on data volume too, a right balance is not easy to find. No cleverer thought, sorry. :)

makss · 2015-07-15 12:21:58

I propose to allow the user to include preloading the forms yourself.
(Admin / Preferences / Publish / enable_preload_form Pref. RadioButton: Yes/No)

Preloading all forms will be when you first load any form. Backward compatibility is completely satisfied, because do not change the call parameters fetch_form().

The proposed changes can be viewed in Pull Request

Even on the default site(after Setup) saves 4 SQL query per page.
Counted for the page http:// … /articles/1/welcome-to-your-site

etc · 2015-07-15 14:58:33

makss wrote #270676:

I get 10-15 forms on one page.

Bloke wrote #284611:

I have a site with close to 100, fairly big, Forms in it.

If makss loads >50% of his Forms on a typical page, he would win from this feature. If Bloke loads only few of them, he could loose. A preference is not quite satisfactory, since sites evolve, and clients are not always able to make a good choice.

Probably, if we separate parsing/processing even more, the parser could be able to detect the forms to prefetch, but don’t hold your breath already.

makss · 2015-07-15 16:30:29

If makss loads >50% of his Forms on a typical page, he would win from this feature. If Bloke loads only few of them, he could loose.

The number of forms does not matter (10-50-100). MySQL server uses query_cache and the result of this request: select name, Form from txp_form as txp_form where 1=1 #Get all forms.

This result is stored in memory as one block, and on subsequent calls MySQL give this result, without a real query of the table txp_form. For MySQL makes no difference to give one form or all at once in a single query execution time is the same ±. It works really fast (just as when using memcache or even faster).

Launched on the site with 33 forms, get all form – request executed in 0.0001 seconds. Get one form – the same time.

The only thing that matters – is the total size of the table txp_form. It must be less than the setting query_cache_limit in MySQL, it is usually set to 2 MB.
And, of course, it will be used as much memory as is a table txp_form

A preference is not quite satisfactory, since sites evolve, and clients are not always able to make a good choice.
Probably, if we separate parsing/processing even more, the parser could be able to detect the forms to prefetch, but don’t hold your breath already.

I would not complicate things with the detective and others. If needed, it is possible insert a diagnostic panel test the overall size of the table txp_forms parameter and MySQL query_cache_limit (need to check whether it is available the average user) and give a warning.

Let’s take a poll, who how much space a table txp_form?
I have been a maximum of 150kb – I’m sorry, but I kept them jquery, jquery plugin, js, some file with css. Simply convenient to edit them, and then give the browser several files as one. (With speed it was all good, because to use an external cache)

etc · 2015-07-15 18:01:27

makss wrote #293257:

The number of forms does not matter (10-50-100). MySQL server uses query_cache and the result of this request: select name, Form from txp_form as txp_form where 1=1 #Get all forms.

This result is stored in memory as one block, and on subsequent calls MySQL give this result, without a real query of the table txp_form. For MySQL makes no difference to give one form or all at once in a single query execution time is the same ±.

The only thing that matters – is the total size of the table txp_form. It must be less than the setting query_cache_limit in MySQL, it is usually set to 2 MB.

Great to know, but is this limit per table or per session? Per query, I guess 1mb by default. ~~Add here textpattern, txp_discuss, txp_lang, txp_plugin, and you quickly reach 2Mb (2.2Mb on my site)~~. In my tests on txp_lang, a single WHERE name='active' is 3 times faster than 1=1, and WHERE name='active' LIMIT 1 is 9 time faster.

Edit: We have query_cache_size=0 on our server, which matches The default size is 0, so the query cache is disabled by default statement. Really appreciate this discussion, few things learned.

Last edited by etc (2015-07-15 18:31:46)

makss · 2015-07-15 19:16:19

To configure or checking the MySQL I would advise MySQLTuner
It shows the basic configuration errors, and will optimally allocate memory for buffers and cache.

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.

hcgtv · 2015-07-15 19:37:00

makss wrote #293270:

To configure or checking the MySQL I would advise MySQLTuner
It shows the basic configuration errors, and will optimally allocate memory for buffers and cache.

Downloading now, thank you sir for the link.

etc · 2015-07-16 11:57:20

makss wrote #293270:

To configure or checking the MySQL I would advise MySQLTuner
It shows the basic configuration errors, and will optimally allocate memory for buffers and cache.

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.

Yes, your site is very fast indeed, but I’m not sure everybody has access to the server config. Your proposal can surely be useful for the happy few, why wouldn’t you rather write some aks_tweaks plugin?

makss · 2015-07-16 12:47:17

etc wrote #293322:

Yes, your site is very fast indeed, but I’m not sure everybody has access to the server config. Your proposal can surely be useful for the happy few, why wouldn’t you rather write some aks_tweaks plugin?

I do not quite understand the question. To change the configuration of MySQL need root access to the server or vds. PHP plugin can not do this.

If you are using shared hosting and it is not configured MySQL query_cache, my advice – change shared hosting. If administrators hosting unable to set up one of the key things is scary to think what else could happen on such hosting. Now the price of hosting low and there is no problem with his choice.

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.

Textpattern CMS

Textpattern CMS support forum

#1 2013-02-19 08:31:54