Textpattern CMS support forum

You are not logged in. Register | Login | Help

#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
From: Lenzing, Austria
Registered: 2005-06-06
Posts: 3,267
Website

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

#25 2015-08-19 12:31:47

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 #294220:

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.

Now I also think that the best would be a plugin.
But I can not access the static variable $forms.
Another possible way is to add a callback forms.prefetch

If this opportunity to implement a plugin, I do not want to be limited to table txp_form. Why not?
The next possible step: We can store the form in the file system, or use virtual forms within their own plugins. Updated plugin and all plugin virtual forms also “update”. Now I have to update plugins with the update and forms.


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

#26 2015-08-29 15:05:24

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

makks, I think you can do it without directly accessing the static $forms variable if you hook into the form.fetch callback event. You could pull the form content from wherever you want… I’m checking to see if I can make a plugin that does this.

Offline

#27 2015-08-29 17:56:49

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

NOTE: this plugin requires TXP 4.6

Does anyone want to try?

Those who try, let us know:
  • the number of form queries
  • runtime before you used this plugin
  • runtime after you used this plugin
  • query time before you used this plugin
  • query time after you used this plugin.
  • if you’re prefetching all forms (even the ones not used on that page) or just a select few.
# rvm_prefetch v0.1
# Prefetch forms, reducing the number of SQL queries
# Ruud van Melick
# http://vanmelick.com/txp/

# ......................................................................
# This is a plugin for Textpattern - http://textpattern.com/
# To install: textpattern > admin > plugins
# Paste the following text into the 'Install plugin' box:
# ......................................................................

H4sIAAAAAAAAA41VyW7jRhA9j76ihhBMKmBILbZsUEuQwwQYIIvj0SHBxBBaZFHsMcmme7FG
GPjfU91NKcqG5CCrl1evXr2qlll2l31R2W0WvKBUXLTBQmWzLBgnE7uaZwEzuhLSbiY3WfBg
TAEvrIUfsOb5kzsen0BbI7k9mRKw0rrL0pSgjUMmuWhS/blLXcgkCwpUueSd7nPeEMu9xBJ1
XkEpZKNikFiYnLd70BVCa5odShAlfPj5e3g2KDkqG3mdBS1r0PFOs0C+NNuuJ+qvK6w7u7yb
U51vltVkfQlapnQwGLxZduv72ux5S3mfDZeoYKk6KjWvmVKrIGedCtabX+6XqT1ew3UyByGh
4vsKZbJMu55lU3EFnacSZUm+wiejNIgWQbN9DIeKU5E5Ue8QjMKCrqBje8qoK6ahFqwARl/a
luvMSOC99n440KUd/yCSHDqJ7I2CFrGgRETm6L2rQqGnBy3OGk/6VCVMXQAVQfmEPELDnhCO
wkg44E5xTbFMaSodNiSoEIdW8QKB91VwTdU+UWoGO+4qabCxPJRLaSHR1XHqgtdGhf7h46/C
OJOQE1CekcDq+jQhXillJBdLUwMvrUC7c+QN+yQk10eb/BxlzWagyIEaneuZT7mkBOtlLgpc
X9V6QbOaXY4JpFd7vRgsU4egEEJ7nT9Jl9RKVVhjrntZPpu9OnBV2bJPXP8zoyVYXT0boRd2
ubVjPonhvJ7GkCSJB/y7vHfevgM7xtAZ7Yz5rzJ7GjuuwHyIFp210fWMTItpdkvbROb09D0o
XPv6Z2cp7PL2hp7lYEOJsj3qKNzgZ90xTZPTbjdsv33APVdaHsPR12vp1iij8FJUOFoMBqer
bU6t3LH86S+YGEKrJDnv6FOyWqENLk2b258auAyJhqSCpmioK5qGVWvqmjbWWbceDb4MgEaV
aZ7D0HdzBUxKdowsJ9hxixwe3q5W4GLAxoBnIbT7/hjav+Gji/FRb7lS5IVn/ehQj6NTMNBL
10bSOLEStyXHuojC7whpq6KGbUu/DvokYZAU4gP9AFRezSgJwoAUWqZXn9MlsnouE3qEaf+u
xN/0KtyVPXFc1DzJch3V35J3kXfDoZ37IazWzgg6GpGZ1uCRN2t4oDG0cqNezNmybyCcrCYh
ZH1F73+EiGqiNu23Erua5RiF6W/qq5g+aRgHYRwGMWjJG881shWPAqtxKG2TnHFSHNSW2ie1
dy8G14Y/e+hVeYX0bGuMhhRHFC0V+iAOtFXnxpxMIsSpp4/WVLd3DXr0Pr0O+v+jTXHjVvQC
ptN8nJc4Z7PZrGBY7PKb8e18Ptshu96N7+6Cxevv8TJEno4HAAA=

Offline

#28 2015-08-30 21:02:35

mrdale
Moderator
From: Walla Walla
Registered: 2004-11-19
Posts: 2,203
Website

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

On a site with an assload of forms.

Before:

<!-- Runtime:    0.3576 -->
<!-- Query time: 0.159883 -->
<!-- Queries: 170 -->
<!-- Memory: 7684Kb, end of textpattern() -->

After:

<!-- Runtime:    0.3738 -->
<!-- Query time: 0.163590 -->
<!-- Queries: 152 -->
<!-- Memory: 7771Kb, end of textpattern() -->

Offline

#29 2015-08-30 21:08:00

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

So apart from the reduction in queries, everything else got worse. Oops.

Offline

#30 2015-08-30 21:17:58

etc
Developer
Registered: 2010-11-11
Posts: 3,410
Website

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

The results are fluctuating, but there seems to be about 3% acceleration (0.087 -> 0.085 overall runtime) on a ~vanilla site on my wamp laptop with query cache enabled.

Offline

Board footer

Powered by FluxBB