Faster Database search

[sreeb]

Hi Everyone,

Since a couple of months, we have our new website live with processwire as CMS system.

In this website there is a huge database (1900+ trees), wich can be found with different filters. Take a look at www.vdberk.com/trees.
The website does not to be seen very fast when you select a couple of filters. Every time there is a delay between 3-5 seconds. 

Now we are migrate the website to another sever, with more preformance. So we hope this give us some timeprofits.

Does anyone have any kind of suggest, to make this filter faster. Now the website is on PHP5.6, is php7 better? Can this make a different? We used ProCache already.

Thanks in advance

 

[abdus]

1 minute ago, sreeb said:

Now the website is on PHP5.6, is php7 better?

Yes. Certainly so.

http://www.zend.com/en/resources/php7_infographic

[sreeb]

Cool thanks @abdus,

Is processwire and the modules like procache and formbuilder etc ready for PHP 7. Or is this a heel of a job to make it work on php7?

We have processwire 3.0.

Are there any other sollution to make this huge database faster?

[bernhard]

very nice site!

maybe profiler pro can help you finding bottlenecks? https://processwire.com/api/modules/profiler-pro

https://processwire.com/api/modules/profiler-pro/#pages

i have not worked with it but would be interested to see an example

[abdus]

It's usually plug and play, I dont remeber having any problems with the upgrade.

Unless you're using deprecated features, you should be ok

http://php.net/manual/en/migration70.php
http://php.net/manual/en/migration70.deprecated.php

Here are some guides on Tuts+ and DigitalOcean

https://code.tutsplus.com/tutorials/upgrading-your-linux-server-to-php-7--cms-27583
https://www.digitalocean.com/community/tutorials/how-to-upgrade-to-php-7-on-ubuntu-14-04

[DaveP]

Definitely with @abdus on this one. PHP7 is noticeably faster than 5.6, and PW has no problems (that I'm aware of?) running under 7. If it is available, do it (taking the usual precautions, obvs).

Also, there might be optimisations you could make to your selectors and there are probably mysterious optimisations you may be able to make to your mySQL setup, depending on how much control you have within your hosting. First of those you will probably get help with here, if you are able to share some code, second is down to black magic, voodoo and selling the soul of your firstborn. Or hiring an expert.

All of that having been said, it's a very good looking website, and it works well - initial loading doesn't feel slow, just the filtering isn't instant.

<edit>Man, 3 more replies while I write this one.</edit>

[Zeka]

Hi @sreeb

Do you cache results of searches? 

I have a similar project with a lot of filtering options. I use WireCache for caching search results ( PageArrays) and MarkupCache for result cards. It works quite fast on shared hosting. 

[Robin S]

Superb website. It should be included in the featured sites if it isn't already.

1 hour ago, sreeb said:

The website does not to be seen very fast when you select a couple of filters.

Perhaps your filter queries are not optimal. Feel free to post the filter code if you would like any feedback on this.

[abdus]

A generous use of WireCache can help with generating overviews for a large number trees.

<?php namespace ProcessWire;
/** @var $cache WireCache */

foreach ($trees as $tree) {
    $overview = $cache->getFor($tree, "tree-overview");
    if (!$overview) {
        $overview = wireRenderFile('parts/tree-overview');
        $cache->saveFor($tree, 'tree-overview', WireCache::expireWeekly);
    }
    echo $overview;
}

 

[sreeb]

Wow, thanks a lot for al the suggets.

I will send this to our developers, so they can expertmate with it. 

@Zeka, do you have an example website so i can see the search?

[adrian]

I am actually wondering if you might be better off not relying on ajax calls for each filter step. Have you considered storing all the details of all trees in a wireCache'd JSON object that you load up when the page originally loads and then filter through that in memory? I have done this before - I have 3MB of data in JSON which is cached, I make sure that data is transferred compressed (so it's only a couple of hundred KBs). The initially page load takes a couple of seconds, but after that, the filtering is instant. I think so long as you know that the data will not expand to an outrageous size, this might be a good approach.

PS - Great looking site!

[Pixrael]

..or if you want realtime search results, maybe you can try this: https://www.algolia.com/product ( I'm not an affiliate or promo agent  )

[sreeb]

On 21-9-2017 at 4:59 PM, Pixrael said:

..or if you want realtime search results, maybe you can try this: https://www.algolia.com/product ( I'm not an affiliate or promo agent  )

This looks very cool. thanks, i will check it.

[sreeb]

Unfortunately, php7 does not give the desired speed gain. Do you have experience with Varnish Cache and PW?
Also extra server capacity did not do the job. It looks like its a code technical problem.

As i can see, all search query's are set as an URL, like:

https://www.vdberk.com/trees/?soil-type=1147&sort=0
https://www.vdberk.com/trees/?soil-type=1147&soil-moisture=1157&sort=0
https://www.vdberk.com/trees/?soil-type=1147&soil-moisture=1157&resistance=1189&sort=0

Etc.etc.

The page will not be change very often. they are quite static. It looks like below. But i'am not sure.
Unfortainly our developers cant find a sollution, so this is my last hope;)

 

 public function getTreesSelector($filterParams){
        
        $selector = array('template=tree-detail');
        
        foreach ($filterParams as $key => $value) {
            if(trim($value) != ""){
                $key = $this->getFilterKey($key);
                
                switch ($key) {
                    case "key":
                        $selector[] = "title|tree_specific_name|tree_family|tree_subfamily%=".urldecode($value);
                        break;

 

 

[bernhard]

i think you need to do some more debugging! i just played around with a local test-setup and got this results with 10.000 pages:

creation of pages:

//foreach($pages->find('parent=8181') as $p) $pages->delete($p);

$i=0;
$tmp = range('A', 'Z');
while($i<10000) {
    $p = new Page();
    $p->template = 'filtertest';
    $p->parent = 8181;
    $p->title = "test$i";
    $p->a = $tmp[array_rand($tmp)];
    $p->b = $tmp[array_rand($tmp)];
    $p->c = $tmp[array_rand($tmp)];
    $p->save();
    $i++;
}

dump (results):

d($pages->find('template=filtertest, a=a, b=b')->each('title'));
array (13)
14.48ms, 0.09 MB

d($pages->find('template=filtertest, a|b|c=a|b|c')->each('title'));
array (3084)
1110.54ms, 6.54 MB

d($pages->find('template=filtertest, a|b|c%=a|b|c')->each('title'));
array (3084)
1339.68ms, 6.54 MB

your filter function looks totally weird to me. you have ID values as filter but then you use the slow %= selector. why? see my first example using " = " as selector should give you an instant result! also i don't understand why you are using the OR operator ( | ) for searching different fields. is the information spread over multiple fields?? shouldn't every filter-value be stored in a separate field?

 

PS: my template "filtertest" has fields A, B and C holding letters from A-Z

PPS: are you sure the selector is slowing the site down? maybe it is the way you count your number of results in the filter sidebar?

[sreeb]

Thanks @bernhard.

I just send you an PM.

[bernhard]

you have 175 labels showing the tree count:

i tried a loop with 200 iterations on my test-install with the SLOW selector from my above example and got all the counts within 500ms:

so i think it should be no problem to get the site to returning results plus all the filter count-labels under 500ms.

but as i said in my pm: that can only be a wild guess as we don't know any details about your setup.

[abdus]

11 minutes ago, bernhard said:

i tried a loop with 200 iterations on my test-install with the SLOW selector from my above example and got all the counts within 500ms:

Once PW fetches pages from DB, it saves them to memory, meaning subsequent find() operations with the same selector don't really have an effect, so it's essentially one DB operation.

// PagesLoaderCache.php

 * Cache the given selector string and options with the given PageArray
 *
 * @param string $selector
 * @param array $options
 * @param PageArray $pages
 * @return bool True if pages were cached, false if not
 *
 */
public function selectorCache($selector, array $options, PageArray $pages) {

    // get the string that will be used for caching
    $selector = $this->getSelectorCache($selector, $options, true);

    // optimization: don't cache single pages that have an unpublished status or higher
    if(count($pages) && !empty($options['findOne']) && $pages->first()->status >= Page::statusUnpublished) return false;

    $this->pageSelectorCache[$selector] = clone $pages;

    return true;
}

 

[bernhard]

ah, thanks, you are right

ok, so now i get 15seconds. if you really need the count-labels it seems you would need to implement some caching...

[adrian]

I haven't been following this closely for a while, but perhaps an SQL query would be helpful?

SELECT COUNT(id) 
FROM `pages` 
WHERE (pages.templates_id=29) 
AND (pages.title='My Page') 
AND (pages.status<1024) 
GROUP BY pages.id

You might find this snippet helpful. It will generate an SQL query from a find selector. I just modified the returned result to be a COUNT instead.

[sreeb]

No if it makes the speed  much faster, the countlabels can go away.

I will ask to our developers to look at this. Maby this give them some information.

Is Varnish caching a good sollution?

[abdus]

23 minutes ago, adrian said:

It will generate an SQL query from a find selector. I just modified the returned result to be a COUNT instead.

A simpler way could be passing a second parameter to $pages->count() method like this

$count = $pages->count('id>0', ['getTotalType' => 'calc']); // default, slower, fetches everything then counts
$count = $pages->count('id>0', ['getTotalType' => 'count']); // uses SQL query, faster

From the core:

Spoiler

<?php
/**
 * Return all pages matching the given selector.
 *
 * @param Selectors|string|array $selectors Selectors object or selector string
 * @param array $options
 * ....
 *  - `getTotalType` (string): Method to use to get total, specify 'count' or 'calc' (default='calc').
 * ....
 */
public function ___find($selectors, array $options = [])
{
    // ...
    if ($this->getTotal) {
        if ($this->getTotalType === 'count') {
            $query->set('select', ['COUNT(*)']);
            $query->set('orderby', []);
            $query->set('groupby', []);
            $query->set('limit', []);
            $stmt = $query->execute();
            $errorInfo = $stmt->errorInfo();
            if ($stmt->errorCode() > 0) throw new PageFinderException($errorInfo[2]);
            list($this->total) = $stmt->fetch(\PDO::FETCH_NUM);
            $stmt->closeCursor();
        } else {
            $this->total = (int)$database->query("SELECT FOUND_ROWS()")->fetchColumn();
        }

    } else {
        $this->total = count($matches);
    }

    // ...
}

Results:

 

[sreeb]

hi @adrian, how can i run that snippet?

[adrian]

2 minutes ago, sreeb said:

hi @adrian, how can i run that snippet?

Which one - the one to generate an SQL query from a PW selector, or the actual SQL query itself?

[sreeb]

Hi @adrian the one to generate an sql query form a pw selector