how to speed up a database - hardware only

Solution 1:

What's the performance bottleneck? The usual culprits are:

• If the system is I/O bound, adding more CPUs won't help. Adding more memory might increase the portion of the database that can be cached in memory, but the best way to improve performance will be to increase the I/O bandwidth.
• If the system is CPU bound, then adding more cores will help if the DBMS software can make good use of them. Otherwise, increasing the speed of the CPUs will help.
• If the system is memory bound - the bottleneck is getting the data from RAM to the CPU - then improving the memory bandwidth will improve the performance.

Note that when you manage to relieve one performance bottleneck, one of the others becomes the new performance bottleneck.

On most systems running database with seriously large data volumes being scanned, the system is I/O bound. Adding more disk controllers and spreading the data across them increases the parallelism available for disk I/O, usually improving performance.

Don't forget that the most dramatic improvements may be algorithmic - if you can answer the question two ways, one scanning 1 TB of data and one scanning just 1 KB, the second is going to outperform the first, regardless of what you do with the hardware.

Elaborating on disk controllers - as requested.

A given disk controller can only transfer so much data from disk to memory in a second, typically using DMA. To increase the bandwidth, you can spread your data over multiple disks controlled by separate controllers. Then, if the system architecture allows it, you can get more data per second from disks into the main memory - thus speeding up operations. Note that if all the data in the database is on a single disk, then extra controllers do not help. If the DBMS cannot trigger parallel reading from the separate disks, then extra controllers do not help. So, whether the extra controllers will help depends on your hardware and your software and the organization of the data in the database.

Solution 2:

1. RAM
2. Faster CPU
3. Store the database on a RAID0 (or RAID10 if redundancy is needed) with as many drives as you can.
4. Multicore processors.
5. Increased sizes of L0 and L1 caches.