At 06:10 PM 11/21/2004, you wrote:
Hi all,
I've got a rather odd performance problem with concurrent queries here. My
query regrettably always needs to do a full table scan - really can't be
helped. So my
idea was to cache the data in a HEAP table to get maximum performance out
of it and it works really well, I'm down to 0.07 seconds for a single query.
Now the problem I just found is when I run 10 or 20 identical queries at
the same time: my CPU goes up to 100% for a surprisingly long time, and
when I look at what mysqld is doing with a profiler, it's burning 70% of
its time in pthread_cond_wait and pthread_mutex_lock.
To me this looks as if the table gets locked and the queries don't really
execute concurrently, but I'm not sure how to find out what is going on
and there are no updates or inserts happening, just plain selects.
Table_locks_immediate increments by 1 for each query, but
Table_locks_waited remains at 0. Also "show processlist" says all queries
are in "sending data" state most of the time.
I'm not sure what to do about this, but it's not an acceptable performance
right now. The table has 100,000 rows at present and each row contains
only ints of different sizes totaling to about 200 bytes per row. The heap
table is small enough to fit into memory, and there is also no swapping or
thrashing during the queries, so it should execute lightning fast, right?
it just doesn't. This is mysql 4.1.7 (official max binary) on MacOS X.
Any help would be very much appreciated!
Bernd
If locking is the problem, have you tried:
lock tables mytable READ;
select ... from mytable ...;
unlock tables;
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
