Author Topic: Big data caused memory exhausted  (Read 9351 times)

Offline smallfish

  • Full Member
  • ***
  • Posts: 129
  • Karma: +15/-2
    • View Profile
Re: Big data caused memory exhausted
« Reply #15 on: January 04, 2017, 10:33:06 PM »
This morning, I have tested it again and there has 450K products. For the 1st, 2nd time, there is no problem. But after I have cleared all the caches a few times and set sorting by Date New -> Old. Then the http 500 error (memory exhausted) occurs again. Then I have increased the memory_limit  to 512M in php.ini, no error occurs later.
I am running the test from production server. The resource may affect by other live websites.
Last night, I also created 1150K products and there is no error even the memory_limit set to 256M....
I am confusing right now and don't know where is the problem.
Anyway, the loading speed of home page and category / product listing page is really slow for 1st visit. If cache is enable, it is fast when the page is cached (2nd visit and no refresh of data and no expiration of caches). Hope that abantecart team can improve the loading speed of the home page and category / product listing page (data size inside one page is small, should load fast. just like the phpmyadmin, browsing a large/big table is very fast without delay).
I am also worrying about the Max number of concurrent processes. My server has 16GB Ram, then it should limit the no. of visitors / users and may caused memory exhausted easily from a few visitors open the web pages simultaneously.
« Last Edit: January 04, 2017, 10:57:20 PM by smallfish »

Offline smallfish

  • Full Member
  • ***
  • Posts: 129
  • Karma: +15/-2
    • View Profile
Re: Big data caused memory exhausted
« Reply #16 on: January 04, 2017, 11:17:05 PM »
I have cleared all the caches again and open the store front home page immediately.
This time, the following error occurs:

aban2/static_pages/index.php
==========================
There has been a critical error processing your request

SQL Error: MySQL server has gone away
Error No: 2006
SQL: DELETE FROM `abc_online_customers` WHERE `date_added`< (NOW() - INTERVAL 1 HOUR) in /aban2/core/database/amysqli.php on line 108
==========================

Waiting for 30 seconds later, go to /aban2 (home page) and open the storefront home page again, there is no problem and the page is cached.
« Last Edit: January 04, 2017, 11:20:23 PM by smallfish »

Offline eCommerce Core

  • Administrator
  • Hero Member
  • *****
  • Posts: 1602
  • Karma: +93/-1
    • View Profile
Re: Big data caused memory exhausted
« Reply #17 on: January 05, 2017, 09:03:54 PM »
Looks like your mysql server is a root of your problems.

FYI. I am working on some improvements for large product database.  I will post update in here soon.     
“If you’re in the luckiest one per cent of humanity, you owe it to the rest of humanity to think about the other 99 per cent.”
― Warren Buffett

Offline abantecart

  • Administrator
  • Hero Member
  • *****
  • Posts: 4232
  • Karma: +269/-9
    • View Profile
    • Ideal Open Source Ecommerce Solution
Re: Big data caused memory exhausted
« Reply #18 on: January 05, 2017, 09:15:18 PM »
This is great. I wonder if we should consider eCommerce site with 380 thousand products as a large scale site. What is next? Entire database of Amazon? :)
Usually, sites with large catalogs and volumes, require special hardware and administration in addition to software tuning. This is a costly exercise.

Our prior focus on performance testing was towards fast growing customers and orders. 1/2 million of products was not ever tested and great that AbanteCart can handle it.
We need your help to build better free open source eCommerce platform for everyone.
See how you can help

Offline smallfish

  • Full Member
  • ***
  • Posts: 129
  • Karma: +15/-2
    • View Profile
Re: Big data caused memory exhausted
« Reply #19 on: January 05, 2017, 09:57:04 PM »
Looks like your mysql server is a root of your problems.

FYI. I am working on some improvements for large product database.  I will post update in here soon.   
May be I have 6 live websites which are requesting data from mysql at the same time and abantecart has problem to access mysql at the moment.
I think that mysql should be tuned for performance setting. But I am not Familiar with it....
It's a good news to hear that you are improving abantecart to handle large product database.
Thank you!
« Last Edit: January 05, 2017, 10:28:45 PM by smallfish »

Offline smallfish

  • Full Member
  • ***
  • Posts: 129
  • Karma: +15/-2
    • View Profile
Re: Big data caused memory exhausted
« Reply #20 on: January 05, 2017, 10:14:11 PM »
This is great. I wonder if we should consider eCommerce site with 380 thousand products as a large scale site. What is next? Entire database of Amazon? :)
Usually, sites with large catalogs and volumes, require special hardware and administration in addition to software tuning. This is a costly exercise.

Our prior focus on performance testing was towards fast growing customers and orders. 1/2 million of products was not ever tested and great that AbanteCart can handle it.
From my testing, abantecart v.1.2.9 is better than v.1.2.7 or before which can handle large products database but it is not ideal. However, it is better than OC, ZC or even some paid shopping cart. Agree with you that handling large catalogs and volumes required more resources. But I have used a free Chinese shopping cart which can already handle 1000K+ products smoothly and it doesn't need powerful hardware or no special request for the software. The cart was developed at least 10 years ago. (it uses smarty template and also apply caching methodology at the beginning. So handling large product database is efficiently and open the category / product listing page, home page is very fast. It's database doesn't has too much normalization, so generate less quiries.(means run faster and use less resource)) I am still using it but I want to use a shopping cart which can provide multi-shop, multi-language and multi-currencies functionality. Now, abantecart may be the choice as I have already checking on abantecart a few years.
« Last Edit: January 06, 2017, 11:20:19 AM by smallfish »

Offline eCommerce Core

  • Administrator
  • Hero Member
  • *****
  • Posts: 1602
  • Karma: +93/-1
    • View Profile
Re: Big data caused memory exhausted
« Reply #21 on: January 06, 2017, 10:03:24 PM »
Looks like your mysql server is a root of your problems.

FYI. I am working on some improvements for large product database.  I will post update in here soon.   
May be I have 6 live websites which are requesting data from mysql at the same time and abantecart has problem to access mysql at the moment.
I think that mysql should be tuned for performance setting. But I am not Familiar with it....
It's a good news to hear that you are improving abantecart to handle large product database.
Thank you!
There are a number of articles about Mysql performance tuning.
http://dba.stackexchange.com/questions/136349/mysql-performance-tuning-for-myisam

We already have plans to add InnoDB engine to boots performance for Mysql and also support PostgreSQL.
Timeframe is not yet clear as this requires a lot of effort and needs funding.
“If you’re in the luckiest one per cent of humanity, you owe it to the rest of humanity to think about the other 99 per cent.”
― Warren Buffett

Offline eCommerce Core

  • Administrator
  • Hero Member
  • *****
  • Posts: 1602
  • Karma: +93/-1
    • View Profile
Re: Big data caused memory exhausted
« Reply #22 on: January 09, 2017, 01:26:20 AM »
Here are some improvements in case of very large products database

https://github.com/abantecart/abantecart-src/commit/63c32b5215bb0162adf20d472d05795e633cfb9d

Give it a try.
“If you’re in the luckiest one per cent of humanity, you owe it to the rest of humanity to think about the other 99 per cent.”
― Warren Buffett

Offline smallfish

  • Full Member
  • ***
  • Posts: 129
  • Karma: +15/-2
    • View Profile
Re: Big data caused memory exhausted
« Reply #23 on: January 09, 2017, 11:38:28 AM »
I have tested it with 450K products.
The backend product browsing is improved. It takes 13-15 seconds to jump to next page previously but now only 4-5 seconds

The frontstore home page: It takes 1-2 minutes to load the home page previously but now 30 seconds (after clear all the caches and reload it).
The categories / products listing page : It takes 15-20 seconds to load the page previously but now 12-14 seconds.
browsing page by page in a category (jump to next page): It takes 15-20 seconds to load the page previously but now also 12-14 seconds.
However, open the product listing page in a category where it has 10-20pcs of products is the same time as the category where it has 450K products....
open product detail page : no change, it takes 4-6 seconds to open the page.

In fact, for the front store, it was failed to load the home page after add the new indexes to the tables. (I have uploaded abantecart v.2 /admin, /core to overwrites my abantecart v.1.2.9 but forget to overwrite the storefront.) The error is http 500 error and find memory exhausted in error log. I have increased memory_limit to 512M in php.ini, then it works.
It seems that the new indexes will also use some memories. But after I uploaded the abantecart v.2 /storefront to overwrites the v.1.2.9, it only needs 384M.

Besides, you have mistake in public_html/install/abantecart_database_upgrade.sql (abantecart v.2.0)
CREATE INDEX `ac_products_status_idx` ON `abc_products` (`product_id`, `status`, `date_available`);
should correct to :
CREATE INDEX `ac_products_status_idx` ON `ac_products` (`product_id`, `status`, `date_available`);

« Last Edit: January 09, 2017, 11:40:00 AM by smallfish »

Offline smallfish

  • Full Member
  • ***
  • Posts: 129
  • Karma: +15/-2
    • View Profile
Re: Big data caused memory exhausted
« Reply #24 on: January 09, 2017, 12:11:59 PM »
Looks like your mysql server is a root of your problems.

FYI. I am working on some improvements for large product database.  I will post update in here soon.   
May be I have 6 live websites which are requesting data from mysql at the same time and abantecart has problem to access mysql at the moment.
I think that mysql should be tuned for performance setting. But I am not Familiar with it....
It's a good news to hear that you are improving abantecart to handle large product database.
Thank you!
There are a number of articles about Mysql performance tuning.
http://dba.stackexchange.com/questions/136349/mysql-performance-tuning-for-myisam

We already have plans to add InnoDB engine to boots performance for Mysql and also support PostgreSQL.
Timeframe is not yet clear as this requires a lot of effort and needs funding.

This is for your reference : https://www.selikoff.net/2008/11/19/why-too-much-database-normalization-can-be-a-bad-thing/

Offline eCommerce Core

  • Administrator
  • Hero Member
  • *****
  • Posts: 1602
  • Karma: +93/-1
    • View Profile
Re: Big data caused memory exhausted
« Reply #25 on: January 10, 2017, 05:37:35 PM »
I have tested it with 450K products.
The backend product browsing is improved. It takes 13-15 seconds to jump to next page previously but now only 4-5 seconds

The frontstore home page: It takes 1-2 minutes to load the home page previously but now 30 seconds (after clear all the caches and reload it).
The categories / products listing page : It takes 15-20 seconds to load the page previously but now 12-14 seconds.
browsing page by page in a category (jump to next page): It takes 15-20 seconds to load the page previously but now also 12-14 seconds.
However, open the product listing page in a category where it has 10-20pcs of products is the same time as the category where it has 450K products....
open product detail page : no change, it takes 4-6 seconds to open the page.

In fact, for the front store, it was failed to load the home page after add the new indexes to the tables. (I have uploaded abantecart v.2 /admin, /core to overwrites my abantecart v.1.2.9 but forget to overwrite the storefront.) The error is http 500 error and find memory exhausted in error log. I have increased memory_limit to 512M in php.ini, then it works.
It seems that the new indexes will also use some memories. But after I uploaded the abantecart v.2 /storefront to overwrites the v.1.2.9, it only needs 384M.

Besides, you have mistake in public_html/install/abantecart_database_upgrade.sql (abantecart v.2.0)
CREATE INDEX `ac_products_status_idx` ON `abc_products` (`product_id`, `status`, `date_available`);
should correct to :
CREATE INDEX `ac_products_status_idx` ON `ac_products` (`product_id`, `status`, `date_available`);
Improvements are there, but I think this is the max that we can squeeze out of Mysql and MyISAM on the code side for this case.

I like us to work on Postgres support and some other tools to make AbanteCart EE (Enterprise ECommerce) ready, but this will require some funding.

I fixed typos. Thank you.
“If you’re in the luckiest one per cent of humanity, you owe it to the rest of humanity to think about the other 99 per cent.”
― Warren Buffett

Offline smallfish

  • Full Member
  • ***
  • Posts: 129
  • Karma: +15/-2
    • View Profile
Re: Big data caused memory exhausted
« Reply #26 on: January 11, 2017, 04:14:44 AM »
I have tested it with 450K products.
The backend product browsing is improved. It takes 13-15 seconds to jump to next page previously but now only 4-5 seconds

The frontstore home page: It takes 1-2 minutes to load the home page previously but now 30 seconds (after clear all the caches and reload it).
The categories / products listing page : It takes 15-20 seconds to load the page previously but now 12-14 seconds.
browsing page by page in a category (jump to next page): It takes 15-20 seconds to load the page previously but now also 12-14 seconds.
However, open the product listing page in a category where it has 10-20pcs of products is the same time as the category where it has 450K products....
open product detail page : no change, it takes 4-6 seconds to open the page.

In fact, for the front store, it was failed to load the home page after add the new indexes to the tables. (I have uploaded abantecart v.2 /admin, /core to overwrites my abantecart v.1.2.9 but forget to overwrite the storefront.) The error is http 500 error and find memory exhausted in error log. I have increased memory_limit to 512M in php.ini, then it works.
It seems that the new indexes will also use some memories. But after I uploaded the abantecart v.2 /storefront to overwrites the v.1.2.9, it only needs 384M.

Besides, you have mistake in public_html/install/abantecart_database_upgrade.sql (abantecart v.2.0)
CREATE INDEX `ac_products_status_idx` ON `abc_products` (`product_id`, `status`, `date_available`);
should correct to :
CREATE INDEX `ac_products_status_idx` ON `ac_products` (`product_id`, `status`, `date_available`);
Improvements are there, but I think this is the max that we can squeeze out of Mysql and MyISAM on the code side for this case.

I like us to work on Postgres support and some other tools to make AbanteCart EE (Enterprise ECommerce) ready, but this will require some funding.

I fixed typos. Thank you.

This is for your reference : https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems

Offline eCommerce Core

  • Administrator
  • Hero Member
  • *****
  • Posts: 1602
  • Karma: +93/-1
    • View Profile
Re: Big data caused memory exhausted
« Reply #27 on: January 12, 2017, 08:19:29 AM »
Thank you for sharing
“If you’re in the luckiest one per cent of humanity, you owe it to the rest of humanity to think about the other 99 per cent.”
― Warren Buffett

 

Powered by SMFPacks Social Login Mod