110 mysql requests for one page loaded from cache! Is it too much?!

79 Posts

tobice Reply #11, 16 years, 3 months ago

Quote from: coroico at Jun 11, 2010, 11:04 AM

I created simple log for storing mysql requests to check what is going on inside.
How did you build this log ? by tracking the sql request in the modx->db api ?

file_put_contents('assets/files/sql_queries', $sql."\n", FILE_APPEND);

I just inserted this to the right place in /manager/includes/extenders/dbapi.mysql.class.inc.php

-- sorry for my english, bad teacher

2,877 Posts

mrhaw Reply #12, 16 years, 3 months ago

Cool log tip!

@hawproductions | http://mrhaw.com/

Infograph: MODX Advanced Install in 7 steps:
http://forums.modx.com/thread/96954/infograph-modx-advanced-install-in-7-steps

Recap: Portland, OR (PDX) MODX CMS Meetup, Oct 6, 2015. US Bancorp Tower
http://mrhaw.com/modx_portland_oregon_pdx_modx_cms_meetup_oct_2015_us_bancorp_tower

79 Posts

tobice Reply #13, 16 years, 3 months ago

Quote from: mrhaw at Jun 11, 2010, 11:27 AM

Cool log tip!

:D Thanks, but it wasn’t that difficult.

-- sorry for my english, bad teacher

79 Posts

tobice Reply #14, 16 years, 3 months ago

Ok, I managed to solve the PHx caching problem (although the solution isn’t exactly clean, as usual

)

I was building an archive page for my web with Ditto/Reflect and I wanted to use pagination. And pagination means that snippet can’t be cached. That’s when the problem with redundant PHx mysql requests returned. My page needed again a lot of sql queries and when I checked the log, PHx was guilty. So I opened PHx plugin and found out, that PHx has its own cache however it doesn’t work for some reason. It is a simple array which should contain already loaded snippets. However stored items always disappear and have to be fetched again. I don’t understand much how PHx or generally plugins work so I wasn’t able to find the real reason. Or maybe I just missed something. However I did a simple thing to fix it. I replaced PHx cache ($PHx->cache["cm"]) with array stored in $modx->vars. It’s working now very well, snippets are being cached correctly and I no more need dozens of queries.

This is the final code (/assets/plugins/phx/phx.parser.class.inc.php around line 290)

<?php
if (!is_array($modx->vars["cm"])) $modx->vars["cm"] = array();
if (!array_key_exists($modifier_cmd[$i], $modx->vars["cm"])) {
        $sql = "SELECT snippet FROM " . $modx->getFullTableName("site_snippets") . " WHERE " . $modx->getFullTableName("site_snippets") . ".name='phx:" . $modifier_cmd[$i] . "';";
        $result = $modx->dbQuery($sql);
        if ($modx->recordCount($result) == 1) {
            $row = $modx->fetchRow($result);
            $cm = $modx->vars["cm"][$modifier_cmd[$i]] = $row["snippet"];
            $this->Log("  |--- DB -> Custom Modifier");
        } else if ($modx->recordCount($result) == 0){ // If snippet not found, look in the modifiers folder
                $filename = $modx->config['rb_base_dir'] . 'plugins/phx/modifiers/'.$modifier_cmd[$i].'.phx.php';
                if (@file_exists($filename)) {
                    $file_contents = @file_get_contents($filename);
                    $file_contents = str_replace('<'.'?php', '', $file_contents);
                    $file_contents = str_replace('?'.'>', '', $file_contents);
                    $file_contents = str_replace('<?', '', $file_contents);
                    $cm = $modx->vars["cm"][$modifier_cmd[$i]] = $file_contents;
                    $this->Log("  |--- File ($filename) -> Custom Modifier");
                }
        }
} else {
    $cm = $modx->vars["cm"][$modifier_cmd[$i]];
    $this->Log("  |--- Cache -> Custom Modifier");
}?>

PHx version 2.1.3

-- sorry for my english, bad teacher

MODX Staff
12,272 Posts

Ryan Thrash Reply #15, 16 years, 3 months ago

tobice could you file this as an improvement ticket in our JIRA tracker please? Sounds like there might be a bit of help PHx could use by default.

Ryan Thrash, MODX Co-Founder
Follow me on Twitter at @rthrash or catch my occasional unofficial thoughts at thrash.me

79 Posts

tobice Reply #16, 16 years, 3 months ago

Done.

http://svn.modxcms.com/jira/browse/MODX-2060

It was actually my first time I filed a bug

Hopefully I did it right.

-- sorry for my english, bad teacher

MODX Staff
10,725 Posts

opengeek Reply #17, 16 years, 3 months ago

Can someone explain why PHx is not using the snippet source that is already available on every request? It should just $modx->runSnippet(’phx:’ . $modifier, $options); no?

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

5 Posts

not_ice Reply #18, 16 years, 3 months ago

Quote from: OpenGeek at Jun 13, 2010, 01:20 PM

Can someone explain why PHx is not using the snippet source that is already available on every request? It should just $modx->runSnippet(’phx:’ . $modifier, $options); no?

I think this was done so because phx stores some of its modifiers also in files (assets\plugins\phx\modifiers).

I modified the solution proposed tobice for phx to use standard modx cache. This reduces the number of unnecessary calls to the database. Here it is:

$snippetName = 'phx:'.$modifier_cmd[$i];
if (isset($modx->snippetCache[$snippetName])) {
	$snippet = $modx->snippetCache[$snippetName];
} else { // not in cache so let's check the db
	$sql = "SELECT snippet FROM " . $modx->getFullTableName("site_snippets") . " WHERE " . $modx->getFullTableName("site_snippets") . ".name='" . $modx->db->escape($snippetName) . "';";
	$result = $modx->dbQuery($sql);
	if ($modx->recordCount($result) == 1) {
		$row = $modx->fetchRow($result);
		$snippet = $modx->snippetCache[$row['name']] = $row['snippet'];
		$modx->Log("  |--- DB -> Custom Modifier");
	} else if ($modx->recordCount($result) == 0){ // If snippet not found, look in the modifiers folder
		$filename = $modx->config['rb_base_dir'] . 'plugins/phx/modifiers/'.$modifier_cmd[$i].'.phx.php';
		if (@file_exists($filename)) {
			$file_contents = @file_get_contents($filename);
			$file_contents = str_replace('<'.'?php', '', $file_contents);
			$file_contents = str_replace('?'.'>', '', $file_contents);
			$file_contents = str_replace('<?', '', $file_contents);
			$snippet = $modx->snippetCache[$snippetName] = $file_contents;
			$modx->snippetCache[$snippetName.'Props'] = '';
			$modx->Log("  |--- File ($filename) -> Custom Modifier");
		}
	}
}
$cm = $snippet;

By the way, in the repository posted a new version of PHx (2.1.4), but the download link does not work (http://modxcms.com/extras/package/?package=342).