If you are new to administrate all your web and database server and wonder how you can tweak to their best, you are just like me. My job was to build a webapp in order to handle roughly 10 grand of users, and this app was database-driven one. Its database is about 15GB with 25M records and is growing steadily. Well, traffic is also growing twice a month, approx. 8,000 visits; 260,000 pageviews a day as of now, according to Google Analytics. It’s relatively small if you compare to all giant sites these days. However, it’s not as easy as it seems once you are a coder, but have to manage all these things too. I’ll try to share as much information as I could as time goes by.

After I tweaked on coding side, e.g. indexing, using cache, bla bla bla, as much as I know. I have faced some weird issue that database server was down randomly while it didn’t even eat up all memory or frequently an popular “Too many connections” popping up. After googling for a while, I found that it could relate to several things: maximum connection on MySQL itself which is 100 as a default; maximum open files which is depending on OS; or simply out of memory. In my case, it surely is not memory issue, since top/free confirmed me about that. Then I have changed both maximum connection on MySQL and maximum open files on Linux:-

in /etc/my.cnf, you have to add another line setting this:

[mysqld]
max_connections = 250 # or whatever

This would set new maximum connection limit for the database server, but it doesn’t guarantee if it can handle that–that’s also OS and memory related. After this change, restart MySQL daemon once to see the change.

For maximum open files, you can check by ulimit -n, if it’s over MySQL maximum connections, you can skip this part. If not, heading to /etc/security/limits.conf, and you can simply add:-

* hard nofile 2048
* soft nofile 2048

If you are using SSH, you have to restart sshd once for new setting. So far, I’m happy with the new role and having fun tweaking it. If you have any suggestion, please share!

Popularity: 1% [?]