Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Can you use SQLite instead of a daemon-based sql server? –  mattdm Feb 6 '11 at 15:11
    
Im just wondering why MySQL does reserves so much RAM and doesn't use it. –  Buddy Feb 6 '11 at 16:37
add comment

3 Answers

up vote 0 down vote accepted

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.

share|improve this answer
    
I get Timeout error occurred trying to start MySQL Daemon. whenever I trying to run script with ulimit -v 65536, ulimit -v 131072 starts perfectly. –  Buddy Feb 6 '11 at 16:29
    
So, clearly it needs more memory than 65536k to start. –  mattdm Feb 6 '11 at 16:39
    
After a long time i returned to resolve this. I found a solution: ulimit -s 128 in init.d start script. –  Buddy May 25 '11 at 12:13
add comment

Use tuning-primer or MySQLTuner.

# wget http://mysqltuner.pl/mysqltuner.pl
--2011-01-31 06:49:46--  http://mysqltuner.pl/mysqltuner.pl
Resolving mysqltuner.pl... 174.143.142.58
Connecting to mysqltuner.pl|174.143.142.58|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 39054 (38K) [text/plain]
Saving to: `mysqltuner.pl'

100%[==============================================================================================================================>] 39,054      54.6K/s   in 0.7s    

2011-01-31 06:49:49 (54.6 KB/s) - `mysqltuner.pl' saved [39054/39054]

# perl mysqltuner.pl

 >>  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: 

Set max_connections = 15

128 MB is too little for nginx, php, Sphinx and MySQL.

share|improve this answer
    
I updated the question. –  Buddy Feb 6 '11 at 12:31
    
also try to decrease query cache size –  rvs Feb 6 '11 at 13:22
    
Nginx, PHP and Sphinx use 25Mb. I also use my.conf file for small system # This is for a system with little memory (<= 64M) where MySQL is only used from time to time and it's important that the mysqld daemon doesn't use much resources. –  Buddy Feb 6 '11 at 13:27
add comment

Try not to use InnoDB, but MyISAM. Adding skip-innodb to my.cnf will shave down a decent amount of memory

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.