>>>>> "Steven" == Steven Roussey <sroussey@stripped>
> writes:
Steven> Arg!
Steven> OK, I switched to the second method below on the site itself. Now things are
Steven> somewhat reversed. Count(*) seems somewhat faster, and both methods are
Steven> creating pauses. Then it occurred to me that both methods are relying on the
Steven> index only. So I'm now using a third method (Type 3, Type 1 and 2 are
Steven> repeated further, below):
Steven> select b from tbl where a=# and c=0;
Steven> count number of rows in result set.
Steven> I'm selecting a column that is not part of the index used in the where
Steven> clause.
Steven> The results are interesting. With no background traffic, Type 1 is twice as
Steven> fast as Type 2 which itself is twice as fast of Type 3. So on a benchmark
Steven> level, I'm selecting a query that is four times slower than the optimal
Steven> choice.
Steven> But when there is real traffic hitting the server at the same time, things
Steven> change. But not as much on the average, so much as on the maximum query
Steven> length:
Steven> Type 1: average is 0.15165980696678 with max of 9.3659509420395
Steven> Type 2: average is 0.2846958220005 with max of 5.8125519752502
Steven> Type 3: average is 0.2899955201149 with max of 0.071884989738464
Steven> The worst part about this, is that while a Type 1 or 2 query runs really
Steven> slow (on occasion) ***it is locking out all other selects*** even though
Steven> there is no WRITE lock in the queue. I see this in 'mysqladmin -i 1
Steven> processlist status'. I suspect that all operations that use only the index
Steven> are affected. This phenomenon causes a cascade effect that really degrades
Steven> performance. And oddly, it seems to happen only when the result set is
Steven> between 1-3 rows. Test results are below. So now we are using Type 3 query,
Steven> and I'm looking at the processlist to determine if other index-only type
Steven> queries are locking things up.
Steven> Does anyone know the code well enough to point me to the location where a
Steven> select gets optimized to use index only and requests a lock when being
Steven> placed in the queue?
Steven> Thanks!
Steven> Steven Roussey
Steven> Network54.com
Hi!
Why do you think there is a WRITE lock. MySQL will never issue a
WRITE lock for a SELECT !
Could you mail me some output from 'mysqladmin proc' that shows what
you mean!
Regards,
Monty
