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

Posted November 7, 2012 86.3k 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,

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Submit an Answer
13 answers

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

is a wizard to generate my.conf
Read this post

it works well with my 512mb vps using wordpress
  • That article merely pontificates about MySQL. Doesn’t give any meaningful values. He could have given some rules of thumb based on how much freemem there is. Useless article.

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

Previous 1 2 Next