Memory Maxed out and Slow Query Performance

[Gary Wales]

We have two main query types running against a table of some 2 million
rows and have gotten query response down to well under a second by
using the right indexes.

Problem is that we are running an advertising campaign that brings a
concentrated block of users to the site. When this happens one of the
queries which relies on a particluar index comes severely of the rails
and can take up to 2 minutes filling the slow query log for 15 to 20
minutes. The other query type never appears in the slow log at these
times.

Can anyone explain why this is happening?

The server has 2Gb of ram and a key_buffer of 1Gb. When the problems
occur mysql uses all of the key_buffer but the total value of the MYI
files is under 250Mb. What is in memory - thread related buffers?

It seems as though the index required for the query is not in memory
and the disks are coming into play. Why is only one index affected?

I would really appreciate some pointers, what can I check to pinpoint
the problem and what settings should I implement to preserve
performance?

Front end is 4 cold fusion web servers using persistent connections.
max_connections is currently at 100.

Many thanks,

Gary

[Good Man]

ga********@hotmail.com (Gary Wales) wrote in
news:d8**************************@posting.google.c om:

Front end is 4 cold fusion web servers using persistent connections.
max_connections is currently at 100.

Might this be the problem? Why are you using persistant connections as
opposed to regular, non-persisting ones?