Database Performance for a lot of Products

[jcschott]

My site is product heavy (currently have 6,400 products) and I am seeing ok performance on the UI and checkout process but adding or editing products from the admin section routinely will spike my RAM usage coming from the Database performance causing timeout/500 errors to be generated. Before I upgrade my hosting again or split my DB and APP to different servers would figure if anyone else had a high product store using abantecart and any other performance tips they could provide.

Current Virtual Machine provided by hosting provider:
6 CPU Cores
8 GB RAM
250 GB SSD Storage
10.6.20-MariaDB - MariaDB Server
Apache 2.4.62

[Basara]

Hello.
Could you please let us know the PHP and AbanteCart versions, number of categories, and storefront template?

[jcschott]

PHP Version 8.3
AbanteCart Version 1.4.0
Template: Novator
Categories: 212 (17 parent categories, the rest childs)

The front end does ok and loads without resource spiking for the volume we get currently (4-5 concurrent users is generally the peak) but as soon as I start to edit a product in the admin section even with no current users hitting the site it causes the DB to spike in resources and often leads to 500 errors/timeouts for the front end.

[abolabo]

PHP Version 8.3
AbanteCart Version 1.4.0
Template: Novator
Categories: 212 (17 parent categories, the rest childs)

The front end does ok and loads without resource spiking for the volume we get currently (4-5 concurrent users is generally the peak) but as soon as I start to edit a product in the admin section even with no current users hitting the site it causes the DB to spike in resources and often leads to 500 errors/timeouts for the front end.

can you run this sql-query in the same time with product editing?

SHOW PROCESSLIST;

It will show list of all queries and you can define what current sql-query overloads you DB.
But probably DB is not a cause.
Database connection can be expired because of some other thing's processing, for example, resizing of images. Resize works in the same http-request and connection to db is open at this point and waiting (php-lock).
You can try to enable debugging int the system->settings->system to see  timings.
Or you can use xdebug profiling (for advanced users).

Probably you have some huge images of product that cannot be resized quickly or your host disk is slow etc..
Or some 3dparty extension slow down your admin..
Also you can look into cache speedup side.. redis or memcached.

Some different causes can be there.. it's require investigation.

[Basara]

Hello.
I wanted to mention that Managed AbanteCart Support can help with investigating these performance issues. You can reach out to them for more detailed assistance here https://managed.abantecart.com/index.html#contact

[jcschott]

Thanks guys, I wanted to update on what I have done so far as it doesn't appear to be an issue with the product table/ or database performance. I am still tracking down the issue on my end but abantecart appears to be handling the amount of products and categories just fine.

I tried to do some tracing and error capturing and I am going through that still. I did do a fresh install of abantecart and pointed that install to the existing database. Adding/Editing products was just fine and storefront was behaving as expected and copied all the resources over to the new store in case we had some heavy processing for some reason. I then made that fresh install my primary store by renaming the folders (My site's store is in /shop directory). Once renamed the now new install is having the same issue with spiking CPU upon editing products and the old install is not having the issue so I am leaning to some kind of local network routing issue or php path issue.

[Basara]

fresh install of abantecart and pointed that install to the existing database.

When installing to an existing database, please ensure you clean it up beforehand by dropping all tables. The CPU spike when editing a product may be related to the AbanteCart cache being removed and regenerated.

[jcschott]

Yes I think it might be a cache issue if Abantecart attempts to clear the product cache after any type of product update I'll do some more digging as I am trying to avoid having to reimport all the data after dropping the tables. Interesting though it only is for one install and not the other using the same DB, same version just different folders on the host. Also running into mysql max users error during some peak CPU processing times (Currently set at the default 150 connections)

[Freddieblakes]

Sounds like maybe the perpetrator is caching, especially since it only happens on one install. Maybe a config or permissions issue between directories? And reaching MySQL max users limit of 150 during CPU spikes might mean it's time to tweak the limit or keep an eye on long-running queries. Curious to know what you find!

[Burlesque]

With over 6,000 products, it's not surprising the admin side gets heavy sometimes. One thing I've seen help is optimizing your database indexes and making sure you're not loading too much data at once when editing products. Also, caching some queries or using a separate database server could really ease the load.