First of all, I don't know what is living avatar. I am not a forum type person. I only use forum general discussion and user database.

I found out that slow queries only appear when I browse the member list. The first few page of the member list is OK, but as soon as I reach page 2.5K+ slow queries appear. So how can I fix this problem.

On the memberlist, the following detail is shown:
- username/posts/reputaion/avatar

Most of my queries are very simple and index probably. It's usually took less than 50ms. Above that would worry me.

5 seconds for a single query is way too much.

Is there anything I can do to reduce this to an accepted level?

tscbh

Are you displaying avatars on your memberlist page. If im correct these pages arnt in your cache.

Yes avatar is displayed on the memberlist. I don't know if it's cached. How do I cached the memberlist?

no no, usually the system drops files that arnt access often out of the cache. Nothing you can do about that, my best advice is to disable avatars in the memberlist.

After removing everything from member list, nothing seemed to change.

I add a condition in member list, only show member > x post count. Then I index post count. I have only about 60 pages left. The longest queries which is the last page is 0.2 seconds. I am feeling much better now. The majority of member is remove from the list :).

There is nothing I can do about it.

vBulletin.org suffer the same problem too:
If there are 80,000+ members, the result should be worse.

Anyway, thanks for all the support guys.

Same problem here with 471.000 members...
Sometimes the query takes 9 seconds and I also found that the only slow queries generated by vBulletin are in the memberlist..

OMG, this thread saved me. I've been trying to troubleshoot an issue for over a month now and I never thought it was MySQL, much less this issue with avatars on the memberlist.

We have almost 400,000 members and we had avatars showing on the memberlist.

So, I disabled avatars on that page, and also made it so that only members with >1 post show up and we haven't had a slow query since then.