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
