The most efficient way to setup MySQL on Ubuntu Server 10 LTS?

Question

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 this 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?

Answer 1

mysqltuner is the best solution. http://blog.mysqltuner.com/

Check for some tips also in mysqlperformanceblog.com

but for me, mysqltuner works very well.

Answer 2

Configuring and tuning mysql for performance is a tedious task. It depends on your system configuration and your mysql structure. For instance, the configuration for a whole MyISAM tables database is different compared to a whole InnoDB tables database.

You may want to have a look at www.mysqlperformanceblog.com *

The mysql site has some information here dev.mysql.com/doc/refman/5.0/en/optimizing-the-server.html * (5.0),

and, if your client is a serious company, mysql also offers some (paid) services here www.mysql.com/products/enterprise/consultativesupport.html *.

I think this Post may be helpful to the non specialist.

( * sorry I'm a stackoverflow starter and cannot post more than one url)