>>>>> "Steven" == Steven Roussey <sroussey@stripped>
> writes:
>> 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.
<cut>
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
Steven> Tuesday.
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
CPU.
Recoding the key cache to use reader/write locks could solve this.
Regards,
Monty
