News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Performance Issue of SMF

Started by humbleworld, March 19, 2012, 06:25:31 AM

Previous topic - Next topic

humbleworld

Hello Friends,

My forum site is very slow and always has a connection problem with the database. I asked the host about the problem and based on their diagnosis, below is the problem:

The below query is also a similar one which causes performance issues.[/size]----CREATE TEMPORARY TABLE smf_topics_posted_in ( id_topic mediumint(8) unsigned NOT NULL default '0', id_board smallint(5) unsigned NOT NULL default '0', id_last_msg int(10) unsigned NOT NULL default '0', id_msg int(10) unsigned NOT NULL default '0', PRIMARY KEY (id_topic) ) SELECT t.id_topic, t.id_board, t.id_last_msg, IFNULL(lmr.id_msg, 0) AS id_msg FROM smf_messages AS m INNER JOIN smf_topics AS t ON (t.id_topic = m.id_topic) LEFT JOIN smf_log_mark_read AS lmr ON (lmr.id_board = t.id_board AND lmr.id_member = 3639) WHERE m.id_member = 3639 GROUP BY m.id_topic ORDER BY null ----As you can see data from the tables has to go through filesort due to lack of appropriate indexes. I would suggest to either re-write the query listed above so it won't force MySQL to use file based sorting to return the data requested or disable this part of the site to see how things will run.

Will you please explain to me how I can fix this? What is this problem all about?

Thank you.

LiroyvH

Yeah, let me guess... you host with someone like HostGator, GoDaddy, DreamHost, etc.?

If that's the case:
It's the host. Not SMF. Overseller hostings can NOT cope with any site properly, not to mention sites that require mySQL or any type of performance at all.

Get a real host instead of a faker to solve the problem :)
((U + C + I)x(10 − S)) / 20xAx1 / (1 − sin(F / 10))
President/CEO of Simple Machines - Server Manager
Please do not PM for support - anything else is usually OK.

humbleworld

No, coreisp. im hosted with a reputable vps company. i can send u the name of my host via pm if u want. I just can't figure out this problem.

LiroyvH

You are one of the first I've encountered that has the problem on a VPS, although it depends on what you did with the configuration. :) Did you optimize mySQL?

What are the specs of your VPS?

Mentioning the company wont be necesarry, though I would scan quickly on the web to check if they are not known to oversell resources... Even VPS providers do that sometimes. *sigh*
((U + C + I)x(10 − S)) / 20xAx1 / (1 − sin(F / 10))
President/CEO of Simple Machines - Server Manager
Please do not PM for support - anything else is usually OK.

vbgamer45

Attach your Sources/Recent.php there is known issue with large forums with temp tables that are slower.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Roph

What's with the spam in your sig, humbleworld?

humbleworld

Quote from: vbgamer45 on March 19, 2012, 01:19:27 PM
Attach your Sources/Recent.php there is known issue with large forums with temp tables that are slower.

hi vbgamer45, here's the recent.php file. thanks.

VPS specs:

1.2 GB RAM
Intel Dual Xeon (10 CPUs)
Centos/WHM/Cpanel
Powerpanel
Disk space: 60 GB
Bandwidth: 3000 TB

What should be the ideal MYSQL, Apache and PHP settings for this spec?

humbleworld

oh signatures. never mind them. these sites will be developed later.

vbgamer45

Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

humbleworld

thank you so much, vbgamer. much appreciated.

vbgamer45

Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

Advertisement: