Post by lukehackett85 » Sun May 16, 2021 6:45 pm

Hello all,
My site is running Opencart 3.0.3.2
It seems when I use the search feature it a) takes a very long time to search b) can cause site to hit errors like this (I tried to load admin panel while waiting for the search feature on frontpage to load)

Warning: mysqli::__construct(): (HY000/1226): User 'XXXXX' has exceeded the 'max_user_connections' resource (current value: 50) in /xxx/xxxx/domains/xxxx.co.uk/public_html/system/library/db/mysqli.php on line 7Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /xxx/xxxx/domains/xxxx.co.uk/public_html/system/library/db/mysqli.php on line 10Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /xxx/xxxx/domains/xxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
Fatal error: Uncaught Exception: Error: <br />Error No: in /xxx/xxxx/domains/xxxx.co.uk/public_html/system/library/db/mysqli.php:10 Stack trace: #0 /xxx/xxxx/domains/xxxx.co.uk/public_html/xxxx/storage/modification/system/library/db.php(36): DB\MySQLi->__construct('localhost', 'xxxxxxx...', 'xxxxxx', 'xxxxx...', '3306') #1 /xxx/xxxx/domains/xxxx.co.uk/public_html/system/framework.php(80): DB->__construct('mysqli', 'localhost', 'xxxxxxx', 'xxxxxxx', 'xxxxxxx_OcDb...', 'xxxx') #2 //xxx/xxxx/domains/xxxx.co.uk/public_html/system/startup.php(104): require_once('/home/xxxxxx...') #3 //xxx/xxxx/domains/xxxx.co.uk/public_html/public_html/admin/index.php(26): start('admin') #4 {main} thrown in //xxx/xxxx/domains/xxxx.co.uk/public_html/public_html/system/library/db/mysqli.php on line 10


I looked at the opencart error log in maintenance error logs for this time and I get this:-


2021-05-16 10:36:06 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:06 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:06 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:06 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:06 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:07 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:07 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:07 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:08 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:08 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:08 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:08 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:08 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:08 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:08 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:08 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:08 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:09 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:09 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:09 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:11 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:11 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:11 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:11 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:11 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:11 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:12 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:12 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:12 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:14 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:14 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:14 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:14 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:14 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:14 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:15 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:15 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:15 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:16 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:16 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:16 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:17 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:17 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:17 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:17 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:17 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:17 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:18 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:18 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:18 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:18 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:18 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:18 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:18 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:18 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:18 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:18 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:18 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:18 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:18 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:18 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:18 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:18 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:18 - PHP Warning: mysqli::__construct(): (HY000/1226): User 'xxxx_xxxxx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 7
2021-05-16 10:36:18 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10
2021-05-16 10:36:18 - PHP Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xxxx/domains/xxxxx.co.uk/public_html/system/library/db/mysqli.php on line 10


The site itself is generally very fast if navigating around admin, navigating category pages and going product to product on the frontpage

It's just the search which is really slow

Has anyone encountered this issue before? any help appreciated

Best regards

Luke

New member

Posts

Joined
Sun Apr 24, 2016 5:09 am

Post by ADD Creative » Sun May 16, 2021 9:31 pm

How many products does your store have? The number has a big effect on search performance.

www.add-creative.co.uk


Expert Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by thekrotek » Sun May 16, 2021 11:00 pm

If you're with SiteGround, then this is who's triggering the max user connections message. A well known scam hosting provider.

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by lukehackett85 » Sun May 16, 2021 11:34 pm

Store has 22,500 products approx at the moment
Not with siteground (with hostinger)

Edit: Should be noted that in the admin panel on product manager when I type a product name it is very responsive and doesn't kill the site the way the frontend search does.

The site is at tita n cards.co.uk (without spaces of course) if you want to recreate the problem

New member

Posts

Joined
Sun Apr 24, 2016 5:09 am

Post by ADD Creative » Mon May 17, 2021 5:35 am

The site seemed generally slow to me. You search page is not standard OpenCart, so your issue could be with whatever extension of theme is making the changes.

www.add-creative.co.uk


Expert Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by thekrotek » Mon May 17, 2021 12:45 pm

lukehackett85 wrote:
Sun May 16, 2021 11:34 pm
Not with siteground (with hostinger)
Hostinger is also a cheap shady hosting. I don't know if they also limit maximum number of connections and script executions like SiteGround, but they might. Try to investigate this and change your hosting provider if they do.

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by lukehackett85 » Mon May 17, 2021 3:03 pm

It seems that its multiple words it gets angry on in terms of response time

searching "lost thunder booster" "lost thunder booster code" (without quotes) takes forever vs "rhyperior" fairly quick and other single word searches. The single word searches actually even come up the auto predicted answers really quick, its just longer ones it seems on further testing.

Hostinger give me a usage panel, I am paying for one of their higher packages and im using like 4pc cpu max, 4pc memory etc.

Update: Seems typing in the same search terms that are crippling the search on the frontend "online booster code" for example into product manager in the admin panel is rapid. It brings up list of all the items near instantly, and when I hit filter it displays them all quick too. Yet it murders the site when I put it in the frontend search and spikes resources ???

New member

Posts

Joined
Sun Apr 24, 2016 5:09 am

Post by ADD Creative » Mon May 17, 2021 5:36 pm

lukehackett85 wrote:
Mon May 17, 2021 3:03 pm
It seems that its multiple words it gets angry on in terms of response time

searching "lost thunder booster" "lost thunder booster code" (without quotes) takes forever vs "rhyperior" fairly quick and other single word searches. The single word searches actually even come up the auto predicted answers really quick, its just longer ones it seems on further testing.

Hostinger give me a usage panel, I am paying for one of their higher packages and im using like 4pc cpu max, 4pc memory etc.

Update: Seems typing in the same search terms that are crippling the search on the frontend "online booster code" for example into product manager in the admin panel is rapid. It brings up list of all the items near instantly, and when I hit filter it displays them all quick too. Yet it murders the site when I put it in the frontend search and spikes resources ???
You need to contact the developer of the extension that modifies your site's search function.

If you are not using a search extension you need to contact Journal theme support.

www.add-creative.co.uk


Expert Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by paulfeakins » Mon May 17, 2021 6:29 pm

ADD Creative wrote:
Mon May 17, 2021 5:36 pm
You need to contact the developer of the extension that modifies your site's search function.

If you are not using a search extension you need to contact Journal theme support.
As above, plus you should consider better hosting.

But you should also check you have the appropriate database indexes added.

UK OpenCart Hosting | OpenCart Audits | OpenCart Support - please email info@antropy.co.uk


User avatar
Guru Member
Online

Posts

Joined
Mon Aug 22, 2011 11:01 pm
Location - London Gatwick, United Kingdom

Post by thekrotek » Mon May 17, 2021 6:30 pm

paulfeakins wrote:
Mon May 17, 2021 6:29 pm
But you should also check you have the appropriate database indexes added.
This!

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by lukehackett85 » Mon May 17, 2021 6:52 pm

I actually contacted Journal already apparently their search just uses opencarts one, on looking at my extensions etc I cannot see any obvious search extension that has been installed independently

How can I check that the databases are indexed?

New member

Posts

Joined
Sun Apr 24, 2016 5:09 am

Post by JNeuhoff » Mon May 17, 2021 7:14 pm

It would really help if you could provide some proper details. What exact extensions do you use? What web theme? What's your website URL? Who is your webhost?

The standard OpenCart search function works pretty well, so something else is wrong, most likely an under-resourced DB server.

Also, bear in mind that this here is an OpenCart forum, not a Journal forum! The Journal3 framework is not standard-compliant with OpenCart, it is quite bloated and 80% non-OpenCart, Journal has its own support system.

Export/Import Tool * SpamBot Buster * Unused Images Manager * Instant Option Price Calculator * Number Option * Google Tag Manager * Survey Plus * OpenTwig


User avatar
Guru Member

Posts

Joined
Wed Dec 05, 2007 3:38 am


Post by by mona » Mon May 17, 2021 11:09 pm

first of all, this is bad:
1.JPG

1.JPG (38.44 KiB) Viewed 4668 times

This is even worse:
2.JPG

2.JPG (63.93 KiB) Viewed 4668 times

If you run a forum, reference site or something like that, fine, on an eCommerce site, no Sir.
People would be interested in cards not identifying bicycles or boats.
So I suggest to drop the cloudflare stuff as it is generally useless for eCommerce sites unless you are in need of DDOS protection or are selling large digital content.

Your top search triggers an ajax call which produces a products query on every character entry, so better optimize that query and why not start that process after at least a number of characters have been entered,as it now runs into this:

pending and 500 on your journal search box:

I cannot see what the 500 error is but at least it is consistent.
It might very well be that those ajax calls are opening new connections because the previous one has not been freed yet thus eventually reaching your limit.
It also seems that you journal theme does not cancel ajax requests before they are fired when a new request is rapidly made so if you type fast, it does not discard some calls but fires them regardless, journal.
Next to that, because you use CF, all these requests are slowed down due to the extra proxy overhead of CF for dynamic content (pending).
3.jpg

3.jpg (280.34 KiB) Viewed 4668 times

cancelled requests on a non-journal site

You can check your max_user_connections setting for mysql and compare that with the Threads_connected and max_used_connections of your database by using these commands:

SHOW VARIABLES LIKE 'max_%';
SHOW STATUS LIKE '%connect%';

You can set the max_user_connections to 0 (unlimited) if your host allows or get a dedicated server if you cannot get a higher limit from your host but at least limit the amount of concurrent user-triggered async ajax calls with db access to heavy queries. And consider dumping CF unless you really really need their additional services like "I am not a bot" (you don't) or DDOS protection (you don't) as their cdn service is futile for eCommerce sites.


and they should stop displaying warnings:
Warning: mysqli::__construct(): (HY000/1226): User ‘xx' has exceeded the 'max_user_connections' resource (current value: 50) in /home/xx/domains/site.co.uk/public_html/system/library/db/mysqli.php on line 7
Warning: DB\MySQLi::__construct(): Couldn't fetch mysqli in /home/xx/domains/site.co.uk/public_html/system/library/db/mysqli.php on line 10

DISCLAIMER:
You should not modify core files .. if you would like to donate a cup of coffee I will write it in a modification for you.


https://www.youtube.com/watch?v=zXIxDoCRc84


User avatar
Expert Member

Posts

Joined
Mon Jun 10, 2019 9:31 am

Post by ADD Creative » Tue May 18, 2021 5:14 am

lukehackett85 wrote:
Mon May 17, 2021 6:52 pm
I actually contacted Journal already apparently their search just uses opencarts one, on looking at my extensions etc I cannot see any obvious search extension that has been installed independently

How can I check that the databases are indexed?
It's clear just by looking at you site, even if they do use the OpenCart search query, they change the way it is used. As well as the multiple calls as pointed out by mona. The actual search page takes you to /filtered/search:red&description=true. So it's clear it's going through an additional filter. Also it searching through descriptions by default. This will all add to the overhead.

You also might find too many search and filtered URLs are being crawled by bots.

www.add-creative.co.uk


Expert Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom

Post by halfhope » Wed May 19, 2021 1:24 am

There is "must have" indexes from my script:

Code: Select all

$non_exists_indexes 	= array(
	'order_product' => array(
		'order_id' => 'order_id'
	) ,
	'product_attribute' => array(
		'attribute_id' => 'attribute_id',
		'language_id' => 'language_id'
	) ,
	'product_description' => array(
		'language_id' => 'language_id'
	) ,
	'product_image' => array(
		'product_id' => 'product_id',
		'sort_order' => 'sort_order'
	) ,
	'product_option' => array(
		'product_id' => 'product_id',
		'option_id' => 'option_id'
	) ,
	'product_option_value' => array(
		'product_option_id' => 'product_option_id',
		'product_id' => 'product_id',
		'option_id' => 'option_id',
		'option_value_id' => 'option_value_id',
		'subtract' => 'subtract',
		'quantity' => 'quantity'
	) ,
	'product_reward' => array(
		'product_id' => 'product_id',
		'customer_group_id' => 'customer_group_id'
	) ,
	'product_to_category' => array(
		'category_id' => 'category_id'
	) ,
	'product_to_store' => array(
		'store_id' => 'store_id',
	) ,
	'setting' => array(
		'store_id' => 'store_id',
		'`code`' => '`code`',
		'`group`' => '`group`',
		'`key`' => '`key`',
		'serialized' => 'serialized'
	) ,
	'url_alias' => array(
		'query' => 'query'
	),
	'session' => array(
		'expire' => 'expire'
	)
);
Also you shoud profile SQL search query manually using EXPLAIN. And add indexes if necessearly.

My extensions in marketplace. [ security | flexibility | speedup ]


User avatar
Active Member

Posts

Joined
Tue Dec 10, 2013 9:44 pm
Location - San Diego

Post by lukehackett85 » Thu May 20, 2021 11:44 pm

Thanks for your replies all.

I use journal who have since modified the search feature and it seems to be exponentially better. I can't seem to bring down my website using the search box now so thats a fairly major improvement

RE Cloudflare mona, I have been getting ddos'd on and off for a while. I actually posted on here a couple of months back because my site kept going down with max user connections, error 500's etc the site was being obliterated and I changed webhosts about 3 times before one pointed out I was getting massive amounts of requests from 1500+ ips every 10 mins lol.

You were also onto a part of the problem with the search box sending loads of requests as they are typed for one phrase. This issue seems to have been corrected.

halfhope, thank you for the code also - is this to optimise the database search efficiency to make it generally quicker? How would I add this? via phpmyadmin?

New member

Posts

Joined
Sun Apr 24, 2016 5:09 am

Post by halfhope » Fri May 21, 2021 1:01 am

is this to optimise the database search efficiency to make it generally quicker?
Yes. If sql quires use indexes they don't scan all records to find result, they find them momentally.
How would I add this?
This is just list of tables and their indexes, that shoud be. manually add using phpmyadmin or adminer. Most of them already exists.

My extensions in marketplace. [ security | flexibility | speedup ]


User avatar
Active Member

Posts

Joined
Tue Dec 10, 2013 9:44 pm
Location - San Diego
Who is online

Users browsing this forum: Bing [Bot] and 156 guests