Post by prototype0104 » Fri Nov 15, 2019 2:33 pm

Hello guys! I;m using OpenCart 3.0.3.2 with Journal 3 theme. I wanted to speed up my store and I used atomix turbo script. Now my site is running pretty fast but my homepage is gone (attached). Found some topics on the forum but with no solution. Maybe someone solved that.

Attachments

home.png

home.png (210.36 KiB) Viewed 2374 times


Active Member

Posts

Joined
Sun Mar 24, 2019 6:08 pm
Location - Romania

Post by letxobnav » Fri Nov 15, 2019 2:54 pm

If you pick up an arbitrary script from the internet and run that against your database you are asking for trouble.
Suggest you restore your database backup.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by paulfeakins » Fri Nov 15, 2019 6:10 pm

letxobnav wrote:
Fri Nov 15, 2019 2:54 pm
If you pick up an arbitrary script from the internet and run that against your database you are asking for trouble.
Yeah, if you're not a developer definitely don't do stuff like that. You need to be able to read the script first and see what it actually does before running it on your live ecommerce site database.

letxobnav wrote:
Fri Nov 15, 2019 2:54 pm
Suggest you restore your database backup.
Well, check your error logs first, it might be something simple.

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 prototype0104 » Fri Nov 15, 2019 7:45 pm

I found this script on opencart forum too. A lot of peoples runned it, I just have journal theme. I got no errors.

Active Member

Posts

Joined
Sun Mar 24, 2019 6:08 pm
Location - Romania

Post by paulfeakins » Fri Nov 15, 2019 7:55 pm

prototype0104 wrote:
Fri Nov 15, 2019 7:45 pm
I got no errors.
What logs did you check?

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 prototype0104 » Fri Nov 15, 2019 8:20 pm

database and site error_log

Active Member

Posts

Joined
Sun Mar 24, 2019 6:08 pm
Location - Romania

Post by paulfeakins » Fri Nov 15, 2019 8:24 pm

prototype0104 wrote:
Fri Nov 15, 2019 8:20 pm
database and site error_log
And what about /system/storage/logs/ ?

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 prototype0104 » Fri Nov 15, 2019 8:38 pm

No errors.. I restored the database and just added indexes.. Means that the problem is the conversion to InonDB. What do you think? Should I try to convert it again manually ? Is that increasing the speed?

Active Member

Posts

Joined
Sun Mar 24, 2019 6:08 pm
Location - Romania

Post by paulfeakins » Fri Nov 15, 2019 8:47 pm

prototype0104 wrote:
Fri Nov 15, 2019 8:38 pm
No errors.. I restored the database and just added indexes.. Means that the problem is the conversion to InonDB. What do you think? Should I try to convert it again manually ? Is that increasing the speed?
Well if you can restore easily you could try it again but comment out the InnoDB bits?

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 prototype0104 » Fri Nov 15, 2019 10:34 pm

Switched to InnonDB manually and let journal 3 tables MyISAM. No errors for now.

Active Member

Posts

Joined
Sun Mar 24, 2019 6:08 pm
Location - Romania

Post by paulfeakins » Fri Nov 15, 2019 10:39 pm

prototype0104 wrote:
Fri Nov 15, 2019 10:34 pm
Switched to InnonDB manually and let journal 3 tables MyISAM. No errors for now.
Good news!

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 letxobnav » Fri Nov 15, 2019 11:17 pm

You can convert any table to innodb and yes, it is faster as it requires less disk i/o under normal conditions, just don't expect your site to suddenly "fly" because of it. Your site temporarily "flew" because it only contained the header and footer.
Perhaps the "script" just does the conversion poorly.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by prototype0104 » Fri Nov 15, 2019 11:35 pm

I feel the difference of speed now.. The only slowly load now is when I'm searching products because it search in description too. It tooks like 6-7 seconds but on 100k products I think is fine. Any ideas of optimization?

Active Member

Posts

Joined
Sun Mar 24, 2019 6:08 pm
Location - Romania

Post by letxobnav » Sat Nov 16, 2019 12:28 am

As far as I read that script, it adds indexes to fields which names contains "_id" if they are not yet defined.
That sounds plausible but it is not enough as there are much more fields used in where clauses with do not have "_id" in their name.
Not to mention the sort order fields.

So I would suggest to list out all queries performed (easy by doing an error_log($sql) in the db query class) for a while, make a note of all the fields used for where clauses, joins and sorts and put indexes on all of them if they do not yet exist.

You can add additional caches to query results as are done here and there in the default install but in my experience those have very little value speed-wise as reading and writing the cache files, in most cases, is not faster than the original query and can actually slow things down.

In general, if you have php/mysql skill, look over the logic and adjust/experiment, the benefit of open-source, there is some very bad logic in the default install.
For instance, purely as an example, in the cart class you have two functions:
countProducts() which should return the total amount of products in the cart
hasProducts() which should return simple the count of the cart items
Both these functions do not simply use the cart table which would suffice, no, they call the function getProducts() which retrieves all data about the products in the cart from the product tables, the attributes tables, the options tables etc.
After retrieving all of that data for every product in the cart, it is kindly discarded and countProducts() just returns the total product count and hasProducts() just returns the cart count.
Why is anybodies guess, perhaps in the name of (ill-considered) function re-use as getProducts() is needed for functions like hasStock() or getTotal(), these ill designed functions are called very frequently all over the place.
If you look, you will find more of this kind of ignorance for optimization.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by IP_CAM » Sat Nov 16, 2019 1:44 am

Well, Atomix Turbo is a great piece of Code, it not only changes old MyIsam to InnoDB, but it also
indexes the Database, at least with OC v.1.x + v.2.x OC Versions. And it has nothing to do with
Themes or the Admin section, it runs independently, and only uses the existing Root or Admin
config.php DB Info, to do it's job.
You must have screwed up someplace else, or then, an OC-3 Database is just no longer built
according standards.
Ernie
---
https://github.com/IP-CAM/opencart-turbo

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by opencartmart » Sun Nov 17, 2019 3:56 pm

Fair warning, creating unnecessary indices not just a wastage of resources but also have a negative impact when it comes to INSERT/UPDATE operation to maintain the meaningless index.

For example, if I am not wrong, according to the script, it creates indices for stock_status_id, weight_class_id, etc. in the product table; those are never going to show any positive impact in terms of OC queries.

XForm - Opencart Form Builder
Xshippingpro - An advanced Shipping Module
Need Professional support? Skype: opencartmart


Active Member

Posts

Joined
Wed Oct 02, 2013 3:59 am

Post by letxobnav » Sun Nov 17, 2019 5:07 pm

True but that would mostly affect the admin side, the front-end has relatively few insert/update queries on big tables.

I would however immediately remove the "viewed" field update in the products table and move that field to a separate table as that field is updated every time a product is viewed and for what, a dumb report.
This renders any query cache on any query involving the product table void.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by IP_CAM » Mon Nov 18, 2019 9:06 am

Well Gentlemen, I'm not sure, if your comments are related to the turbo.php code,
but I know, that that piece of Code had a remarkable effect on uncounted amounts
of OC-installations. And it's a real shame, that, due to some 'nasty' Arguments on Github
with our master of the OC Universe, not related to the DB-Indexer Code, that famous fellow
turned his back on OC for good. :'(

But if anyone knows it better, why not just go to work, and create a new free DB-Indexer
Code? ;) It's not much work, I assume, since the basic FRAME already exists, in a visually near
perfect form. It would be used thousands of times, and the best advertizing, one could make.
Better than arguing, in any case, strictly from the business point of view at least ... ;) :D

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by letxobnav » Mon Nov 18, 2019 1:35 pm

Well, I didn't say that the script does not do what it says it does.
I do have against it that it creates a false sense of "I am done with indexing" when it only solves part of the issues by simply looking at columns with "_id" in them, those OC could and should already do in the install sql script.

No idea what the nasty argument was about and yes your master is an insecure narcissistic idiot but still no reason to turn your back I would say.

But this script only covers the easy part, to identify the remaining columns which require indexes you would have to pick apart all queries in terms of where clauses, joins-on clauses and order-by clauses and make a judgement on whether that added index would do more good than bad (read-vs-write-wise).
When you have done that work, then you can add those required index columns to the $index_list array which in this published script is conveniently left empty aside from model.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by letxobnav » Mon Nov 18, 2019 5:39 pm

the indexes I have on table columns which do not have "_id" in their name.
You could add those in the script $index_list.

Code: Select all

api.status
api_ip.ip
api_session.date_modified
attribute.sort_order
attribute_group.sort_order
banner.status
banner_image.sort_order
cart.option
category.top
category.sort_order
category.status
category_description.name
country.name
country.status
coupon.code
coupon.start_date
coupon.end_state
coupon.status
currency.code
currency.status
customer.status
customer_group.sort_order
customer_ip.ip
customer_login.email
customer_login.ip
custom_field.sort_order
custom_field_value.sort_order
event.code
event.status
event.sort_order
event.trigger (full text index or change field to varchar 255 first)
extension.type
extension.code
filter.status
filter.sort_order
filter_group.status
filter_group.sort_order
guest.email
information.status
information.sort_order
language.name
language.code
language.locale
language.directory
language.sort_order
language.status
layout.name
layout_module.code
layout_module.position
layout_module.sort_order
layout_route.route
location.name
manufacturer.sort_order
modification.status
module.code
option.sort_order
option_description.name
option_value.sort_order
order_total.sort_order
product.model
product.quantity
product.price
product.date_available
product.sort_order
product.status
product.date_added
product.date_modified
product.viewed (better move this field to a separate table or just remove the update query if not needed as it is killing here)
product_description.tag
product_discount.date_start
product.discount.date_end
product_image.sort_order
product_special.priority
product_special.date_start
product_special.date_end
product_tags.tag
recurring.sort_order
seo_url.query
seo_url.keyword
session.expire
setting.key
statistics.code
theme.theme
theme.route
user.username
user.password
user.email
user.status
voucher.status
zone.status

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan
Who is online

Users browsing this forum: No registered users and 38 guests