too many MySQL childs

cerebrum · Posted: Thu Jul 21, 2005 10:55 am Post subject: too many MySQL childs

Hi all. Well, now i have next problem - into DB have 200 requests... load avg > 35 (Dual XEON 2.8 MHz)... SCSI hard drive...
Why so many childs? Can i reduce value of this via editing my.cnf? Or it happen because too many requests per second?

Kruegi · Posted: Thu Jul 21, 2005 11:01 am Post subject:

What is the DB used for?
How many connections do you have at the same time?

Thomas

cerebrum · Posted: Thu Jul 21, 2005 11:34 am Post subject:

Kruegi · Posted: Thu Jul 21, 2005 12:00 pm Post subject:

The DB server needs at least one thread per connection. So there is not much you can do about their number.

Thomas

cerebrum · Posted: Thu Jul 21, 2005 12:35 pm Post subject:

In this part of config how can i do it

skip-locking
skip-innodb
datadir=/var/lib/mysql
max_connections = 2500
max_connect_errors = 1500
log = /var/log/mysql_data.log
key_buffer = 128M
max_allowed_packet = 4M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 128
query_cache_size= 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

Kruegi · Posted: Thu Jul 21, 2005 1:08 pm Post subject:

What?
You can't reduce the connections. Your application will not work then.
If you have 200 open connection and your system load is too high you should:
- configure the applications to use less connections (that will not work if you are having 200 clients)
- cluster the DB
- get a high end server

Thomas

cerebrum · Posted: Thu Jul 21, 2005 1:18 pm Post subject:

Kruegi · Posted: Thu Jul 21, 2005 1:25 pm Post subject:

kashani · Posted: Thu Jul 21, 2005 2:24 pm Post subject:

I'd drop the number of connections the server allows down to 1024 or maybe even 512. IIRC Mysql will reserve RAM to handle that many connections when it could be using it for other things... but that might have been a 3.2.x thing.

There are a number of things that can be causing high load. Generally it's not going to be the number of connections as I've got 400-ish on a similar sized box. Chances are your tables are very big or your SQL is very complicated. A search engine would probably make both of those things happen.

How much RAM do you have?
What's the output of status; from within MySQL?
What size are your main tables? Over a few million?
Are you doing any indexing?

You may want to look at having your robots feed into one database which then replicates out to two or more databases that your client can query. That's the easiest way to scale up assuming your SQL isn't screwy.

kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape.