https://www.digitalocean.com/community/questions/mysql-configuration-file-my-cnf-ideal-setup-for-256-and-512mb-on-ubuntu
n 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,
12 Answers
This https://tools.percona.com/wizard is a wizard to generate my.conf
Read this post http://digitaloceanvps.blogspot.com/2014/04/best-configuration-for-512mb-1gb-ram.html 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 host-tracker.com 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: https://www.digitalocean.com/community/articles/how-to-add-swap-on-ubuntu-12-04 https://www.digitalocean.com/community/articles/how-to-add-swap-on-centos-6 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?
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. https://github.com/rackerhacker/MySQLTuner-perl
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 Source: http://library.linode.com/troubleshooting/memory-networking#sph_apache-2-low-memory-settings
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.
https://www.digitalocean.com/community/questions/mysql-configuration-file-my-cnf-ideal-setup-for-256-and-512mb-on-ubuntu
