He's right, just set your max_connections in MYSQL. Before you do that, you want to read the following. Itmay help you configure MYSQL better.
TYPE:
mysqladmin {user/password} extended-status
This will have MySQL tell you what's going on. I like to think of "how are you doing?" It's best to know what the
numbers mean before you go tweaking MySQL settings. Like a doctor, you shouldn't prescribe
medication just by looking at the patient, without asking what is ailing. I'm sure my sister will agree. Check the
MySQL documentation for an explanation of each of the variables (see reference below). I'll add my
comments on some of my observations. At the time of this writing, I've played with MySQL for about 9 months
(there goes my credibility), but it seems correct.
I'll also point out that it's a weekend and I'm a little short of sleep.
Created_tmp_tables - This could also mean that the one of the work buffers are too small so a temporary
buffer was needed (i.e. join_buffer, sort_buffer, etc.)
Key_blocks_used - This is how much of your key buffer you allocated is being used. It is represented in K
(kilobytes or 1024 bytes). Once it peaks out at the top (or near it), all your key blocks are in use. I'm pretty sure
once it tops out, it should be recycling old least-recently-used blocks (or I'm hoping it is recycling).
Slow_queries - Watch this number. When your slow queries (as defined as the long_query_time setting) start
going up rapidly - red flag.
Bear in mind, when the status shows you've peaked out or bad numbers are coming up, don't panic and
quickly start changing the settings to MySQL. Is it still returning results in a reasonable time? If so, just note
that it peaked, and keep an eye on it. (see rule #2) If not performing well, is it recovering quickly? If it did, keep
a closer eye on it. If it's not recovering quickly enough or at all, then it's time to dig deeper, find out why and
make some changes.
OK. Now break out the calculator and lets get analytical (sorry, bad engineer humor). Here's a general
formula to keep in mind. Let's look at the worse case scenario.
MySQL memory used = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer +
thread_stack + tmp_table_size)
Notice the max_connections, especially the multiplier part. As your connections increase, so will your memory
usage. It is important to not go extreme on these settings or you'll be headed to swapping land. (see rule
#1) Similarly, notice your key_buffer. Given a fixed amount of memory available: big key_buffer, few
connections; small key_buffer, more connections.
Simple Neanderthal language - add a few grunts for good measure.
The key buffer is a strange little item. It will start out small but over usage time, it will grow. It will to continue to
grow until it peaks to your allocated key buffer size, and it doesn't shrink back down (even if there are
key buffers that will never be used again). Here's where having good indexes comes into play. (Actually, you
can shrink the buffer completely, with a flush command). I believe the next version of MySQL will have a
smarter
scheme to counter those unused key buffers, but I'm just speculating.
Referring back to that general equation above, should you assign the maximums to make use of all available
memory? NO!
Linux has a dynamic disk cache. Allocate too much and you take away memory from the disk cache. When
MySQL (or other applications) need to read some
disk data, it will have to go for direct disk access to get the data. Very bad. Also, other processes (e.g. system
processes, daemons, cron jobs, admin tools, some user who wants to play a game, etc.) may be using or
will need some unknown amount of memory. I enjoy math, but even I'm not going to calculate all the
unknowns.
Generally, you want your settings to be enough to get the job done within a reasonable amount of time. Not
too big, not too small. You can make them a little bigger so you can sleep better at night, but make things too
big and you'll suffer the consequences - slow system performance or worse, nasty hate mail from your users.
What about the max_connections? Do you need to have alot of them? You might not. Many client/server
applications make their requests to the database
server and leave. The trick is to make them leave quickly, like an unwelcomed relative who has stayed over
too long and hasn't showered. If the analogy
didn't help, read the previous paragraph again. Service the request quickly and move on to the next one.
However, you will need enough connections to
handle the majority of the time.
When you realize no matter what you're doing isn't helping, then it might be time for some hardware
upgrades.
To change the variables
simply type
mysqladmin username password --set-variables=variable_name=value
