List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 29 1999 5:26pm
Subject:too many mysqld processes
View as plain text  
Hi!

>>>>> "Axel" == Axel Schwenke <schwenke@stripped> writes:

>> Description:
Axel> I have some questions on the tuning parameters for mysqld. This time
Axel> our database uses the following parameters:

Axel> blue:~ # mysqladmin variables
Axel> +----------------------------+---------------------------------+
Axel> | Variable_name              | Value                           |
Axel> +----------------------------+---------------------------------+
Axel> | back_log                   | 5                               |
Axel> | connect_timeout            | 5                               |
Axel> | basedir                    | /usr/local/                     |
Axel> | datadir                    | /JobsAdverts/SqlServer/var/     |
Axel> | delayed_insert_limit       | 100                             |
Axel> | delayed_insert_timeout     | 300                             |
Axel> | delayed_queue_size         | 1000                            |
Axel> | join_buffer                | 131072                          |
Axel> | flush_time                 | 0                               |
Axel> | key_buffer                 | 25161728                        |
Axel> | language                   | /usr/local/share/mysql/english/ |
Axel> | log                        | OFF                             |
Axel> | log_update                 | ON                              |
Axel> | long_query_time            | 10                              |
Axel> | low_priority_updates       | OFF                             |
Axel> | max_allowed_packet         | 1047552                         |
Axel> | max_connections            | 100                             |
Axel> | max_connect_errors         | 10                              |
Axel> | max_delayed_insert_threads | 20                              |
Axel> | max_join_size              | 4294967295                      |
Axel> | max_sort_length            | 1024                            |
Axel> | net_buffer_length          | 16384                           |
Axel> | pid_file                   | /var/run/mysqld.pid             |
Axel> | port                       | 3306                            |
Axel> | protocol_version           | 10                              |
Axel> | record_buffer              | 131072                          |
Axel> | skip_locking               | ON                              |
Axel> | skip_networking            | OFF                             |
Axel> | socket                     | /tmp/mysql.sock                 |
Axel> | sort_buffer                | 1048568                         |
Axel> | table_cache                | 128                             |
Axel> | thread_stack               | 65536                           |
Axel> | tmp_table_size             | 1048576                         |
Axel> | tmpdir                     | /tmp/                           |
Axel> | version                    | 3.22.26a-log                    |
Axel> | wait_timeout               | 28800                           |
Axel> +----------------------------+---------------------------------+

Axel> The database is the backend of our webserver, mainly used for
Axel> searching structured data (selects in ca. 10 tables).

Axel> Since we upgraded our webserver, allowing around 200 processes
Axel> acessing the database we got the following problems:

Axel> a) Sometimes a lengthy search (database search + fulltext index)
Axel> leads to zombies of the search programm (cgi running on the
Axel> webserver). The connection to the database does not seem to
Axel> be closed. Setting wait_timeout as low as 10 seconds did
Axel> not help.

Can you somehow simulate this?  Normally MySQL will notice at once
when doing a write that the other end doesn't work and will abort the
connection.  You can also try using TCP/IP instead of sockets for you
connection (by connectiong to 'hostname' instead of 'localhost')
Note that 'wait_timeout' will only affect threads that are 'sleeping'.

Axel> b) After (re)starting the database, everything is fine for about
Axel> ten minutes. There are about 10 mysqld processes on the system,
Axel> the cpu load is around 1. Later we get many more mysqlds running,
Axel> which (I suppose) are slowed down due to resource access conflicts.
Axel> Now there's an avalanche effect, the database gets slower, spawnes
Axel> more processes and gets even slower.
Axel> What settings could prevent this? I read the manual on tuning
Axel> mysql for performance, but got no clue.

Are you sure you can't optimize your queries?
Try looking at 'mysqladmin extend-status' to verify that you are
really using keys!

Setting 'wait_timeout' to a low value, like 300, will force MySQL to
close connections that haven't been sleeping for 5 minutes;  This will 
help free some resources.

Regards,
Monty
Thread
too many mysqld processesAxel Schwenke28 Sep
  • too many mysqld processesMichael Widenius29 Sep