In the case of RAID, parity bits are used to ensure the integrity of the data stored on each drive. For example, we can store really important data, such as host information and logs, on a RAID 0+1 or RAID N disk, whereas we can store semi-important data on a RAID 0 disk. Depending on how critical the type of data is, we may vary the RAID level. There is another approach to it, as well.With a good volume management software, we can manage this setup efficiently. This approach needs 2 x N drives to hold N drives of data. RAID stands for Redundant Array of Independent Drives. To gain reliability, we may decide to set up with striping and mirroring (RAID 0+1). We have to choose if we want to optimize for random access or sequential access. The difference in performance also depends on the number of disks. The speed difference while striping depends on the parameters we have used, like stripe size. In an ideal case, we would benchmark the application with different stripe sizes. Striping is dependent on the stripe size and the operating system. This will help to improve the performance. If the stripe size is perfectly aligned, the normal data size will be less than the stripe size. The N block on the (N % number of-disks) disk.

We put the first block on the first disk, the second block on the second disk, and so on. Striping: In striping, we have many disks.However, we can place InnoDB data and log files on different physical disks. Symbolic links are not supported for InnoDB tables. The assumption is that the disk is not used concurrently for other purposes. The symlink points from default locations in the data directory to another disk in the case of MyISAM tables. Using symbolic links: When using symbolic links, we can create a Unix symbolic links for index and data files.In order to resolve the disk seek performance issue, increasing the number of available disk spindles, symlinking the files to different disks, or stripping disks can be done. We should regulate or minimize the disk seek times using appropriate disks. We need at least one disk seek operation to read, and several disk seek operations to write things in large databases where the data access is done more or less randomly. When the amount of data grows large enough to make caching impossible, the problem with disk seeds becomes apparent. A major performance bottleneck is disk seeking (finding the correct place on the disk to read or write content). There are quite a few ways to configure storage devices to devote more and faster storage hardware to the database server. In this book, authors have presented hands-on techniques for tackling the common and not-so-common issues when it comes to the different administration-related tasks in MySQL 8.

The following excerpt is taken from the book MySQL 8 Administrator’s Guide, written by Chintan Mehta, Ankit Bhavsar, Hetal Oza and Subhash Shah. It would also help developers prepare scripts (which includes setting up the database) and users run MySQL for development and testing to maximize the productivity. It is more relevant to database administrators, to ensure performance and scalability across multiple servers. Our article focuses on optimization for MySQL 8 database servers and clients, we start with optimizing the server, followed by optimizing MySQL 8 client-side entities.

Mysql optimizer settings