Log In
Register
Home
Add Reply
Add Topic
Topic RSS
QuoteHi guys, I'm back for some help (I'm from Shadowville.com, with 110k users…more like 180k now).
Anyway, I've noticed a string of queries that is slowing my entire site down, and I pinpointed the root cause of the problem to SimplePress — specifically sf_show_forum_avatar (or anything avatar related with the query SELECT ID FROM wp_users WHERE user_email = xyz@xyz.com).You might have already addressed the problem in your latest versions, but unfortunately I'm stuck with v4.0 because of alot of hacking done to the profile section associated with our shopping cart system and order history.
Maybe there is a way I can still address this issue by mini/manual patching without a full upgrade? Any help would be appreciated, as I really don't want to abandon SimplePress. But when you've got 200 people at a time running the slow query at the same time, ranging from 2 to 16 seconds per query — it really bogs the site down.
Thanks in advance!
not sure why that would be a slow query… but where are you using that query and why? its really left for backwards compatibility… if you are using it to display sp avatar within wp, there is an option you can select on the forum – profile – avatars panel that will auto replace wp avatars with sp ones…
Visit I think Steve missed the version number! Those options are not available to you back in the 4.0 family.
You seem to be saying that the true 'wait' is with the simple query to retrieve a users ID. And I can underatand this but not sure if there is anyway we could get around it. To be honest, the base WP 'users' and 'usermeta' tables are not really that well designed but worse is the need to pass the email address to the avatar routines.Your speed problem is MySQL searching a text field on a large table.
I can only really think of two things that might help. The first would be to keep those tables optimised – check them regularly and make sure the overhead is kept to a minimum.
But what may help more than that would be to create an index on the email column. There is no magic answer to this but that might help.
Can you work with the DB in this way?
But – at the same time – please tell me just what your avatar settings are…
Yellow Swordfish
I'll have to consult a DB expert for indexing — I'm not too keen on that, but yeah I can optimize the tables in phpmyadmin, though this seems inefficient to do on a routine basis.
What do you mean what the avatar settings are?
If I upp'ed to the later versions, I could do what Steve suggested? And yeah, the SF avatars are being used throughout the entire site (ie avatar of author displayed next to WP posts, in comments, ext.).
Thanks in advance.
"I can optimize the tables in phpmyadmin, though this seems inefficient to do on a routine basis" – quite the opposite actually. Keeping them as optimised as possible s a very good plan.
So – just let me confirm here – are you saying that you ONLY use SP Avatars? No uploads or gravatars allowed?
Yellow Swordfish
No no, what I meant is we allow upload of avatars but not through any other plugin or WP function — only through SimplePress's functions.
Also noticed these two extremely slow queries originating from SimplePress right before my MYSQL server crapped out:
# Time: 100806 22:21:10# User@Host: eximstats[eximstats] @ localhost []# Query_time: 5583 Lock_time: 0 Rows_sent: 0 Rows_examined: 0use eximstats;LOCK TABLES sends WRITE,smtp WRITE;
# Query_time: 5640 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
use svp_wpdb;
SET timestamp=1281147670;
INSERT INTO wp_sfposts (post_content, post_date, topic_id, forum_id, user_id, guest_name, guest_email, post_pinned, post_index, post_status, poster_ip) VALUES…
Any ideas? I'm at aloss. It seems also to be occuring once a day or every other day at around peak hours (noonish, Eastern Standard Time)
11:48 am
SP Master
Nov 9, 2008
Offline
I am going to be perfectly honest and say I have no idea what the first one is. I am not familiar with whatever 'eximstats' is and I don't think this is coming from anything to do with WordPress. It may be some internal MySQL thing. Whatever it is I can see your reason for worrying.
The second one is an SP Insert of a standard forum post.
OK – Google 'eximstats'. Must be something you guys have installed.
Yellow Swordfish
Aha I didn't notice that. Eximstats is a syslog tool for mysql. Okay well that explains the insanely long query because the log file it was posting and then merging to is 21 GIGABYTES big, which explains why it pretty much locked up the entire site.
But it still doesn't explain the 2-16 second queries coming from the sf_show_forum_avatar query. So there's pretty much nothing I can do at this point besides upgrading? Does the newer version have a redone query for avatars which makes it more efficient? Or is it because of the way the meta tables work for WP that is the slowdown?
if you are using sf_show_forum_avatar() then clearly you are using the template tag which needs the email address as a parameter. So I guess somene there write the query to cull the email address from the USERS table.
if you want to continue to use that template tag then I am afraid it will need the query. Where are you using it and is it necessary?
Yellow Swordfish
Most Users Ever Online: 444
Currently Online: steve.engelking, ndc
91 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
-Radio-: 1251
Lee H: 606
Luffer: 535
Conrad_Farlow: 502
jim: 478
neon: 263
ovizii: 240
Tal: 240
Member Stats:
Guest Posters: 2625
Members: 7360
Moderators: 1
Admins: 2
Forum Stats:
Groups: 5
Forums: 16
Topics: 10888
Posts: 79519
Newest Members: ndc, MacBravO, irlandes1, triphop, betokan, Jonathan Yovani Muñoz, somosguatemala, ahcreate, eatonrabbit, indifrendz.com
Moderators: Brandon C (162)
Administrators: Yellow Swordfish (22201), Mr Papa (23688)
Privacy Policy
