Hi Mike,
I tried read-locking the tables as you suggested, but regrettably it didn't help a bit. If there is nothing running but selects on that database, mysql shouldn't lock ever, should it? or is it locking the table if it's doing a full table scan (why?!)?
Bernd
Bernd,
It doesn't make sense to me either. It's almost as if it is doing a "select ... lock in share mode" which adds a lock to the table. See http://dev.mysql.com/doc/mysql/en/InnoDB_locking_reads.html
Here are a few ideas:
1) Have you tried eliminating the Order By clause? (If this works, try creating the table in sorted order so the Order By clause is not needed)
2) If you don't need the indexes on the Heap table, try dropping them.
3) Have you run Analyze on the heap table or Explain to see what it is doing?
4) If you have an AutoInc column try getting rid of it. Since you are not adding rows to the heap table, try changing it to a simple Integer column.
Mike
On 23.11.2004, at 5:39 Uhr, mos wrote:
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
