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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
