Home >Database >Mysql Tutorial >How MySQL Tuning Improves the Laravel Performance
MySQL Configuration tuning is an important component of database management implemented by database professionals and administrators. It aims to configure the database to suit its hardware and workload. But beyond the database management sphere, the usefulness of MySQL Configuration tuning is largely ignored.
We hypothesize that MySQL tuning can significantly affect the performance of web apps. If we can showcase the value of MySQL tuning, we believe that enterprises and organizations may be keen to incorporate this practice on a larger scale.
Improving application performance with tuning is best achieved with a comprehensive approach that addresses the following areas:
Many experienced 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 developers.
Our testing procedure for Laravel Aimeos lets us compare the app's performance before and after configuration using seeded data. By running the test with the default configuration first, we gain valuable control results to compare the tuned configuration against.
We used the following process to prepare and test each application:
We published JMeter tests, MySQL Status, and MySQL Variables during tests on Github.
The metrics we looked at during this research are:
We collected CPU Utilization and Queries per second metrics to compare the workload.
Aimeos Laravel is a popular e-commerce web app framework for creating online shops, marketplaces, and B2B apps. With Aimeos, users can create API-first eCommerce shops for Laravel that can scale to support over 1 billion items. It's available in over 30 languages and has over 300,000 installs.
To test Aimeos, we started the test with ten users, but we had to decrease the number of users because we couldn't finish the test with the default configuration.
We seeded the database with 500 Mb data.
Our test duration was 10 minutes.
We used:
The configuration used for Aimeos Laravel is as follows:
query_cache_type=1 query_cache_size=134217728 query_cache_limit=16777216 query_cache_min_res_unit=4096 thread_cache_size=0 key_buffer_size=8388608 max_allowed_packet=1073741824 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 innodb_buffer_pool_size=805306368 max_heap_table_size=16777216 tmp_table_size=16777216 join_buffer_size=8388608 max_connections=151 table_open_cache=2048 table_definition_cache=1408 innodb_flush_log_at_trx_commit=1 innodb_log_file_size=201326592 innodb_log_buffer_size=16777216 innodb_write_io_threads=4 innodb_read_io_threads=4 innodb_file_per_table=1 innodb_flush_method=O_DIRECT innodb_thread_concurrency=0 innodb_purge_threads=4 optimizer_search_depth=0 thread_handling=pool-of-threads thread_pool_size=2
The Aimeos Laravel testing results showcased dramatic performance improvements between the default and tuned configurations.
The optimization of MySQL resulted in a significant improvement in the average server Response Time, which was reduced from 1.4 seconds to under 800 milliseconds.
Response Time ( Latency ) fell by 42% and average CPU utilization by 86%, while Queries per second increased by an incredible 291%, from 12 to 35 queries per second.
The graph of the results is available below:
Response Time (ms), Aimeos Tuned MySQL Configuration vs Default
CPU Utilization (%), Aimeos Tuned MySQL Configuration vs Default
Queries Per Seconds, Aimeos Tuned MySQL Configuration vs Default
We teamed up with Laravel developers Gevorg Mkrtchyan and Sergey Sinitsa from Initlab company to investigate this line of questioning and are very grateful for their expertise.
Sergey deployed Aimeos, and Gevorg prepared code for seeding the database.
Our testing procedure, using Aimeos Laravel, showed dramatic improvements in Response Time (Latency), CPU Utilization, and Queries per second after configuring the database server configuration.
Responce Time (Latency) dropped between 42%, while CPU Utilization fell 86%. Queries per second increased in Aimeos Laravel 500MB by 291%.
In conclusion, MySQL tuning is an essential aspect of database management that can have a significant impact on the performance of Laravel applications. Poorly performing web applications can lead to increased page load times, slow request handling, and a poor user experience, which can negatively affect SEO and sales. By optimizing the performance of web apps with MySQL tuning, enterprises and organizations can increase sales, pageviews, conversion rates, and SEO rankings.
With this research, we hope to showcase the value of MySQL tuning as a means to improve the performance of Laravel applications and encourage Laravel developers to consider this practice when optimizing the performance of their apps.
Using tools like Releem, databases can be automatically configured for optimal performance, reducing the burden on software development teams.
The above is the detailed content of How MySQL Tuning Improves the Laravel Performance. For more information, please follow other related articles on the PHP Chinese website!