>>>>> "Steven" == Steven Roussey <sroussey@stripped>
>> Sinisa wrote:
>> First of all, would you explain what are those numbers.
>> Second, what is '#'. If you change '#' and get various speed results,
>> that is OK, because query speed does depend on the number of rows
>> satisfying some conditions.
Steven> mysqladmin processlist shows the command 'count(*) from tbl where a=# and
Steven> c=0' as sending data, while all the other threads fill up with selects on
Steven> that same table. All these other selects are Locked. No
Steven> inserts/updates/deletes in the queue. If I change the queries such that no
Steven> query relies solely on the index, then mysql will process and send data for
Steven> all the selects at the same time. This is more important than the fact that
Steven> the query that locks everything up is taking 5-10 seconds.
Steven> I will try and write a multithreaded test program and send the results on
What do you mean with 'locked'.
Steven> On a side note, changing to the Query Type 3 throughout our server has had
Steven> an amazing impact. It should be somewhat slower, but instead it is much
Steven> faster. Before, we would limit apache to 100 or so processes, and it was
Steven> never enough. They would all be full, the site slow. Today, even with higher
Steven> traffic, we have only an average of about 22 apache servers in use. The
Steven> total number of processes for the server fell drastically and the server
Steven> responds much much faster. I'd suggest other people watch their processlist
Steven> for strange behavior. If nothing else, you get to see what the server really
Steven> is doing. Its fun!
Steven> Thanks for all your help!
Could you also test this query:
select count(b) from tbl where a=# and c=0;
The problem could be that the temporary locks on the key cache (when
using index scanning) somehow doesn't give the other threads enough
Recoding the key cache to use reader/write locks could solve this.