MySQL / apache tuning

[Kenneth P. Stox]

On Tue, 2003-06-24 at 16:51, Ralph Forsythe wrote:
> I need some pointers on tuning a server.  System has 1gb of RAM, running
> MySQL 3.23.57 and Apache 1.3.27.  The web site has at any given moment, at
> least 200 people browsing online forums (UBB - uses PHP), which are
> entirely mysql-driven.
> 
> I don't have the option of separating the DB out onto another box.  What
> would some of you recommend for values to properly tune MySQL for this
> environment?  Any special ways to make Apache run with as little memory
> and as much speed as possible?
> 
> I am noticing that the box is using swap space, so obviously some changes
> need to be made.  The forum in question has over 18,000 members, tens of
> thousands of posts, and is updated 24/7 - so I'm thinking a table
> optimization wouldn't hurt, but I'm looking for ways to tune the mysql
> process itself.  What environment variables should I be looking to change
> to get this as fast and small as possible?  (Yes I know RTFM, I'm looking
> more for some answers along the lines of "I've tried this with the same
> type of application and got best results with 'x'".
> 
> Basically I want to get this under the 1gb mark with lots of room to spare
> (if possible), and increase the overall speed in the process

Ummmm, Do you really have a problem??

FreeBSD will aggressively consume memory for cache, and write pages out
to swap. This is a good thing. A few questions to answer:

1) Where is the machine spending most of its cycles? If it is spending
the majority of its time on mySQL, that would be the first place to
look, if not......

2) If mySQL is the prime offender, the first optimization, I would look
for, is frequently executed queries. Chances are that this will buy you
far more than optimizing the underlying O/S. In my experience, when
there is a performance problem, query optimizations beats O/S
optimizations by 100 to 1. 

3) I'm not a mySQL user myself, but I would guess that ideally, you
would want as much of the database resident in memory as possible. This
may involve shared memory tuning to achieve ideal results.