본문 바로가기

컴퓨터/노트북/인터넷

IT 컴퓨터 기기를 좋아하는 사람들의 모임방

쓰기
🕰️ 2023.01.09 10:52
Best Practices for MySQL(mariadb) with SSD, NVME
막내둥이
조회 수 43 추천 수 0 댓글 0

단축키

Prev이전 문서

Next다음 문서

수정 삭제

단축키

Prev이전 문서

Next다음 문서

수정 삭제
Extra Form

Best Practices for MySQL with SSDs  Page 1  Best Practices for MySQL with SSDs A whitepaper by: Vijay Balakrishnan  Changho Choi  Veronica Lagrange  Hubbert Smith  Samsung Semiconductor, Inc. Best Practices for MySQL with SSDs  Page 2  Table of Contents 1.  Introduction ....................................................................................................................................... 3  2.  Hardware Configurations .................................................................................................................. 3  3.  Optimizing MySQL Server and Percona Server for SSDs ................................................................... 5  4.  Flush Method and Buffer Pool ........................................................................................................... 6  5.  Impact of Latency .............................................................................................................................. 6  6.  Optimization Guidelines .................................................................................................................... 7  7.  Performance Results Comparison ..................................................................................................... 8  8.  tpcc‐mysql System Resource Utilization.......................................................................................... 11  9.  Conclusions ...................................................................................................................................... 12  10.  Appendix A: MySQL and Percona Configurations ........................................................................... 12     Best Practices for MySQL with SSDs  Page 3  1. Introduction Imagine a world where networking speeds became mired  at 1MB/s, stayed “stuck” at that  speed for 20 years and then became “unstuck,”  suddenly jumping to 1GB/s (1000X).  For  storage, something similar has indeed happened with the transition from Hard Disk Drives  (HDDs) to Solid State Devices (SSDs).  In the past, storage administrators sized systems by  estimating the IOPS needed, then buying a quantity of 15K rpm HDDs to provide those IOPS.  Often, this resulted in buying more HDD capacity than was necessary.  Now, storage  administrators typically size a system based on the needed capacity, then buy a quantity of SSDs  to provide that capacity, and are pleasantly surprised with the performance that comes along  with the capacity. This paper underscores that all SSDs are not alike and that specific  configurations and settings need to be considered to maximize performance and system value.  In the software domain, MySQL Server is a commonly used Relational Database Management  System (RDBMS).This whitepaper clarifies how today's data centers combine SSDs and MySQL to  achieve a substantial business advantage. The paper focuses on Percona Server – a free, fully  compatible, open source MySQL Server enhancement. Because Percona Server is optimized for  the I/O subsystem, it was selected for the experiments herein described, with the best known  methods reported on the following pages.  About the Benchmark: TPC‐C and tpcc‐mysql TPC‐C is an OLTP industry standard benchmark developed and maintained by the Transaction  Processing Performance Council (TPC) [www.tpc.org]. It is widely used and well understood,  which enables database administrators to quickly correlate quoted TPC‐C results to their specific  application. TPC‐C simulates a wholesale supplier and is centered on processing orders. It is a  mixture of read‐only and update‐intensive transactions that represent complex OLTP application  activities. The benchmark implements five types of transactions (new order, payment, delivery,  order status and stock level), and reports performance by measuring the number of new orders  processed per minute (tpmC). It contains strict guidelines that must be observed in any official  implementation. Furthermore, in spite of its complexity, TPC‐C is easily scaled up or down to fit  the system under test.  Tpcc‐mysql is Percona’s TPC‐C implementation. It is written in C and follows Revision 5.11 of the  MySQL standard specification.  We tested with tpcc‐mysql “out‐of‐the box” code, downloaded  from the Percona GitHub site.   2. Hardware Configurations In the past, OLTP systems were frequently bottlenecked by IO, meaning CPUs were constantly  waiting on HDDs to respond.  But when we replace HDDs with NVMe SSDs on a Dual Socket Best Practices for MySQL with SSDs  Page 4  Server, the CPU becomes the bottleneck. In an attempt to minimize the CPU bottleneck,  we  tested two types of database servers: Dual Socket (12 core) and Quad socket (28 core).  Dual‐socket (12 Core) configuration  Database Server  (Client) Load Generation Server  CPU  Model Name  Intel(R) Xeon(R) CPU E5‐2670 v3 @  2.30GHz  Intel(R) Xeon(R) CPU E5‐2640 v3 @ 2.60GHz  Processors  12  8  Cores  24  32  Memory  64GB  128GB  OS version  Linux 4.4.0‐040400‐generic   Linux 3.19.0‐14‐generic  MySQL Server  5.7.11  Percona Server  5.7.10‐3  Storage        SAS HDD  2x SEAGATE  ST600MP0005 15K  rpm    SATA SSD  2x Samsung 850 PRO     NVMe SSD  2x Samsung XS1715         Quad‐socket (28 Core) Configuration     Database Server  (Client) Load Generation Server  CPU     Model Name  Intel(R) Xeon(R) CPU E7‐4850 v3 @ 2.20GHz  Intel(R) Xeon(R) CPU E5‐2640 v3 @ 2.60GHz  Processors  28  8  Cores  112  32  Memory   124GB   126GB  OS version  Linux 4.4.0‐040400‐generic   Linux 3.19.0‐14‐generic  Percona Server  5.7.11‐4     Storage        SAS HDD  2x SEAGATE  ST600MP0005 15K rpm    SATA SSD  2x Samsung 850 Pro     SAS SSD  2x Samsung PM1633  NVMe  2x Samsung PM1725     It is generally accepted that OLTP database applications have been I/O bound. They typically  have not exhausted CPU capacity. While this is true for HDDs, we see a paradigm shift with  NVMe SSDs.  Best Practices for MySQL with SSDs  Page 5  With HDDs, CPUs are always less than 1% busy, while with NVMe SSDs CPU utilization goes up  to more than 30% with 100+ connections. This 30 times CPU increase translates into 110+ times  more transactions per minute (tpmP), making the case for better server utilization when using  NVMe SSDs.  Mean CPU % utilization (User+Sys) on the Quad Socked Server  Mean CPU %  15K rpm  SAS‐HDD  SATA SSD  SAS SSD  NVMe SSD  50 connections  0.4%  5.8%  15.9%  22.3%  100 connections  0.4%  13.4%  23.3%  32.1%  150 connections  0.5%  15.0%  26.4%  28.9%  200 connections  N A  16.4%  27.2%  33.8%  This report presents current results for four storage devices: PM1725 (NVMe SSD), PM1633 (SAS  SSD), 850Pro (SATA SSD), and a Seagate 15Krpm HDD (HDD). We show how faster SSD storage is  revolutionizing typical OLTP applications that have been traditionally I/O bound, thereby  increasing throughput by orders of magnitude, measured with tpcc‐mysql benchmark. 3. Optimizing MySQL Server and Percona Server for SSDs We observed that the NVMe SSDs performed either 200% or 700% better than SATA SSDs,  depending on the selected configuration. With that in mind, we then proceeded to determine  the best performance level for each device. In addition to adjusting MySQL configuration  parameters, we tried increasing and decreasing the database sizei , as well as partitioning  (sharding) the database across multiple storage volumes.   MySQL Server throughput (tpmP1 ) for 200 and 250 connections ‐ SATA versus NVMe on the Dual Socket Server  1,000‐wh23; 200‐connections  SATA SSD  NVMe SSD  pct diff  Config #1 – MySQL baseline        7,366.55      24,440.57  232%  Config #2 – sub‐optimal config        4,478.28      37,802.13  744%  pct diff  ‐39%  55%     1,000‐wh; 250‐connections  SATA  NVMe  pct diff  Config #1 – MySQL baseline        6,668.33      23,175.19  248%  Config #2‐ sub‐optimal config        4,457.47      33,857.05  660%  pct diff  ‐33%  46%                                                                  1  tpmP= New Order transactions per minute  2  TPC‐C database size is defined by number of warehouses (wh). All tables are scaled up or down based on the  number of warehouses defined. A 10‐wh database occupies roughly 1GB of disk space. Best Practices for MySQL with SSDs  Page 6  BKM  Use SSDs instead of HDDs; there is no viable reason to use HDDs.  First preference: NVMe SSD  Second preference: SAS SSD  4. Flush Method and Buffer Pool BKM  Switch the flush_method to ‘O_DIRECT’  MySQL uses its buffer pool as disk cache, rather than using Linux filesystem as buffer  space.  BKM  Increase the buffer pool from 3GB to 12GB  Larger buffer pool improves performance for all storage types.  After considerable experimentation and analysis, we switched the flush_method to ‘O_DIRECT’,  and then stopped using Linux’s filesystem buffer space and instead began using MySQL buffer  pool as the disk cache.  Configuration #2 (sub‐optimal, see appendix A) has 3GB of buffer pool,  and therefore very little buffer space to be spared as disk cache.   In Configuration #3 (MySQL Optimal, see appendix A) we increased the buffer pool to 12GB,  which benefitted all three storage types.   The NA for 15K rpm SAS‐HDD in Configuration #2 (sub‐optimal) indicates we cannot run tpcc‐ mysql with that setup (we get “lock wait timeout” error messages, meaning that the system is  not able to complete transactions within the expected time). When we move to Configuration  #3 (MySQL optimal), we get a significant boost for all devices, especially for 15K rpm SAS‐HDD,  which is now able to complete the test without errors.  5. Impact of Latency Think of latency as a traffic signal on a popular roadway. If the roadway has few cars, it’s an  inconvenience. If the roadway has too many cars, traffic backs up which can be a disaster; this is  true in I/O.  This is when data center managers can really appreciate the benefits of faster  storage devices. When everything else is the same, most users connected to an “NVMe system”  will see their transactions completed in less than 90 milliseconds – two orders of magnitude  faster than the 5,000+ milliseconds users connected to the “HDD system” experience. Best Practices for MySQL with SSDs  Page 7  MySQL Server 95th percentile response times for Config #3 (dual socket)  MySQL Server maximum response times for Config #3 (dual socket) In the case of SAS‐HDD with 150 connections, the I/O traffic backs up and the system becomes  unresponsive. If this happens in an actual market environment, anyone using the database  (bank tellers, cash registers, RFID checkers) will see their transactions cease to operate and their  business grind to a halt.  6. Optimization Guidelines BKM  innodb_thread_concurrency. Different values were tried; the best performance came with  the default 0 (unlimited thread_concurrency).  50 c 100 c 150 c sas‐hdd 6,329.74 5,288.26 sata 1,066.15 1,188.02 1,191.95 nvme 36.86 54.22 85.61  ‐  1,000.00  2,000.00  3,000.00  4,000.00  5,000.00  6,000.00  7,000.00 milliseconds 95th percentile Response Time ‐ New Order 50 c 100 c 150 c sas‐hdd 20,300.16 49,452.06 sata 4,699.63 15,759.15 37,367.16 nvme 1,716.82 1,242.70 1,932.78  ‐  10,000.00  20,000.00  30,000.00  40,000.00  50,000.00  60,000.00 milliseconds Maximum Response Time ‐ New OrderBest Practices for MySQL with SSDs  Page 8  BKM  innodb_adaptive_hash_index. Turned ‘OFF’, since OLTP workloads typically do not reuse  data from previous queries.  BKM  innodb_fill_factor. Indicates the percentage of space on each B‐tree page that is filled  during a sorted index build. 50 works best for workloads with lots of INSERTs.  BKM  Separate log_dir and datadir. All storage types benefit from this. For both Percona and  MySQL Server, it means setting up the parameters from Appendix A marked with either  <data storage> or <log storage> to the appropriate storage location.  7. Performance Results Comparison Here throughput results for all storage types were reviewed. Observe that the SATA SSD result is  32+ times better than that of the HDD, and that NVMe SSD throughput is two to five times  better than SATA, and up to 51% better than SAS SSD.  Throughput in tpmP – Quad Socket  Connec tions SAS‐ HDD  SATA  SSD  SAS  SSD  NVMe  SSD  SAS_HDD  ‐> SATA  SAS_HDD ‐>  SAS‐SSD  SAS_HDD ‐ > NVMe  SATA  ‐ > SAS  SATA ‐>  NVMe  SAS‐>  NVMe  50  742  23,868  73,236  110,357  3117%  9770%  14773%  207%  362%  51%  100  757  55,378  94,702  136,479  7218%  12415%  17935%  71%  146%  44%  150  1,090  58,123  96,691  138,748  5234%  8773%  12632%  66%  139%  43%  200  N A  58,276  97,796  139,158           68%  139%  42%  The best tpcc‐mysql throughput, 139K tpmP, is obtained using NVMe SSDs running with 200  connections. The 100 connection result, 136K tpmP, is 180+ times better than HDD. Next figure  shows New Order transactions executed over time for all four storage types for the 100‐ connection case.     Best Practices for MySQL with SSDs  Page 9  New Order transactions over time (Higher is better)  New Order 95th percentile response times (Lower is better)  Notice for 15K SAS‐HDD 200c, the result took too long to obtain (failed test). In an actual  market environment, this represents a non‐responsive system impacting business  operations.      0 50000 100000 150000 200000 60 540 1020 1500 1980 2460 2940 3420 3900 4380 4860 5340 5820 6300 6780 Time interval NewOrder Transactions per Minute ‐ 100  Connections nvme SAS sata sas‐hdd 50 c 100 c 150 c 200 c sas‐hdd 4,182 8,572 10,847 sata 100 142 181 239 SAS 57 68 128 145 nvme 26 32 33 34  ‐  2,000  4,000  6,000  8,000  10,000  12,000 milliseconds Connections NewOrder 95th Percentile Response TimesBest Practices for MySQL with SSDs  Page 10  New Order maximum response times (Lower is better)  The 95th percentile response times for New Order transactions over the duration of each  measurement.  SATA SSD and SAS SSD latencies (Lower is better)  SAS SSDs demonstrate significantly lower latencies than SATA SSD for this OLTP  workload.  50 c 100 c 150 c 200 c sas‐hdd 13,884 42,896 76,668 sata 592 899 1,243 1,483 SAS 286 454 1,148 1,759 nvme 219 343 401 528  ‐  10,000  20,000  30,000  40,000  50,000  60,000  70,000  80,000  90,000 milliseconds Connections NewOrder Maximum Response Times 0 50 100 150 200 120 480 840 1200 1560 1920 2280 2640 3000 3360 3720 4080 4440 4800 5160 5520 5880 6240 6600 6960 milliseconds time interval NewOrder 95th Percentile Response Time ‐ 100 connections sata SASBest Practices for MySQL with SSDs  Page 11  SAS and NVMe latencies (Lower is better)  NVMe SSDs demonstrate significantly lower latencies than SAS SSDs for this OLTP workload.  8. tpcc‐mysql System Resource Utilization All Disk Writes 100 connections (Higher is better)  0 10 20 30 40 50 60 70 80 90 120 480 840 1200 1560 1920 2280 2640 3000 3360 3720 4080 4440 4800 5160 5520 5880 6240 6600 6960 milliseconds time interval NewOrder 95th Percentile Response Time ‐ 100 Connections SAS nvmeBest Practices for MySQL with SSDs  Page 12  All Disk Reads 100 connections (Higher is better)  9. Conclusions Not all SSDs are the same but all SSDs perform substantially better than HDDs.  NVMe SSDs can  be up to five times more performant than SATA SSDs, as tested with Percona.  SAS‐SSD can be  up to 125 more performant than SAS‐HDD. NVMe SSDs are 180 times more performant than  15K rpm HDDs.  10. Appendix A: MySQL and Percona Configurations This appendix contains the many configuration settings for both MySQL Server and Percona Server  discussed in this report.  MySQL Server and Percona Server configurations  Parameter Name  Config #1 MySQL Initial  Config #2 MySQL  Suboptimal  Config #3  MySQL  Optimal  Config #4 Percona  Optimal  datadir  /<data  storage>/mysql_data/mysql     tmpdir    /tmp  /<log  storage>/mysql_log  lc‐messages‐dir   /usr/share/mysql    explicit_defaults_for_timestamp       innodb_log_group_home_dir   /<log storage>/mysql_log   Best Practices for MySQL with SSDs  Page 13  innodb_undo_directory   /<log storage>/mysql_log    innodb_buffer_pool_size   3GB  12GB  innodb_thread_concurrency  0 innodb_temp_data_file_path   '../../../<log  storage>/mysql_log/ibtmp1:72 M:autoextend'  innodb_page_cleaners 32    8 innodb_buffer_pool_instances 32    8 innodb_io_capacity 300000    15,000  innodb_io_capacity_max  600000    25,000 innodb_adaptive_hash_index OFF'     0 innodb_fill_factor 50    100 innodb_write_io_threads 16 innodb_read_io_threads 16 innodb_flush_method <empty>  O_DIRECT O_DIRECT  O_DIRECT innodb_flush_neighbors 1    0 query_cache_size 0 log_timestamps  'SYSTEM'  table_open_cache 8000 table_open_cache_instances 16    64 back_log 1500 max_connections 4000 innodb_use_native_io  ON (default)    OFF / ON ## To be able to use many  connections in TPC‐C:    max_prepared_stmt_count 64000 # files    innodb_log_files_in_group 3 innodb_log_file_size 48MB  1G 1G  10G innodb_open_files 4000    # tune =     innodb_checksum_algorithm NONE  crc32 innodb_max_dirty_pages_pct 90    innodb_max_dirty_pages_pct_lwm 10    innodb_lru_scan_depth 4000 8192 join_buffer_size 32K     sort_buffer_size 32K     innodb_spin_wait_delay 96 6 # perf special       innodb_flush_neighbors 0   Best Practices for MySQL with SSDs  Page 14  innodb_max_purge_lag_delay 30000000 0 # Monitoring       innodb_monitor_enable  '%'  <empty> performance_schema OFF     ..oo0oo..                                                               i  TPC‐C database size is defined by number of warehouses (wh). All tables are scaled up or down based on the  number of warehouses defined. A 10‐wh database occupies roughly 1GB of disk space. 

 

best-practices-for-mysql-with-ssds_2.jpg

 

best-practices-for-mysql-with-ssds_3.jpg

 

best-practices-for-mysql-with-ssds_4.jpg

 

best-practices-for-mysql-with-ssds_5.jpg

 

best-practices-for-mysql-with-ssds_6.jpg

 

best-practices-for-mysql-with-ssds_7.jpg

 

best-practices-for-mysql-with-ssds_8.jpg

 

best-practices-for-mysql-with-ssds_9.jpg

 

best-practices-for-mysql-with-ssds_10.jpg

 

best-practices-for-mysql-with-ssds_11.jpg

 

best-practices-for-mysql-with-ssds_12.jpg

 

best-practices-for-mysql-with-ssds_13.jpg

 

Best Practices for MySQL with SSDs  Page 1  Best Practices for MySQL with SSDs A whitepaper by: Vijay Balakrishnan  Changho Choi  Veronica Lagrange  Hubbert Smith  Samsung Semiconductor, Inc. Best Practices for MySQL with SSDs  Page 2  Table of Contents 1.  Introduction ....................................................................................................................................... 3  2.  Hardware Configurations .................................................................................................................. 3  3.  Optimizing MySQL Server and Percona Server for SSDs ................................................................... 5  4.  Flush Method and Buffer Pool ........................................................................................................... 6  5.  Impact of Latency .............................................................................................................................. 6  6.  Optimization Guidelines .................................................................................................................... 7  7.  Performance Results Comparison ..................................................................................................... 8  8.  tpcc‐mysql System Resource Utilization.......................................................................................... 11  9.  Conclusions ...................................................................................................................................... 12  10.  Appendix A: MySQL and Percona Configurations ........................................................................... 12     Best Practices for MySQL with SSDs  Page 3  1. Introduction Imagine a world where networking speeds became mired  at 1MB/s, stayed “stuck” at that  speed for 20 years and then became “unstuck,”  suddenly jumping to 1GB/s (1000X).  For  storage, something similar has indeed happened with the transition from Hard Disk Drives  (HDDs) to Solid State Devices (SSDs).  In the past, storage administrators sized systems by  estimating the IOPS needed, then buying a quantity of 15K rpm HDDs to provide those IOPS.  Often, this resulted in buying more HDD capacity than was necessary.  Now, storage  administrators typically size a system based on the needed capacity, then buy a quantity of SSDs  to provide that capacity, and are pleasantly surprised with the performance that comes along  with the capacity. This paper underscores that all SSDs are not alike and that specific  configurations and settings need to be considered to maximize performance and system value.  In the software domain, MySQL Server is a commonly used Relational Database Management  System (RDBMS).This whitepaper clarifies how today's data centers combine SSDs and MySQL to  achieve a substantial business advantage. The paper focuses on Percona Server – a free, fully  compatible, open source MySQL Server enhancement. Because Percona Server is optimized for  the I/O subsystem, it was selected for the experiments herein described, with the best known  methods reported on the following pages.  About the Benchmark: TPC‐C and tpcc‐mysql TPC‐C is an OLTP industry standard benchmark developed and maintained by the Transaction  Processing Performance Council (TPC) [www.tpc.org]. It is widely used and well understood,  which enables database administrators to quickly correlate quoted TPC‐C results to their specific  application. TPC‐C simulates a wholesale supplier and is centered on processing orders. It is a  mixture of read‐only and update‐intensive transactions that represent complex OLTP application  activities. The benchmark implements five types of transactions (new order, payment, delivery,  order status and stock level), and reports performance by measuring the number of new orders  processed per minute (tpmC). It contains strict guidelines that must be observed in any official  implementation. Furthermore, in spite of its complexity, TPC‐C is easily scaled up or down to fit  the system under test.  Tpcc‐mysql is Percona’s TPC‐C implementation. It is written in C and follows Revision 5.11 of the  MySQL standard specification.  We tested with tpcc‐mysql “out‐of‐the box” code, downloaded  from the Percona GitHub site.   2. Hardware Configurations In the past, OLTP systems were frequently bottlenecked by IO, meaning CPUs were constantly  waiting on HDDs to respond.  But when we replace HDDs with NVMe SSDs on a Dual Socket Best Practices for MySQL with SSDs  Page 4  Server, the CPU becomes the bottleneck. In an attempt to minimize the CPU bottleneck,  we  tested two types of database servers: Dual Socket (12 core) and Quad socket (28 core).  Dual‐socket (12 Core) configuration  Database Server  (Client) Load Generation Server  CPU  Model Name  Intel(R) Xeon(R) CPU E5‐2670 v3 @  2.30GHz  Intel(R) Xeon(R) CPU E5‐2640 v3 @ 2.60GHz  Processors  12  8  Cores  24  32  Memory  64GB  128GB  OS version  Linux 4.4.0‐040400‐generic   Linux 3.19.0‐14‐generic  MySQL Server  5.7.11  Percona Server  5.7.10‐3  Storage        SAS HDD  2x SEAGATE  ST600MP0005 15K  rpm    SATA SSD  2x Samsung 850 PRO     NVMe SSD  2x Samsung XS1715         Quad‐socket (28 Core) Configuration     Database Server  (Client) Load Generation Server  CPU     Model Name  Intel(R) Xeon(R) CPU E7‐4850 v3 @ 2.20GHz  Intel(R) Xeon(R) CPU E5‐2640 v3 @ 2.60GHz  Processors  28  8  Cores  112  32  Memory   124GB   126GB  OS version  Linux 4.4.0‐040400‐generic   Linux 3.19.0‐14‐generic  Percona Server  5.7.11‐4     Storage        SAS HDD  2x SEAGATE  ST600MP0005 15K rpm    SATA SSD  2x Samsung 850 Pro     SAS SSD  2x Samsung PM1633  NVMe  2x Samsung PM1725     It is generally accepted that OLTP database applications have been I/O bound. They typically  have not exhausted CPU capacity. While this is true for HDDs, we see a paradigm shift with  NVMe SSDs.  Best Practices for MySQL with SSDs  Page 5  With HDDs, CPUs are always less than 1% busy, while with NVMe SSDs CPU utilization goes up  to more than 30% with 100+ connections. This 30 times CPU increase translates into 110+ times  more transactions per minute (tpmP), making the case for better server utilization when using  NVMe SSDs.  Mean CPU % utilization (User+Sys) on the Quad Socked Server  Mean CPU %  15K rpm  SAS‐HDD  SATA SSD  SAS SSD  NVMe SSD  50 connections  0.4%  5.8%  15.9%  22.3%  100 connections  0.4%  13.4%  23.3%  32.1%  150 connections  0.5%  15.0%  26.4%  28.9%  200 connections  N A  16.4%  27.2%  33.8%  This report presents current results for four storage devices: PM1725 (NVMe SSD), PM1633 (SAS  SSD), 850Pro (SATA SSD), and a Seagate 15Krpm HDD (HDD). We show how faster SSD storage is  revolutionizing typical OLTP applications that have been traditionally I/O bound, thereby  increasing throughput by orders of magnitude, measured with tpcc‐mysql benchmark. 3. Optimizing MySQL Server and Percona Server for SSDs We observed that the NVMe SSDs performed either 200% or 700% better than SATA SSDs,  depending on the selected configuration. With that in mind, we then proceeded to determine  the best performance level for each device. In addition to adjusting MySQL configuration  parameters, we tried increasing and decreasing the database sizei , as well as partitioning  (sharding) the database across multiple storage volumes.   MySQL Server throughput (tpmP1 ) for 200 and 250 connections ‐ SATA versus NVMe on the Dual Socket Server  1,000‐wh23; 200‐connections  SATA SSD  NVMe SSD  pct diff  Config #1 – MySQL baseline        7,366.55      24,440.57  232%  Config #2 – sub‐optimal config        4,478.28      37,802.13  744%  pct diff  ‐39%  55%     1,000‐wh; 250‐connections  SATA  NVMe  pct diff  Config #1 – MySQL baseline        6,668.33      23,175.19  248%  Config #2‐ sub‐optimal config        4,457.47      33,857.05  660%  pct diff  ‐33%  46%                                                                  1  tpmP= New Order transactions per minute  2  TPC‐C database size is defined by number of warehouses (wh). All tables are scaled up or down based on the  number of warehouses defined. A 10‐wh database occupies roughly 1GB of disk space. Best Practices for MySQL with SSDs  Page 6  BKM  Use SSDs instead of HDDs; there is no viable reason to use HDDs.  First preference: NVMe SSD  Second preference: SAS SSD  4. Flush Method and Buffer Pool BKM  Switch the flush_method to ‘O_DIRECT’  MySQL uses its buffer pool as disk cache, rather than using Linux filesystem as buffer  space.  BKM  Increase the buffer pool from 3GB to 12GB  Larger buffer pool improves performance for all storage types.  After considerable experimentation and analysis, we switched the flush_method to ‘O_DIRECT’,  and then stopped using Linux’s filesystem buffer space and instead began using MySQL buffer  pool as the disk cache.  Configuration #2 (sub‐optimal, see appendix A) has 3GB of buffer pool,  and therefore very little buffer space to be spared as disk cache.   In Configuration #3 (MySQL Optimal, see appendix A) we increased the buffer pool to 12GB,  which benefitted all three storage types.   The NA for 15K rpm SAS‐HDD in Configuration #2 (sub‐optimal) indicates we cannot run tpcc‐ mysql with that setup (we get “lock wait timeout” error messages, meaning that the system is  not able to complete transactions within the expected time). When we move to Configuration  #3 (MySQL optimal), we get a significant boost for all devices, especially for 15K rpm SAS‐HDD,  which is now able to complete the test without errors.  5. Impact of Latency Think of latency as a traffic signal on a popular roadway. If the roadway has few cars, it’s an  inconvenience. If the roadway has too many cars, traffic backs up which can be a disaster; this is  true in I/O.  This is when data center managers can really appreciate the benefits of faster  storage devices. When everything else is the same, most users connected to an “NVMe system”  will see their transactions completed in less than 90 milliseconds – two orders of magnitude  faster than the 5,000+ milliseconds users connected to the “HDD system” experience. Best Practices for MySQL with SSDs  Page 7  MySQL Server 95th percentile response times for Config #3 (dual socket)  MySQL Server maximum response times for Config #3 (dual socket) In the case of SAS‐HDD with 150 connections, the I/O traffic backs up and the system becomes  unresponsive. If this happens in an actual market environment, anyone using the database  (bank tellers, cash registers, RFID checkers) will see their transactions cease to operate and their  business grind to a halt.  6. Optimization Guidelines BKM  innodb_thread_concurrency. Different values were tried; the best performance came with  the default 0 (unlimited thread_concurrency).  50 c 100 c 150 c sas‐hdd 6,329.74 5,288.26 sata 1,066.15 1,188.02 1,191.95 nvme 36.86 54.22 85.61  ‐  1,000.00  2,000.00  3,000.00  4,000.00  5,000.00  6,000.00  7,000.00 milliseconds 95th percentile Response Time ‐ New Order 50 c 100 c 150 c sas‐hdd 20,300.16 49,452.06 sata 4,699.63 15,759.15 37,367.16 nvme 1,716.82 1,242.70 1,932.78  ‐  10,000.00  20,000.00  30,000.00  40,000.00  50,000.00  60,000.00 milliseconds Maximum Response Time ‐ New OrderBest Practices for MySQL with SSDs  Page 8  BKM  innodb_adaptive_hash_index. Turned ‘OFF’, since OLTP workloads typically do not reuse  data from previous queries.  BKM  innodb_fill_factor. Indicates the percentage of space on each B‐tree page that is filled  during a sorted index build. 50 works best for workloads with lots of INSERTs.  BKM  Separate log_dir and datadir. All storage types benefit from this. For both Percona and  MySQL Server, it means setting up the parameters from Appendix A marked with either  <data storage> or <log storage> to the appropriate storage location.  7. Performance Results Comparison Here throughput results for all storage types were reviewed. Observe that the SATA SSD result is  32+ times better than that of the HDD, and that NVMe SSD throughput is two to five times  better than SATA, and up to 51% better than SAS SSD.  Throughput in tpmP – Quad Socket  Connec tions SAS‐ HDD  SATA  SSD  SAS  SSD  NVMe  SSD  SAS_HDD  ‐> SATA  SAS_HDD ‐>  SAS‐SSD  SAS_HDD ‐ > NVMe  SATA  ‐ > SAS  SATA ‐>  NVMe  SAS‐>  NVMe  50  742  23,868  73,236  110,357  3117%  9770%  14773%  207%  362%  51%  100  757  55,378  94,702  136,479  7218%  12415%  17935%  71%  146%  44%  150  1,090  58,123  96,691  138,748  5234%  8773%  12632%  66%  139%  43%  200  N A  58,276  97,796  139,158           68%  139%  42%  The best tpcc‐mysql throughput, 139K tpmP, is obtained using NVMe SSDs running with 200  connections. The 100 connection result, 136K tpmP, is 180+ times better than HDD. Next figure  shows New Order transactions executed over time for all four storage types for the 100‐ connection case.     Best Practices for MySQL with SSDs  Page 9  New Order transactions over time (Higher is better)  New Order 95th percentile response times (Lower is better)  Notice for 15K SAS‐HDD 200c, the result took too long to obtain (failed test). In an actual  market environment, this represents a non‐responsive system impacting business  operations.      0 50000 100000 150000 200000 60 540 1020 1500 1980 2460 2940 3420 3900 4380 4860 5340 5820 6300 6780 Time interval NewOrder Transactions per Minute ‐ 100  Connections nvme SAS sata sas‐hdd 50 c 100 c 150 c 200 c sas‐hdd 4,182 8,572 10,847 sata 100 142 181 239 SAS 57 68 128 145 nvme 26 32 33 34  ‐  2,000  4,000  6,000  8,000  10,000  12,000 milliseconds Connections NewOrder 95th Percentile Response TimesBest Practices for MySQL with SSDs  Page 10  New Order maximum response times (Lower is better)  The 95th percentile response times for New Order transactions over the duration of each  measurement.  SATA SSD and SAS SSD latencies (Lower is better)  SAS SSDs demonstrate significantly lower latencies than SATA SSD for this OLTP  workload.  50 c 100 c 150 c 200 c sas‐hdd 13,884 42,896 76,668 sata 592 899 1,243 1,483 SAS 286 454 1,148 1,759 nvme 219 343 401 528  ‐  10,000  20,000  30,000  40,000  50,000  60,000  70,000  80,000  90,000 milliseconds Connections NewOrder Maximum Response Times 0 50 100 150 200 120 480 840 1200 1560 1920 2280 2640 3000 3360 3720 4080 4440 4800 5160 5520 5880 6240 6600 6960 milliseconds time interval NewOrder 95th Percentile Response Time ‐ 100 connections sata SASBest Practices for MySQL with SSDs  Page 11  SAS and NVMe latencies (Lower is better)  NVMe SSDs demonstrate significantly lower latencies than SAS SSDs for this OLTP workload.  8. tpcc‐mysql System Resource Utilization All Disk Writes 100 connections (Higher is better)  0 10 20 30 40 50 60 70 80 90 120 480 840 1200 1560 1920 2280 2640 3000 3360 3720 4080 4440 4800 5160 5520 5880 6240 6600 6960 milliseconds time interval NewOrder 95th Percentile Response Time ‐ 100 Connections SAS nvmeBest Practices for MySQL with SSDs  Page 12  All Disk Reads 100 connections (Higher is better)  9. Conclusions Not all SSDs are the same but all SSDs perform substantially better than HDDs.  NVMe SSDs can  be up to five times more performant than SATA SSDs, as tested with Percona.  SAS‐SSD can be  up to 125 more performant than SAS‐HDD. NVMe SSDs are 180 times more performant than  15K rpm HDDs.  10. Appendix A: MySQL and Percona Configurations This appendix contains the many configuration settings for both MySQL Server and Percona Server  discussed in this report.  MySQL Server and Percona Server configurations  Parameter Name  Config #1 MySQL Initial  Config #2 MySQL  Suboptimal  Config #3  MySQL  Optimal  Config #4 Percona  Optimal  datadir  /<data  storage>/mysql_data/mysql     tmpdir    /tmp  /<log  storage>/mysql_log  lc‐messages‐dir   /usr/share/mysql    explicit_defaults_for_timestamp       innodb_log_group_home_dir   /<log storage>/mysql_log   Best Practices for MySQL with SSDs  Page 13  innodb_undo_directory   /<log storage>/mysql_log    innodb_buffer_pool_size   3GB  12GB  innodb_thread_concurrency  0 innodb_temp_data_file_path   '../../../<log  storage>/mysql_log/ibtmp1:72 M:autoextend'  innodb_page_cleaners 32    8 innodb_buffer_pool_instances 32    8 innodb_io_capacity 300000    15,000  innodb_io_capacity_max  600000    25,000 innodb_adaptive_hash_index OFF'     0 innodb_fill_factor 50    100 innodb_write_io_threads 16 innodb_read_io_threads 16 innodb_flush_method <empty>  O_DIRECT O_DIRECT  O_DIRECT innodb_flush_neighbors 1    0 query_cache_size 0 log_timestamps  'SYSTEM'  table_open_cache 8000 table_open_cache_instances 16    64 back_log 1500 max_connections 4000 innodb_use_native_io  ON (default)    OFF / ON ## To be able to use many  connections in TPC‐C:    max_prepared_stmt_count 64000 # files    innodb_log_files_in_group 3 innodb_log_file_size 48MB  1G 1G  10G innodb_open_files 4000    # tune =     innodb_checksum_algorithm NONE  crc32 innodb_max_dirty_pages_pct 90    innodb_max_dirty_pages_pct_lwm 10    innodb_lru_scan_depth 4000 8192 join_buffer_size 32K     sort_buffer_size 32K     innodb_spin_wait_delay 96 6 # perf special       innodb_flush_neighbors 0   Best Practices for MySQL with SSDs  Page 14  innodb_max_purge_lag_delay 30000000 0 # Monitoring       innodb_monitor_enable  '%'  <empty> performance_schema OFF     ..oo0oo..                                                               i  TPC‐C database size is defined by number of warehouses (wh). All tables are scaled up or down based on the  number of warehouses defined. A 10‐wh database occupies roughly 1GB of disk space. 

 

?

컴퓨터/노트북/인터넷

IT 컴퓨터 기기를 좋아하는 사람들의 모임방

쓰기
List of Articles
번호 분류 제목 조회 수 날짜
공지 뉴스 구글 최신 뉴스 file 1384 2024.12.12
공지 사랑LOVE 포인트 만렙! 도전 4623 2025.03.19
공지 🚨(뉴비필독) 전체공지 & 포인트안내 2 25844 2024.11.04
공지 URL만 붙여넣으면 끝! 임베드 기능 20427 2025.01.21
10639 일반 "갤럭시S6엣지 플러스, 3000mAh 대용량 배터리 탑재"… 왜? 1082 2015.07.20
10638 정보 # TAKIS 서버용 my.cnf... 830 2023.11.06
10637 ' 크래시 밴디쿳 4: 이츠 어바웃 타임', PS5, Xbox Series X, Switch판의 발매가 3월 12일로 결정. PC판은 금년 후반을 예정. 51 2021.02.11
10636 일반 ' 크래시 밴디쿳 4: 이츠 어바웃 타임', PS5, Xbox Series X, Switch판의 발매가 3월 12일로 결정. PC판은 금년 후반을 예정. 497 2023.03.27
10635 일반 '10배 빠른' 5G 지하철 와이파이…다음달부터 중단 1245 2023.11.24
10634 정보 '11시간 쓸 수 있다' 광고한 300만 원대 맥북 배터리 5시간이면 '아웃'...6개월째 수리도 못받아 - 소비자가 만드는 신문 786 2021.02.21
10633 정보 '5G 아이폰12'는 4종으로…애플, 제품군 다변화 나섰다 - 한겨레 749 2021.02.21
10632 일반 'LG G8 씽큐' 배터리 수명 늘어날까…3400~3500mAh 배터리 탑재 전망 191 2019.02.08
10631 일반 'LG G8 씽큐' 배터리 수명 늘어날까…3400~3500mAh 배터리 탑재 전망 1784 2023.03.27
10630 정보 'LG 롤러블폰 개발 중단 우려' 中BOE, 아이폰13 물량 공략 - 이데일리 694 2021.02.24
10629 정보 'M1 맥북 에어 vs. M1 맥북 프로' 어떤 제품을 어떤 구성으로 구매해야 할까 - ITWorld Korea 735 2021.03.06
10628 정보 'M자 탈모' 사라지는 아이폰… "갤럭시 닮아간다" - 머니S - Money S 747 2021.03.06
10627 정보 '美-中 대립' 최고조에도...中서 애플 아이폰만 유일한 성장 - 뉴데일리경제 771 2021.02.21
10626 정보 '갤럭시 vs 아이폰' 12만 LG 임직원 선택은 - IT조선 777 2021.02.21
10625 정보 '갤럭시S21 울트라' 국내모델 엑시노스2100·12GB램 탑재 69 2020.12.26
10624 정보 '국대폰' 갤럭시S21 및 아이폰12 미니 포함한 기존 기종 프로모션 진행 - 잡포스트 763 2021.02.21
10623 정보 '국대폰' 아이폰12 미니· 갤럭시S21 특가 판매 및 무료 나눔 이벤트 진행 - 에너지경제신문 751 2021.02.24
10622 정보 '국대폰' 최신 스마트폰 갤럭시S21 및 아이폰12 미니 특가 판매 프로모션 진행 - ITBizNews 775 2021.02.22
10621 정보 '국대폰', 갤럭시S21·아이폰12 특가 판매 진행 - 이투뉴스 768 2021.03.06
10620 정보 '극딜나라' 갤럭시S20, 아이폰12 PRO 35만원 이상 할인 - 아이티비즈 767 2021.03.01
10619 일반 '눈에 띄게 얇아진' Apple Watch Series 10, 결국 수면 무호흡증 감지 기능 탑재 file 137 2024.09.06
10618 일반 '모토로라폰’ LG헬로비전 통해 재상륙 544 2022.12.31
10617 일반 '모토로라폰’ LG헬로비전 통해 재상륙 1795 2023.03.27
10616 '미르4' 희귀 강화석 어디서 얻어요? 86 2021.02.03
10615 일반 '미르4' 희귀 강화석 어디서 얻어요? 404 2023.03.27
10614 정보 '배신자 삼성 무너뜨리는게 목표'…혐한에 빠진 세계 최대 제조대행 [뉴스人사이드] - 아시아경제 782 2021.03.01
10613 정보 '비트코인 인기 상당하네'...업비트, 구글·애플 앱마켓 1위 석권 734 2021.02.22
10612 정보 '빨간색이 주황색으로'…아이폰12서 '색빠짐 현상' 나타나 - 이데일리 783 2021.03.17
10611 정보 '빵집폰' 갤럭시노트10 및 갤럭시S10 5G, LG V50s ThinQ 재고정리 - 아이티비즈 718 2021.03.06
10610 일반 '신학기 시즌 시작'…유통업계 할인 大戰 - 아시아경제 53 2023.03.27
목록
쓰기
Board Pagination Prev 1 2 3 4 5 6 7 8 9 10 ... 355 Next
/ 355