Hardware for Data Storage

In the previous lecture we discussed interfaces for traditional data devices, and introduced the concept of RAIDing drives to support reliability and to increase data transfer speeds. Lets discuss how our systems and MySQL help further improve performance.

In your computer, data is stored long term in persistent storage using Hard Disk Drives and/or Solid State Drives. These devices provide data to the CPU at the rates that were mentioned in the previous class, but these speeds are considerably slower than when we use memory that is connected directly to the motherboard or cache that is attached to the CPU itself.

Random Access Memory

Random Access Memory (RAM) in a computer is a form of volatile (non-persistent) storage that is used to store data and machine code currently being used or processed by the CPU.

When a program is run, data is transferred from the hard drive to RAM. This data includes the program's executable code and any files or data it needs to access. The CPU accesses this data from RAM for processing since accessing data from RAM is much faster than from a hard drive or SSD. Since RAM is volatile, it's constantly refreshed to maintain the data. When new tasks are started, old data is overwritten.

RAM is typically installed in modules, such as DIMMs (for desktops) or SO-DIMMs (for laptops). The amount of RAM in a computer can significantly affect its performance, especially in tasks that require a lot of data to be stored temporarily. Modern systems tend to support dual or quad-channel mode, which allows two (or four) RAM modules to workin in parallel which will double (or quadruple) performance.

Memory comes in different speed grades, often labeled as DDR3-800, DDR3-1066, DDR3-1333, DDR3-1600, DDR3-1866, and DDR3-2133. The number after "DDR3-" represents the maximum transfer rate in megatransfers per second (MT/s). For example, DDR3-1600 refers to a peak transfer rate of 1600 MT/s. The actual data rate in megabytes per second (MB/s) can be calculated by multiplying the transfer rate by 8 (since DDR memory transfers 8 bytes per cycle). For instance, DDR3-1600 has a peak data rate of 1600 × 8 = 12,800 MB/s.. Remember that this is per module, and systems use their memory in parallel.

CPU Cache

The cache on a CPU is a small amount of very fast memory located on or very close to the CPU chip. It is used to temporarily store instructions and data that the CPU is likely to reuse. This improves the efficiency and speed of data access, as accessing data from the cache is much faster than fetching it from the main memory (RAM).

Types of CPU cache

• L1 Cache: The fastest and smallest, typically split into two parts - one for data and the other for instructions, runs at the speed of the CPU itself!
• L2 Cache: Slower but larger than L1, it serves as a bridge between the fast L1 cache and the slower L3 cache.
• L3 Cache: The slowest and largest, shared among cores in multi-core processors.

Process

• Fetching: When the CPU needs data, it first checks the L1 cache. If the data is not there (a 'cache miss'), it checks the L2 cache, and then the L3 cache if necessary.
• Storing: If the data is found (a 'cache hit'), it is used immediately. If not, the CPU fetches it from the main memory and stores a copy in the cache for future access.
• Cache Replacement Algorithms: When the cache is full and new data needs to be loaded, the CPU uses algorithms to decide which data to replace. Common algorithms include Least Recently Used (LRU) and First In First Out (FIFO).

Write Policies

• Write-Through: Data is written to both the cache and the main memory simultaneously.
• Write-Back: Data is initially written only to the cache. The write to the main memory is delayed until the cache block is replaced.

Pro's and Con's

Some of the benefits of a CPU cache include reducing the average time to access data and decreasing the load on the main memory bus of the computer. There are limitations though, since a larger cache will consume more power and surface area on the CPU, and after a certain point increasing the size of a cache does not proportionaly increase the performance.

The graph above illustrates a hypothetical relationship between cache size and cache hit rate. In this example, as the cache size increases (measured in megabytes), the cache hit rate (measured in percentage) also increases. However, the relationship is not linear; the rate of increase in hit rate diminishes as the cache size becomes larger, indicating a plateau effect.

This kind of behavior is typical in real-world scenarios. Initially, increasing the cache size significantly improves the hit rate, but beyond a certain point, the returns diminish. This plateau effect reflects the principle of locality: most programs tend to access only a limited set of data and instructions repeatedly. Once the cache is large enough to hold this set, further increases in cache size result in smaller improvements in hit rate.

Caching in MySQL

MySQL implements caching to improve performance and efficiency, with various places in the pipeline where we can use this to our advantage.

1. Query Cache - The query cache stores the text of a SELECT statement along with the corresponding result set. When an identical query is received later, MySQL can return the stored result set immediately without re-executing the query. It's important to note that the query cache in MySQL has some limitations. It is sensitive to table changes; any modification to a table causes all cache entries associated with that table to be invalidated. This can limit the usefulness of the query cache in databases with frequent write operations.
2. InnoDB Buffer Pool - This caches data pages (actual table data) and index pages, which significantly reduces disk I/O by keeping frequently accessed data in memory. The size of the buffer pool is configurable and can have a substantial impact on system performance, especially for read-intensive operations. It's generally recommended to allocate as much memory to the buffer pool as is feasible, considering the total system memory and other processes' needs.
3. Table Cache - This cache stores file descriptors, table structures, and other information about open tables. The effectiveness of the table cache can be particularly noticeable in environments with a large number of tables or frequent table opening and closing.
4. Adaptive Hash Index - InnoDB uses an adaptive hash index that builds hash indexes on frequently accessed pages in the buffer pool to speed up point queries (queries that look up a single row). The system monitors which pages are most frequently accessed, and if it notices that queries could benefit from a hash index, it automatically builds one.

Storage Engines

MySQL supports multiple storage engines, these define specific methods on which to store and access information. We will discuss two such engines, the default InnoDB and the Memory engine.

InnoDB

1. Durability and Reliability: InnoDB is a durable and reliable storage engine. It supports ACID (Atomicity, Consistency, Isolation, Durability) properties and uses transactional logs for data recovery. This makes it suitable for applications where data integrity is critical.
2. Row-level Locking and Transactions: InnoDB supports row-level locking and transactions, which is beneficial for high-concurrency environments. It allows multiple users to read and write simultaneously without significant interference.
3. Crash Recovery: InnoDB provides automatic crash recovery through its log files.
4. Buffer Pool for Caching: It uses a buffer pool to cache data and indexes in memory.
5. Full-text Search Indexes: Recent versions of InnoDB include support for full-text search indexes.

Memory Engine

1. Speed: The MEMORY storage engine stores data in memory, which makes data access significantly faster than disk-based storage engines like InnoDB. However, this speed comes at the cost of volatility.
2. Volatility: Data stored in MEMORY tables is lost when the database server restarts or crashes. Therefore, it's only suitable for temporary data or data that can be easily reconstructed.
3. Limited by Memory Size: The size of MEMORY tables is limited by the amount of physical memory available and the MySQL configuration.
4. Table-level Locking: MEMORY uses table-level locking rather than row-level locking, which can be a limitation in high-concurrency environments.
5. No Foreign Key Support: It does not support foreign keys or ensure referential integrity.
6. No Transaction Support: MEMORY does not support transactions, which makes it unsuitable for applications requiring ACID compliance.
7. Use Cases: Often used for read-heavy, temporary data like session management or caching, where the loss of data on restart is acceptable.

Considering the descriptions of the two database engines above, you could imagine that nearly all of your data would be maintained in the default InnoDB engine, and for tables that include session data (authentication, where someone is in a website, etc) that a memory table could be used. In most web environments the state is refreshed each time a user visits a new page.

Example Usage

Creating a table using a different engine is as simple as specifying the engine in the CREATE TABLE statement. Lets see a couple quick examples from the MySQL Documentation.

CREATE TABLE tablename (
  fieldOne INT
) ENGINE=MEMORY;

CREATE TABLE tablename ENGINE=MEMORY
  SELECT * FROM othertable;

CREATE TABLE tablename (
  fieldOne INT,
  fieldTwo INT,
  INDEX USING HASH (fieldOne)
) ENGINE=MEMORY;

CREATE TABLE tablename (
  fieldOne INT,
  fieldTwo INT,
  INDEX USING BTREE (fieldOne)
) ENGINE=MEMORY;

Practice Problems

1. The Data Science file server nfs.cs.usna.edu, has a /home drive that was constructed from 9 drives in RAID6, this drive is mounted as /home on every lab workstation as well as ssh.cs.usna.edu and desktop.cs.usna.edu.
   • Bring up a terminal on your workstation (or ssh into ssh.cs.usna.edu) and type the following to show all of the mounted drives on the machine.
     
     

     df -BG

   • You will see something like
     
     

     Filesystem                    1G-blocks  Used Available Use% Mounted on
     tmpfs                                2G    1G        2G   1% /run
     /dev/sda2                          234G   41G      181G  19% /
     tmpfs                                8G    1G        8G   1% /dev/shm
     tmpfs                                1G    1G        1G   1% /run/lock
     /dev/sda1                            1G    1G        1G   2% /boot/efi
     nfs.cs.usna.edu:/home            ?????G  ???G    ?????G  ??% /home

   • Under the 1G-blocks column you will see the total available space in this drive pool. Since this system has 9 drives in RAID6, what is the size of each of the 9 drives?
2. Each of the drives on nfs.cs.usna.edu is a solid state drive, so lets assume that we can get a sustained write speed of 500MB/s, lets imagine that our RAID controller adds no latency and can stripe across the data drives perfectly. What is the maximum possible write speed to this array?
3. The Data Science backup server, backups.cs.usna.edu, which you can see mounted on ssh.cs.usna.edu as
   
   

   Filesystem                           1G-blocks   Used Available Use% Mounted on
   backups.cs.usna.edu:/snapshot/shared   204081G 13097G   190984G   7% /backups 

   Has an array of 12 Hard Disk Drives (with about 150MB/s sustained write speeds) in RAID6. What are the sizes of the individual drives and whats the total possible throughput on the system?
4. The Data Science major has multiple virtualization servers, you may not have ever connected to them at this point, their drive configuration is a bit more since they are using NVMe (PCI Gen 3) drives. These machines have 24 U.2 NVMe Drives in the equivalent of a RAID0! What is the maximum possible throughput across this array?
5. Our file server is connected to the Academy Network via a 1Gb/s network port, Are we able to fully utilize the throughput of our server? What about when the network gets upgraded to 10Gb/s in the next year? Our file server is connected to our backup server via a seperate 40Gb/s link, how does that compare?
6. Walk through the process, as you would expect it to flow from a row of data stored on a drive, through each step as the CPU would read it in and compute the results.