Slow queries

[tscbh]

I had this type of query which show on my slow-queries log. It always cost my server about 5~6 seconds.

Could you please tell me if this is normal. How do I optimize it?

I do not have any other queries as slow as that.

# Time: 080507 18:05:58
# User@Host: forum @ [192.168.0.1]
# Query_time: 5 Lock_time: 0 Rows_sent: 30 Rows_examined: 328846
use forum;
SELECT user.*,usertextfield.*,userfield.*, user.userid, options,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
,IF((NOT(options & 1024) AND (user.usergroupid IN (-1,6) OR FIND_IN_SET('6', membergroupids))), 0, reputation) AS reputationscore,level
,avatar.avatarpath,NOT ISNULL(customavatar.userid) AS hascustomavatar,customavatar.dateline AS avatardateline, customavatar.width AS avwidth, customavatar.height AS avheight

, IF((options & 512 AND user.userid <> 0), 1, lastactivity) AS lastvisittime

FROM user AS user
LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid=user.userid)
LEFT JOIN userfield AS userfield ON(userfield.userid=user.userid)
LEFT JOIN reputationlevel AS reputationlevel ON(user.reputationlevelid=reputationlevel.reputati onlevelid)
LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid)

WHERE 1=1
AND user.usergroupid IN (-1,6,7,2,5)
ORDER BY reputationscore asc , user.username
LIMIT 61680, 30;

[ssslippy]

Do you have your avatars stored in the database?

[tscbh]

Yeah,

Thanks ssslippy. I move it to file storage now. I couldn't believe it's served from the database! I have more than 80,000 members. That's why my database server so slow. :)

[ssslippy]

Dont forget to move your attachments there also and user album pictures should also be stored to files instead of the database.

[Lynne]

And, don't forget that if they are stored in the filesystem, they do NOT get backed up when you backup your database. You must back those files/directories up separately now.

[tscbh]

Hi guys,

Thanks for all the tips.

I already moved all attachments (I don't have user album pictures), but yesterday I still got slow queries on my sql server.

I used a separate server for sql. This server is using only 5% of the CPU on average.

I don't know why I got that sql queries. Do I have to index anything to make it faster. What I see is mysql server examine 328,846 rows. This is not good isn't it?

Any suggestion?

[ssslippy]

Can you tell us what page and what query is causing this? I dont see why anything should be looking at 300k rows.

[tscbh]

I have no idea which page does it. My forum is pretty simple, very limited mod used.

I got about 20 of this queries in 1 days. This isn't much, but hackers can use this to attach my server.

After examining the slow queries log, I found that some queries examine as much as 420K rows.

Any idea?

[Lynne]

You need to go do a search through your pages to see where this query is being used. Do a global search for something like "ISNULL(customavatar.userid) AS hascustomavatar" since it looks like a pretty specific thing that doesn't get used often.

edit: Yep, only 22 instances. It looks very much like the query in memberlist.php on line 562. Start with that, see if it's the same query and if there are additions to the query from the standard (like from the hook above it - memberlist_fetch).

[ssslippy]

Would you happen to have living avatars installed?

[tscbh]

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

[ssslippy]

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

[tscbh]

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

[ssslippy]

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.

[tscbh]

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:
Showing results 17576 to 17600 of 17601
Search took 3.01 seconds.

If there are 80,000+ members, the result should be worse.

Anyway, thanks for all the support guys.

[mainframe]

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..

[mindfrost82]

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.