Is there a way to cache queries?

[BPickle]

Hi. I'm trying to tweak my client's Phoenix Cart 1.1.0.6 setup to lessen the load on the server CPU. Right now high CPU usage seems to be triggering 503 errors due to exceeding the limit.

Is caching queries an option? I would think caching only some queries would work, if it's even possible, since customer-specific info, like what's in their cart, couldn't be cached.

Would the addon Ultimate_SEO_Urls help resolve my issue, since it has a caching system?

[BrockleyJohn]

It only caches the urls that it writes, so no it won't help.

First step - make sure that Prevent Spider Sessions is set to True

Update the includes/spiders.txt if not recently done. You can get one here:
https://github.com/BrockleyJohn/spiders

Make sure there's a robots.txt set up and that it has a crawl delay for all of them

[BPickle]

It only caches the urls that it writes, so no it won't help.

First step - make sure that Prevent Spider Sessions is set to True

Update the includes/spiders.txt if not recently done. You can get one here:
https://github.com/BrockleyJohn/spiders

Make sure there's a robots.txt set up and that it has a crawl delay for all of them

Thanks, John, for the suggestions.

Prevent Spider Sessions was already set to true. I've updated spiders.txt.

.htaccess already has this directive:

Code: Select all

User-agent: *
Crawl-delay: 30

Is that how it should read? It had two spaces between the * and "Crawl-delay." I've changed that to a new line.

[BrockleyJohn]

It should be in robots.txt not .htaccess
You might want to add some disallows while you're at it

Code: Select all

User-agent: *
Disallow: /account*
Disallow: /address*
Disallow: /advanced_search*
Disallow: /checkout*
Disallow: /cookie_usage.php
Disallow: /account*
Disallow: /create_account*
Disallow: /login.php
Disallow: /logoff.php
Disallow: /password_forgotten.php
Crawl-delay: 30

(edit) forgot to mention - adjust paths above if shop not in domain root

[ecartz]

MySQL (and probably MariaDB) can cache database queries if you turn it on.

Cloudflare can cache page hits if you turn that on. It has a free tier.

In addition to robots.txt, etc.

[burt]

Check your clients hosting situation. Shared hosting + 503 errors -> almost always a plan limit.

If it's a `bucket` host (GoDaddy, HostGator, etc.) with zillions of sites on every server, your 503 could be someone elses fault entirely.

Get the error logs. If see "worker connections exceeded" or "max_connections," caching might help a bit but won't save you. That's a hosting upgrade conversation.

[BPickle]

It should be in robots.txt not .htaccess
You might want to add some disallows while you're at it

Code: Select all

User-agent: *
Disallow: /account*
Disallow: /address*
Disallow: /advanced_search*
Disallow: /checkout*
Disallow: /cookie_usage.php
Disallow: /account*
Disallow: /create_account*
Disallow: /login.php
Disallow: /logoff.php
Disallow: /password_forgotten.php
Crawl-delay: 30

(edit) forgot to mention - adjust paths above if shop not in domain root

My mistake. I meant robots.txt.

I've added what you've recommended.

[BPickle]

MySQL (and probably MariaDB) can cache database queries if you turn it on.

Cloudflare can cache page hits if you turn that on. It has a free tier.

In addition to robots.txt, etc.

CloudFlare is already caching static files, but not PHP files.

I will inquire from the web host about caching database queries.

[BPickle]

Check your clients hosting situation. Shared hosting + 503 errors -> almost always a plan limit.

If it's a `bucket` host (GoDaddy, HostGator, etc.) with zillions of sites on every server, your 503 could be someone elses fault entirely.

Get the error logs. If see "worker connections exceeded" or "max_connections," caching might help a bit but won't save you. That's a hosting upgrade conversation.

The site was on BlueHost, which was trying to sell an expensive upgrade to remedy the 429 error problem. My client hadn't had issues before several months ago.

We've moved to a new web host. On Friday, tech support added two indexes:

Code: Select all

ALTER TABLE `sessions`
  ADD PRIMARY KEY (`sesskey`),
  ADD KEY `idx_expiry` (`expiry`);

ALTER TABLE `whos_online`
  ADD KEY `session_id` (`session_id`),
  ADD KEY `idx_time_last_click` (`time_last_click`);

They felt that fixed the problem, after clearing "out accumulated stale data." And the CPU usage and CPU faults were indeed down after that. But since midnight Friday night they're still too high sometimes, and 503 errors are still occurring.

I've inquired about MySQL/MariaDB caching the queries.

They do make available Redis, and have said that that would move the session data into memory. I'm hesitant to try that until we've fixed the current problem, since I don't want to make anything worse in the short term.

[BPickle]

I went ahead and enabled Redis for sessions in PhoenixCart, and the errors have suddenly stopped.

So on Friday when the web host tech support added two indexes and cleared out stale data, whatever that means, things improved. I repeatedly clicked various categories in the tree, and didn't come up with any errors.

Just now I couldn't do that without ending up with 503 errors. After enabling Redis for sessions in PhoenixCart, I can do that again.

But while it seems to be better, as if sessions are a major problem, I can still trigger some 503 errors by clicking up and down the category list.