LowEndBox

Hosting Websites on Bare Minimum VPS/Dedicated Servers

Reducing MySQL Memory Usage for Low End Boxes

Tags: Date/Time: April 15, 2008 @ 1:46 am, by LowEndAdmin

MySQL is pretty much the de facto database engine for most open source scripts, and it is almost-always installed on hosted servers. However the default MySQL installation on Debian/Ubuntu takes around 25MB RSS on a cold-start. Well, not too bad I guess, unless you are running a tight ship with only 64MB of total memory. Depending on how important MySQL is to your stack, you might wish to bring the memory usage down so you can fit other applications in memory.

The easiest way is to start from an existing configuration file to bring down the memory usage, and then tune it to bring back adequate performance. On Debian/Ubuntu you might wish to replace your installed /etc/mysql/my.cnf with my-small.cnf that can be found in /usr/share/doc/my-server-5.0/examples. I am also attaching a my.cnf here that is taken out from CentOS 4 installation of LxAdmin (which has been famous for its low-memory foot print). It is pretty much based on my-small.cnf. Some important notes:

  • skip-bdb and skip-innodb are added, so you don’t get BSD DB nor InnoDB support. BSD DB support in MySQL is pretty much obsolete, and many open source scripts don’t rely on the presence of InnoDB. A low end VPS is not likely to enjoy the concurrency InnoDB is offering anyway. Transaction and referential integrity? Real ProgrammersTM write their own rollback routines :)

  • key_buffer is only 16K which is far from enough. key_buffer is pretty much one of the most important parameter for MyISAM tables and I usually bump it up to at least 1MB. The same can be said about table_cache — 4 is way too small. A WordPress page will likely touch 10 tables, and much more for apps like Drupal or MediaWiki.

  • Query cache might be a good thing if you intend to run a busy site on such a low end VPS (provided that it has small data set, mostly read, like blogs, news sites, etc). I have my query_cache_limit set to 256K and query_cache_size set to 4M here.

After a few adjustment you should be able to start mysqld at around 5-6MB RSS. You might need to check the runtime variables to monitor the performance and how everything works out.

26 Comments

  1. Good article. Another good idea is to avoid MySQL. There is some nice blog system on flat file system. Flatpress(php), blosxom (perl), pyblosxom (python), blosxomy (ruby). And wiki systems: dokuwiki (php), Oddmuse (perl), MoinMoin (python).

    Though I haven’t heard of any forum on flat file.

    April 15, 2008 @ 12:32 pm | Reply
  2. Thanks for the suggestion. Personally I found file-based apps are also easier to backup than DB-backed, but then the choice would be a bit limiting.

    April 24, 2008 @ 12:21 am | Reply
  3. As of flat file forums — according to ForumMatrix, these are the candidates:

    http://www.forummatrix.org/compare/E-Blah+Vistix+YaBB

    April 24, 2008 @ 12:33 am | Reply
  4. Thanks for your information. IMO, E-Blah seems the best of them.

    April 24, 2008 @ 12:05 pm | Reply
  5. flat file (sqlite2) DB engine forum = phpBB3

    April 24, 2008 @ 5:38 pm | Reply
    • Cat:

      SQLite is not flat file. Can you open it in a text editor?

      November 12, 2012 @ 4:53 pm | Reply
  6. invar:

    PunBB/Flux also supports SQLite

    August 26, 2008 @ 1:51 pm | Reply
  7. i have a problem about that on my VPS.
    with 128 RAM but it won’t run.
    can you give me the example my.conf ?

    i’m really need
    thanks

    September 25, 2008 @ 4:08 pm | Reply
  8. Theo:

    Hello, i bought a vps with 128mb ram..

    The first commands i executed:
    apt-get update
    apt-get upgrade
    apt-get install mysql-server mysql-client

    And i got Memory Alocate Problem.. I pressed Ctrl+C..Did the mysql installed ok or will i have problems?

    November 22, 2008 @ 6:47 pm | Reply
  9. Theo, memory alocate sounds like yer outta ram yes..

    mysql is probably not suitable for a 128 ram’ed VPS (out of the box).. personally i’ve got my VPS running plenty of sites+email+spam filters+nginx+apache the lot.. but you need to be alert and monitor your RAM.

    I suggest you try the following:
    check your ram when you start/stop services: free
    monitor your ram/cpu longterm: sysstat
    try something else than mysql: sqlite, pmwiki etc
    not to forget looking at the attached mysql.cnf in the article.

    December 11, 2008 @ 12:54 am | Reply
  10. I found two free mysql hoster:
    – freemysql.net –> very slow…
    – db4free –> fast, not yet sure if its very stable because its designate for testers and developers.

    January 15, 2009 @ 8:09 am | Reply
  11. Pingback: AceLNMP [aT] 誰的部落格

  12. Just a little error noted: the my-small.cnf in Debian 5 is locate in the directory /usr/share/doc/mysql-server-5.0/examples not /usr/share/doc/my-server-5.0/examples (note missing ‘sql’). Perhaps you could update the post?

    Thanks for a great tip though.

    November 19, 2010 @ 4:47 am | Reply
  13. calvin:

    I’m using configuration from this:
    http://wordpress.org/support/topic/high-traffic-database-tips?replies=3

    now my6SQL in my vps only using 71 MB ram. I hope nothing goes wrong though…

    March 28, 2011 @ 7:46 am | Reply
  14. Richard:

    Sometimes, it won’t run since it can’t find bdb to disable. It will just hang. Check /var/log/mysql/mysql.err for more info.

    April 7, 2011 @ 12:05 am | Reply
  15. Yash:

    I know this is an old post, but the LxAdmin my.cnf that I blindly copied and pasted didn’t work (mysql wouldn’t start), so I took the Debian mysql-small.cnf, modified it a bit, and now I have MySQL down to 5.2 meg RSS :).
    Here is my “my.cnf” –

    # The following options will be passed to all MySQL clients
    [client]
    #password = your_password
    port = 3306
    socket = /var/run/mysqld/mysqld.sock

    # Here follows entries for some specific programs

    # The MySQL server
    [mysqld]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock
    skip-locking
    key_buffer = 16K
    max_allowed_packet = 1M
    table_cache = 4
    sort_buffer_size = 64K
    read_buffer_size = 256K
    read_rnd_buffer_size = 256K
    net_buffer_length = 2K
    thread_stack = 64K

    # Don’t listen on a TCP/IP port at all. This can be a security enhancement,
    # if all processes that need to connect to mysqld run on the same host.
    # All interaction with mysqld must be made via Unix sockets or named pipes.
    # Note that using this option without enabling named pipes on Windows
    # (using the “enable-named-pipe” option) will render mysqld useless!
    #
    #skip-networking
    server-id = 1

    # Uncomment the following if you want to log updates
    #log-bin=mysql-bin

    # Uncomment the following if you are NOT using BDB tables
    skip-bdb

    # Uncomment the following if you are using InnoDB tables
    #innodb_data_home_dir = /var/lib/mysql/
    #innodb_data_file_path = ibdata1:10M:autoextend
    #innodb_log_group_home_dir = /var/lib/mysql/
    #innodb_log_arch_dir = /var/lib/mysql/
    # You can set .._buffer_pool_size up to 50 – 80 %
    # of RAM but beware of setting memory usage too high
    #innodb_buffer_pool_size = 16M
    #innodb_additional_mem_pool_size = 2M
    # Set .._log_file_size to 25 % of buffer pool size
    #innodb_log_file_size = 5M
    #innodb_log_buffer_size = 8M
    #innodb_flush_log_at_trx_commit = 1
    #innodb_lock_wait_timeout = 50
    skip-innodb
    [mysqldump]
    quick
    max_allowed_packet = 16M

    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates

    [isamchk]
    key_buffer = 1M
    sort_buffer_size = 1M

    [myisamchk]
    key_buffer = 1M
    sort_buffer_size = 1M

    [mysqlhotcopy]
    interactive-timeout

    August 29, 2011 @ 3:38 am | Reply
  16. Just a note skip-bdb is deprecated in latest mysql versions and adding it to my.cnf may result in mysql server not starting or restarting. So remove it.

    For MariaDB 5.2 mysql folks add skip-pbxt if you don’t use PBXT storage engine.

    September 1, 2011 @ 2:53 am | Reply
    • Jack:

      Thanks George! Solved a problem from. I’ll add that skip-innodb in mysql 5.5 is a culprit as well. Both of those kept mysql from restarting.

      March 5, 2014 @ 1:45 pm | Reply
  17. Hi there this is kind of of off topic but I was wanting to know if blogs use WYSIWYG editors or if you have to manually code with HTML.
    I’m starting a blog soon but have no coding skills so I wanted to get guidance from someone with experience. Any help would be greatly appreciated!

    July 9, 2013 @ 4:49 am | Reply
    • Aidan:

      you can use WordPress, blogspot as starting point. they have nice editor for you.

      September 29, 2013 @ 1:40 pm | Reply
  18. Can you tell us more about this? I’d care to find out some additional information.

    March 2, 2015 @ 9:52 am | Reply
  19. Although this config is a little outdated , but it got in the right direction for optimizing my mysql configuration .

    Innodb is so fat , it needs a lot of memory , wish their was a ligher alternative to Mysql , may be their is and i dont know about it ?

    May 4, 2015 @ 3:58 am | Reply
  20. Dr.Rockso:

    I too basicly managed to use this config to take my single core vps from 99-99.7% cpu usage down to about 8%.

    Thanks

    June 20, 2015 @ 11:52 am | Reply
  21. monitor your ram/cpu longterm: sysstat
    try something else than mysql: sqlite, pmwiki etc
    not to forget looking at the attached mysql.cnf in the article.

    how i fix it..?

    June 23, 2015 @ 10:54 pm | Reply

Leave a Reply

Some notes on commenting on LowEndBox:

  • Do not use LowEndBox for support issues. Go to your hosting provider and issue a ticket there. Coming here saying "my VPS is down, what do I do?!" will only have your comments removed.
  • Akismet is used for spam detection. Quoting webhostingtalk.com URL seems to get binned consistently here, but I do peek into the spam box frequently to publish those comments.
  • Use <pre>...</pre> to quote the output from your terminal/console, or consider using a pastebin service.

Your email address will not be published. Required fields are marked *