Showing results for 
Search instead for 
Did you mean: 

Speed issues with large database

Speed issues with large database


Hi All,


Trying very hard to make the move to Magento2 ! 

Getting closer and closer everyday.


The last thing i am struggling with is SPEED !! I understand this is subjective but let me try and explain.


I have a large database, about 38000 simple products that are attached to about 300 configurable product. its multi store, 3 websites with one of those websites with 3 store fronts.


In magento1 i was able to tune the web server and mysql and throw hardware at it and it ran fine with a full page cache module.

From cold cache I was getting 3-4 second page loads, from a warmed cache getting 2.5 seconds, which i was happy with.


The same database migrated over to magneto2 on the same hardware, i am getting 15-30 seconds from cold cache.

Once the cache is warm, its very very fast less that 2 seconds.  When i watch "top" on the linux service i can see that it has 90% CPU and Ram and its not even being used hard.


I can get a basic cache warmer to solve part of that problem, but i feel like thats only a cover up of something much bigger.

Also i can put Varnish in place, but i believe i need to get it running right with no load first before going to Varnish.


Now whats strange the backend admin runs really fast. I have tired with developer mode and production mode


My environment:


8gb ram

8 virtual CPU 


all on 1 server

Apache 2.4


mysql 5.6 (and tried 5.7 as well)


I have also tried turing on and off JS and CSS merging as some people have mentioned that as a solution. 


I find the same even when i run it locally on AMPPS on my mac, which is an i7 with 16gb ram and SSD.

In addition i have tried NGNIX and Litespeed enterprise with PHP 5.6 and 7 to see if there is a difference. No luck there.


How are people debugging this type of issue? I don't think i am under cooking the hardware. Any help would be appreciated





Re: Speed issues with large database

Try to enable and adjust the PHP OPcache. The default settings are not appropriate to Magento 2 because of too large codebase.

Re: Speed issues with large database

Thanks ! I have OPCACHE enabled; how do these settings look?

It seems its only using 50% of the 128mb anyway.

opcache.enable true

opcache.enable_cli false

opcache.use_cwd true

opcache.validate_timestamps true

opcache.inherited_hack true

opcache.dups_fix false

opcache.revalidate_path false

opcache.log_verbosity_level 1

opcache.memory_consumption 128.00 MB

opcache.interned_strings_buffer 8

opcache.max_accelerated_files 4000

opcache.max_wasted_percentage 0.05

opcache.consistency_checks 0

opcache.force_restart_timeout 180

opcache.revalidate_freq 2


opcache.blacklist_filename /etc/php.d/opcache*.blacklist

opcache.max_file_size 0


opcache.protect_memory false

opcache.save_comments true

opcache.fast_shutdown true

opcache.enable_file_override false

opcache.optimization_level 2147467263


opcache.file_cache_only false

opcache.file_cache_consistency_checks true