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, theresponse timeexperienced a remarkabledecreasefrom 610ms to 370ms, marking a39%improvement. Similarly, the website’s ability to handle requests surged by50%, from 8 to 12 requests per second.

CPU utilizationsaw a reduction, moving from full capacity at 100% down to 90%, a10%decreasein 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.

サーバーの準備、ウェブサイトの立ち上げ、初期データの入力において、ジュゼッペの関与は非常に重要でした。彼のクラウド インフラストラクチャに関する豊富な経験と、PrestaShop のような e コマース プラットフォームの最適化に熱心に取り組んでいることは、テストがスムーズに実行されることを保証する上で重要な役割を果たしました。 Giuseppe が私たちのプロジェクトにもたらしてくれた知識と効率性に非常に感謝しています。

結論

PrestaShop を使用したテスト手順では、データベース サーバー構成を構成した後、応答時間 (レイテンシー)、CPU 使用率、および1 秒あたりのクエリ数の改善が見られました。

応答時間 (レイテンシー)は 39% の間で低下し、CPU 使用率は 10%低下しました。1 秒あたりのクエリ数PrestaShop が 53% 増加しました。ウェブサイトへの 1 秒あたりのリクエスト数が 50% 増加しました。

この調査により、PrestaShop アプリケーションのパフォーマンスを向上させる手段として MySQL チューニングの価値を示し、PrestaShop ユーザーが Web サイトのパフォーマンスを最適化する際にこの実践を検討するよう奨励したいと考えています。Releem のようなツールを使用すると、最適なパフォーマンスを実現するようにデータベースを迅速かつ簡単に構成でき、ソフトウェア開発チームの負担を軽減できます。

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!