GC MySQL Database Optimized

Recently, GoldenChoco MySQL server has troubled for consuming too much memory. It caused by GoldenChoco Portal Web (http://www.goldenchoco.web.id) WordPress. When it loads MySQL consuming much memory, in actual there's not too much connection in the same time. Just for 1 client it can consumed much memory, while at the same time more than 1 connection it can caused server crashed.

Then we found that trouble from MySQL server didnt have any cache option for it when it loads query from client. 

From Internet we found some information about what MySQL server did when it consumed more memory:

• MySQL default cache settings are not too tight on Ubuntu/Debian, making it suitable for moderate loads, not low loads. If you don’t have much content, everything is just kept in memory (even if not needed)
• MySQL uses round robin for connections and if there is 100 max connections it will allocate a thread stack for each connection (someone please confirm this – I found contracting infos).

It mentioned about option to reduce memory consumption when MySQL loaded. Here some tips we found:

MySQL is mostly configured in /etc/mysql/my.cnf on Ubuntu / Debian.

• Let’s set key_buffer from 16M to 8M. It is used by MyISAM table cache.
• Set the query_cache_size. query_cache_size  = 8M. Also, decrease query_cache_limit to 512 K.
• Each connection, even if idle, will have 256 KB buffer. Decrease the number of max. connections. (XXX: not sure about this). Drop max connections from 100 -> 30, as we do not have that many concurrent visitors on the site. Also, set less aggressive thread_stack size.

After we followed that tips, we found our MySQL memory consumption be more moderate. 

Source: http://fwd.rf.gd/7k-f1