MySql configuration file my.cnf. ideal setup for 256 and 512Mb on Ubuntu ???

November 7, 2012 44.4k views
In order to avoid databases crashes or overloads. Which is the ideal configuration of the my.cnf file of the mysql database, for 256 or 512Mb memory sizes hostings running Ubuntu,
13 Answers
is a wizard to generate my.conf
Read this post

it works well with my 512mb vps using wordpress
That depends on if you are running any other applications such as a webserver on the same server of if the entire server is reserved just for DB.
Thanks for your reply. The server is running as webserver with apache, for hosting a php webpage.
The thing is that when I test the webpage with it overload the database.
Best thing to do first is add a bit of swap to your server, you can use one of these articles to get that done:

Then you look at the usage after the DB is no longer crashing and see if you want to increase any of the caches.

Also are your tables optimized with indexes?
by Etel Sverdlov
Linux swaps allow a system to harness more memory than was originally physically available. Here's how to set up a linux swap file on Ubuntu 12.04
I already add the swap partition according the articles you suggest, and configure the mysql base on the tuning-primer script recomendations. Base on this, the mysql database do not crash (before was crashing) but it overloads.

I also optimized the tables via phpadmin. And I think that the tables have indexes because the webpage is base on joomla.
I believe joomla does include table indexes by default.

If you already have SWAP setup and then MySQL should not be crashing out because it should first use up swap.

How much swap space did you add and how much is being used when everything is running normally?
It could be too late now, but if you still have a problem with the database setup, I would advise to use MysqlTunner - this small script will help you tweak config based on your usage.
These settings fixed my low memory issues with the LAMP stack and on this OS:
256MB - Ubuntu 12.04 X32 Server

MySQL Low-Memory Settings
(typically found in /etc/mysql/my.cnf)

key_buffer = 16K
max_allowed_packet = 1M
thread_stack = 64K
table_cache = 4
sort_buffer = 64K
net_buffer_length = 2K

Apache 2 Low-Memory Settings
(typically found at /etc/apache2/apache2.conf)

StartServers 1
MinSpareServers 3
MaxSpareServers 6
ServerLimit 24
MaxClients 24
MaxRequestsPerChild 3000

SWAP or changing my.cnf didn't work for me. What else can I do?
@Marcos: That means 512MB is simply not enough to match your droplet's requirements. I recommend upgrading your droplet.
Also useful, there are some pre-made configs for mysql in /usr/share/doc/mysql-server-5.5/examples. I used the "small" config and it's working quite nicely. I have a fairly low-traffic site running on a 512 meg droplet, and with apache/mysql/php/postfix/proftpd all running I'm looking at around 300 megs free. Also should mention this is using a Debian 7 image.

And do not forget about

performance_schema = OFF

Seems, like starting from 5.6 it is enabled by default. And it is killer of all mem-optimizations inside 512Mb box)))

Have another answer? Share your knowledge.