MySQL reserves too much RAM

memoryMySQLvps

I have a cheap VPS with 128Mb RAM and 256Mb burst. MySQL starts and reserves about 110Mb, but uses not more than 20Mb of them. My VPS Control Panel shows, that I use 127Mb (I also running nginx and sphinx), I know, that it shows reserved RAM, but when I reach over 128Mb, my VPS reboots automatically every 4 hours. So I want to force MySQL to reserve less RAM. How can i do that? I did some tweaks with my.conf but it helped not so much.

top output:

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    1 root      15   0  2156  668  572 S  0.0  0.3   0:00.03 init
11311 root      15   0 11212  356  228 S  0.0  0.1   0:00.00 vzctl
11312 root      18   0  3712 1484 1248 S  0.0  0.6   0:00.01 bash
11347 root      18   0  2284  916  732 R  0.0  0.3   0:00.00 top
13978 root      17  -4  2248  552  344 S  0.0  0.2   0:00.00 udevd
14262 root      15   0  1812  564  472 S  0.0  0.2   0:00.03 syslogd
14293 sphinx    15   0 11816 1172  672 S  0.0  0.4   0:00.07 searchd
14305 root      25   0  7192 1036  636 S  0.0  0.4   0:00.00 sshd
14321 root      25   0  2832  836  668 S  0.0  0.3   0:00.00 xinetd
15389 root      18   0  3708 1300 1132 S  0.0  0.5   0:00.00 mysqld_safe
15441 mysql     15   0  113m  16m 4440 S  0.0  6.4   0:00.15 mysqld
15489 root      21   0 13056 1456  340 S  0.0  0.6   0:00.00 nginx
15490 nginx     18   0 13328 2388  992 S  0.0  0.9   0:00.06 nginx
15507 nginx     25   0 19520 5888 4244 S  0.0  2.2   0:00.00 php-cgi
15508 nginx     18   0 19636 4876 2748 S  0.0  1.9   0:00.12 php-cgi
15509 nginx     15   0 19668 4872 2716 S  0.0  1.9   0:00.11 php-cgi
15518 root      18   0  4492 1116  568 S  0.0  0.4   0:00.01 crond

MySQL tuner:

 >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 1M (Tables: 1)
[OK] Total fragmented tables: 0

-------- Performance Metrics -------------------------------------------------
[--] Up for: 38m 43s (37 q [0.016 qps], 20 conn, TX: 4M, RX: 3K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 28.1M global + 832.0K per thread (100 max threads)
[OK] Maximum possible memory usage: 109.4M (42% of installed RAM)
[OK] Slow queries: 0% (0/37)
[OK] Highest usage of available connections: 1% (1/100)
[OK] Key buffer size / total MyISAM indexes: 128.0K/64.0K
[OK] Query cache efficiency: 42.1% (8 cached / 19 selects)
[OK] Query cache prunes per day: 0
[!!] Temporary tables created on disk: 27% (3 on disk / 11 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 57% (8 open / 14 opened)
[OK] Open file limit used: 1% (12/1K)
[OK] Table locks acquired immediately: 100% (22 immediate / 22 locks)
[!!] Connections aborted: 10%
[OK] InnoDB data size / buffer pool: 1.5M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Your applications are not closing MySQL connections properly
Variables to adjust:
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)

I think if I do what MySQLtuner says, MySQL will use more RAM.

Best Answer

There are several mechanisms available on Linux to hard-cap the memory available to a process.

On newer systems, you can use cgroups, and if that's available that's probably the best solution. (Use the tools from the libcgroup package to automatically classify the mysql daemon into a memory control group with memory.limit_in_bytes = 64M; or whatever.

On older systems, if the mysql daemon is being started by a script, you could simply use ulimit to restrict the memory available — put a ulimit -v 65536 statement in the script up above where the daemon is actually started.

That said, with modern software, this is really pushing what you can get away with. You may just plain need more RAM than you have. One option might be to switch to SQLite instead of MySQL — then you don't have a separate daemon running at all.

Related Solutions

Ubuntu – MySQL: How to get the “Maximum possible memory usage” down

You have a server with 256M, but you can't use all of that -- remember there's some OS overhead. Add to that with the fact you're over committing as other folks have mentioned and you'll definitely thrash here. 256M is only enough for a small DB, 20 connections is a lot with what you've got configured.

1) reduce your max connections to 4 (you're using 3 out of 20)

2) optimize your query cache better; 8M is really large, and 64M total is a lot based on your hits/prunes; try a 4/32 combo and see how it goes. Really I think a 2/24 combo would work for you.

3) you have no sorts requiring temp tables, why is that max_heap_table_size verb in there? Comment that out, use the defaults

4) do you actually have 128 tables? Try cutting that table_cache in half to 64 or 48

5) reduce thread_cache_size to 4

6) optimize those tables to reduce fragmenting

Those are some things to start with. It looks like you threw a bunch of numbers in a config without any actual profiling to know what you needed and have created a mess; if all else fails go back to the defaults and get rid of your custom settings and start over using some performance tuning guides you can find on Google. Get the output of SHOW VARIABLES and SHOW STATUS, find any one of a bajillion tuning guides and plug in your actual, real numbers into their equations and that'll tell you the exact-ish numbers you need to put in your config file.

MySQL DB causing high IO running Zabbix server

innodb_buffer_pool_size > 1G, because your dataset size is 1,8GB.

To decrease number of reads you need to increase innodb_buffer_pool_size. To decrease number of writes you need to edit your zabbix templates (disabling some unnecessary items like free inodes, increase intervals between checks).

You have Reads / Writes ratio at 57% / 43%, so enabling Query Cache will not help (it may make things worse, because writes to tables invalidates cache).

Think about increasing tmp_table_size and max_heap_table_size to avoid creating tmp tables on disk (13% of tmp tables). Temporary tables are in MB? it is count? If it is counter, its too high.

Decrease number of connection to 50 (your highest number was 33).

innodb_support_xa = false
innodb_buffer_pool_size = 256M # It depends how many memory is available to MySQL, more is better.
innodb_flush_log_at_trx_commit = 0 # disable writing to logs on every commit and disable fsync on each write
innodb_max_dirty_pages_pct = 90 # avoid flushing dirty pages to disk
innodb_flush_method = O_DIRECT # direct access to disk without OS cache
thread_cache_size = 4
query_cache_size = 0
table_cache = 80? # a little more than number_of_tables_in_zabbix_database

Usefull link about InnoDB optimization.

Best Answer

Related Solutions

Ubuntu – MySQL: How to get the “Maximum possible memory usage” down

MySQL DB causing high IO running Zabbix server

Related Topic