How to fix ERROR 1037: Out of memory; restart server in MySQL

free -h
# Output shows Mem and Swap usage

cat /proc/meminfo | grep -E 'MemFree|SwapFree'

# Check swap configuration
swapon --show

vm_stat
# Shows free, inactive, and wired memory pages

# Check swap usage
ls -lh /var/vm/swap*

# Current process limits
ulimit -n
# Should typically be 1024 or higher

# System-wide limits
cat /proc/sys/fs/file-max
cat /proc/sys/fs/file-nr

mysql soft nofile 65536
mysql hard nofile 65536
mysql soft nproc 65536
mysql hard nproc 65536

sudo systemctl restart mysql

mysql --help | grep 'Default options' -A 1
# Usually /etc/mysql/mysql.cnf, /etc/mysql/my.cnf, or /etc/my.cnf

SHOW VARIABLES LIKE '%buffer%';
SHOW VARIABLES LIKE '%cache%';
SHOW VARIABLES LIKE '%pool%';

# Rule of thumb: 50-80% of available RAM, never more than physical RAM
innodb_buffer_pool_size = 1G

sort_buffer_size = 2M          # Used for ORDER BY, GROUP BY
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 1M
tmp_table_size = 64M
max_heap_table_size = 64M

total = innodb_buffer_pool_size +
        (sort_buffer_size * max_connections) +
        (read_buffer_size * max_connections) +
        (join_buffer_size * max_connections) +
        (key_buffer_size) +
        (query_cache_size) +
        ~500MB for OS and other overhead

sudo systemctl restart mysql

# Navigate to MySQL data directory (usually /var/lib/mysql)
cd /var/lib/mysql

# Check for missing or corrupted files
# .frm = table definition
# .ibd = InnoDB data file (if using InnoDB)
# .par = partition definition

# Look for incomplete files or permission issues
ls -la your_database/ | grep -E '\.(frm|par)$'

-- Connect to MySQL and repair the suspected table
REPAIR TABLE table_name;

-- For InnoDB, use CHECK and OPTIMIZE
CHECK TABLE table_name;
OPTIMIZE TABLE table_name;

-- If that fails, use innochecksum (InnoDB only)
-- Stop MySQL first

-- Enable slow query log if not already enabled
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Or check the error log for patterns
SHOW VARIABLES LIKE 'log_error';

SELECT * FROM large_table
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 1000;

-- Add indexes to avoid full scans and temporary table sorts
CREATE INDEX idx_status_created ON large_table(status, created_at DESC);

-- Use LIMIT to reduce result set
SELECT id, status, created_at FROM large_table
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100;

-- For very large aggregations, consider materialized views or caching
SELECT status, COUNT(*) FROM large_table
GROUP BY status;

EXPLAIN FORMAT=JSON SELECT ... \G
-- Look for "Using temporary", "Using filesort" indicators

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

# Default is often 151, reduce to match actual concurrent connections needed
max_connections = 100

# Also reduce thread cache to free memory
thread_cache_size = 8

# Linux/Unix
sudo systemctl restart mysql

# Or using service
sudo service mysql restart

# Check it restarted successfully
sudo systemctl status mysql

# Or monitor the log
tail -f /var/log/mysql/error.log