Large number of rows in database causing server to be overused

[codnerd]

From my webhost:
These are causing large numbers of rows to be read from your database which is causing degraded performance for all users on the server. You will need to take action and lower the number of rows from which are inside of these databases and specific rows that they are requesting information from.

This is causing the "SMF was unable to connect to the database." problem.

I have tried multiple things to get this to be fixed. The outcome each time is where:

Code Select Expand

*************************** 1. row ***************************
USER: custo96_custo
DB: custo96_custo
STATE: Waiting for table level lock
TIME: 22
COMMAND: Query
INFO: SELECT
c.id_cat, b.name AS bname, b.description, b.num_topics, b.member_groups,
b.id_parent, c.name AS cname, IFNULL(mem.id_member, 0) AS id_moderator,
mem.real_name, b.id_board, b.child_level,
b.id_theme, b.override_theme, b.count_posts, b.id_profile, b.redirect,
b.unapproved_topics, b.unapproved_posts, t.approved, t.id_member_started
FROM cfps_smf_boards AS b
INNER JOIN cfps_smf_topics AS t ON (t.id_topic = 12698)
LEFT JOIN cfps_smf_categories AS c ON (c.id_cat = b.id_cat)
LEFT JOIN cfps_smf_moderators AS mods ON (mods.id_board = t.id_board)
LEFT JOIN cfps_smf_members AS mem ON (mem.id_member = mods.id_member)
WHERE b.id_board = t.id_board
*************************** 2. row ***************************
USER: custo96_custo
DB: custo96_custo
STATE: Waiting for table level lock
TIME: 30
COMMAND: Query
INFO: UPDATE cfps_smf_members
SET id_msg_last_visit = 112819, last_login = 1390921890, member_ip = '109.199.172.211', member_ip2 = '109.199.172.211'
WHERE id_member = 296056
*************************** 3. row ***************************
USER: custo96_custo
DB: custo96_custo
STATE: Copying to tmp table
TIME: 120
COMMAND: Query
INFO: SELECT
l.id_download, l.name AS item_name, l.views, l.downloads, l.date, l.total_rates, l.author, mem.real_name, l.cat_id, c.name, c.redirect, IFNULL(f.id_download,0) AS is_favorite, IFNULL(log.id_download,0) AS new
FROM cfps_smf_downloads AS l
LEFT JOIN cfps_smf_members AS mem ON (mem.id_member = l.author)
INNER JOIN cfps_smf_downloads_categories AS c ON (l.cat_id = c.id_cat)
LEFT JOIN cfps_smf_downloads_favorites AS f ON (f.id_download = l.id_download AND f.id_member = 296035)
LEFT JOIN cfps_smf_downloads_log AS log ON (log.id_download = l.id_download AND log.id_member = 296035)

WHERE l.approved = 1
AND l.cat_id = 24
AND (FIND_IN_SET(0, c.permissions) OR FIND_IN_SET(4, c.permissions))
AND (log.time = 0 OR ISNULL(log.time))
ORDER BY l.date DESC
LIMIT 0, 10

[Kindred]

Get a new host.

[codnerd]

It's hostgator. I have been using them for years, and they are very reliable.

Any suggestions on how to fix this?

[margarett]

Your third query suggests it is not part of SMF "core", so you should have a downloads MOD or something related.

In either case, Kindred is probably right. Hostgator --> "Unlimited" everything and that is usually a sign of an oversold host.

How many users do you have online?

[codnerd]

Avg. 200 new members per day steady for the last few years. 5-10 posts per day, 1-2 topics per day. The download system is the active part of the site, the forum is made for archived reference for many people.

[margarett]

And users at the same time?

[codnerd]

Users, about 30 on per 30 minutes.
It has been a active site for years, but the forum and forum less over time as very little to talk about.

[青山 素子]

It's hostgator. I have been using them for years, and they are very reliable.

Hostgator was bought by EIG not too long ago. I can only guess they're starting their decline just like every other acquisition made by EIG.

Also, yes, unlimited is a lie. It just means they don't tell you the limits up-front. I wouldn't be surprised if they didn't adjust their ToS to lower limits and start stuffing more customers on fewer servers to cut costs. That seems to be the way the new owners operate.

[codnerd]

Switching to Ramnode, thanks.

[margarett]

VPS? That's quite a leap
Good luck!