There is no universal “best” my.cnf for a 1GB or 2GB Ubuntu server. The safe approach is to budget memory for Ubuntu, web and PHP workers, MySQL’s global structures, and connection-specific buffers before choosing values. A conservative starting point is often a 256MB InnoDB buffer pool and 25 connections on a shared 1GB host; a 2GB host may start around 512MB, but only after you account for the other services running there.
Use the examples below as testable baselines, not copy-and-paste defaults. Measure resident memory, swap, concurrent connections, temporary-table spills, file-descriptor use, and query latency after each change. If memory pressure appears, lower connection and per-thread limits before enlarging caches.
As an Amazon Associate I earn from qualifying purchases.
A safe starting configuration
Create or edit the [mysqld] section in the configuration file used by your Ubuntu package (commonly a file under /etc/mysql/mysql.conf.d/; layouts vary by release). This conservative pattern is intended for a small shared host:
[mysqld]
# Recalculate this after reserving RAM for Ubuntu and other services.
innodb_buffer_pool_size = 256M
# Match this to the application's real pool and peak concurrency.
max_connections = 25
# Keep connection-specific working memory modest.
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256K
join_buffer_size = 256K
# Keep internal temporary tables bounded.
tmp_table_size = 16M
max_heap_table_size = 16M
# Raise gradually only if file-descriptor headroom exists.
table_open_cache = 512Every number is an example. The buffer pool must be derived from the host’s actual free memory, and max_connections must reflect the application’s pool size and observed peak, not a round number chosen for comfort.
#1 Best Overall
Why low-RAM tuning is a memory-budget problem
MySQL is only one consumer of a small VPS. Ubuntu itself needs memory for the kernel, systemd services, SSH, filesystem cache, and background jobs. A web server, PHP workers, queue consumer, monitoring agent, or game-backend process can consume more RAM than the database. MySQL then adds global allocations such as the InnoDB buffer pool and per-connection allocations that multiply with concurrency.
Oracle’s MySQL documentation says the default configuration is designed to let a server start on a virtual machine with approximately 512MB of RAM. That statement describes startup, not a comfortable production workload. The same documentation gives 50–75 percent of system memory as a typical buffer-pool range for a dedicated database server. On a shared 1GB or 2GB Ubuntu machine, that range is an upper reference, not a promise that the host will remain stable.
Think in terms of a hard budget:
- Operating-system reserve: memory for Ubuntu, kernel activity, SSH, filesystem cache, and essential services.
- Co-resident applications: web/PHP workers, reverse proxies, queues, game services, agents, and scheduled jobs.
- MySQL global memory: the InnoDB buffer pool, data dictionaries, performance instrumentation, and other caches.
- Connection-driven memory: thread stacks, sort, read, join, read-rnd, and binlog buffers, plus temporary tables.
- Safety margin: enough unused RAM to absorb traffic bursts without immediate swapping or an out-of-memory kill.
If the sum is close to physical RAM, the configuration is already too aggressive. Swap is a warning signal, not extra capacity: sustained swap-in and swap-out usually means the working set is larger than the machine can support.
Choosing innodb_buffer_pool_size
The InnoDB buffer pool caches table data and indexes and is normally the largest MySQL allocation. A larger pool can reduce disk reads, but on a small shared host it can evict Ubuntu and application memory. Start with the amount left after reserving the non-MySQL budget, then increase only when measurements show both cache benefit and safe headroom.
Reference values and limits
- In the cited MySQL 8.0 reference, the default is
134217728bytes (128MB). - The documented minimum is
5242880bytes. - For a dedicated database server, MySQL describes 50–75 percent of system memory as a typical recommendation. A shared low-memory Ubuntu host generally needs a substantially smaller percentage.
- The pool can be resized dynamically on versions that support online resizing, but test any change under representative load rather than assuming a live resize is harmless.
Illustrative 1GB and 2GB starting points
The following figures are workload-dependent examples, not vendor defaults. They assume Ubuntu and at least one other service share the machine. A database-only host with a small working set may tolerate more; a host with many workers may need less.
| Host RAM | Illustrative first pool | Why this is conservative | What to verify before increasing |
|---|---|---|---|
| 1GB | 256MB | Leaves room for Ubuntu, a web/PHP stack, connection overhead, and filesystem cache. | Resident memory stays below physical RAM, swap remains inactive, and latency improves when the pool grows. |
| 2GB | 512MB | Provides more cache while preserving a large reserve for workers and bursts. | Peak worker count, connection memory, temporary-table usage, and swap remain within the remaining budget. |
| 2GB with almost no co-resident work | 512–768MB, tested incrementally | A larger pool may be reasonable only when the database is effectively the main service. | File cache, system services, and peak MySQL allocations still have measured headroom. |
Do not select 75 percent merely because the host has 2GB. On a 2GB server running a web application, 1.5GB in the pool can leave too little for workers, thread memory, and the operating system.
Relate the pool to the working set
If the frequently accessed portion of the dataset is smaller than the proposed pool, a larger value may not improve latency. If the hot data is much larger, increasing the pool can help only if the host has spare RAM. Watch buffer-pool hit behavior together with disk I/O and query latency; a cache increase that triggers swapping is a regression.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallControlling max_connections and per-thread memory
max_connections is a ceiling, not a target throughput number. Every possible connection adds potential thread and work buffers, so a high limit creates a latent worst-case memory commitment even when most connections are idle. Set it from the application’s actual pool size, queue behavior, and measured peak.
Use the application’s concurrency
- Find the maximum number of database sessions the application can open, including all web workers, background jobs, maintenance tasks, and replicas or proxies that connect to this instance.
- Measure MySQL’s peak with
SHOW GLOBAL STATUS LIKE 'Max_used_connections';during representative traffic. - Add a small operational margin, then set
max_connectionsnear that observed requirement rather than several hundred by default. - If bursts exceed the limit, fix pooling, queueing, or request fan-out before simply raising the ceiling.
A starting value of 25 in the sample configuration is appropriate only when the application’s real concurrency fits it. A service that genuinely needs 80 simultaneous sessions requires a different memory budget; lowering the number without changing the application will produce connection errors.
Keep working buffers modest
The global values below are intentionally small for a constrained host:
| Variable | Sample value | Purpose and caution |
|---|---|---|
sort_buffer_size |
256K | Per-connection sort workspace. Raising it globally to speed one query multiplies the worst case. |
read_buffer_size |
128K | Sequential-read buffer used by eligible operations. |
read_rnd_buffer_size |
256K | Read-after-sort workspace; keep it modest on a small host. |
join_buffer_size |
256K | Used for joins that cannot use an index. Fixing missing indexes is usually safer than making this globally large. |
These buffers are allocated as needed, so the machine may not consume the full theoretical maximum at all times. Capacity planning must nevertheless account for concurrent sessions using them simultaneously.
The documented estimate
MySQL’s startup guidance presents an estimate containing innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size + read_buffer_size + binlog_cache_size) + max_connections*2MB. The manual notes that the formula must be adapted for the MySQL version and configuration. Treat it as a warning about multiplication, not as a precise resident-memory prediction: metadata, performance instrumentation, temporary tables, thread stacks, plugins, and the operating system add more usage.
Bounding internal temporary tables
tmp_table_size and max_heap_table_size limit the size of in-memory internal temporary tables. Set them to the same conservative value so a query cannot obtain a larger allowance through the other variable. The sample uses 16MB for each.
A small limit can cause a query to spill to an on-disk temporary table. That costs I/O and can increase latency, but it is usually safer than allowing many concurrent sessions to reserve large amounts of RAM. Do not raise both variables globally to fix one report query. First inspect the query plan, indexes, grouping, sorting, and result size; optimize the expensive statement or apply a session-level setting for a controlled job when appropriate.
Track Created_tmp_tables and Created_tmp_disk_tables with SHOW GLOBAL STATUS LIKE 'Created_tmp%';. A rising disk-table ratio is a prompt to investigate workload and storage performance, not an automatic instruction to double the limits.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Setting table_open_cache without exhausting file descriptors
table_open_cache controls how many table definitions MySQL can keep open. Too low a value can cause repeated table opens; too high a value consumes file descriptors and memory. Ubuntu guidance recommends increasing it gradually while checking the operating system’s descriptor limit.
- Record
Opened_tables,Open_tables, andOpened_table_definitionswithSHOW GLOBAL STATUS LIKE 'Opened%';andSHOW GLOBAL STATUS LIKE 'Open_tables';. - Check the service’s file-descriptor limit and current usage before changing the cache. Systemd limits and package defaults differ by Ubuntu release.
- Start at a moderate value such as 512, then raise it only when counters show churn and descriptor headroom remains.
- After each increase, watch for “Too many open files,” failed connections, or a rising resident set.
A larger table cache cannot compensate for poor schema design or an application that opens excessive connections. Stop increasing it when table-open churn is no longer material.
Options that should remain conservative
innodb_buffer_pool_instances
MySQL associates multiple buffer-pool instances with pools of at least 1GB and with scalability on larger pools. For a small pool such as 256MB or 512MB, keep the default or use one instance unless measurements demonstrate contention that the change actually resolves. Splitting a small pool adds management overhead without creating more memory.
Rank #4
innodb_dedicated_server
Do not enable this automatically on a shared VPS. MySQL documents the option for an instance on a dedicated server that can use all available system resources. Explicitly setting the pool and related limits is safer when Ubuntu, a web server, or workers share the machine.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsExample budgets for 1GB and 2GB hosts
Use a worksheet rather than a template. The numbers below show how the same setting can be safe in one layout and unsafe in another.
| Budget item | Illustrative 1GB shared host | Illustrative 2GB shared host |
|---|---|---|
| Physical RAM | 1024MB | 2048MB |
| Ubuntu and essential services reserve | At least 256MB, measured | At least 384MB, measured |
| Web/PHP or other co-resident workers | Reserve their measured peak before sizing MySQL | Reserve their measured peak before sizing MySQL |
| Initial InnoDB pool | 256MB example | 512MB example |
| Connection ceiling | 25 only if observed concurrency fits | 40–60 only if observed concurrency and memory fit |
| Temporary-table limits | 16MB each as a test value | 16–32MB each, workload dependent |
| Remaining headroom | Must cover per-thread use, filesystem cache, bursts, and monitoring | Must cover per-thread use, filesystem cache, bursts, and monitoring |
The 2GB connection and temporary-table figures are illustrative planning values, not documented universal recommendations. If the application has many workers, use fewer connections or smaller limits. If the database is the only substantial service, a larger pool may be justified after observation.
How to apply a change safely on Ubuntu
- Identify the active configuration. Inspect the package’s included files and confirm which file contains the active
[mysqld]section. Do not assume a path copied from another Ubuntu release is authoritative. - Record a baseline. Save
free -h,vmstat 1during a representative interval, MySQL’sThreads_connectedandMax_used_connections, query latency, and temporary-table counters. - Change one logical group. For example, adjust the buffer pool alone, or adjust connection and per-thread limits together. Keep a dated copy of the previous file.
- Check syntax and restart deliberately. Use the service’s normal Ubuntu systemd unit, commonly
sudo systemctl restart mysql, and immediately inspectsudo systemctl status mysqland recent logs if startup fails. A restart interrupts active sessions, so schedule it for a maintenance window. - Observe representative traffic. Check resident memory, swap-in and swap-out, connection peaks, temporary-table spills, table-open counters, file-descriptor errors, and application latency.
- Keep or revert based on evidence. If latency improves without shrinking safety headroom, retain the change. If swap or OOM events appear, revert the last increase and reduce connection-driven memory first.
Some MySQL variables are dynamic and others require a restart; support differs by version. Verify the variable’s behavior in the reference for the exact MySQL release installed on the server instead of assuming that every edit can be applied online.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Diagnosing common failure modes
MySQL is killed by the OOM killer
- Confirm the event in the kernel or service logs.
- Reduce
max_connectionsto the application’s measured requirement. - Lower globally oversized sort, read, join, and temporary-table limits.
- Reduce the buffer pool until Ubuntu and co-resident services retain headroom.
- Check for a worker-process leak or an unexpected batch job; changing MySQL alone may not solve the cause.
The server starts swapping after increasing max_connections
Undo the increase first. A larger ceiling multiplies potential thread memory even if only some sessions are active, and a connection storm can make every buffer relevant at once. Fix pooling and request fan-out, then retest with a lower ceiling.
Queries became slower after lowering temporary-table limits
Inspect the increase in Created_tmp_disk_tables, identify the statements responsible, and optimize their indexes and execution plans. Raise limits only for a proven workload and only after calculating the concurrent-memory cost.
Best Value
Table-open errors appear after raising table_open_cache
Lower the cache, inspect the systemd file-descriptor limit, and check for “Too many open files” in the MySQL log. Increase descriptor capacity and table cache gradually, not both blindly at once.
A larger buffer pool did not improve latency
Compare the hot working set, disk I/O, query plans, and swap activity. If the workload is CPU-bound, index-bound, or limited by another service, more cache will not help. Revert the increase when gains flatten or memory safety worsens.
What to monitor continuously
- Resident memory and available memory: watch the MySQL process and the whole host, not just the database’s internal counters.
- Swap activity: occasional allocation is different from sustained swap-in and swap-out during normal traffic.
- Connections: compare
Threads_connectedwithMax_used_connectionsand the application pool’s configured maximum. - Temporary tables: track memory versus disk-created temporary tables and correlate spikes with specific jobs.
- Table cache: watch opened-table counters and descriptor errors before raising the cache.
- Latency and throughput: a cache hit improvement is not useful if request latency rises because the host is reclaiming memory.
Version and package caveats
The figures and behavior cited here come from Oracle MySQL 8.0/8.4 reference material and current Ubuntu documentation. Defaults, deprecated variables, systemd limits, included configuration files, and dynamic-variable support vary among MySQL and Ubuntu releases. Check the exact server version with SELECT VERSION(); and consult that release’s variable reference before applying a setting.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Do not mix a configuration written for a different major release into a production server without checking whether each variable still exists and whether its units or allocation behavior changed. Keep a rollback copy and test startup after every edited group.
When tuning is no longer the right fix
Configuration changes have diminishing returns. If the host still swaps after conservative limits, if peak concurrency cannot fit physical RAM, or if the application’s hot dataset is far larger than the available cache, improve query and application behavior or move the database to a more powerful environment. More RAM, a separate database host, workload separation, or an appropriate scaling design can solve a capacity problem that no my.cnf value can safely hide.
Quick Recap
Practical decision checklist
- Have you reserved measured memory for Ubuntu and every co-resident service?
- Is the proposed buffer pool based on that reserve rather than on the 50–75 percent dedicated-host rule?
- Does
max_connectionsmatch observed application concurrency? - Are sort, read, read-rnd, and join buffers modest globally?
- Are
tmp_table_sizeandmax_heap_table_sizeequal and bounded? - Did you verify file-descriptor headroom before increasing
table_open_cache? - Are you leaving
innodb_buffer_pool_instancesat the default for a small pool? - Have you avoided
innodb_dedicated_serveron a shared VPS? - Did you measure swap, OOM events, latency, temporary-table spills, and connection peaks after the change?
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
