Does your site ever get high traffic spikes that start to bring the site down? If so, you might be a good candidate for running a database slave. PrestaShop natively supports running slave databases, using them can take the load off of your main database and improve performance for users on your site. As a note, slave databases are best used with servers that are already taking advantage of other optimizations such as Opcode caching and PrestaShop caching modules.
Setting up a slave
The first thing you will need is root access to your server. If you are on a shared sever this more than likely is not going to happen. The same goes if you are on a managed VPS as well. If you are on a managed VPS though, your host should be able to set up the replication from one database though. Then you would have to set up the slave server manually. Using the salve database is better set up for users using cloud servers such as Digital Ocean, Amazon, Or Google instances. That way you can pick a server that is geographically close to your main server, because internet latency can be an issue with using a slave. We would not suggest replicating across regions, unless you are load balancing with multiple front end servers.
There are many tutorials online on how to set up the master slave connection between the databases, or you can get your system administrator to set it up for you as well. For that reason we are not going very in depth on how to set them up on the MYSQL level. We will focus more on the PrestaShop aspect of setting the database up.
Making PrestaShop use the slave
Once you have the master and slave databases setup, you will need to edit a file in your PrestaShop installation. In the /config directory there is a file called db_slave_server.inc.php, that is the file you will need to edit to bring the slave server online. The file contents look like this.
To set up the slave database(s) what you would need to do is uncomment the array and add the information for your database in there. Notice how they use IP addresses, this is the correct way to access the database, if you use a web address you will put in an unneeded DNS lookup which will slow the connection down considerably. Using the built in system you can use as many slave databases as you want, I would not recommend using over 4 though, at that point you might need more than one master database.
If your site’s performance is suffering and you think you might be a good candidate for using slave databases contact us and we can evaluate your site and let you know if we think you would be a good fit for using one. There are a lot of variables that go into determining if a site is a good fit and we know what questions to ask and what to check.
About the Author: Lesley Paone
Lesley has worked in e-commerce for over a decade, and is the founder of dh42. Starting out with PrestaShop and brancing out into other platforms like Shopify. He loves all things e-commerce and loves a challenge, in his spare time he helps moderate several forums on SEO, e-commerce, as well as the PrestaShop forum. If you have any questions for him about any of his articles just use our contact form to contact him.