Introduction

Running MySQL on a small Ubuntu AWS instance is mostly a memory-budgeting exercise. The database can run acceptably on a micro instance, but only if you keep global buffers modest and avoid per-connection settings that multiply into unexpected RAM usage.

The right approach is not to disable random features blindly. First identify where memory goes, then trim the settings that have the highest impact on a low-memory host.

Understand Global Buffers Versus Per-Connection Buffers

MySQL memory use is split roughly into two categories:

• global allocations, which are reserved once for the server
• per-connection allocations, which can grow with each active client

On a tiny instance, per-connection settings often cause the biggest surprise. A single sort_buffer_size that looks harmless becomes expensive when many connections allocate it at once.

You can inspect a few important values directly:

mysql -e "SHOW VARIABLES WHERE Variable_name IN ('max_connections','innodb_buffer_pool_size','tmp_table_size','max_heap_table_size','sort_buffer_size','join_buffer_size');"

If the server is already swapping, fix the configuration before chasing query-level tuning. A micro instance usually cannot tolerate aggressive defaults.

Start With a Conservative MySQL Configuration

For a small single-purpose host, a lean mysqld configuration often looks like this:

1[mysqld]
2innodb_buffer_pool_size = 128M
3innodb_buffer_pool_instances = 1
4innodb_log_buffer_size = 8M
5max_connections = 20
6table_open_cache = 128
7tmp_table_size = 16M
8max_heap_table_size = 16M
9sort_buffer_size = 256K
10join_buffer_size = 256K
11read_buffer_size = 128K
12read_rnd_buffer_size = 256K
13thread_cache_size = 8
14performance_schema = OFF
15skip-name-resolve = ON

This is not a universal template, but it shows the general shape of a low-memory setup:

• keep the InnoDB buffer pool much smaller than total system RAM
• cap connection count aggressively
• keep per-connection buffers small
• disable optional instrumentation if you do not need it

After editing the config file, restart MySQL and verify that it came back cleanly:

sudo systemctl restart mysql
sudo systemctl status mysql --no-pager

Prioritize the Biggest Memory Consumers

On modern MySQL, innodb_buffer_pool_size is usually the largest single global allocation. That makes it the first knob to adjust.

For a micro instance, a good pattern is:

• reserve RAM for the operating system first
• leave space for filesystem cache and background services
• assign only the remaining safe portion to MySQL

max_connections is the next setting to challenge. If the application never needs 151 concurrent sessions, do not leave the default just because it exists. Lowering the cap reduces the worst-case memory ceiling and makes failures more predictable.

You should also review application connection pooling. A tiny server works much better with a small, steady connection pool than with large bursts of short-lived connections.

Measure Real Memory Pressure

Do not tune blindly. Check whether the kernel is under pressure and whether MySQL is the main consumer:

free -m
ps -o pid,rss,command -C mysqld
vmstat 1 5

These commands help answer three different questions:

• how much RAM is free right now
• how large the MySQL process is
• whether the system is actively swapping

If vmstat shows swap activity and high wait times, your instance is too close to the edge. At that point, configuration tuning may buy time, but a larger instance size might be the more honest fix.

Keep Query Behavior Cheap

Some memory pressure comes from workload rather than server settings. Large sorts, joins without useful indexes, and temporary tables can force MySQL to allocate buffers repeatedly.

A basic indexing review can reduce memory spikes more effectively than another round of buffer tuning. For example, a query that stops creating large temporary tables may free far more RAM than shaving a few megabytes off tmp_table_size.

You can identify expensive queries with:

SHOW FULL PROCESSLIST;
EXPLAIN SELECT * FROM your_table WHERE indexed_column = 42;

That is why low-memory tuning should always combine configuration work with query review.

Common Pitfalls

• Shrinking every buffer at once without understanding which ones are global and which ones multiply per connection.
• Leaving max_connections high on a server that only serves one small application.
• Tuning only MySQL while ignoring swap usage and overall host pressure.
• Using old advice about query_cache_size on modern MySQL versions where query cache is removed or irrelevant.
• Forgetting that a micro instance may simply be too small for the workload, even after careful tuning.

Summary

• Reduce MySQL memory use by focusing first on innodb_buffer_pool_size and max_connections.
• Keep per-connection buffers small because they scale with concurrent clients.
• Disable optional features such as performance_schema if you do not need them on a tiny host.
• Measure memory pressure with free, ps, and vmstat before and after each change.
• If the server still swaps under normal load, the real fix may be a larger instance rather than more aggressive tuning.