5. Setting Up Memory for the Operating System
Calculating the Memory Required for the Operating System
Before allocating memory to MySQL, it is important to ensure that the operating system (OS) has enough memory to perform its basic tasks. If the operating system is low on memory, this can lead to serious performance issues, including swapping memory to disk, which can drastically slow down MySQL performance.
To calculate the memory required for the operating system, consider the following factors:
OS Base Memory:
The operating system requires a basic amount of memory to run core processes and services. On Linux systems, this typically ranges from 200MB to 1GB, depending on the distribution and configuration.
Additional Processes and Services:
If the server is running additional services such as web servers (Apache/Nginx), application servers, or monitoring services, additional memory should be allocated to them.
OS Buffer and Cache:
Operating systems use buffers and caches to improve I/O performance. For example, Linux uses file caching to store frequently accessed data. This requires sufficient additional memory.
In general, a good rule of thumb is to leave about 20-25% of the total physical memory for the operating system and other services. For example, on a server with 16GB of RAM:
Memory for OS = 0.20 × 16GB = 3.2GBAvoiding Virtual Memory to Disk Swapping
Swapping is the process by which data from physical memory is moved to disk when physical memory is full. This can be very detrimental to MySQL performance because disk access is much slower than memory access. To avoid swapping, the following steps can be taken:
Adjusting Swappiness:
On Linux systems, the swappiness parameter determines how aggressively the kernel will use swaps. The swappiness value can be adjusted to reduce the use of swaps. Lower values (e.g., 10) reduce the system’s tendency to use swaps:
sudo sysctl vm.swappiness=10To make these changes permanent, add them to /etc/sysctl.conf:
vm.swappiness=10Memory Usage Monitoring:
Use monitoring tools to keep an eye on system memory usage and swaps. Tools like htop, free, or graphics monitoring like Grafana can help monitor memory usage in real time.
Allocating Memory Wisely for MySQL:
Make sure that the MySQL configuration does not use all of the physical memory. Leave enough memory for the operating system and other services. For example, if you have 16GB of RAM, and have calculated that the OS needs about 3.2GB, then allocate about 12-13GB for MySQL.
MySQL Memory Usage Optimization:
As explained earlier, adjust MySQL memory parameters such as innodb_buffer_pool_size, key_buffer_size, and query_cache_size to ensure that the total MySQL memory usage does not exceed the available memory.
6. Setting Memory for Cache
Cache is an important component in MySQL that helps improve performance by storing frequently accessed data in memory, thereby reducing the need to access slower disks. By using caching effectively, you can improve query speed and reduce the load on the storage system. The two main types of caches used in MySQL are MyISAM cache and InnoDB cache.
Other Interesting Articles
Setting Up Cache for MyISAM and InnoDB
MyISAM Cache
MyISAM is an older MySQL storage engine and uses key_buffer_size as the main parameter to set the size of its index cache. This parameter is very important because cached indexes can be accessed much faster than indexes that need to be read from disk.
key_buffer_size: Specifies the cache size for the MyISAM index. Setting this size too small can lead to frequent slow disk access while setting it too large can leave a bit of memory for other needs. As a general rule, key_buffer_size should be set between 25-30% of the total physical memory on a server that only uses MyISAM.
Example settings in the MySQL configuration file (my.cnf):
[mysqld]
key_buffer_size = 4GInnoDB Cache
InnoDB is the default and most widely used storage engine in MySQL. InnoDB uses innodb_buffer_pool_size to set up the main cache that stores the data and indexes of InnoDB tables.
innodb_buffer_pool_size: Specifies the size of the buffer pool used to store data and indexes. This setting is critical to InnoDB’s performance because frequently accessed data is stored in a buffer pool, reducing disk access. As a general rule, innodb_buffer_pool_size should be set between 60-80% of the total physical memory on a server that only uses InnoDB.
Example settings in the MySQL configuration file (my.cnf):
[mysqld]
innodb_buffer_pool_size = 12Ginnodb_buffer_pool_instances: If the buffer pool is large (more than 1GB), you can split it into multiple instances to increase the concurrency. For example, if innodb_buffer_pool_size is set to 12GB, you can split it into 4 instances of 3GB each:
[mysqld]
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 4Query Cache (General)
MySQL also has a query cache that stores the results of frequently repeated queries to speed up the execution time of subsequent queries. However, in the latest version, the query cache is considered obsolete and has been removed in MySQL 8.0 due to performance issues in environments with a lot of writes.
query_cache_size: If you are using an older version of MySQL, you can set the cache query size with this parameter. However, we recommend using app-level caching or cache proxies for better management.
[mysqld]
query_cache_size = 256M