« Back to Database...

Tuning the Database

Introduction #

This page is about tuning your own database. Since tuning your database is part of configuring it, I'm adding this page.

Assumptions #

I'm going to make a couple of assumptions here that might be dangerous. First off, I'm assuming that this is a production box, meaning that it's taking a fair amount of traffic and not just a development instance. I'm also assuming that you're running MySQL5 or better. These instructions are MySQL specific but most of what is here will apply to other databases. Things won't be called the same, but most database engines will have very similar tuning parameters. I'm also assuming that you're using tomcat5 or better. Again, most of this will apply regardless of which servlet container you use (Glassfish, Jetty, etc.) but it might not be called quite the same thing. The reaons is that at least one of the parameters used comes from the servlet container configuration.

Tuning #

Find your my.cnf. This will exist in different places on Windows and Unix. On most nix boxes, it's in /etc/mysql. I'll leave it to someone else to supply the default path for Windows. Open it in your favorite text editor.

Find your key_buffer and set it to 20% of your physical RAM. That DOES NOT include swap space or anything other than RAM. If you have a single 1GB stick of RAM, set it to 204 MB. I mention 1GB since that's pretty much the minimum to get Liferay running.

Set your max_allowed_packet to at least 16MB if it isn't already. Many of the newer versions of MySQL come with it set by default.

Set your max_connections to what ever you have in your server.xml for for accept count. In our case, that's 100.

Set your table_cache to at least 64 if its not already there.

Save that and restart mysql.

0 Attachments
14030 Views
Average (0 Votes)
The average rating is 0.0 stars out of 5.
Comments
Threaded Replies Author Date
¿Something about MySQL HA? Alba García December 28, 2011 7:44 AM

¿Something about MySQL HA?
Posted on 12/28/11 7:44 AM.