How to set memory limit in my.cnf file

How to set memory limit in my.cnf file

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Why memory_limit Fails in MySQL Configuration

If you are attempting to adjust the memory available to a MySQL server by adding memory_limit = 64M to your my.cnf file, you are likely encountering a restart failure or seeing the setting ignored. The immediate reason is that this specific configuration directive does not exist within the MySQL Server architecture. Unlike PHP, which exposes a memory_limit variable in its runtime environment and allows it to be tuned via php.ini, MySQL manages its own memory allocation differently.

When you modify the my.cnf file and restart the service, MySQL parses the configuration block under [mysqld]. If it encounters an unknown option like memory_limit, the server will refuse to start, throwing an error regarding an unrecognized system variable or configuration option. There is no global switch in the MariaDB or MySQL source code that accepts a generic "memory limit" parameter to cap total RAM usage for the process.

To effectively control how much memory MySQL consumes, you must configure specific buffers and cache sizes rather than setting a hard ceiling on the entire application memory pool. The configuration file provided in the source material attempts to set memory_limit = 64M, but this line has no functional effect on the server's resource usage and actively prevents the service from starting if not commented out or removed.

Tuning Specific Memory Buffers

Instead of a global limit, MySQL relies on a collection of buffer pools and cache sizes. The most critical setting for controlling overall memory consumption is innodb_buffer_pool_size. This variable dictates how much RAM InnoDB uses to cache data and indexes. If your workload is heavily read-heavy or involves large datasets, this should be the primary focus. You can set this in my.cnf like so:

[mysqld]
innodb_buffer_pool_size = 4G

However, setting a massive buffer pool isn't always the solution if you are hitting memory constraints elsewhere. Other significant buffers include key_buffer_size for MyISAM tables, query_cache_size (though deprecated in newer versions), and connection-level buffers like sort_buffer_size and read_buffer_size. The example configuration shows these set relatively low (e.g., 5M), which is a safe starting point to prevent runaway memory usage per query.

It is also important to distinguish between server variables that are global and those that are session-specific. Some settings, such as max_allowed_packet, limit the size of a single packet sent from a client to the server. While this isn't a "memory limit" for the server itself, exceeding it causes queries to fail immediately.

Understanding Memory Constraints and Trade-offs

A common misconception is that lowering these values will speed up the database or save memory globally. While reducing sort_buffer_size or read_rnd_buffer_size does prevent individual queries from allocating excessive RAM during complex sorts or scans, MySQL has built-in safeguards against infinite growth. For instance, temporary tables created in memory (tmp_table_size) are automatically moved to disk if they exceed the configured limit. Similarly, the InnoDB buffer pool will not grow beyond its defined size.

If you are running on a constrained VPS or shared hosting environment where you must strictly cap MySQL's RAM usage to prevent it from starving other processes, you cannot do this via a simple configuration flag. You must rely on operating system-level controls. This typically involves using ulimit in the systemd service unit file or cgroups (control groups) to enforce a hard memory ceiling on the mysqld process itself.

For example, in a systemd unit file, you might add:

MemoryMax=512M

This ensures that even if your MySQL configuration requests more memory via large buffer sizes, the OS will terminate the process if it attempts to exceed 512MB. This is often the correct approach for resource-constrained environments where my.cnf tuning alone is insufficient.

Practical Configuration Adjustments

Looking back at the provided configuration snippet, the line memory_limit = 64M must be removed or commented out immediately if the server fails to start. The rest of the file contains valid directives like key_buffer, max_connections, and innodb_file_per_table. These are legitimate ways to shape MySQL's behavior.

If you are using Laravel, which often runs on top of PHP and interacts with a MySQL backend, it is vital to keep these two environments distinct. You might see memory_limit set in your php.ini file for the web application logic (e.g., for large file uploads or complex calculations), but this has zero impact on the database layer. Confusing these two settings leads to debugging nightmares where the app crashes due to PHP limits while the database is fine, or vice versa.

Finally, always test your my.cnf changes by restarting the service and checking the error logs before deploying to production. A syntax error in a critical buffer setting can bring down an entire application stack instantly. Remember that MySQL does not have a memory_limit directive; managing resources requires a combination of specific buffer tuning and, if necessary, OS-level constraints.

Our network