{"id":406080,"date":"2024-06-29T18:42:34","date_gmt":"2024-06-29T18:42:34","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=406080"},"modified":"-0001-11-30T00:00:00","modified_gmt":"2024-06-29T18:42:34","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=406080","title":{"rendered":"<span>MySQL 8 Performance Benchmark<\/span>"},"content":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<p>In this article, we benchmark the performance of MySQL 8 default configuration vs. innodb_dedicated_server enabled configuration vs. the configuration recommended by Releem.<\/p>\n<figure class=\"\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/05e\/365\/01e\/05e36501ed847934268475db9b81e844.png\" alt=\"MySQL 8 Performance Benchmark\" title=\"MySQL 8 Performance Benchmark\" width=\"504\" height=\"358\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/05e\/365\/01e\/05e36501ed847934268475db9b81e844.png\"\/><figcaption>MySQL 8 Performance Benchmark<\/figcaption><\/figure>\n<p>In most cases, databases are instrumental to technological setups and their operation, so their performance and overall efficiency are of critical importance. If your database fails to deliver well, the entire application runs poorly, and poor application performance makes clients sour and and quickly turning into former clients.<\/p>\n<p>For MySQL 8 benchmarking purposes, we used SysBench utility and server with the following configuration:<\/p>\n<ul>\n<li>\n<p>Operating System: Debian 10;<\/p>\n<\/li>\n<li>\n<p>CPU: 2 cores;<\/p>\n<\/li>\n<li>\n<p>RAM: 4GB;<\/p>\n<\/li>\n<li>\n<p>MySQL version: 8.0.23<\/p>\n<\/li>\n<\/ul>\n<p>To prepare the database, we ran following command:<\/p>\n<pre><code class=\"bash\">sysbench --db-driver=mysql --mysql-user=root --mysql-password='root' --mysql-db=test --mysql_storage_engine=innodb --table_size=10000000 --tables=2 --threads=2 \/usr\/share\/sysbench\/oltp_read_write.lua prepare<\/code><\/pre>\n<p>After that we ran Releem agent to get the recommended configuration.<\/p>\n<p>We compared performance of MySQL 8 server running under different configurations:<\/p>\n<ul>\n<li>\n<p>default<\/p>\n<\/li>\n<li>\n<p>innodb_dedicated_server enabled<\/p>\n<\/li>\n<li>\n<p>Recommended configuration by Releem<\/p>\n<\/li>\n<\/ul>\n<h3>Default MySQL 8 configuration<\/h3>\n<p>The default configuration allows launching a MySQL server on a virtual machine with approximately 512MB of RAM.<\/p>\n<h3>Innodb_dedicated_server enabled<\/h3>\n<p>MySQL 8.0 introduced\u00a0<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/innodb-dedicated-server.html\" rel=\"noopener noreferrer nofollow\"><u>innodb_dedicated_server<\/u><\/a>. When innodb_dedicated_server is enabled enabled, InnoDB automatically configures the following variables:<\/p>\n<ul>\n<li>\n<p>innodb_buffer_pool_size<\/p>\n<\/li>\n<li>\n<p>innodb_log_file_size<\/p>\n<\/li>\n<li>\n<p>innodb_log_files_in_group<\/p>\n<\/li>\n<li>\n<p>innodb_flush_method<\/p>\n<\/li>\n<\/ul>\n<p>Based on the system\u2019s RAM, InnoDB automatically sizes the following:<\/p>\n<ul>\n<li>\n<p><strong>innodb_buffer_pool_size:<\/strong>Keep in mind that innodb_dedicated_server allocates 80% of physical RAM for InnoDB buffer pool.\u00a0&#8212; Less than 1G: 128M(default value \u2014 if innodb_dedicated_server is off)- Less than or Equal to 4G: Detected Physical RAM * 0.5- Greater than 4G: Detected Physical RAM * 0.75<\/p>\n<\/li>\n<li>\n<p><strong>innodb_log_file_size:<\/strong>For the log record size, InnoDB needs access to one hour of records, which would enable it to enhance composing the retry log to the disk.- Less than 1G: 48M(default value \u2014 if innoDB_dedicated_server is off)- Less than or Equal to 4G: 128M- Less than or Equal to 8G: 512M- Less than or Equal to 16G: 1024M- Greater than 16G: 2G<\/p>\n<\/li>\n<\/ul>\n<h3>Recommended configuration by Releem<\/h3>\n<p>The configuration recommended by Releem that we got after preparing database:<\/p>\n<pre><code>[mysqld]  thread_cache_size = 16 ### Previous value : 9  key_buffer_size = 8388608 ### Previous value : 8388608  max_allowed_packet = 1073741824 ### Previous value : 67108864  sort_buffer_size = 262144 ### Previous value : 262144  read_rnd_buffer_size = 262144 ### Previous value : 262144  bulk_insert_buffer_size = 8M ### Previous value : 8388608  myisam_sort_buffer_size = 8388608 ### Previous value : 8388608  innodb_buffer_pool_instances = 2 ### Previous value : 1  innodb_buffer_pool_size = 3221225472 ### Previous value : 134217728  max_heap_table_size = 256M ### Previous value : 16777216  tmp_table_size = 256M ### Previous value : 16777216  join_buffer_size = 8M ### Previous value : 262144  max_connections = 151 ### Previous value : 151  table_open_cache = 4096 ### Previous value : 4000  table_definition_cache = 2432 ### Previous value : 2000  innodb_flush_log_at_trx_commit = 2 ### Previous value : 1  innodb_log_file_size = 402653184 ### Previous value : 50331648  innodb_write_io_threads = 4 ### Previous value : 4  innodb_read_io_threads = 4 ### Previous value : 4  innodb_file_per_table = 1 ### Previous value : ON  innodb_flush_method = O_DIRECT ### Previous value : fsync  innodb_thread_concurrency = 0 ### Previous value : 0  innodb_buffer_pool_chunk_size = 134217728 ### Previous value : 134217728<\/code><\/pre>\n<h2>MySQL 8 Configurations Benchmarking<\/h2>\n<p>Sometimes you must simulate a hefty database workload to get a concise and clear scenario for benchmarking. Performance can differ depending on various factors, and a gain of a few percentage points may not be perceived as a clear win. Moreover, tested in another environment, the configuration may deliver very different results.<\/p>\n<p>Our benchmarking relied on code from\u00a0<a href=\"https:\/\/github.com\/geekgogie\/mysql57_vs_8-benchmark_scripts\" rel=\"noopener noreferrer nofollow\"><u>github repository<\/u><\/a>.<\/p>\n<p>We ran SysBench tests using different thread counts with the following parameters:<\/p>\n<ul>\n<li>\n<p>table_size=10000000<\/p>\n<\/li>\n<li>\n<p>tables=2<\/p>\n<\/li>\n<li>\n<p>time=60.<\/p>\n<\/li>\n<\/ul>\n<h2>Benchmarking Results<\/h2>\n<p>Following diagrams show results of the tests:<\/p>\n<ol>\n<li>\n<p>Reads and writes counts for three MySQL configurations with two threads<\/p>\n<\/li>\n<\/ol>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/df4\/df2\/182\/df4df21824bdca369caf31238108a00d.png\" alt=\"MySQL 8 Performance Benchmark Reads and Writes count, 2 threads\" title=\"MySQL 8 Performance Benchmark Reads and Writes count, 2 threads\" width=\"838\" height=\"522\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/df4\/df2\/182\/df4df21824bdca369caf31238108a00d.png\"\/><figcaption>MySQL 8 Performance Benchmark Reads and Writes count, 2 threads<\/figcaption><\/figure>\n<p>2. Read operations count for three MySQL configurations depending on threads count.<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/196\/b71\/e2a\/196b71e2a472fffd0c3e487040fcf4f7.png\" alt=\"MySQL 8 Performance Benchmark Writes Count Depending on Threads Count\" title=\"MySQL 8 Performance Benchmark Writes Count Depending on Threads Count\" width=\"838\" height=\"585\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/196\/b71\/e2a\/196b71e2a472fffd0c3e487040fcf4f7.png\"\/><figcaption>MySQL 8 Performance Benchmark Writes Count Depending on Threads Count<\/figcaption><\/figure>\n<p>3. Write operation count for three MySQL configurations depending on threads count.<\/p>\n<figure class=\"full-width\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/1f8\/2a9\/e98\/1f82a9e984f1316e540a8f1e344a69c3.png\" alt=\"MySQL 8 Performance Benchmark Reads Count Depending on Threads Count\" title=\"MySQL 8 Performance Benchmark Reads Count Depending on Threads Count\" width=\"838\" height=\"585\" data-src=\"https:\/\/habrastorage.org\/getpro\/habr\/upload_files\/1f8\/2a9\/e98\/1f82a9e984f1316e540a8f1e344a69c3.png\"\/><figcaption>MySQL 8 Performance Benchmark Reads Count Depending on Threads Count<\/figcaption><\/figure>\n<h2>Conclusion<\/h2>\n<p>The performance benchmark results show that Releem ( <u>MySQL Performance Tuning as a Service<\/u>\u00a0) recommended configuration\u00a0<strong>delivers a 64% boost<\/strong>\u00a0compared to the default configuration and a 15% boost over what was achieved with innodb_dedicated_server enabled.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<p><!----><!----><\/div>\n<p><!----><!----><br \/> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/articles\/555222\/\"> https:\/\/habr.com\/ru\/articles\/555222\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<p>In this article, we benchmark the performance of MySQL 8 default configuration vs. innodb_dedicated_server enabled configuration vs. the configuration recommended by Releem.<\/p>\n<figure class=\"\"><figcaption>MySQL 8 Performance Benchmark<\/figcaption><\/figure>\n<p>In most cases, databases are instrumental to technological setups and their operation, so their performance and overall efficiency are of critical importance. If your database fails to deliver well, the entire application runs poorly, and poor application performance makes clients sour and and quickly turning into former clients.<\/p>\n<p>For MySQL 8 benchmarking purposes, we used SysBench utility and server with the following configuration:<\/p>\n<ul>\n<li>\n<p>Operating System: Debian 10;<\/p>\n<\/li>\n<li>\n<p>CPU: 2 cores;<\/p>\n<\/li>\n<li>\n<p>RAM: 4GB;<\/p>\n<\/li>\n<li>\n<p>MySQL version: 8.0.23<\/p>\n<\/li>\n<\/ul>\n<p>To prepare the database, we ran following command:<\/p>\n<pre><code class=\"bash\">sysbench --db-driver=mysql --mysql-user=root --mysql-password='root' --mysql-db=test --mysql_storage_engine=innodb --table_size=10000000 --tables=2 --threads=2 \/usr\/share\/sysbench\/oltp_read_write.lua prepare<\/code><\/pre>\n<p>After that we ran Releem agent to get the recommended configuration.<\/p>\n<p>We compared performance of MySQL 8 server running under different configurations:<\/p>\n<ul>\n<li>\n<p>default<\/p>\n<\/li>\n<li>\n<p>innodb_dedicated_server enabled<\/p>\n<\/li>\n<li>\n<p>Recommended configuration by Releem<\/p>\n<\/li>\n<\/ul>\n<h3>Default MySQL 8 configuration<\/h3>\n<p>The default configuration allows launching a MySQL server on a virtual machine with approximately 512MB of RAM.<\/p>\n<h3>Innodb_dedicated_server enabled<\/h3>\n<p>MySQL 8.0 introduced\u00a0<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/innodb-dedicated-server.html\" rel=\"noopener noreferrer nofollow\"><u>innodb_dedicated_server<\/u><\/a>. When innodb_dedicated_server is enabled enabled, InnoDB automatically configures the following variables:<\/p>\n<ul>\n<li>\n<p>innodb_buffer_pool_size<\/p>\n<\/li>\n<li>\n<p>innodb_log_file_size<\/p>\n<\/li>\n<li>\n<p>innodb_log_files_in_group<\/p>\n<\/li>\n<li>\n<p>innodb_flush_method<\/p>\n<\/li>\n<\/ul>\n<p>Based on the system\u2019s RAM, InnoDB automatically sizes the following:<\/p>\n<ul>\n<li>\n<p><strong>innodb_buffer_pool_size:<\/strong>Keep in mind that innodb_dedicated_server allocates 80% of physical RAM for InnoDB buffer pool.\u00a0&#8212; Less than 1G: 128M(default value \u2014 if innodb_dedicated_server is off)- Less than or Equal to 4G: Detected Physical RAM * 0.5- Greater than 4G: Detected Physical RAM * 0.75<\/p>\n<\/li>\n<li>\n<p><strong>innodb_log_file_size:<\/strong>For the log record size, InnoDB needs access to one hour of records, which would enable it to enhance composing the retry log to the disk.- Less than 1G: 48M(default value \u2014 if innoDB_dedicated_server is off)- Less than or Equal to 4G: 128M- Less than or Equal to 8G: 512M- Less than or Equal to 16G: 1024M- Greater than 16G: 2G<\/p>\n<\/li>\n<\/ul>\n<h3>Recommended configuration by Releem<\/h3>\n<p>The configuration recommended by Releem that we got after preparing database:<\/p>\n<pre><code>[mysqld]  thread_cache_size = 16 ### Previous value : 9  key_buffer_size = 8388608 ### Previous value : 8388608  max_allowed_packet = 1073741824 ### Previous value : 67108864  sort_buffer_size = 262144 ### Previous value : 262144  read_rnd_buffer_size = 262144 ### Previous value : 262144  bulk_insert_buffer_size = 8M ### Previous value : 8388608  myisam_sort_buffer_size = 8388608 ### Previous value : 8388608  innodb_buffer_pool_instances = 2 ### Previous value : 1  innodb_buffer_pool_size = 3221225472 ### Previous value : 134217728  max_heap_table_size = 256M ### Previous value : 16777216  tmp_table_size = 256M ### Previous value : 16777216  join_buffer_size = 8M ### Previous value : 262144  max_connections = 151 ### Previous value : 151  table_open_cache = 4096 ### Previous value : 4000  table_definition_cache = 2432 ### Previous value : 2000  innodb_flush_log_at_trx_commit = 2 ### Previous value : 1  innodb_log_file_size = 402653184 ### Previous value : 50331648  innodb_write_io_threads = 4 ### Previous value : 4  innodb_read_io_threads = 4 ### Previous value : 4  innodb_file_per_table = 1 ### Previous value : ON  innodb_flush_method = O_DIRECT ### Previous value : fsync  innodb_thread_concurrency = 0 ### Previous value : 0  innodb_buffer_pool_chunk_size = 134217728 ### Previous value : 134217728<\/code><\/pre>\n<h2>MySQL 8 Configurations Benchmarking<\/h2>\n<p>Sometimes you must simulate a hefty database workload to get a concise and clear scenario for benchmarking. Performance can differ depending on various factors, and a gain of a few percentage points may not be perceived as a clear win. Moreover, tested in another environment, the configuration may deliver very different results.<\/p>\n<p>Our benchmarking relied on code from\u00a0<a href=\"https:\/\/github.com\/geekgogie\/mysql57_vs_8-benchmark_scripts\" rel=\"noopener noreferrer nofollow\"><u>github repository<\/u><\/a>.<\/p>\n<p>We ran SysBench tests using different thread counts with the following parameters:<\/p>\n<ul>\n<li>\n<p>table_size=10000000<\/p>\n<\/li>\n<li>\n<p>tables=2<\/p>\n<\/li>\n<li>\n<p>time=60.<\/p>\n<\/li>\n<\/ul>\n<h2>Benchmarking Results<\/h2>\n<p>Following diagrams show results of the tests:<\/p>\n<ol>\n<li>\n<p>Reads and writes counts for three MySQL configurations with two threads<\/p>\n<\/li>\n<\/ol>\n<figure class=\"full-width\"><figcaption>MySQL 8 Performance Benchmark Reads and Writes count, 2 threads<\/figcaption><\/figure>\n<p>2. Read operations count for three MySQL configurations depending on threads count.<\/p>\n<figure class=\"full-width\"><figcaption>MySQL 8 Performance Benchmark Writes Count Depending on Threads Count<\/figcaption><\/figure>\n<p>3. Write operation count for three MySQL configurations depending on threads count.<\/p>\n<figure class=\"full-width\"><figcaption>MySQL 8 Performance Benchmark Reads Count Depending on Threads Count<\/figcaption><\/figure>\n<h2>Conclusion<\/h2>\n<p>The performance benchmark results show that Releem ( <u>MySQL Performance Tuning as a Service<\/u>\u00a0) recommended configuration\u00a0<strong>delivers a 64% boost<\/strong>\u00a0compared to the default configuration and a 15% boost over what was achieved with innodb_dedicated_server enabled.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<p><!----><!----><\/div>\n<p><!----><!----><br \/> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/articles\/555222\/\"> https:\/\/habr.com\/ru\/articles\/555222\/<\/a><br \/><\/br><\/br><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-406080","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/406080","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=406080"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/406080\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=406080"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=406080"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=406080"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}