How MySQL Tuning Can Improve PrestaShop Performance

PrestaShop is a specialized, free, and open-source e-commerce platform developed in PHP, aimed at providing businesses, from startups to large enterprises, with the infrastructure to launch, manage, and scale their online stores.

Offering a rich selection of customizable themes and a comprehensive suite of e-commerce features, PrestaShop supports a wide array of functionalities, such as product management, secure payments, shipping, and inventory management, directly out of the box.

How to Improve Application Performance

Improving application performance with tuning is best achieved with a comprehensive approach that addresses the following areas:

• Server Resources — CPU, Memory, Storage
• Software Configurations — Linux, Nginx, Php…
• Database Management System (DBMS) Configurations — MySQL, PostgreSQL
• Optimize database scheme and change indexes
• Optimize applications — Code, Queries, Architecture…

Many experienced PrestaShop developers don’t look at database performance tuning as an opportunity to improve the performance of their apps because they know little about this domain. They spend a lot of time optimizing the codebase, but it reaches a point where it no longer brings a valuable result for the time and energy invested. Our research on how MySQL tuning positively affects the performance of popular open-source web applications is aimed at showcasing this fact to users.

Testing Setup

The version of PrestaShop being utilized for testing is 8.1.2, which is specifically optimized for use with PHP 8.1. Detailed guidance on installing this version of PrestaShop can be found in the official documentation.

We installed the PrestaShop with the default installation with demo data, export all demo products and import it as a new products multiples times, then we install multiples languages for the e-commerce.

Our test duration was 2 days. To handle this longer testing period, we switched from BlazeMeter (max test duration of 20 minutes) to Locust, an open-source load-testing tool.

We used:
GCP Compute Engine instance c2-standard-4, equipped with Debian Bullseye for the operating system, Apache for the web server, and MariaDB 10.5 in its default configuration with a database size of 1GB.

MySQL Configuration

Tuned Configuration for Prestashop:

[mysqld]
innodb_change_buffering=none
innodb_change_buffer_max_size=25
innodb_adaptive_flushing_lwm=25.000000
innodb_max_dirty_pages_pct=70.000000
innodb_autoextend_increment=48
thread_stack=299008
transaction_prealloc_size=8192
thread_cache_size=172
max_connections=172
query_cache_type=1
query_cache_size=134217728
query_cache_limit=33554432
query_cache_min_res_unit=4096
key_buffer_size=8388608
max_heap_table_size=16777216
tmp_table_size=16777216
innodb_buffer_pool_size=3355443200
innodb_log_file_size=25165824
innodb_file_per_table=1
sort_buffer_size=2097152
read_rnd_buffer_size=262144
bulk_insert_buffer_size=8388608
myisam_sort_buffer_size=8388608
innodb_buffer_pool_chunk_size=134217728
join_buffer_size=8388608
table_open_cache=2048
table_definition_cache=512
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16777216
innodb_write_io_threads=4
innodb_read_io_threads=4
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=0
optimizer_search_depth=0
innodb_purge_threads=4
thread_handling=one-thread-per-connection
thread_pool_size=6

Testing Results

The performance evaluation of PrestaShop revealed significant enhancements post-optimization.

Notably, the response time experienced a remarkable decrease from 610ms to 370ms, marking a 39% improvement. Similarly, the website’s ability to handle requests surged by 50%, from 8 to 12 requests per second.

CPU utilization saw a reduction, moving from full capacity at 100% down to 90%, a 10% decrease in load. Most impressively, MySQL’s query processing capability jumped from 5078 to 7816 queries per second, a substantial increase of 53%.

We recorded screencast to show the impact on Response Time after applying recommended MySQL configuration.

Prestashop Tuned MySQL Configuration vs Default

Graphs of the testing results are available below:

Response Times (ms), Prestashop Tuned MySQL Configuration vs Default

CPU Utilization (%), PrestaShop Tuned MySQL Configuration vs Default

Queries Per Seconds, Prestashop Tuned MySQL Configuration vs Default

Community Contributions

In setting up our test environment, we collaborated closely with Giuseppe Pompeo, CEO of Multiservicios Austral. Giuseppe’s firm is at the forefront of digital services in Chile, particularly known for its expertise in e-commerce through PrestaShop. Their team, recognized and certified by PrestaShop, has been delivering specialized and professional services in this field for over six years.

Giuseppe’s involvement was crucial in getting our server ready, launching our website, and populating it with initial data. His vast experience with cloud infrastructure and a keen focus on optimizing e-commerce platforms like PrestaShop played a significant role in ensuring our tests ran smoothly. We’re immensely thankful for the knowledge and efficiency Giuseppe brought to our project.

Conclusion

Our testing procedure, using PrestaShop, showed improvements in Response Time (Latency), CPU Utilization, and Queries per second after configuring the database server configuration.

Responce Time (Latency) dropped between 39%, while CPU Utilization fell 10%. Queries per second increased PrestaShop by 53%. Requests per Second to Website 50%.

With this research, we hope to showcase the value of MySQL tuning as a means to improve the performance of PrestaShop applications and encourage PrestaShop users to consider this practice when optimizing the performance of their websites.

Using tools like Releem, databases can be quickly and easily configured for optimal performance, reducing the burden on software development teams.

The above is the detailed content of How MySQL Tuning Can Improve PrestaShop Performance. For more information, please follow other related articles on the PHP Chinese website!