mysql address space

Address space can be a significant problem on 32-bit mysql installations. After several IRC discussions attempting to explain this to some people, I decided to write this post about it.

What address space is

Each process running on a multitasking virtual memory OS has its own private address space. This is a range of addresses of a fixed size. The exact size depends on the CPU architecture, but on most 32-bit processors it is 32-bits. Intel/AMD chips address memory in bytes individually, so the maximum capacity of the address space is 2^32 bytes.

Address space has nothing whatsoever to do with the physical amount of RAM installed on a machine; you may have more address space than ram, but critically, you may also have less.

It is not possible to modify or upgrade the address space of a machine or software program, except by recompiling it for a different architecture.

How much space is available

Unfortunately, not all of the approximately 4Gb of address space is usable by mysql for buffers; some is used by:

• The operating system kernel reserving address space for its own use
• Code from the mysql executable file and its libraries etc
• Fixed data allocated by mysql and its libraries
  
• Thread stacks
• etc
  

The amount that these use up is extremely system dependent, but it is normally safe to assume that on a 32-bit Linux OS, roughly 1.5Gb is available for mysql's buffers. On some systems there may be a bit more than this, but it's not likely to exceed 3Gb on any platform.

What impact running out has on mysql

MySQL is a multithreaded server process - each instance of mysql running on a machine (there is typically only one; several applications or databases can use the same server). This means that all its threads share the same, limited address space.

The database server can be tuned to use a lot of memory. There are two kinds of buffer - per thread and global buffers. The MyISAM key buffer is an example of a global buffer, the packet read buffer is an example of a per thread buffer. This is explained in more detail in the manual here: How MYSQL uses memory

All this memory comes from the same, limited address space. Running out of address space is very bad. When you run out of address space, the following happens:

• A memory allocation attempt will fail. How gracefully this happens depends on where the allocation attempt was made. It could be: in the mysql core, in a storage engine, in an underlying library (e.g. the C or C++ runtime libraries) or in some other extension.
• MySQL will probably crash. If a crash happens:
  
• The server will restart, kicking all clients off.
• InnoDB will start its recovery process. This may take a long time during which the server will be unusable.
• Eventually all transactions will be rolled back and normal service will resume.
• MyISAM tables which were in use at the time will be marked as crashed and needing a repair - this can take a very long time on large tables, during which time those tables are unusable.

This is very inconvenient for everyone concerned (the app developer, the sysadmin/DBA and especially the end user).

Mitigation: How to prevent running out

The most obvious and convenient solution is to run a 64-bit mysql server on a 64-bit OS. These have a lot more address space and are very unlikely to run out.

If, for some reason, you cannot upgrade to 64-bit, it is a very good idea to ensure that you do not run out of address space. This is mostly done by tuning the server parameters.

• Do not set max_connections higher than necessary.
• Keep per-thread buffers to a minimum
• Tune other buffers reasonably

Monitoring the address space in use by mysql is a good idea. This is normally possible under Linux with "top" or "ps", as the "virt" or "VSZ" columns show the amount of address space used.

Final notes

• Run a 64-bit OS if possible and for all new development. Run a 64-bit mysql if you run a 64-bit OS.
  
• Running out of address space is not the same as running out of memory- it will probably happen a lot sooner (your server does have more than 2Gb of RAM, right?)
• You can still run out of address space if you have less than 2Gb of RAM, because virtual memory is included
  
• None of this is Linux-specific, it will happen on other OSs too (but the amount of space reserved by the kernel may be less)
• Avoiding address space exhaustion is important in production systems