Download - All IT eBooks

for this in “Effects of Options” on page 35 and in Chapter 3, particularly in “Calculating
Safe Values for Options” on page 142.
You can check whether mysqld is not swapping by checking vmstat on
Linux/Unix or the Windows Task Manager on Windows.
Here is an example of swapping on Linux. Important parts are in bold.
For a server that is not swapping, all these values should be equal to zero:
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---r b swpd
free buff cache
si
so
bi
bo
in
cs us sy id wa
1 0 1936 296828 7524 5045340
4
11 860
0 470 440 4 2 75 18
0 1 1936 295928 7532 5046432 36
40 860
768 471 455 3 3 75 19
0 1 1936 294840 7532 5047564
4
12 868
0 466 441 3 3 75 19
0 1 1936 293752 7532 5048664
0
0 848
0 461 434 5 2 75 18
In those sections, we discussed how configuration variables can affect memory usage.
The basic rule is to calculate the maximum realistic amount of RAM that will be used
by the MySQL server and to make sure you keep it less than the physical RAM you
have. Having buffers larger than the actual memory size increases the risk of the MySQL
server crashing with an “Out of memory” error.
■ The previous point can be stated the other way around: if you need larger buffers,
buy more RAM. This is always a good practice for growing applications.
■ Use RAM modules that support extended error correction (EEC), so if a bit of
memory is corrupted, the whole MySQL server does not crash.
A few other aspects of memory use that you should consider are listed in a chapter
named “How MySQL Uses Memory” in the MySQL Reference Manual. I won’t repeat
its contents here, because it doesn’t involve any new troubleshooting techniques.
One important point is that when you select a row containing a BLOB column, an
internal buffer grows to the point where it can store this value, and the storage engine
does not return the memory to RAM after the query finishes. You need to run FLUSH
TABLE to free the memory.
Another point concerns differences between 32-bit and 64-bit architectures. Although
the 32-bit ones use a smaller pointer size and thus can save memory, these systems also
contain inherent restrictions on the size of buffers due to addressing limits in the
operating system. Theoretically, the maximum memory available in a 32-bit system is
4GB per process, and it’s actually less on many systems. Therefore, if the buffers you
want to use exceed the size of your 32-bit system, consider switching to a 64-bit
architecture.
148 | Chapter 4: MySQL’s Environment