Databases Reference
In-Depth Information
buffer was limited to 4 GB, the size addressable by a 32-bit integer. (You can create
multiple key buffers to work around this, though.)
Make sure you use a 64-bit operating system on your 64-bit hardware! It's less common
these days than it used to be, but for a while most hosting providers would install 32-
bit operating systems on servers even when the servers had 64-bit CPUs. This meant
that you couldn't use a lot of memory: even though some 32-bit systems can support
large amounts of memory, they can't use it as efficiently as a 64-bit system, and no
single process can address more than 4 GB of memory on a 32-bit system.
Scaling to Many CPUs and Cores
One place where multiple CPUs can be quite helpful is an online transaction processing
(OLTP) system. These systems generally do many small operations, which can run on
multiple CPUs because they're coming from multiple connections. In this environment,
concurrency can become a bottleneck. Most web applications fall into this category.
OLTP servers generally use InnoDB, which has some unresolved concurrency issues
with many CPUs. However, it's not just InnoDB that can become a bottleneck: any
shared resource is a potential point of contention. InnoDB gets a lot of attention because
it's the most common storage engine for high-concurrency environments, but MyISAM
is no better when you really stress it, even when you're not changing any data. Many
of the concurrency bottlenecks, such as InnoDB's row-level locks and MyISAM's table
locks, can't be optimized away internally—there's no solution except to do the work
as fast as possible, so the locks can be granted to whatever is waiting for them. It doesn't
matter how many CPUs you have if a single lock is causing them all to wait. Thus, even
some high-concurrency workloads benefit from faster CPUs.
There are actually two types of concurrency problems in databases, and you need dif-
ferent approaches to solve them:
Logical concurrency issues
Contention for resources that are visible to the application, such as table or row
locks. These problems usually require tactics such as changing your application,
using a different storage engine, changing the server configuration, or using dif-
ferent locking hints or transaction isolation levels.
Internal concurrency issues
Contention for resources such as semaphores, access to pages in the InnoDB buffer
pool, and so on. You can try to work around these problems by changing server
settings, changing your operating system, or using different hardware, but you
might just have to live with them. In some cases, using a different storage engine
or a patch to a storage engine can help ease these problems.
The number of CPUs MySQL can use effectively and how it scales under increasing
load—its “scaling pattern”—depend on both the workload and the system architec-
ture. By “system architecture,” we mean the operating system and hardware, not the
