A computer can be compared to a living organism, where different parts are responsible for different functions.
This is the backbone and nervous system of any computer. All other parts will plug into it in some fashion. Of note is its bus speed. This is the speed of the front side bus (FSB), which connects the CPU to the Northbridge. This is constantly getting faster, which makes newer computers of a similar price range generally faster than older ones.
The Northbridge is an integrated circuit that connects the CPU, graphics, and memory together.
This is the computer’s brain. Instructions from code flow through the CPU and get processed. Prior to 2001, processors only had one core1. CPU manufacturers such as Intel and AMD were continuously trying to make these faster and faster by increasing the number of cycles that could flow through the CPU per second. The terms megahertz (MHz) and gigahertz (GHz) are used to indicate a million or billion cycles per second, respectively.
Modern CPUs have multiple cores, and the number of these are continuously improving. This allows for better parallelism, which means to run multiple threads at the exact same time.
Before processors had multiple cores, parallelism was not possible2. Concurrency was achieved by rapidly switching between threads. Andrew Gerrand explains it like this, “Concurrency is about dealing with lots of things at once. Parallelism is about doing lots of things at once.”3
This is significant because programming languages created prior to 2001 didn’t have the luxury of parallelism. In 2020, some popular languages are still struggling with the concept. Ruby MRI, because of its infamous Global Interpreter Lock (GIL), is able to achieve concurrency to a degree, but not parallelism.4
In 2011, Intel was still trying to push C and C++ to properly use parallelism.5
This is important because relational databases such as MySQL6 and PostgreSQL7 were first released more than 20 years ago though they’ve been improving parallel queries8 in recent versions.
Databases have to be highly concurrent to be able to process many queries simultaneously.
When a program is first loaded, persistent data is moved from the hard drive into memory. Memory is considered ephemeral, meaning it goes away when the power is shut off. While exact speeds are ever changing, it’s generally accepted that RAM is a lot faster than hard drives (HDDs) and solid state drives (SSDs). Although SSDs are closing the gap, when your data is available in RAM, it is going to be quite a bit faster than if it has to be fetched from disk.
Saving data essentially commits the data to a persistent storage device. Hard drives have spinning plates that are accessed by magnetic arms. Because there are moving parts, it takes longer to fetch data. When all of the information you need is next to each other on the disk, it is able to read it faster. This is why sequential reads and writes are faster than random ones.
Solid State Drives don’t have moving parts and are able to read and write much faster. Sequential reads/writes are still faster than random ones, however.
Naturally, the need for garbage collection affects an SSD’s performance, because any write operation to a “full” disk (one whose initial free space or capacity has been filled at least once) needs to await the availability of new free space created through the garbage collection process. Because garbage collection occurs at the block level, there is also a significant performance difference, depending on whether sequential or random data is involved. Sequential files fill entire blocks, which dramatically simplifies garbage collection. The situation is very different for random data. - Seagate9
SSDs are either connected to the motherboard via a SATA cable or directly plugged in. SATA has gotten faster as new generations are released (SATA 2 and 3), but is currently plateaued at 6GB/s. NVMe SSD drives are connected directly to the motherboard via the M.2 interface and is capable of faster speeds. They are also smaller in size, which allows for better airflow or more devices to be physically present in a computer case.
One of the biggest bottlenecks for a database is how quickly it can read from or write to disk (Disk IO). This is especially true once there is more data than available RAM.
Swap space is a small portion of the hard drive that is used as an overflow buffer if the server needs more memory than it has available RAM.
Using swap is both good and bad. It’s good because the server doesn’t produce an OOM error (Out of Memory) and crash; however, it’s bad because the hard drive is much slower than memory.
When a program fetches data from a hard drive to put into memory and it has to save it to swap, it will read data then immediately write it. This can cause what is known as “thrashing”, which essentially is when the hard drive’s IOPS has reached capacity.
Therefore, it’s good to have a small amount of available swap, but to only be used as a safety net to avoid OOM crashes.
Graphics cards are typically used to render pixels on a screen; however, there are databases that have been specifically designed to take advantage of graphics cards such as OmnisciDB10. Where a CPU might have 2-32 cores, a GPU can have thousands. Being able to maintain massive parallelism has the advantage that these types of databases do not need to deal with creating indices. Instead, they execute highly concurrent table scans.