Please help us to make AbanteCart Ideal Open Source Ecommerce Solution for everyone.

Support AbanteCart eCommerce

Author Topic: Database Performance Issues  (Read 19229 times)

Offline Ollie

  • Sr. Member
  • ****
  • Posts: 253
  • Karma: +27/-1
    • View Profile
    • Assistech
Database Performance Issues
« on: February 04, 2016, 06:14:06 AM »
Some of my database search queries are taking too long. Is there a way to improve its performance?
I see a bunch of abc tables at the top. Are they necessary for Abantecart to work properly? Can they be deleted without affecting the performance of the shopping cart?

I appreciate whatever information is provided to help me improve performance/speed?

Also, I have a slow query log file, but I don't know how to read it. Any insights on that?
Assistive technologies for people with special needs:
https://assistech.com/store/

Offline Basara

  • Administrator
  • Hero Member
  • *****
  • Posts: 5776
  • Karma: +274/-2
    • View Profile
Re: Database Performance Issues
« Reply #1 on: February 08, 2016, 01:46:39 AM »
Some of my database search queries are taking too long. Is there a way to improve its performance?

Hi. Can you post what queries are slow?

Offline Ollie

  • Sr. Member
  • ****
  • Posts: 253
  • Karma: +27/-1
    • View Profile
    • Assistech
Re: Database Performance Issues
« Reply #2 on: February 08, 2016, 10:34:07 PM »
Some of my database search queries are taking too long. Is there a way to improve its performance?
Hi. Can you post what queries are slow?

I upgraded my hosting plan and it seems to have improved as far as the query speed. Most of them took less and 1.5 sec. I found a couple that took longer. See below:

# Mon Feb  8 15:05:17 2016
# Thread_id: 684639  Schema: xxx_liveperson  Last_errno: 0  Killed: 0
# Query_time: 1.924218  Lock_time: 0.000038  Rows_sent: 0  Rows_examined: 1  Rows_affected: 1  Rows_read: 1
# Bytes_sent: 52
use xxx_liveperson;
SET timestamp=1454969117;
UPDATE livehelp_users SET cookieid='7ba998a5b51db0da3e7fdf88bc70b735',identity='208.184.161-

cslhVISITOR',ipaddress='208.184.161.172',cookied='Y',new_session='N',lastaction='20160208220515',expires='20160208222515'

WHERE sessionid='785c0a545d1655d5e3b70261d0e05a0d'

# Sun Feb  7 21:20:52 2016
# Thread_id: 247482  Schema: xxx_abantecart  Last_errno: 0  Killed: 0
# Query_time: 14.013470  Lock_time: 0.000076  Rows_sent: 16  Rows_examined: 2152  Rows_affected: 0  Rows_read: 2152
# Bytes_sent: 452
use xxx_abantecart;
SET timestamp=1454905252;
SELECT DISTINCT p.manufacturer_id, m.name
  FROM products p
  LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
  WHERE p.product_id IN (SELECT DISTINCT ptc.product_id
  FROM products_to_categories ptc
  WHERE ptc.category_id IN (64, 307))
Assistive technologies for people with special needs:
https://assistech.com/store/

Offline Ollie

  • Sr. Member
  • ****
  • Posts: 253
  • Karma: +27/-1
    • View Profile
    • Assistech
Re: Database Performance Issues
« Reply #3 on: February 08, 2016, 10:49:17 PM »
Hi. Can you post what queries are slow?

I also got a critical error message when using Advanced Search. Here's the error log:

2016-02-08 20:38:19 - App Error: AbanteCart core v.1.2.0 MySQL class error: Try to escape non-string value: array (
0 => 'additional receivers',
1 => 'tv/audio listening',
) in /home2/xxx/public_html/store/core/database/mysql.php on line 128
2016-02-08 20:38:19 - database error: AbanteCart core v.1.2.0 Error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
Error No: 1104
SELECT pt.tag as name, count(DISTINCT p.product_id) as total_products FROM products p
INNER JOIN product_descriptions pd
ON (p.product_id = pd.product_id AND pd.language_id = 1 ) INNER JOIN products_to_categories p2c
ON (p.product_id = p2c.product_id) INNER JOIN product_tags pt ON (p.product_id = pt.product_id and pt.language_id = '1') INNER JOIN product_tags p2t ON (p.product_id = p2t.product_id and p2t.language_id = '1') INNER JOIN product_tags p2t0 ON (p.product_id = p2t0.product_id and p2t0.language_id = '1' AND LCASE(p2t0.tag) = 'additional receivers') INNER JOIN product_tags p2t1 ON (p.product_id = p2t1.product_id and p2t1.language_id = '1' AND LCASE(p2t1.tag) = 'tv/audio listening') WHERE p.date_available <= NOW() AND p.status = '1' AND p2c.category_id = '67' AND p.manufacturer_id = '36' GROUP BY pt.tag in <b>/home2/xxx/public_html/store/core/database/mysql.php</b> on line <b>114</b>
Assistive technologies for people with special needs:
https://assistech.com/store/

Offline abolabo

  • core-developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 2046
  • Karma: +318/-13
  • web for all, all for web!
    • View Profile
    • AbanteCart
Re: Database Performance Issues
« Reply #4 on: February 09, 2016, 07:52:31 AM »
1. you should switch ON amysqli database-driver (just open your system/config.php file and replace 'mysql' with 'amysqli').
2. replace file /code/database/amysqli.php by attached

« Last Edit: February 09, 2016, 10:45:50 AM by eCommerce Core »
“No one is useless in this world who lightens the burdens of another.”
― Charles Dickens

Offline Ollie

  • Sr. Member
  • ****
  • Posts: 253
  • Karma: +27/-1
    • View Profile
    • Assistech
Re: Database Performance Issues
« Reply #5 on: February 11, 2016, 11:24:59 PM »
1. you should switch ON amysqli database-driver (just open your system/config.php file and replace 'mysql' with 'amysqli').
2. replace file /code/database/amysqli.php by attached

Should I change to "amysqli" even though I'm using version 1.2.0?
Assistive technologies for people with special needs:
https://assistech.com/store/

Offline abolabo

  • core-developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 2046
  • Karma: +318/-13
  • web for all, all for web!
    • View Profile
    • AbanteCart
Re: Database Performance Issues
« Reply #6 on: February 12, 2016, 08:23:02 AM »
Should I change to "amysqli" even though I'm using version 1.2.0?

yes.
simple mysql-driver is deprecated since php v.5.5 and will be removed.
use amysqli instead, or apdomysql since Abantecart v.1.2.5
“No one is useless in this world who lightens the burdens of another.”
― Charles Dickens

Offline Ollie

  • Sr. Member
  • ****
  • Posts: 253
  • Karma: +27/-1
    • View Profile
    • Assistech
Re: Database Performance Issues
« Reply #7 on: February 13, 2016, 08:43:58 AM »
Should I change to "amysqli" even though I'm using version 1.2.0?
yes.
simple mysql-driver is deprecated since php v.5.5 and will be removed.
use amysqli instead, or apdomysql since Abantecart v.1.2.5
Something weird happened and I'm not sure if it's related to the changes I made following your suggestion. Four new orders are not showing in Abantecart. It's as if they do not exist. I got an email confirmation for them but they are not showing under Sales > Orders. They were in the download file so I had to reupload them to show in the right place. I cleared cache, tried a couple new orders and  this time I got an email confirmation for each. Any idea why this happened?
« Last Edit: February 13, 2016, 11:15:10 AM by Ollie »
Assistive technologies for people with special needs:
https://assistech.com/store/

Offline abolabo

  • core-developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 2046
  • Karma: +318/-13
  • web for all, all for web!
    • View Profile
    • AbanteCart
Re: Database Performance Issues
« Reply #8 on: February 13, 2016, 01:07:45 PM »
Something weird happened and I'm not sure if it's related to the changes I made following your suggestion. Four new orders are not showing in Abantecart. It's as if they do not exist. I got an email confirmation for them but they are not showing under Sales > Orders. They were in the download file so I had to reupload them to show in the right place. I cleared cache, tried a couple new orders and  this time I got an email confirmation for each. Any idea why this happened?

it's not related to db-driver.
try to find this "mystic" order inside table orders, via phpMyAdmin. You should to know order_id. Check order_status_id field.
“No one is useless in this world who lightens the burdens of another.”
― Charles Dickens

Offline Ollie

  • Sr. Member
  • ****
  • Posts: 253
  • Karma: +27/-1
    • View Profile
    • Assistech
Re: Database Performance Issues
« Reply #9 on: February 14, 2016, 05:27:35 AM »
it's not related to db-driver. try to find this "mystic" order inside table orders, via phpMyAdmin. You should to know order_id. Check order_status_id field.

I found them all in the database. Amazing how the performance improved. The database is much faster now. Thank you for your help.

I have another database-related question. Yesterday, I installed v 1.2.5 (while still keeping v 1.2.0), and after toying with it for a while I realized it would be too time consuming to proceed. So I deleted "store2" which was associated with v 1.2.5. However, I noticed that all the sample products were imported into my regular database. I'd like to delete all the data associated with the sample store. Here are my  questions:

Is all the data in the abc_ tables related to the sample store? (and not my regular store)

If that's the case, I'd like to delete this unnecessary data since it's just taking disk space. In fact, I already deleted the data from the three top tables. When I deleted the data from abc_addresses, for example, I noticed that some related table information remained (e.g. address_id, customer_id, company etc). See attached screenshot. Can I delete this information too?

Assistive technologies for people with special needs:
https://assistech.com/store/

Offline abolabo

  • core-developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 2046
  • Karma: +318/-13
  • web for all, all for web!
    • View Profile
    • AbanteCart
Re: Database Performance Issues
« Reply #10 on: February 14, 2016, 03:24:08 PM »
it's not related to db-driver. try to find this "mystic" order inside table orders, via phpMyAdmin. You should to know order_id. Check order_status_id field.

I found them all in the database. Amazing how the performance improved. The database is much faster now. Thank you for your help.

I have another database-related question. Yesterday, I installed v 1.2.5 (while still keeping v 1.2.0), and after toying with it for a while I realized it would be too time consuming to proceed. So I deleted "store2" which was associated with v 1.2.5. However, I noticed that all the sample products were imported into my regular database. I'd like to delete all the data associated with the sample store. Here are my  questions:

Is all the data in the abc_ tables related to the sample store? (and not my regular store)

If that's the case, I'd like to delete this unnecessary data since it's just taking disk space. In fact, I already deleted the data from the three top tables. When I deleted the data from abc_addresses, for example, I noticed that some related table information remained (e.g. address_id, customer_id, company etc). See attached screenshot. Can I delete this information too?

we recommends to remove your sample customers via Abantecart admin (for ex from grid). There cascade deleting. It will delete all customers data in single pass.
You can find more details about sample data in "install" directory of AbanteCart distibutive, see file abantecart_sample_data.sql
“No one is useless in this world who lightens the burdens of another.”
― Charles Dickens

Offline Ollie

  • Sr. Member
  • ****
  • Posts: 253
  • Karma: +27/-1
    • View Profile
    • Assistech
Re: Database Performance Issues
« Reply #11 on: February 15, 2016, 07:01:58 AM »
we recommends to remove your sample customers via Abantecart admin (for ex from grid). There cascade deleting. It will delete all customers data in single pass.
You can find more details about sample data in "install" directory of AbanteCart distibutive, see file abantecart_sample_data.sql
I looked in Admin and I don't see any samples customers listed in there. Leaving that issue aside for now, I'm having some serious issues with my website. For some reason, it's not displaying the way it should. The layout is messed up; it's showing a mix of Chinese, English and Spanish for the text; the product names are not showing and the product dropdown menus are either missing or incomplete. I tried to attach the screenshots, but I couldn't. Below are the image links. I wonder if these problems are related to the changes I made (mysql to amysqli). I tried reverting back to mysql, but it didn't solve the problem so I put config back to amysqli and replaced the corresponding file. When I logged into Admin, it was all in Chinese!

Good display: https://assistech.com/store/image/screenshot-good.jpg
Bad display: https://assistech.com/store/image/screenshot-bad1.jpg
Bad display: https://assistech.com/store/image/screenshot-bad2.jpg
« Last Edit: February 15, 2016, 07:11:16 AM by Ollie »
Assistive technologies for people with special needs:
https://assistech.com/store/

Offline Basara

  • Administrator
  • Hero Member
  • *****
  • Posts: 5776
  • Karma: +274/-2
    • View Profile
Re: Database Performance Issues
« Reply #12 on: February 15, 2016, 08:08:36 AM »
Hello.

It looks like some translations are missing. First clear AbanteCart cache and then if issues not solved you can try to Load missing language data from your base language English. You can run this in Admin > System > Localization > Language > Edit language page.

Do not forget to backup.

Offline Ollie

  • Sr. Member
  • ****
  • Posts: 253
  • Karma: +27/-1
    • View Profile
    • Assistech
Re: Database Performance Issues
« Reply #13 on: February 15, 2016, 10:44:27 AM »
Hello.
It looks like some translations are missing. First clear AbanteCart cache and then if issues not solved you can try to Load missing language data from your base language English. You can run this in Admin > System > Localization > Language > Edit language page.
Do not forget to backup.
Last night, I cleared cache but it didn't solve the problem. This morning, the website seems to be running okay. This is the second time it happens in a matter of a week. I wonder if the issue is related to the changes I made (from mysql to amysqli). I don't use the Abantecart translation feature. I use a third party company but I don't believe the problem is related to that. It should have no interference on the English portion of the website and yet when I tried to access English last night, it was showing a mix of three different languages on the homepage. What do you recommend at this point so this problem doesn't keep reoccuring? Here are some other changes that took place recently:

- Upgraded hosting package to Cloud Business Pro (with three data centers)
- Renamed the database (since I was trying to import an identical copy of it); changed the name back to what it was; tested the shopping cart and didn't see any problem at the time.
- Changed config.php from mysql to amysqli and replaced the amysqli file (as suggested by Abolabo)

PS: I spoke with my host this morning and they suggested restoring the website prior to the date when I was having this problem. At the same time, I had Cloudflare purge the cached version of the website. I'm waiting to see what happens. I appreciate your feedback on this.
« Last Edit: February 15, 2016, 11:52:54 AM by Ollie »
Assistive technologies for people with special needs:
https://assistech.com/store/

Offline abolabo

  • core-developer
  • Administrator
  • Hero Member
  • *****
  • Posts: 2046
  • Karma: +318/-13
  • web for all, all for web!
    • View Profile
    • AbanteCart
Re: Database Performance Issues
« Reply #14 on: February 15, 2016, 03:08:40 PM »
language definitions and descriptions are not the same!
Just open any category in admin and look into name field for english and chinese variants. You will see that chinese value is empty. Name is "description" of your category and you responses for this data. 
Other text, that built-in Abantecart calls language definitions and can be changed via system->localization->language definitions. Probably some definitions are missed. You can enable warnings in settings about that and catch all of them.
“No one is useless in this world who lightens the burdens of another.”
― Charles Dickens

 

Powered by SMFPacks Social Login Mod