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-bdbandskip-innodbare 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_bufferis only 16K which is far from enough.key_bufferis 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 abouttable_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_limitset to 256K andquery_cache_sizeset 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.
Comments 9
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.
Posted 15 Apr 2008 at 12:32 pm ¶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.
Posted 24 Apr 2008 at 12:21 am ¶As of flat file forums — according to ForumMatrix, these are the candidates:
http://www.forummatrix.org/compare/E-Blah+Vistix+YaBB
Posted 24 Apr 2008 at 12:33 am ¶Thanks for your information. IMO, E-Blah seems the best of them.
Posted 24 Apr 2008 at 12:05 pm ¶flat file (sqlite2) DB engine forum = phpBB3
Posted 24 Apr 2008 at 5:38 pm ¶PunBB/Flux also supports SQLite
Posted 26 Aug 2008 at 1:51 pm ¶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
Posted 25 Sep 2008 at 4:08 pm ¶thanks
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?
Posted 22 Nov 2008 at 6:47 pm ¶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:
Posted 11 Dec 2008 at 12:54 am ¶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.
Post a Comment