MySQL and MariaDB are the most common cause of an OOM kill on a 1\u20132 GB VPS, and the usual fix \u2014 “lower innodb_buffer_pool_size” \u2014 often makes things worse because it ignores where the rest of the memory went. This article works through the actual budget on constrained servers: what each buffer costs, which ones scale with connections, and how to size them so the database stays in RAM and the kernel does not reach for the OOM killer.<\/p>\n\n\n\n
Start from what the running server reports, not from a config template. Connect and run:<\/p>\n\n\n\n
SELECT\n VARIABLE_NAME, VARIABLE_VALUE\nFROM performance_schema.global_status\nWHERE VARIABLE_NAME IN (\n 'Innodb_buffer_pool_pages_total',\n 'Innodb_buffer_pool_pages_free',\n 'Threads_connected'\n);<\/code><\/pre>\n\n\n\nThen check the OS view with ps_mem<\/code> or smem -tk<\/code>, which accounts for shared pages properly. RSS from ps<\/code> overstates MySQL’s footprint because the buffer pool is shared memory; PSS<\/code> from smem<\/code> is the number to compare against your RAM budget.<\/p>\n\n\n\nThe four buffers that actually matter<\/h2>\n\n\n\n\n- innodb_buffer_pool_size<\/strong> \u2014 the big one. Targets 60\u201370% of RAM on a dedicated database host; on a web VPS that also runs PHP, keep it lower (see below).<\/li>\n
- Per-connection buffers<\/strong> \u2014
sort_buffer_size<\/code>, join_buffer_size<\/code>, read_buffer_size<\/code>, read_rnd_buffer_size<\/code>. These are allocated per connection<\/em> when needed, so the worst case is workers \u00d7 size.<\/li>\n- tmp_table_size \/ max_heap_table_size<\/strong> \u2014 in-memory temp tables, also per connection.<\/li>\n
- InnoDB log and dictionary<\/strong> \u2014
innodb_log_buffer_size<\/code> plus a few hundred MB of fixed overhead you cannot configure away.<\/li>\n<\/ul>\n\n\n\nThe trap is the per-connection group. Setting sort_buffer_size = 16M<\/code> with 50 max connections means a theoretical 800 MB of sort memory on a server that only has 1 GB. Most connections never allocate it, but a bad query plan on a busy day will.<\/p>\n\n\n\nA measured budget for a 2 GB VPS<\/h2>\n\n\n\n
Assume the VPS runs the web stack too. Reserve roughly: 350 MB for the OS and page cache floor, 300 MB for PHP-FPM and nginx, then give the rest to MySQL. That lands here:<\/p>\n\n\n\n
[mysqld]\ninnodb_buffer_pool_size = 512M\ninnodb_buffer_pool_instances = 2\ninnodb_log_file_size = 128M\ninnodb_flush_method = O_DIRECT\n\n# per-connection, deliberately small\nsort_buffer_size = 1M\njoin_buffer_size = 1M\nread_buffer_size = 512K\nread_rnd_buffer_size = 512K\ntmp_table_size = 16M\nmax_heap_table_size = 16M\n\nmax_connections = 40\ntable_open_cache = 400\ninnodb_open_files = 400\n\nperformance_schema = OFF\n<\/code><\/pre>\n\n\n\nTwo deliberate choices stand out. First, innodb_buffer_pool_instances = 2<\/code> with a 512M pool reduces mutex contention on multi-core instances without splitting the pool so finely that it wastes memory. Second, disabling performance_schema<\/code> frees 100\u2013200 MB on MariaDB builds where it is enabled by default \u2014 re-enable it temporarily when you need to diagnose, then turn it off again.<\/p>\n\n\n\nWatch it rather than guess it<\/h2>\n\n\n\n
After applying changes, restart (buffer pool size is not fully online-settable in older versions) and watch the working set over a few days:<\/p>\n\n\n\n
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';\n-- read_requests vs reads: the miss ratio is reads \/ read_requests<\/code><\/pre>\n\n\n\n