Hi! I have a problem with MySQL that hangs when concurrent queries run.
The symptom is this: A) A Perl program that performs several queries using temporary tables takes 8 seconds to run. CPU utilisation around 30% (using the 'top' command) B) Running two instances of the same perl program in parallel causes each program to "wait for each other", doubling the response time for each program, that is each program is now 16 seconds to finish. CPU utilisation around 40% (using the 'top' command) Lets call them process 1 and process 2, each started at the same time. They both run for 16 seconds and they both finish at the same time. C) Doubling the amount of processes again, running 4 in parallel, shows the same symptom: They are all started at the same time, and they all return results at the same time. Each process is now 32 seconds to finish CPU utilisation around 60% (using the 'top' command) etc. etc. This shows that all processes seem to wait for each other to finish before returning a result instead of returning the results immediately! Has anyone solved this problem before? SERVER INFO: ----------------------------- * MySQL v 3.23.52 on FreeBSD 4.7, dual 1200 MHz Intel CPU, 1Gb RAM, 10Gb Free swap space * and lots of free space on all partitions, especially in /var * no limits on CPU, Memory, open files etc. in FreeBSD kernel * No errors shown in /var/messages * No errors shown in /var/db/mysql/host.err * The queries involve the use of temporary tables and a lot of READ queries on regular tables. * No locks have been issued and no locks are needed. * No other processes on the server uses MySQL while the program runs. /etc/my.cnf: [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking skip-innodb set-variable = max_tmp_tables=1024 set-variable = tmp_table_size=128M set-variable = key_buffer=384M set-variable = max_heap_table_size=128M set-variable = max_allowed_packet=16M set-variable = join_buffer_size=4M set-variable = long_query_time=3 set-variable = table_cache=4096 set-variable = sort_buffer=32M set-variable = record_buffer=16M set-variable = thread_cache=16 set-variable = max_connections=300 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=4 set-variable = myisam_sort_buffer_size=64M server-id = 1 # Point the following paths to different dedicated disks tmpdir = /home0/mysqltmp/ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
