Topic: Site Runs Slow - Seems to be related to MySQL

[georgepdog]

I'm on SiteGround for what I hope to be the very short time being...we are on a VPS with 512MB RAM, but they are saying we keep blowing out our memory due to slow queries. 

I've tried to tweak just about everything I can think of, but I have not had any success.

I am posting here as a last resort to help keep us running until we move to another host.

hxxp:www.arsportstalk.net/smf/status.php [nonactive]

hxxp:arsportstalk.net/smf/phpinfo.php [nonactive]

[Fiery]

First of all do you have any mods etc. installed?

Have you made any large modifications to anything?


Also, it is strong encouraged that everyone upgrades to 1.1.1

[georgepdog]

First of all do you have any mods etc. installed?

Have you made any large modifications to anything?


Also, it is strong encouraged that everyone upgrades to 1.1.1

Thank for your response.  I am working on implementing the 1.1.1 upgrade today.  It's been a painful process between keeping our server up and running and my real job. 

We have the following mods:
MessagePreviewOnHover 1.3
Ignore User 1.2
Streaming 3 4.2.2a
vReportBoard 0.97
Simple ImageShack 2.0.2
Global Announcements 2.0

Our forum default theme is Bloc's Helios MultiColor.

As of now, we are running with avatars and attachments turned off.  I've selected a persistent connection.  I've removed the attachment and avatar size checks.  Please let me know if you need more information.

[georgepdog]

One more note to add, I've followed the checklist as much as I can being hosted on a VPS.  All my most active tables are InnoDB.

Edit:  We are also running FlashChat, integrated into our forum, but it is hardly used and seems to be a non-factor with small tables involved.

[sirwoogie]

Server seems pretty busy, even for a VPS system.

I noticed you don't have a bytecode cache installed (e.g. APC, eAccelerator, xcache). That will do a ton for the high PHP processing you have.

Also, dump VPS if you can.

[georgepdog]

Also, dump VPS if you can.

We are working towards that.  We are in talks with a host for two possible options.  We've been given, by one of our forum members, a retired DL360 G3 with 1GB RAM to use.  We are investigating whether it would be cheaper to do the setup and deploy it to a host to keep up and running or if it would be cheaper just to lease a decent dedicated server in the same hosts environment.  Our network guru is talking with them now to see what bandwidth, etc. we can get for what price.

I'm just trying to find the best way to keep the VPS solution going for as long as possible until then.  I'll say something to SiteGround about the accelerator.  Thanks for the tip.

[jw00dy]

I noticed you don't have a bytecode cache installed (e.g. APC, eAccelerator, xcache). That will do a ton for the high PHP processing you have.

xcache isn't support

I have it installed on my Dedicated server for other sites I host, and SMF doesn't recognize it.

Anyone know if their are plans to support it?

[Oldiesmann]

I've never even heard of xcache before, but with a few changes, you should be able to get SMF to support it.

Sources/Admin.php

Find

Code: (Line 255) [Select]

$context['can_admin'] = allowedTo('admin_forum');
Add before that

Code: [Select]

if(defined('XCACHE_VERSION'))
$context['current_versions']['xcache'] = array('title' => 'XCache', 'version' => XCACHE_VERSION);
Sources/Load.php

Find

Code: (Lines 2110-2112) [Select]

// Zend Platform/ZPS/etc.
elseif (function_exists('output_cache_put'))
output_cache_put($key, $value);
Add after that

Code: [Select]

// XCache
elseif (function_exists('xcache_set'))
{
if($value === null)
@xcache_unset($key);
else
xcache_set($key, $value, $ttl);
}
Find

Code: (Lines 2176-2178) [Select]

// Zend's pricey stuff.
elseif (function_exists('output_cache_get'))
$value = output_cache_get($key, $ttl);
Add after that

Code: [Select]

// XCache
elseif (function_exists('xcache_get'))
$value = xcache_get($key);
Sources/ManageServer.php

Find

Code: (Lines 408-409) [Select]

else
$detected = 'no_caching';
Add before that

Code: [Select]

elseif(function_exists('xcache_set'))
$detected = 'XCache';
Themes/default/languages/ModSettings.english.php

Find

Code: (Line 92) [Select]

<li>Zend Platform/Performance Suite (Not Zend Optimizer)</li>
Add before that

Code: [Select]

<li>XCache</li>
Find

Code: (Line 111) [Select]

$txt['detected_Zend'] = '<b style="color: green">SMF has detected that your server has Zend installed.';
Add before that

Code: [Select]

$txt['detected_XCache'] = '<b style="color: green">SMF has detected that your server has XCache installed.';
I will talk to the developers about getting this added in a future version of SMF. Even though it's not well-known, there's no reason why we can't support it

[qtime]

this is a very great help for me, just installed xcache, because of the good reviews on the internet and this forum!!!

[Rumbaar]

Are you still experiencing this issue with your VPS georgepdog?

Not sure but this might free up some resources, try to disable/remove MessagePreviewOnHover 1.3 mod.

[dheeraj]

i have did the modifications above but i was not able to detect xcache in SMF any help??

[Rumbaar]

Yeah going from the code posted, there was never any indication the changes made were successful in the first place.  You might want to create your own new thread on the subject of xCache and SMF detection of that accelerator.

[Chawl]

Any updates?

X-Cache 1.3.0 seems to work perfectly, and "List Var Data" under XC admin page is populating nicely after the above hacks, on 1.1.9.

[Arantor]

I'll mark this topic solved, since the original question was posted two years ago. Any further concerns please do post in another topic with details of your specific situation.