Dell EMC SC Series Arrays with MySQL Abstract Best practices, configuration options, and sizing guidelines for Dell EMC™ SC Series storage in Fibre Channel environments when deploying MySQL®.
Table of contents Revisions Date Description April 2016 Initial release August 2018 Content and format change Acknowledgements This document was produced by the following members of the Dell EMC storage engineering team: Author: Mark Tomczik The information in this publication is provided “as is.” Dell Inc.
Table of contents Table of contents Revisions.............................................................................................................................................................................2 Acknowledgements .............................................................................................................................................................2 Audience ..........................................................................................................
Table of contents 3.23 Volume cache settings .....................................................................................................................................44 3.24 Server multipathing ...........................................................................................................................................44 4 MySQL............................................................................................................................................................
Executive summary Audience This document is intended for information technology professionals seeking to integrate MySQL® with Dell EMC™ SC Series storage.
Introduction 1 Introduction According to the DB-Engines April 2016 ranking, MySQL™ is the world's second most popular database and most popular open-source database. In addition, it is used in a wide variety of industries (see MySQL Customers by Industry). To deliver cost-effective, high-performance, and scalable solutions, MySQL can be coupled with Dell EMC™ SC Series storage.
Fibre Channel connectivity 2 Fibre Channel connectivity SC Series arrays have been tested to work with Dell™, Emulex®, and QLogic® HBA cards. They also support simultaneous transport protocols including Fibre Channel (FC), iSCSI, and FCoE. Although this document was created for FC environments with QLogic HBAs, much of it should also apply to iSCSI and FCoE.
Fibre Channel connectivity The example in Figure 1 shows four FC zones, one per server HBA port. Zones in each of the fabrics share the same targets, but have a different initiator.
Fibre Channel connectivity WWN of physical port 1 of the server HBA in slot 5 WWNs of controller 1 virtual ports for the front-end primary ports WWNs of controller 2 virtual ports for the front-end primary ports Soft zone When soft zoning with multiple controllers in virtual port mode, the ports from each controller must be equally distributed between the two fabrics. It does not matter which ports are chosen for a fabric if the ports are chosen in a similar manner for all zones in the same fabrics.
Fibre Channel connectivity Two zones (one for port 1 from each HBA) for fabric 2 Server HBA ports and SC Series controller front-end ports for zones FC Zone Fabric Server HBA Server HBA port SC Series controller SC Series controller ports 1 1 1 1 1 1, 2 or 1, 3 2 1 2 1 2 1, 2 or 1, 3 3 2 1 2 1 1, 2 or 1, 3 4 2 2 2 2 1, 2 or 1, 3 Soft zone example FC zone Fabric WWN MT_r730xd_1_s4 1 Description 2001000e1ec2d343 Server HBA 1, port 1 5000d3100002b90b Controller 1 front-end v
Fibre Channel connectivity 2.2 QLogic settings Configure the server HBAs according to the recommendations in the Dell Storage Manager Administrator’s Guide available on the Dell Support website to improve connection speeds between the database server and SC Series arrays. Note: Both the BIOS settings and OS driver module for QLogic control the performance of an HBA. Settings specified in the driver module take precedence over the BIOS settings.
Fibre Channel connectivity QLogic HBA BIOS Settings QLogic BIOS menu QLogic BIOS attribute Value Adapter Settings Host Adapter BIOS Enable Connection Options 1 (for point-to-point only) Login retry count 60 Port down retry count 5, or 601 Link down timeout 30 Execution Throttle 256 LUNs per Target 128 Enable LIP Reset Yes Selectable Boot 2 Enable Boot Port Name, Lun (nnnnn,0) (This was configured after the FC fabric zones were created) 2 WWN for the 1st boot volume path, and Lun shoul
Fibre Channel connectivity The default queue depth value of 32 may be adequate, but a value of 64 or 128 may work well too. The optimal queue depth value is dependent on a number of parameters, including creating snapshots (Replays) of the MySQL database. Determining the optimal value is out of scope of this document.
SC Series array 3 SC Series array Storage administrators have to make complex decisions daily on storage configuration, usage, and planning. For example, when creating a volume, the question may be asked: Will it be sized appropriately? If all volumes are oversized, there is the added issue of over provisioning the array. Also, traditional arrays do not provide an easy way to match performance metrics of disks with the performance characteristics of the data.
SC Series array 3.1 Virtual port mode Dell EMC strongly recommends using SC Series virtual port mode over Legacy port mode to provide redundancy at the port and storage-controller level. If virtual port mode is used, the WWNs of the virtual ports must be used in the zone rather than the physical ports.
SC Series array 3.2 Fault domains in virtual port mode Fault domains group front-end ports that belong to the same network and transport media (FC or iSCSI, but not both). There must be a fault domain for each front-end Fibre Channel fabric (see Figure 1). Ports that belongs to the same fault domain can fail over to each other because they have the same connectivity and transport protocol.
SC Series array 3.4 Creating server objects in SC Series arrays Dell EMC recommends that at least two, dual-port HBAs be installed in the server before it is identified in the SC Series system. Two dual-port HBAs provide redundancy of initiator ports and HBAs. Figure 8 shows a server object with four initiators (two, dual-port HBAs). HBAs assigned to a server objet in the SC Series system An important attribute of the server object is the operating system.
SC Series array 3.5 Disk pools In most configurations, SC Series arrays group all disks, regardless of the disk’s performance characteristics, into a single folder to create one virtual pool of storage. The virtual pool of storage is referred to as the pagepool and it corresponds to the Assigned disk folder in the SC Series system. There are some cases where multiple disk folders (multiple pagepools) may be needed.
SC Series array 3.6 Datapage A datapage is the space that is allocated from the Assigned disk folder to a volume when a request for space is made. Possible datapage sizes are: 512 KB: This datapage size is appropriate for application programs with high performance needs, or environments in which snapshots (replays) are taken frequently under heavy I/O. Selecting this size reduces the amount of space System Manager can present to servers. Flash-optimized storage types use 512 KB by default.
SC Series array Storage types in SC Series arrays Frequently accessed data remains on tier 1 (T1), and data that has not be accessed for the last 12 progression cycles is gradually migrated to tier 2 (T2) (should it exist) and then to tier 3 (T3).
SC Series array 3.8 Tier redundancy and RAID Data within tiers is protected by redundancy through the implementation of RAID technology. RAID requirements for each disk tier are based on the type, size, and number of disks in the tier, and will result in either single or dual redundancy of the data on a volume. In rare cases, redundancy for a tier can be disabled by using RAID 0, but caution should be exercised if using RAID 0 in MySQL deployments (see Table 5 for details).
SC Series array By default, SC Series storage implements dual redundancy. With RAID 6-10, a minimum of 11 disks are required. With RAID 6-6 a minimum of seven disks are required. With respect to RAID 6, if a tier does not have at least seven disks, the default tier redundancy is set to single redundancy RAID 5-5 for any disk drive size.
SC Series array 3.9 Tier redundancy and media type Media type used within a tier can influence the type of redundancy enforced on the tier. Media type and redundancy requirements Media type Redundancy requirements HDD By default, disks under 966 GB are set to single redundancy. Dual redundancy is recommended for 966 GB to 1.93 TB disks. Dual redundancy is required when adding 1.93 TB or larger disks to a new disk pool. SSD By default, disks under 1.7 TB are set to single redundancy.
SC Series array 3.10 RAID stripe width The default stripe width is 10 wide for RAID 6 (RAID 6-10) storage profiles, and 9 wide for RAID 5 (RAID 5-9). Modifying stripe width updates the corresponding RAID 5 or 6 selections for all predefined storage profiles. User-created storage profiles are not affected. Table 7 shows the stripe widths for all RAID levels in SC Series arrays.
SC Series array RAID stripes in SC Series arrays SC Series arrays store the most active data on RAID 10, and the least active data on RAID 5 or RAID 6 (see Tier redundancy and RAID). Distributing data across more drives is marginally less efficient, but decreases vulnerability. Conversely, distributing data across fewer drives is more efficient, but marginally increases vulnerability.
SC Series array To view RAID stripe widths and efficiencies, in DSM right-click the array, select Edit Settings, and click Storage. The RAID Stripe Width drop-down fields show the available stripe widths for RAID 5 and RAID 6. RAID efficiencies of stripe widths In most MySQL environments, using the default and recommended RAID stripe width provides good I/O performance and storage conservation for all types of database volumes and applications.
SC Series array 3.11 RAID penalty Depending on the RAID level implemented, data and parity information may be striped between multiple disks. Before any write operation is considered complete, the parity must be calculated for the data and written to disk. The time waiting for the parity information to be written to disk, or the number of extra I/Os required for parity, is referred to the RAID (or write) penalty. The penalty only comes into play when a write is required.
SC Series array • • • • • hour period that includes the peak workload. The simulation will help define I/O requirements. It might be possible to use IOMeter to simulate the production system. After go live, repeat the process again. Have a good understanding of the MySQL workloads (OLTP, OLAP, or hybrid). The workload will have an impact on how an SC Series array is sized and configured. For OLTP systems, generally SSDs or 15K drives are used to deliver high IOPS with low latencies.
SC Series array 3.13 Data Progression Data Progression is a separately licensed application feature of the SC Series array that provides a constant balance of performance and cost, leveraging different storage tiers and drive types, as well as among multiple RAID levels within the same tier. Data Progression provides the maximum use of lower-cost drives for stored data, while maintaining high-performance drives for frequently-accessed data.
SC Series array SC Series arrays also use Data Progression to move snapshots. When a snapshot is created, the data is frozen and moved immediately to the tier specified by the storage profile to hold snapshots. SC Series arrays also use Data Progression to move snapshots. When a snapshot is created, the data is frozen and moved immediately to the tier specified by the storage profile to hold snapshots.
SC Series array 3.14 Data Progression pressure reports A tier can become full through normal usage, by data movement from Data Progression cycles, or from frequent database snapshots with long retention periods. When a tier becomes full, the SC Series array writes data to the next lower tier which can cause performance issues because of the RAID penalty. Therefore, Dell EMC recommends using Data Progression pressure reports to monitor disk usage.
SC Series array A drop-down field (shown in Figure 18) can be used to report on a specific date. For more information on Data Progression pressure reports, see the Storage Center System Manager Administrator’s Guide available on the Knowledge Center at the SC Series Customer Portal. Specific date period for pressure report 3.15 Volume distribution reports Dell EMC also recommends using the metrics reported using the subtabs for Volumes, Volume Growth, and Storage Chart.
SC Series array Volume distribution reports 3.
SC Series array data) to T1, consider the impacts on T1 capacity and Data Progression caused by complete data rebuilds from nightly extract, transform, load (ETL) operations, index rebuilds, and associated snapshots. Depending on storage media, storage profiles will vary. The default standard storage profiles are described in Table 10.
SC Series array Recommended (All Tiers): With this storage profile, all new data is written to T1 RAID level 10 storage. Data Progression moves less-active data to T1 RAID 5/RAID 6 or a slower tier based on how frequently the data is accessed. In this way, the most active blocks of data remain on high-performance SSDs or Fibre Channel drives, while less active blocks automatically move to lower-cost, high-capacity SAS drives.
SC Series array Name Initial write tier Tier and RAID levels Progression Low Priority with Progression (Tier 3 to Tier 2) 3 Writeable: To tier 2 only • T3 RAID 10, RAID 10-DM Snapshots: • T2 RAID 5-9, RAID 6-10 • T3 RAID 5-9, RAID 6-10 Low Priority (Tier 3) 3 Writeable: No • T3 RAID 10, RAID 10-DM Snapshots: • T3 RAID 5-9, RAID 6-10 Flash Optimized with Progression (Tier 1 to All Tiers): This storage profile provides the most efficient storage for an enclosure containing both read-intensive an
SC Series array In hybrid SC Series arrays (SSDs and HDDs), if there are not enough SSDs to retain the entire database, use custom storage profiles to separate the data between the different media, then monitor the performance to determine a best fit for each media type. When using SLCs with automated tiering, the tier needs to be sized for 100 percent IOPS and percentage of the capacity of the volumes using the SSD tier. If it is not and the tier becomes full, performance can degrade considerably.
SC Series array To see if the Data Instant Replay (Snapshots) feature is licensed, right-click an SC Series array, select Edit Settings from the context menu, then select License. Verifying Data Instant Replay (Snapshots) license Some scenarios for using snapshots with MySQL databases are: • • • • • 3.
SC Series array Consistent snapshot profile Since a snapshot is taken on one or a set of multiple volumes, use care when defining the snapshot profile. For example, if there are multiple databases that reside in a set of one or more volumes, and a consistent snapshot is taken of that volume set, it will contain an image of all the databases. If a snapshot of only one of the databases was needed, disk space will be wasted by the other database snapshots.
SC Series array Dell EMC recommends creating snapshots under these conditions: • • • Immediately before the database goes live, before and after an upgrade, major change, repair or any major maintenance operation Once per day to allow Data Progression to move age-appropriate data more efficiently and effectively to other storage types and tiers On a schedule that satisfies appropriate business requirements for recovery point objective (RPO) and recovery time objective (RTO).
SC Series array 3.19 SC Series cache settings The snapshot (Replay) feature is a licensed technology available on SC Series arrays that provides continuous space-efficient data protection. Snapshots create space-efficient, write-consistent, point-in-time copies (PITC) of one or more volumes based on a collection of rules that exist in a snapshot profile. Snapshots can be used for immediate recovery from data loss caused by hardware failures or logical errors.
SC Series array Many of the SC Series features and options discussed up to this point can be specified at the time a volume is created. This is possible because of the way in which SC Series arrays virtualize storage. In this section, general guidelines about volume creation in SC Series arrays will be shown. Specific guidelines for volume creation in a MySQL environment are covered in the section, MySQL volume considerations. To create a volume, perform the following: 1.
SC Series array Frozen preallocated pages Therefore, for high-performance database systems, where performance can be affected by dynamic block allocation, consider the tradeoff between preallocating and not preallocating space, especially if snapshots are a requirement. Also, consider the tradeoff between expanding or not expanding a preallocated volume.
SC Series array 3.23 Volume cache settings The snapshot (Replay) feature is a licensed technology available on SC Series arrays that provides continuous space-efficient data protection. Snapshots create space-efficient, write-consistent, point-in-time copies (PITC) of one or more volumes based on a collection of rules that exist in a snapshot profile. Snapshots can be used for immediate recovery from data loss caused by hardware failures or logical errors.
SC Series array devices { device { vendor "COMPELNT" product "Compellent Vol" features "0" hardware_handler "0" no_path_retry fail } } blacklist { devnode "^(ram|raw|loop|fd|md|dm-|sr|scd|st)[0-9]*" devnode "^hd[a-z]*" devnode "^cciss!c[0-9]d[0-9]*[p[0-9]*]" } multipaths { multipath { wwid "36000d3100002b9000000000000081c82" alias mysql1 } For additional information on multipath and configuring multipathing, refer to the best practices document Dell EMC SC Series with Red Hat Enterprise Linux 6x a
SC Series array WWNs of initiators Defining mapping paths for volume presentation Leave the check-boxes for Map to All Available Server Ports and Maximum number of paths per Server default to take advantage of the maximum number of paths provided by the OS. I/O requests for a volume are processed by the controller that owns the volume, and a volume can only be owned by one controller at a time. By default, all available paths are selected.
MySQL 4 MySQL MySQL is available in several editions. MySQL Community Edition is available on over 20 platforms, is free for download, and is made available under GPL licensing. Support is provided by a rather large active community of open source developers.
MySQL 4.2 The ACID model and MySQL ACID (Atomicity, Consistency, Isolation, and Durability) is a concept or set of design principles in relational database theory that guarantees database transactions are processed reliably and can play an important part in determining other business application design strategies. Any database management system that does not adhere to ACID properties cannot be considered reliable.
MySQL The show engines command displays the details of the storage engines: mysql> show engines; +--------------------+---------+---------+ | Engine | Support | Comment Savepoints | +--------------------+---------+---------+ | MRG_MYISAM | YES | Collect | | CSV | YES | CSV sto | | PERFORMANCE_SCHEMA | YES | Perform | | BLACKHOLE | YES | /dev/nu | | InnoDB | DEFAULT | Support | | MyISAM | YES | MyISAM | | ARCHIVE | YES | Archive | | MEMORY | YES | Hash ba | | FEDERATED | NO | Federat | +--------------------+
MySQL ALTER TABLE [ENGINE=INNODB]; If the ENGINE clause is not specified when creating the table, the table type is set to InnoDB. mysql> create table mytest (c1 char); Query OK, 0 rows affected (0.05 sec) mysql> select table_name, engine -> from information_schema.tables -> where table_schema='mysql' and table_name='mytest'; +------------+--------+ | table_name | engine | +------------+--------+ | mytest | InnoDB | +------------+--------+ 1 row in set (0.
MySQL • • • • • Applications requiring frequent table lock escalations under heavy but slow reads for long periods of time If a read is slow or has not completed, and a read-write is waiting on the first read to finish Separate buffer pools for caching data and indexes Tables not limited to OS with 2 GB file size limit Benefits of MySQL file-per-table tablespaces can be realized The following MySQL statements will set a table’s type to InnoDB: CREATE TABLE ( ); CREATE TABLE <
MySQL Disk space can be reclaimed in SC Series arrays when truncating or dropping a MySQL InnoDB table that uses file-per-table tablespaces, assuming the filesystem supports the SCSI UNMAP command and the volume was mounted with option DISCARD. See section, Ext4 file systems and MySQL, for more information on mounting ext4 filesystems.
MySQL When a system tablespace file becomes full, MySQL will auto extend the file in increments of MB defined by option innodb_autoextend_increment. The default size is 64. For best performance and efficient space management, this value should be the same as or a multiple of the SC Series datapage size (512 KB, 2 MB, or 4 MB). Use the following MySQL query to verify the increment size.
MySQL autoextend directs MySQL to extend the file in increments of 8 MB when it runs out of free space in the tablespace. max:max_file_size should be used when autoextend is specified and there is a possibility of the file size exceeding the file size limit of the filesystem. Since InnoDB is unaware of the filesystem maximum file size, if the maximum size is not specified and autoextend pushes the file beyond the filesystem maximum, fatal errors and possible crashing of the system could occur.
MySQL MyISAM table files 4.4.3 MyISAM table file File description .frm Table format .MYD Contains the table data (My Data = MYD) .MYI Contains the table indexes (My Index = MYI) Converting from MyISAM to InnoDB If a table type needs to be changed from MyISAM to InnoDB, it can be done so dynamically by executing the following MySQL command, providing the table is not using full-text indexing. MyISAM tables that are used with full-text indexing cannot be converted.
MySQL Fine-grained volume configuration in MySQL Volume name ext4 mount point Volume description Performance Snapshot frequency -boot / Boot partition Low Infrequent -mysql1-data /mysql1-data Data for MySQL instance 1 High Frequent -mysql1-stbs /mysql1-innodb-stbs InnoDB system tablespace instance 1 High * Frequent -mysql1-binlog /mysql1-redo Binary log MySQL instance 1 redo High * Frequent -mysql1-undo /mysql1-undo InnoDB undo for MySQL ins
MySQL If Table 14 contains too many dedicated LUNs for the business requirement, it can be simplified. For example, if log files, data directory, and PID file can exist on a single volume for each MySQL instance, then option --basedir can be used to direct MySQL to use that one location for certain files.
MySQL If it is not, MySQL will experience an issue when initializing its environment because it expects the base directory to be void of all entries. There are several ways to declare and set --ignore-db-dir: • --Ignore-db-dir is not a dynamic MySQL option, so add and set it in section [mysqld] of section in /etc/my.cnf. Once it is added, start the MySQL service. # cat /etc/my.
MySQL 7. Restart MySQL. service mysqld start 4.6 Ext4 file systems and MySQL Because ext4 supports the SCSI UNMAP command, Dell EMC recommends using ext4 file systems for at least the datadir location. SCSI UNMAP allows space to be reclaimed in SC Series arrays (version 5.4 or newer). If a MySQL MyISAM or InnoDB file-per-table tablespace is truncated or deleted, the filesystem will release the space and SC Series arrays will return the space back to it pagepool.
MySQL 4.8 Deleting MySQL volumes from Linux Should data in MySQL out live its business purpose and retention period, it could be considered a candidate for cleanup. If the underlying volume only contained this data, it too could be considered a candidate for cleanup. However, removing the underlying volume from Linux OS is a little more involved. To remove the associated LUNs that were used to store the MySQL data, Dell EMC recommends using the following steps to remove a MySQL volume from Linux: 1.
MySQL 13. Unmap and delete the volume from the Dell Storage Center. Dell EMC strongly recommends taking a cold backup of the entire database prior to and after the any cleanup operation, and then ensuring only reads be executed on the database during the cleanup operation. For more information on volume expansion, refer to the Dell EMC SC Series with Red Hat Enterprise Linux 6x best practices document. 4.
MySQL 3. Create a new volume in the SC Series array for the new data directory and present it to the server. 4. Scan Linux for the new device, set up multipath, and mount the device. 5. Verify that the dedicated LUN for MySQL data is mounted. # df -k Filesystem /dev/mapper/mysql-inst1 1K-blocks 206293688 Used Available Use% Mounted on 60684 195747244 1% /mysql1 6. Copy or move the original data files to the new location. # cp -rap /var/lib/mysql/* /mysql1 7.
MySQL 4.10 MySQL and SELinux SELinux (Security-Enhanced Linux) provides access control security policies and governs resources from accessing objects they should not. By default, in Linux 6 when installing MySQL, SELinux is configured with a set of policies that expects MySQL databases to reside in /var/lib/mysql. If MySQL attempted to write to databases in some other directory, SELinux would prevent the writes unless additional access control policies were added to SELinux to allow the access.
MySQL 4. Add the corresponding SELinux context mappings for the new location and socket file. semanage fcontext -a -t mysqld_db_t "/mysql1(/.*)?" semanage fcontext -a -t mysqld_var_run_t -f -s "/mysql1/mysql\.sock" 5. Verify the new context mappings exist. # semanage fcontext -l | grep '^\/mysql1' /mysql1(/.*)? all files system_u:object_r:mysqld_db_t:s0 /mysql1/mysql\.sock socket system_u:object_r:mysqld_var_run_t:s0 6. Apply the new SELinux context to the running system and make the changes persistent.
MySQL backups and SC Series snapshots 5 MySQL backups and SC Series snapshots Using SC Series snapshots (replays) as backups can be an efficient backup strategy in terms of performance, effort, and resources required to create the backup and meet the RTOs. This is in contrast to using mysqldump to create backups which will take up the entire space of the database on disk for each backup.
MySQL backups and SC Series snapshots MySQL command show binary logs; displays all binary log files, and show master status; displays the name of the current binary log. mysql> show binary logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | server-bin.000001 | 177 | | server-bin.000002 | 177 | | server-bin.000003 | 36166 | +-------------------+-----------+ 3 rows in set (0.
MySQL backups and SC Series snapshots 5.3 Cold backups with SC Series snapshots The most complete, reliable, and consistent backup is a full, cold database backup of all the MySQL databases. This type of backup may not be appropriate for all implementations because it requires all database activity and the mysql service to be stopped, but it is by far the simplest to implement and guarantees consistent backups.
MySQL backups and SC Series snapshots The procedures described in this section can be applied to both MyISAM and InnoDB backups. 5.5.1 MyISAM When creating a snapshot of a MyISAM database, amend the procedure in section 5.5 as necessary. For example, to get a consistent backup, acquire read locks and flush the tables, or stop the MySQL server. The flush is needed to ensure that all active index pages are written to disk.
MySQL backups and SC Series snapshots A Additional resources A.1 Technical support and resources Dell.com/support is focused on meeting customer needs with proven services and support. Storage Solutions Technical Documents provide expertise that helps to ensure customer success on Dell EMC storage platforms. A.
