Hello everyone,
I browsed a lot of pages here before starting this thread, also did a lot of searching online and in the forums, didn't find anything related to my issue though so here goes.
I have a 2.1.0.2 installation with around 15-18 extensions, fully functional live website ( 4K unique visitors/month with a fair amount of orders every day ), CDN enabled ( keycdn ), Opencart Lightning, Journal theme, Memcached and soon Varnish will be installed as well, with very good performance on the front-end ( Gtmetrix both values around 90% and webpagetest all A's with all issues being related to external factors/scripts ), so far so good.
Up until some days ago, I had 8 thousand products inside my installation. They were all live and were shown to the customers. I took a step forward though, because I hate data entry more than leftists hate Trump ( or the opposite ), and imported all the products from my wholesalers to have them ready to be enabled when a new shipment arrives, so less work for me in the long run. Total products right now are around 27K.
I don't consider them that many, but the thing is that my server really struggles and it's definitely not a hardware issue ( 8-core opteron, 32GB ram ) whenever I try to enter the products section in my admin area.
It takes a lot of time to load the list at first ( I have it set to display only 50 ), around 10-20 seconds, and then it's a pain in the **s when doing daily tasks such as updating descriptions, stock levels and all that.
If I do a search -> filter -> edit to more than 50-60 products at once, the server load will gradually go up to 5-6 or even 8 and 9 which will eventually slow down the frontend as well. The service that is shown on htop in the linux console to be taking up most of the CPU resources, is mysql, so I guess there's something that needs to be done to allow mysql to "breathe" like it used to.
When there were only 8K products I could open 100-120 tabs with products and do all sorts of stuff here and there with no noticeable lag at all.
I have also asked my host to investigate but they're not that helpful this time, don't know why, so I need to at least find what's causing the heavy load and maybe give them a pointer on what to fix.
Do you have any suggestions or any experience such as the above? Since I don't know that much about mysql I'm hesitating to start experimenting with settings, and it's a live shop as well, so everything has to be done with that in mind.
Thanks a lot for your time!
Hello, thanks for your answer.pipoy wrote:You do have 8-core opteron, 32GB ram but can you monitor your server utilization?
I have not experienced 27k product catalogs though so Im not sure if this is a normal behavior
I tried using htop, iotop and a handful of other commands to see where the load originates from, and it all points to the mysql service.
Don't know if that's what you asked though. If you mean something else, please collaborate so I can look into it.
The server is very powerful and could handle many more products than my current configuration I guess.
Well, the Admin Section usually run's separated from 'speed enhancements', added, to make a Shop
Frontend faster, exept for admin Sections, where VqMod's are doing some work. The best thing to do
would therefore be, to have your DB delievering the data very quick. And to make sure, this is the case,
you could use this simple PHP-Page-Tool, to fully INDEX your DB, and possibly change the DB-Engine to
InnoDB, to still get a little more speed out of the DB. The File just need to be uploaded into your Shop or
ADMIN ROOT. And it's just called by it's name + .ext, and it works on/with all OC Versions.
Compared to MySql, the InnoDB way of doing it, resulted in a dramatic Performance Gain. But compared
to more modern MySqli, it end's up, beeing about the same. Still, some Extensions use default MyISAM,
and some use InnoDB as DB-Engine, so, I advise, to frequently make sure, that all Extensions are driven
by ONE Engine only, such also will add at least a fraction to the overall speed, when it comes to a busy Shop.
But Full Indexing is a MUST, if one cares, to make sure...
The genious Atomix INNO_DB Changer and DB Indexer Power Tool:
http://www.ipc.li/os/opencart-turbo-master.zip
But you should be careful anyway, adding a lot of UPPERS to an existing Journal Themed OC, because, Journal
usually has/uses it's own Drug's, to speed up it's Theme with all those richly enhanced Styles and Scripts.
Just an Idea...
Good Luck!
Ernie
PS: And when it comes to TOP Performance, one is required to decide, either on using VqMods only, by manually
removing the OcMod Startup Procedure, or then, use the OcMod Way of doing Things only. And if one still
want's to use VqMod's and OcMods, the best Solution, to me, would be, to get the famous integrated VqMod, so avoiding VqMod to use it's own CACHE Section, but placing VqMod Content into the Database, and handling it's from there, to add it's content into the OcMod Extension Cache File Section Files, exactly as OcMods do. It just makes no sense, to repeat such a procedure twice, just to have it done...
But then, your Lightning Mod will be History as well...
https://www.opencart.com/index.php?rout ... n_id=19501
Frontend faster, exept for admin Sections, where VqMod's are doing some work. The best thing to do
would therefore be, to have your DB delievering the data very quick. And to make sure, this is the case,
you could use this simple PHP-Page-Tool, to fully INDEX your DB, and possibly change the DB-Engine to
InnoDB, to still get a little more speed out of the DB. The File just need to be uploaded into your Shop or
ADMIN ROOT. And it's just called by it's name + .ext, and it works on/with all OC Versions.
Compared to MySql, the InnoDB way of doing it, resulted in a dramatic Performance Gain. But compared
to more modern MySqli, it end's up, beeing about the same. Still, some Extensions use default MyISAM,
and some use InnoDB as DB-Engine, so, I advise, to frequently make sure, that all Extensions are driven
by ONE Engine only, such also will add at least a fraction to the overall speed, when it comes to a busy Shop.
But Full Indexing is a MUST, if one cares, to make sure...
The genious Atomix INNO_DB Changer and DB Indexer Power Tool:
http://www.ipc.li/os/opencart-turbo-master.zip
But you should be careful anyway, adding a lot of UPPERS to an existing Journal Themed OC, because, Journal
usually has/uses it's own Drug's, to speed up it's Theme with all those richly enhanced Styles and Scripts.
Just an Idea...
Good Luck!
Ernie
PS: And when it comes to TOP Performance, one is required to decide, either on using VqMods only, by manually
removing the OcMod Startup Procedure, or then, use the OcMod Way of doing Things only. And if one still
want's to use VqMod's and OcMods, the best Solution, to me, would be, to get the famous integrated VqMod, so avoiding VqMod to use it's own CACHE Section, but placing VqMod Content into the Database, and handling it's from there, to add it's content into the OcMod Extension Cache File Section Files, exactly as OcMods do. It just makes no sense, to repeat such a procedure twice, just to have it done...
But then, your Lightning Mod will be History as well...
https://www.opencart.com/index.php?rout ... n_id=19501
My Github OC Site: https://github.com/IP-CAM
5'600 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.
Hello and thanks for your help.IP_CAM wrote:Well, the Admin Section usually run's separated from 'speed enhancements', added, to make a Shop
Frontend faster, exept for admin Sections, where VqMod's are doing some work. The best thing to do
would therefore be, to have your DB delievering the data very quick. And to make sure, this is the case,
you could use this simple PHP-Page-Tool, to fully INDEX your DB, and possibly change the DB-Engine to
InnoDB, to still get a little more speed out of the DB. The File just need to be uploaded into your Shop or
ADMIN ROOT. And it's just called by it's name + .ext, and it works on/with all OC Versions.
Compared to MySql, the InnoDB way of doing it, resulted in a dramatic Performance Gain. But compared
to more modern MySqli, it end's up, beeing about the same. Still, some Extensions use default MyISAM,
and some use InnoDB as DB-Engine, so, I advise, to frequently make sure, that all Extensions are driven
by ONE Engine only, such also will add at least a fraction to the overall speed, when it comes to a busy Shop.
But Full Indexing is a MUST, if one cares, to make sure...
The genious Atomix INNO_DB Changer and DB Indexer Power Tool:
http://www.ipc.li/os/opencart-turbo-master.zip
But you should be careful anyway, adding a lot of UPPERS to an existing Journal Themed OC, because, Journal
usually has/uses it's own Drug's, to speed up it's Theme with all those richly enhanced Styles and Scripts.
Just an Idea...
Good Luck!
Ernie
PS: And when it comes to TOP Performance, one is required to decide, either on using VqMods only, by manually
removing the OcMod Startup Procedure, or then, use the OcMod Way of doing Things only. And if one still
want's to use VqMod's and OcMods, the best Solution, to me, would be, to get the famous integrated VqMod, so avoiding VqMod to use it's own CACHE Section, but placing VqMod Content into the Database, and handling it's from there, to add it's content into the OcMod Extension Cache File Section Files, exactly as OcMods do. It just makes no sense, to repeat such a procedure twice, just to have it done...
But then, your Lightning Mod will be History as well...
https://www.opencart.com/index.php?rout ... n_id=19501
I tried using Integrated vqmod but caused random issues with some of my extensions, when the "normal" vqmod didn't cause anything. So I didn't bother troubleshooting and I just uninstalled it.
I really don't know in depth about Ocmod and vqmod and which is better or not, even though I read a lot about it. I'm a shop owner and my focus is ( and should be ) sales/marketing my brand and stuff like that. I wish I had plenty of time to make the best choices at any given time but everyone knows that this is not possible especially when there are development issues/knowledge that need to be considered to make the choice.
Journal is indeed heavy, but I think it's really light for what it offers. It could be a lot heavier if it was based on another platform, for example Wordpress. So yeah, it's just a good compromise, at the very best.
I will now backup the database and run the script you gave me. I know it's a database issue and I'm sure OC can handle more products than mine rather easily so I'll try it out and get back to you with the results.
well, VqMod and OcMod basically do the same, their Routines CHANGE something, in some
Default Source Files. The RESULT of this 'Interaction' will be saved, individually, as socalled 'Cached Output-
Template' file, carrying the Default OC File 'name+.ext' of the default-OC Source-file in charge.
---
Those Files are then called, if a page is 'constructed', instead of calling the default OC Files in charge,
first, the OcMod Function generated Files, and then, the VqModded Files, and the FINAL Output Files
would be the VqModded One's, if some of them are part of a page building process.
---
In case of OcMod, such FINAL Output files are stored in the System - Modification - Directories, and
they are placed, and look exactly like regular OC Files, in their corresponding Subdirectories. This actually
means, that the complete CACHED Directory Content, as far as it contains subs and files, could technically be
used as/like 'default' OC files, so having all the extension-changes already included !
So, one could build a 'final' Shop, and then, kill anything on surplus!
Exept for possible VqModded Content!
---
Example: An OC-modded File is placed in the system/storage/modification/system/library/...
Subdirectory, ad it's called/named like it's default original, by example db.php
The same db.php File, handled by VqMod, would be placed in the vqmod/vqcache/... Sub
and then named vq2-system_library_db.php.
---
VqModded file names also look like this:
vq2-catalog_language_english_common_header.php
vq2-admin_language_english_catalog_product.php
vq2-system_modification_system_engine_loader.php
vq2-system_modification_catalog_view_theme_oc-bootstrap_template_product_category.tpl
-
meaning, that
LIne 1: adds/changes something to the header language section
Line 2: changes something in the admin language product Page
Line 3 +4: shows files in charge, already used / handled / modified in some way by OcMod!
---
So whenever a Url is called, in Case of OcMod/VqMod, the first step is to get the OcModded Files,
if some exist.
If VqMod is also Part of the Game, the OcModded OUTPUT Template Files will be used by VqMod,
instead of the Default OC File Source, to create it's own Bunch of 'cached Output-Template' files,
whereever one of it's own VqMod's add's/changes/removes something to/from it, and finally sends
this OC-Source_OcMod_VqMod - combined Output Content to the Visitor.
This 'way' is required, and also a possible Source of Errors, if an OcMod already changed something,
somewhere, and the later 'following' VqMod-Routine can no longer find it's 'anchor-tag', on the already
OcModified Page, to interact, as planned, before releasing the file to the public.
OcMod keeps it's OcMod Source in it's DB, whereby VqMod keeps it's Source in the vqmod/xml/...Sub.
Still, VqMod uses a simple easy FLAT-FILE Type DB Reservoir, if 'Source' is used, due to rebuilding new
output files, and OcMod needs to get it's Source from an already heavy loaded and very busy DB.
---
So, one should really decide on, what to use, in a very busy shop Environment. Or then, settle for less,
when it comes to heavy traffic, on already busy shared Servers, limited Bandwidth-Lines, or so....
It all depends, and it's seemengly making a difference, because, as less Code a Server has to 'handle', as
more 'tolerant' a Server may react, when it comes to larger-sized images, more and more popular in the scene.
I hope, I'm right, but in any case, it's no as hard as it looks, after reading all this...
Good Luck!
Ernie
PS: I am presently in the process of building a 1.5.6.5 wich a similarly equipped OcMod and VqMod Section,
in order to make further tests on this. Then, I will decide, how to proceed. And I use the Last of the old Versions,
because I already have and know, what I need for this...
Default Source Files. The RESULT of this 'Interaction' will be saved, individually, as socalled 'Cached Output-
Template' file, carrying the Default OC File 'name+.ext' of the default-OC Source-file in charge.
---
Those Files are then called, if a page is 'constructed', instead of calling the default OC Files in charge,
first, the OcMod Function generated Files, and then, the VqModded Files, and the FINAL Output Files
would be the VqModded One's, if some of them are part of a page building process.
---
In case of OcMod, such FINAL Output files are stored in the System - Modification - Directories, and
they are placed, and look exactly like regular OC Files, in their corresponding Subdirectories. This actually
means, that the complete CACHED Directory Content, as far as it contains subs and files, could technically be
used as/like 'default' OC files, so having all the extension-changes already included !
So, one could build a 'final' Shop, and then, kill anything on surplus!
Exept for possible VqModded Content!
---
Example: An OC-modded File is placed in the system/storage/modification/system/library/...
Subdirectory, ad it's called/named like it's default original, by example db.php
The same db.php File, handled by VqMod, would be placed in the vqmod/vqcache/... Sub
and then named vq2-system_library_db.php.
---
VqModded file names also look like this:
vq2-catalog_language_english_common_header.php
vq2-admin_language_english_catalog_product.php
vq2-system_modification_system_engine_loader.php
vq2-system_modification_catalog_view_theme_oc-bootstrap_template_product_category.tpl
-
meaning, that
LIne 1: adds/changes something to the header language section
Line 2: changes something in the admin language product Page
Line 3 +4: shows files in charge, already used / handled / modified in some way by OcMod!
---
So whenever a Url is called, in Case of OcMod/VqMod, the first step is to get the OcModded Files,
if some exist.
If VqMod is also Part of the Game, the OcModded OUTPUT Template Files will be used by VqMod,
instead of the Default OC File Source, to create it's own Bunch of 'cached Output-Template' files,
whereever one of it's own VqMod's add's/changes/removes something to/from it, and finally sends
this OC-Source_OcMod_VqMod - combined Output Content to the Visitor.
This 'way' is required, and also a possible Source of Errors, if an OcMod already changed something,
somewhere, and the later 'following' VqMod-Routine can no longer find it's 'anchor-tag', on the already
OcModified Page, to interact, as planned, before releasing the file to the public.
OcMod keeps it's OcMod Source in it's DB, whereby VqMod keeps it's Source in the vqmod/xml/...Sub.
Still, VqMod uses a simple easy FLAT-FILE Type DB Reservoir, if 'Source' is used, due to rebuilding new
output files, and OcMod needs to get it's Source from an already heavy loaded and very busy DB.
---
So, one should really decide on, what to use, in a very busy shop Environment. Or then, settle for less,
when it comes to heavy traffic, on already busy shared Servers, limited Bandwidth-Lines, or so....
It all depends, and it's seemengly making a difference, because, as less Code a Server has to 'handle', as
more 'tolerant' a Server may react, when it comes to larger-sized images, more and more popular in the scene.
I hope, I'm right, but in any case, it's no as hard as it looks, after reading all this...
Good Luck!
Ernie
PS: I am presently in the process of building a 1.5.6.5 wich a similarly equipped OcMod and VqMod Section,
in order to make further tests on this. Then, I will decide, how to proceed. And I use the Last of the old Versions,
because I already have and know, what I need for this...
My Github OC Site: https://github.com/IP-CAM
5'600 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.
Wow, what a reply!
I did the migration from Myisam to Innodb and everything went fine.
There's an improvement on performance, but some tables can't be switched to Innodb due to a fulltext index error or something ( I thought I've kept it in a txt file but I can't find it
)
The tables that are still on Myisam engine are :
category description
manufacturer
product
product description
url redirect
url_redirect
user
user_group
voucher
voucher
voucher_theme
voucher_theme_description
weight_class
weight_class_description
zone
zone_to_geo_zone
I also added all indexes that were missing, but most of the tables had the required indexes already.
How do I change the above tables to InnoDB without breaking anything? Some of them are also rather large, mainly product description and url alias.
I get what you're trying to say about Ocmod and Vqmod even though it gets a bit technical. I always try to have Ocmod only but this is not possible in a live shop with a handful of extensions that are needed for the shop to run. I keep it on the minimum level though, if that alone can help the situation that is.
The change to Innodb didn't help on the server load though, it's still facing issues when there's a lot of windows/tabs open. I tried finding an optimal my.cnf file but I think first I need to safely transfer all remaining tables to InnoDB and then find a configuration file that fits my needs.
Thanks for your help so far!!
I did the migration from Myisam to Innodb and everything went fine.
There's an improvement on performance, but some tables can't be switched to Innodb due to a fulltext index error or something ( I thought I've kept it in a txt file but I can't find it
) The tables that are still on Myisam engine are :
category description
manufacturer
product
product description
url redirect
url_redirect
user
user_group
voucher
voucher
voucher_theme
voucher_theme_description
weight_class
weight_class_description
zone
zone_to_geo_zone
I also added all indexes that were missing, but most of the tables had the required indexes already.
How do I change the above tables to InnoDB without breaking anything? Some of them are also rather large, mainly product description and url alias.
I get what you're trying to say about Ocmod and Vqmod even though it gets a bit technical. I always try to have Ocmod only but this is not possible in a live shop with a handful of extensions that are needed for the shop to run. I keep it on the minimum level though, if that alone can help the situation that is.
The change to Innodb didn't help on the server load though, it's still facing issues when there's a lot of windows/tabs open. I tried finding an optimal my.cnf file but I think first I need to safely transfer all remaining tables to InnoDB and then find a configuration file that fits my needs.
Thanks for your help so far!!
You will have to look into your server configuration.
wich version of apache, php and mariadb are you running,
Wich system, whm/cpanel or direct admin
make sure at least youre runing apache 2.4 php 7 and maria db 10.X
this will speed up things already, next optimize php to work with those numbers.
Tweaking a server is time consuming, but a real chalange an great fun
wich version of apache, php and mariadb are you running,
Wich system, whm/cpanel or direct admin
make sure at least youre runing apache 2.4 php 7 and maria db 10.X
this will speed up things already, next optimize php to work with those numbers.
Tweaking a server is time consuming, but a real chalange an great fun
Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Alle niet stekplichtige onderdelen zoals scharnieren, sloten, randverwarming en verlichting voor alle typen koelingen en vriezers.
https://koelcel-onderdelen.com
Hello, I finally decided to change hosting company since the old one didn't want to help me find what was causing the issue.
The migration process is on the way, I will know tomorrow how it will behave. So far, on the websites that were migrated it's a lot better and on an Opencart installation ( almost unique to the one having the problem in terms of configuration, apart from having so many products ) it's running very fast.
The new server is setup with mariadb and almost same configuration like the previous one ( apache + nginx reverse proxy ) so I will see if it starts slowing down when working on the admin area.
Will update with new info soon. Thanks so far for your help.
The migration process is on the way, I will know tomorrow how it will behave. So far, on the websites that were migrated it's a lot better and on an Opencart installation ( almost unique to the one having the problem in terms of configuration, apart from having so many products ) it's running very fast.
The new server is setup with mariadb and almost same configuration like the previous one ( apache + nginx reverse proxy ) so I will see if it starts slowing down when working on the admin area.
Will update with new info soon. Thanks so far for your help.
Who is online
Users browsing this forum: Amazon [Bot] and 4 guests
