Need help with extreme slow performance on large product site

[jack310]

Dear friends,

we are running:
Site: 1
Store: 4
category: 50,000+
products: 6 million+

we are running into problem with very slow database, search, promotion setting, basically everything is extremely slow, is there any suggestion I can do to help my website?

We are running community version 1.9.2.2 currently. At current stage seems Magento isn't handling this large amount of category and products smoothly, product indexing usually runs so many hours that nothing can be done at all. Please, if you have suggestion on improving the situation please kindly let me know!

Thank you very very much!

Jack

[Finestshops]

Hi Jack,

I hope you are not running 6 million+ products on a shared hosting but are you using a separate database server? We set all our larger clients on multiple servers - at least one database server running Percona SQL database (https://www.percona.com/software/mysql-database/percona-server) and one or more nginx/varnish/php7 web servers with a load balancer. 

If your traffic is not too heavy, you can start with only one web server but I would still go with a separate database server and go big - like 40-60Gb RAM and 6-12 CPUs - servers are not very expensive nowadays. And your database server has to be configured and optimized properly to handle that many records. 

hope this helps. 

[AHLeslie]

Hello,

I agree with Finestshops entirely. 6m+ products means your database is quite intensive and you will need some very beefy hardware to keep it running smoothly.

If you can let us know some information on your current hosting setup we may be able to assist further.

[jack310]

Hi,

Thank for the reply, we are currently running with Amazon AWS C4.4xlarge service which has:

vCPU x 16
Mem 30GB

https://aws.amazon.com/ec2/instance-types/?nc1=h_ls

Do you recommend we run other type of service? Yes the product is currently 5.8 millions and growing, we pull product data from Yahoo and Rakuten websites and list them on our website plus our own listings.

Thank you very much!

Jack

[jack310]

Forgot to list the dbase server spec:

Amazon RDS Instance db.m4.4xlarge

vCPU: 16

mem: 64GB

Thanks!

[Finestshops]

We use Rackspace Cloud not AWS but from the description of the instances, it looks like your database server is on m4.4xlarge which is a General Purpose server. For a database server, better choice will be M3 (with SSD drives) or even X1, R3 or R4 which are Memory Optimized servers specifically designed for the databases.

In addition, a database server has to be properly optimized for the maximum performance. And I would switch to Percona database server instead of MySQL. Percona is an enhanced drop-in MySQL replacement so not changes will be required in the code to use it. We switched all high traffic clients to Percona - stores are much more stable on it.

If this will not help, the next step is to try Rackspace database:
https://www.rackspace.com/cloud/databases
We are setting up a similar store for a client right now who will have 3-5 million products and will be using Percona database set as Rackspace cloud database instance. It's in development now so I can not tell you how well it works but this is the best you can do without getting into $30k-50k/mo setups.
But those are still expensive beasts - from $1,000 to $10,000/mo for 64gb High-Availability Group (multi-instance db cluster).

[TutorialMagento]

I think the solution to this should be a separate remote server for running only your Database.

[jack310]

Hi,

Database is running off separate database server:

Amazon RDS Instance db.m4.4xlarge

vCPU: 16

mem: 64GB

[Robert_Hoffmann]

The first time I try to put a ask on the forum and I think this is not the place. But I cannot find a place to put in the question. I have 3 shops Magento Version 1.9.2.4. One shop is realy fast! www.modellbauprofishop.ch This shop have 13000 products The other two shops are very slow. Loading time 30 seconds are possible. www.hoffmannuhrenshop.ch Testshop for webshop is the second www.swiss-watch-shop.ch I all 2 weeks delete the cach in the var folder and I start to delete the log file and this is raly big. Nothing helps to make the shops faster. www.swiss-watch-shop.ch and www.hoffmannuhrenshop.ch Have anybody an Idee what is to do? I Up to now make copys of shops for tests. I am not an informatiker. Best regards Robert robert.hoffmann@gmx.ch

[Finestshops]

Hello Robert,

If I were you, I would start with finding out what is different in one store. It will give you some ideas on how to fix the other two. Do you run all 3 stores from the same server?
Are those separate Magento installations or you run one installation in multi-store mode?

Looking at the speed test, this is definitely a server configuration problem:
https://www.webpagetest.org/result/170212_0Y_JXM/

https://www.webpagetest.org/result/170212_YA_JY1/
this is the same problem in both stores.

The best people who can help you here is your hosting company because they know your configuration and can adjust/upgrade it for you. If they can not help you - change to a better hosting company who understands Magento and can optimize your server and stores to run fast.