MySQL Forums
Forum List  »  General

MySQL is bogged down when running multiple queries simultaneously
Posted by: Chandrakumar Muthaiah
Date: November 30, 2004 11:31AM

We are having some serious problem, which I have no clue on how to resolve it.

This problem exists from version 3 till 4.1

When running mutple queries simultaneously the server takes ten fold or more of the time that it normally takes to complete the job.

This is irrelevent of indices and irrelevent of table size.

One table may have 30mm records and other might have 1000 recs.
Once we start a simple queries like

select * from table a where x = 'something';

and

select * from table b where y = 'something else';

where a having 30m rows and b having 1000 rows. As soon I hit go, it takes very long time for both queries to complete. Not to mention if run more than 2 queries, then that is it, I will have to wait for hours to get the results.


If I run them independantly it just comes back immediately within less than a fraction of second.

Is there any one know what is going on and what happens which simultaneous queries?
Like the kind of thread library mysql uses is not synchronized properly or do I need to use some other thread library for building mysql to optimize it?

Please help and I am including my server configuration below.


# The MySQL server

[mysqld]
basedir=/usr/local/mysql
datadir=/var/lib/mysql/DB
tmpdir=/var/lib/mysql/DB/tmp
log-slow-queries=/var/lib/mysql/slow.queries
port = 3306
pid-file = /var/lib/mysql/mysqld.pid
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = key_buffer=3400M
set-variable = key_buffer_size=3400M
set-variable = max_allowed_packet=32M
set-variable = table_cache=1024
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = thread_cache=8

# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=4
set-variable = myisam_sort_buffer_size=64M
set-variable = tmp_table_size=1099511627776
#set-variable = max_delayed_threads=5
set-variable = max_connections=64

#log-bin

server-id = 1

Options: ReplyQuote


Subject
Written By
Posted
MySQL is bogged down when running multiple queries simultaneously
November 30, 2004 11:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.