Searching for Server Optimization Service

[FabioPaz]

Hello,

I have a board with almost 100.000 users and more than 1 milion posts, and I'm having trouble with performance. The root of the problem seems to be one specific IPB query that is always taking more than 3 seconds to be executed and leaving all other queries waiting, making the board very slow or inacessible.

After a discussion with my host provider, IPB support and with the people of this community, I'm still confused of what is the best solution to my problem.

My host provider don't agree that I need more powerfull resources and blame the mysql query, IPB support told me to cut a lot of users features to decrease the size of the query or buy a more powerfull server and some people here told me to search for Gary, to optimize my server, but I can't get a reply from him and my problem is very time sensitive.

So I'm searching for someone like Gary that works with this to analyze my server and see what really is the problem. To see if the server can be tweaked and optimize it to run the IPB or I really need to move to another, and more powerfull, server.

Obs: My board is running in a Dual Six-Core Intel CPU, with 4288 MB ram and RAID-10 HD at Knownhost.com

Thank you for the help and sorry for my english.

[PeterUK]

Hi,

Do you have an example of the query that's causing the problem for you?

Please send me it in a PM if so, I might be able to help you.

[FabioPaz]

Hello Peter,

Here's the query:

SELECT p.*,m.member_id as mid,m.name,m.member_group_id,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m.login_anonymous,m.title as member_title, m.warn_level, m.warn_lastwarn, m.members_display_name, m.members_seo_name, m.member_banned, m.has_gallery, m.has_blog, m.members_bitoptions,m.mgroup_others,pp.*,w.wl_id,rep_index.rep_rating as has_given_rep,rep_cache.rep_points, rep_cache.rep_like_cache,cca.*,ccb.cache_content as cache_content_sig, ccb.cache_updated as cache_updated_sig FROM ibf_posts p LEFT JOIN ibf_members m ON ( m.member_id=p.author_id )
LEFT JOIN ibf_profile_portal pp ON ( m.member_id=pp.pp_member_id )
LEFT JOIN ibf_members_warn_logs w ON ( w.wl_content_app='forums' and w.wl_content_id1=p.pid )
LEFT JOIN ibf_reputation_index rep_index ON ( rep_index.app='forums' AND
rep_index.type='pid' AND
rep_index.type_id=p.pid AND
rep_index.member_id=0 )
LEFT JOIN ibf_reputation_cache rep_cache ON ( rep_cache.app='forums' AND rep_cache.type='pid' AND rep_cache.type_id=p.pid )
LEFT JOIN ibf_content_cache_posts cca ON ( cca.cache_content_id=p.pid )
LEFT JOIN ibf_content_cache_sigs ccb ON ( ccb.cache_content_id=m.member_id ) WHERE p.topic_id=26890 AND p.queued=0 ORDER BY p.pid asc LIMIT 6960,20

[PeterUK]

It's what I thought, is there a way you can give me FTP to your server, I believe I can patch it for you.

[FabioPaz]

Thank you for the help Peter, but I can't give you access to my FTP.

This query is a known problem ? What you thought ?

Thanks again :)

[PeterUK]

Thank you for the help Peter, but I can't give you access to my FTP.

This query is a known problem ? What you thought ?

Thanks again :smile:

It's a known problem yes, IPS have a staff-only ticket open about it but there's no official patch. I had the same problem myself and successfully patched it. I can give you instructions via PM to patch the file yourself if you wish.

[wmarcy]

I would appreciate the patch instructions also. Thanks!

[PeterUK]

I would appreciate the patch instructions also. Thanks!

I'm going to make a new topic in the server optimisation forum as this issue seems to be coming up more and more.

[PeterUK]

And here it is:
http://community.invisionpower.com/topic/368696-slow-forum-patch-only-applicable-if-your-forum-has-topics-with-many-posts/

[Grumpy]

Obs: My board is running in a [font=Verdana, Arial, Helvetica][size=2]Dual Six-Core Intel CPU, with [/size][/font]4288 MB ram and RAID-10 HD at Knownhost.com

A bit random... but...
dual hex core is matched with 4gig of ram? Rather off balance. Just sayin'...