Subscribe via

Setting up a FreeBSD 6.2 Web Server: Optimizing MySQL (Part 5)

Similar to Apache, you do not want MySQL to start hogging all the memory in your system. To configure your MySQL settings, open your /etc/my.cnf file for editing. Under the [mysqld] section of the file modify the following variables:

max_connections = 10
max_user_connections = 10

These numbers are a bit tricky to setup. The value depends on the ratio of dynamic requests and static requests. Dynamic requests are typically PHP pages that make MySQL queries and get regenerated every time you access a page. Static requests are typically files that are loaded/imported (e.g. *.js, *.css, *.jpg, *gif, etc) . The average ratio for this is usually 1 dynamic request for every 5 requests, but I recommend you using a log analyzing software to find the exact number.

I used Analog to help me find this number. After you’ve ran Analog on your /var/log/httpd-access.log file, you should get a report file. In the report file, find the section “File Type Report” and note the percent of calls made to the *.php file type. That is your ratio.

Multiply your ratio by the number of maximum connections you’ve configured for Apache. For example, if my max number of Apache connections is 27 (from previous post) and my ratio is 1/6, my MySQL max_connections should be CEILING(27 * 1/6) = 5. I multiplied that number by 2 for safety measures to get max_connections = 10.

You have to set max_user_connections = 10 too because sometimes MySQL thinks that Apache is just 1 user and you do not want Apache to be limited to only one connection.

max_allowed_packet=1M

I’ve set max_allowed_packet to 1M as a measure to prevent run-away queries. I’m pretty sure that I won’ be querying anything that will returning a data packet greater than 1M.

table_cache=100

The table cache setting helps speed up MySQL queries. The value set should be max_user_connections multiplied by the number of JOINs your heaviest MySQL query contains (In my case, 10 JOINs).

query-cache-type=1
query-cache-size=10M

This enables the query-cache feature. WordPress doen’t need a big query cache so I’ve set mine at 10M.

After all this, restart MySQL and watch your WordPress page generation time dip by 50%. Ooo sweet sweet reward. Next I will show you how to optimize PHP!

Save and Share
StumbleUpon
Reddit

2 Responses to “Setting up a FreeBSD 6.2 Web Server: Optimizing MySQL (Part 5)”

[go to last comment]
  1. Setting up a FreeBSD 6.2 Web Server: Optimizing Apache (Part 4) | OMNINOGGIN

    […] your Apache server and the changes should take effect. Now, you can move onto the next step, MySQL optimization. Related posts:Setting up a FreeBSD 6.2 Web Server: Optimizing MySQL (Part 5)Recapping: Setting up […]

  2. Recapping: Setting up a FreeBSD 6.2 Web Server | OMNINOGGIN

    […] Optimizing MySQL […]

[go to first comment]