This question was originally on stackoverflow.com, but I was told I may get a better answer on this site...

Very soon I will setting up a web server for a client who has a very busy site. He has a forum (run on Simple Machines Forum SMF written in PHP) that cannot really be changed. I want to move this to a separate virtual private server on it's own, as it's very busy at around 40,000 hits a day with 400+ posts a day. With the version of SMF it has (the best, most stable production version 1.1) it needs MySQL to run, so I can't change it to PostgreSQL (which I would maybe prefer)

When installing MySQL Server on Ubuntu Server 10 LTS, I'm curious to know if there is anything I can do to ensure it's the most efficient it can be. I'm wanting to run as fast as possible to keep resources down and to ensure we can stay under our resource limit for the server. With a virtual private server, I have the option of switching up the resources in a flash, but I'm wanting to ensure MySQL Server is setup as efficiently as possible as I'm not exactly an expert at that type of thing.

I was doing some research and found an article which suggests doing this to ensure it's efficient:

# open mysql conf and set these settings:
#    key_buffer = 16k
#    max_allowed_packet = 1M
#    thread_stack = 64K
nano /etc/mysql/my.cnf
# restart mysql
/etc/init.d/mysql restart

Can anyone provide me with some tips, hints, links, knowledge on this subject?


First of all; Janne is correct: 40k reads and 400 writes a day is zero load. You should have absolutely no performance drawbacks with nearly any kind of setup.

I'm running a similar setup on a VPS with SMF. I've found that it benefits alot from caching mechanisms, as it utilizes APC.

My setup (that I recommend to you):

  • Nginx
  • MySQL (with InnoDB-convertion of most of SMF's tables)
  • PHP-FPM (extremely scalable, can use very little resources if you tell it to start with few spawns)
  • APC cache
  • @erik-p-skaalerud Ok, so PHP-FPM looks good. I take it this is a replacement for FastCGI that I would of installed. After looking on the official site for PHP-FPM, it seems PHP 5.3.3 is bundled with it. That seems good to me. There is no reason I shouldn't be using PHP 5.3.3 with it's bundled PHP-FPM for SMF? – littlejim84 Aug 11 '10 at 12:19
  • @erik-p-skaaleru I'm wanting to avoid compiling software myself. I want to do it through packages. I've red this and it seems to make installing PHP with PHP-FPM very easy indeed: constantshift.com/… ...what do you think to this? – littlejim84 Aug 11 '10 at 12:54
  • That's the exact way I installed PHP-FPM (while waiting for 5.3.3 to get backported in ubuntu) – pauska Aug 11 '10 at 13:23
  • @erik-p-skaalerud May I ask... is it totally necessary to port my MySQL tables to InnoDB? After looking around, it seems complex and may cause errors for SMF. Do you have a guide/link that, you know in your experience, works? Does it have any other implications? – littlejim84 Aug 11 '10 at 14:19
  • Well - it's not necessary. It's useful for SMF forums with a large hit ratio as every table update causes a lock with MyISAM. See here for a good explanation/guide: simplemachines.org/community/index.php?topic=50217.0 – pauska Aug 11 '10 at 23:49

First of all, 40 000 hits a day and 400+ posts a day doesn't sound like something what would make MySQL sweat. 40 000 hits a day is about 2 hits a second if spread among the day, and 400+ posts means that there's a new post after every three minutes.

And that is not much. Really.

You didn't mention what storage engine is in use. With MyISAM tables you need to tune especially the key_buffer and table_cache values. With InnoDB tables innodb_buffer_pool_size is the most important one.

The key_buffer = 16k in your example sounds strange. A 16 kilobytes key_buffer? No way, make it at least 16M (megabytes), or if your server has a decent amount of memory, then it can be much more. Please be aware though that many MySQL variables are connection specific and not global values, so if your server load means thousands of simultaneous MySQL connections, then you'll need to tune the values down, but if you typically have only handful of connections but large datasets, then you need to tune values up.

  • @janne-pikkarainen Thank you for the reply. Basically, the plan is to have this running on a 512mb VPS that, apart from MySQL Server, it'll run Nginx, FastCGI and some sort of email server (most probably Postfix). I'm looking to have this all as efficient as possible, but as I'm aware, I'm not sure what to set correctly. I'm planning to give the MySQL a fair chunk of resources if I can, because it's this that was causing resources problems on the previous server (mt) Grid Server + 512Mb MySQL Container. – littlejim84 Aug 11 '10 at 8:28
  • @janne-pikkarainen After much research, it seems that maybe I should stick to a default setup and tweak later down the line when I know more about it. Thank you. – littlejim84 Aug 12 '10 at 7:31
  • 1
    You're welcome. Oh, one more tip: whenever you start to tune things, do that lazily one thing at a time. Change, let's say, key_buffer to some new value and let that run a day or two. See how it affected. If needed, change some other value and then let the site run a day or two. That way you'll learn precisely what was the change that lead to better/worse performance. – Janne Pikkarainen Aug 12 '10 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.