Hostgator temporarily banned my hosting LOL

caiocald · Post by **caiocald** » Tue Aug 11, 2020 9:56 pm

just received an email saying that my account was consuming a large part of the server and that I will need to optimize the scripts to get it back.

They informed me that the processes that are consuming a large part are these here:

Code: Select all

Running Processes:
proje148 818880 5.5 0.0 367172 46032 ? S 16:14 0:00 /opt/cpanel/ea-php73/root/usr/bin/php-cgi /home2/proje148/public_html/app.php
proje148 818881 4.0 0.0 367172 46172 ? S 16:14 0:00 /opt/cpanel/ea-php73/root/usr/bin/php-cgi /home2/proje148/public_html/app.php
proje148 818882 6.3 0.0 367172 46088 ? S 16:14 0:00 /opt/cpanel/ea-php73/root/usr/bin/php-cgi /home2/proje148/public_html/app.php
proje148 818883 7.0 0.0 367172 46016 ? S 16:14 0:00 /opt/cpanel/ea-php73/root/usr/bin/php-cgi /home2/proje148/public_html/app.php
proje148 818885 6.3 0.0 367172 46156 ? S 16:14 0:00 /opt/cpanel/ea-php73/root/usr/bin/php-cgi /home2/proje148/public_html/app.php
proje148 818888 5.6 0.0 367172 46172 ? S 16:14 0:00 /opt/cpanel/ea-php73/root/usr/bin/php-cgi /home2/proje148/public_html/app.php
proje148 818889 6.6 0.0 367172 46016 ? S 16:14 0:00 /opt/cpanel/ea-php73/root/usr/bin/php-cgi /home2/proje148/public_html/app.php
proje148 818890 6.6 0.0 367172 46012 ? S 16:14 0:00 /opt/cpanel/ea-php73/root/usr/bin/php-cgi /home2/proje148/public_html/app.php
proje148 818935 11.0 0.0 367176 45864 ? S 16:14 0:00 /opt/cpanel/ea-php73/root/usr/bin/php-cgi /home2/proje148/public_html/app.php

Running Queries:
*************************** 1. row ***************************
STATE: Sending data
TIME: 2
COMMAND: Query
INFO: SELECT u.username, u.user_id, u.user_rank, u.user_posts, u.user_sig, u.user_avatar, u.user_avatar_type, u.user_avatar_width,
u.user_avatar_height, p.post_id, p.post_text, t.topic_id, t.forum_id, t.topic_title, t.topic_last_post_time,
t.topic_first_poster_colour, t.topic_last_post_id, t.topic_last_poster_name, t.topic_last_poster_colour, t.topic_posts_approved FROM (phpbbkk_topics t CROSS JOIN phpbbkk_posts p CROSS JOIN phpbbkk_users u) WHERE p.topic_id = t.topic_id
AND p.post_visibility = 1
AND p.post_id = t.topic_last_post_id
AND u.user_id = p.poster_id ORDER BY t.topic_last_post_time DESC
LIMIT 4
*************************** 2. row ***************************
STATE: Sending data
TIME: 3
COMMAND: Query
INFO: SELECT COUNT(p.post_id) AS total
FROM phpbbkk_topics t, phpbbkk_posts p
WHERE p.topic_id = t.topic_id
AND p.post_visibility = 1
AND p.post_id = t.topic_last_post_id
*************************** 3. row ***************************
STATE: Sending data
TIME: 2
COMMAND: Query
INFO: SELECT u.username, u.user_id, u.user_rank, u.user_posts, u.user_sig, u.user_avatar, u.user_avatar_type, u.user_avatar_width,
u.user_avatar_height, p.post_id, p.post_text, t.topic_id, t.forum_id, t.topic_title, t.topic_last_post_time,
t.topic_first_poster_colour, t.topic_last_post_id, t.topic_last_poster_name, t.topic_last_poster_colour, t.topic_posts_approved FROM (phpbbkk_topics t CROSS JOIN phpbbkk_posts p CROSS JOIN phpbbkk_users u) WHERE p.topic_id = t.topic_id
AND p.post_visibility = 1
AND p.post_id = t.topic_last_post_id
AND u.user_id = p.poster_id ORDER BY t.topic_last_post_time DESC
LIMIT 4
*************************** 4. row ***************************
STATE: Sending data
TIME: 3
COMMAND: Query
INFO: SELECT COUNT(p.post_id) AS total
FROM phpbbkk_topics t, phpbbkk_posts p
WHERE p.topic_id = t.topic_id
AND p.post_visibility = 1
AND p.post_id = t.topic_last_post_id
*************************** 5. row ***************************
STATE: Sending data
TIME: 1
COMMAND: Query
INFO: SELECT COUNT(p.post_id) AS total
FROM phpbbkk_topics t, phpbbkk_posts p
WHERE t.forum_id NOT IN (37, 38, 39) AND p.topic_id = t.topic_id
AND p.post_visibility = 1
AND p.post_id = t.topic_last_post_id
*************************** 6. row ***************************
STATE: Sending data
TIME: 3
COMMAND: Query
INFO: SELECT COUNT(p.post_id) AS total
FROM phpbbkk_topics t, phpbbkk_posts p
WHERE p.topic_id = t.topic_id
AND p.post_visibility = 1
AND p.post_id = t.topic_last_post_id
*************************** 7. row ***************************
STATE: Sending data
TIME: 2
COMMAND: Query
INFO: SELECT u.username, u.user_id, u.user_rank, u.user_posts, u.user_sig, u.user_avatar, u.user_avatar_type, u.user_avatar_width,
u.user_avatar_height, p.post_id, p.post_text, t.topic_id, t.forum_id, t.topic_title, t.topic_last_post_time,
t.topic_first_poster_colour, t.topic_last_post_id, t.topic_last_poster_name, t.topic_last_poster_colour, t.topic_posts_approved FROM (phpbbkk_topics t CROSS JOIN phpbbkk_posts p CROSS JOIN phpbbkk_users u) WHERE p.topic_id = t.topic_id
AND p.post_visibility = 1
AND p.post_id = t.topic_last_post_id
AND u.user_id = p.poster_id ORDER BY t.topic_last_post_time DESC
LIMIT 4
*************************** 8. row ***************************
STATE: Sending data
TIME: 2
COMMAND: Query
INFO: SELECT u.username, u.user_id, u.user_rank, u.user_posts, u.user_sig, u.user_avatar, u.user_avatar_type, u.user_avatar_width,
u.user_avatar_height, p.post_id, p.post_text, t.topic_id, t.forum_id, t.topic_title, t.topic_last_post_time,
t.topic_first_poster_colour, t.topic_last_post_id, t.topic_last_poster_name, t.topic_last_poster_colour, t.topic_posts_approved FROM (phpbbkk_topics t CROSS JOIN phpbbkk_posts p CROSS JOIN phpbbkk_users u) WHERE p.topic_id = t.topic_id
AND p.post_visibility = 1
AND p.post_id = t.topic_last_post_id
AND u.user_id = p.poster_id ORDER BY t.topic_last_post_time DESC
LIMIT 4
*************************** 9. row ***************************
STATE: Sending data
TIME: 2
COMMAND: Query
INFO: SELECT COUNT(p.post_id) AS total
FROM phpbbkk_topics t, phpbbkk_posts p
WHERE p.topic_id = t.topic_id
AND p.post_visibility = 1
AND p.post_id = t.topic_last_post_id

any hints about the origin of these processes and what can I do to optimize them?

Siguza · Post by **Siguza** » Tue Aug 11, 2020 10:09 pm

How is this funny?

Toxyy · Post by **Toxyy** » Tue Aug 11, 2020 10:15 pm

Can you create a support ticket? What extensions do you have installed? Do you have gzip caching enabled?

caiocald · Post by **caiocald** » Tue Aug 11, 2020 10:22 pm

qcz wrote: ↑
Tue Aug 11, 2020 10:09 pm
How is this funny?

I'm being sarcastic

caiocald · Post by **caiocald** » Tue Aug 11, 2020 10:23 pm

Toxyy wrote: ↑
Tue Aug 11, 2020 10:15 pm
Can you create a support ticket? What extensions do you have installed? Do you have gzip caching enabled?

I don't know if this would be a ticket case, it's not a problem with phpbb.
I just need help to identify where these processes are coming from.

Do you have gzip caching enabled? No.

Toxyy · Post by **Toxyy** » Tue Aug 11, 2020 11:48 pm

The support ticket just generates some bbcode for you to post here with more info about your setup and extensions.

The only query I can find that it is similar to in the db is here , but it is not this query exactly, and the topics/posts in the FROM are flipped, so I'm guessing this is from an extension. I couldn't find anything about the other query really, but it's strange that it would hang up with LIMIT 4 there. So that's why I wanted to know what extensions you had installed.

caiocald · Post by **caiocald** » Wed Aug 12, 2020 1:08 am

thank you very much for the comments. I'm still at the beginning of my studies in programming and I don't know how to interpret this data.

and the topics/posts in the FROM are flipped, so I'm guessing this is from an extension wow, perfect observation! do you have a link for me to study more about? I would like to understand how you thought about it.

but it's strange that it would hang up with LIMIT 4 there. what does this limit 4 mean? is there a link explaining this meaning as well?

my extensions are:
Auto Groups 2.0.2
BBAvatars 1.3.3
BBbreadcrumbs 1.0.1
BBDonations 1.0.9
BBlazy 1.0.9
BBopenGraph 1.0.6
BBseo 1.1.0
BBsidebar 1.4.4
BBsitemap 2.0.2
Categorize Announcements and Stickies 1.0.1
CloudFlare IP 1.1.2
Fancy Lazy Topics Loader 2.1.15
Pages 2.0.3
phpBB Media Embed PlugIn 1.1.1
PM Notify & Guest Register bar
PM Welcome 1.0.1
Pontuação ENEM NOVA 1.0.0-dev (I developed this extension, I suppose it could be it.)
Simple mentions 1.0.5
SiteSplat BBCore 3.2.7
Snappy Smart Reply

Toxyy · Post by **Toxyy** » Wed Aug 12, 2020 1:37 am

caiocald wrote: ↑
Wed Aug 12, 2020 1:08 am
and the topics/posts in the FROM are flipped, so I'm guessing this is from an extension wow, perfect observation!

Yeah, in the link I sent you it writes FROM ' . POSTS_TABLE . ' p, ' . TOPICS_TABLE . " t, so in the actual query that's outputted the order should be preserved if you tell php to echo the query before execution AFAIK. I searched for SELECT COUNT(p.post_id) AS total in the phpbb github and this was the best result that fit the query that's being executed, but as I said, it's not entirely the same. There aren't many that use as total so that's why I'm assuming it's from an extension.

do you have a link for me to study more about? I would like to understand how you thought about it.
...
but it's strange that it would hang up with LIMIT 4 there. what does this limit 4 mean? is there a link explaining this meaning as well?

LIMIT 4 means it only takes the first 4 results of the query, so if you query for the entire posts table for example, you'll only get the first 4 results.

Here is an article about LIMIT in MySQL.

my extensions are:
...
Pontuação ENEM NOVA 1.0.0-dev (I developed this extension, I suppose it could be it.)
...

Unless you added some SQL queries to it, I doubt it, but it wouldn't hurt to profile your extension using xdebug to produce a dump file to view in kcachegrind/qcachegrind. It could be related to it I suppose, but you'd have to make a github repository, though the before that it would be best to profile it and see if you can find the slowdowns yourself.

I can't confirm the other query isn't phpbb related, but the only queries that I could find that cross join those tables are related to the mcp and searching.

If you profile your forum yourself using xdebug on a local setup like LAMP/XAAMP or something, you can analyze the dump files per page to see what's causing the hangups. You can also set the xdebug aggregate flag that will keep adding onto your dump files as you use the forum, so you can do a few things before analyzing it and seeing what's taking up your memory/cpu.

mamba · Post by **mamba** » Wed Aug 12, 2020 4:12 am

My CPU problems were caused by an extension. So were the CPU problems of another guy here with CPU spikes.

Get rid of all extensions and then add them back one by one all the while watching your cpanel's Resource Usage graphs

Oh, and get another host. I recently left Hostgator because they simply suspended my account without warning, and it took me 3 days to get them to put it back.

WelshPaul · Post by **WelshPaul** » Wed Aug 12, 2020 8:44 am

Check your Fancy Lazy Topics Loader settings in ACP as I think it is this extension causing your problem.

Board Configuration > Board Settings > Topic Fetch Limit
(Set the amount in days for how far back to fetch topics from the database. 0 disables the limit.)

I'm guessing you have zero for the above setting? If so, set it to 20 or less.

caiocald · Post by **caiocald** » Wed Aug 12, 2020 10:11 am

Toxyy wrote: ↑
Wed Aug 12, 2020 1:37 am

Thanks for the class!
It clarified a lot! I'll do it!

caiocald · Post by **caiocald** » Wed Aug 12, 2020 10:13 am

mamba wrote: ↑
Wed Aug 12, 2020 4:12 am
My CPU problems were caused by an extension. So were the CPU problems of another guy here with CPU spikes.

Get rid of all extensions and then add them back one by one all the while watching your cpanel's Resource Usage graphs

Oh, and get another host. I recently left Hostgator because they simply suspended my account without warning, and it took me 3 days to get them to put it back.

It was exactly the same with me.

I was already having error 500 problem with them. I opened a call asking them to tell me what queries were causing the cpu overhead.

They replied that they couldn't help me with that.

Now, months later, SURPRISE, they took my website down and sent me an email with the queries I had asked for.

A real joke.

caiocald · Post by **caiocald** » Wed Aug 12, 2020 10:14 am

WelshPaul wrote: ↑
Wed Aug 12, 2020 8:44 am
Check your Fancy Lazy Topics Loader settings in ACP as I think it is this extension causing your problem.

I changed it.
Now I have to wait for the hostgator to evaluate to release
Thank you!

WelshPaul · Post by **WelshPaul** » Wed Aug 12, 2020 10:34 am

The processes you listed in your original post are definitely from the Fancy Lazy Loader extension. How you configure it in ACP should be based on how many new posts you get in a day and what your server specs are capable of handling.

Good luck.

caiocald · Post by **caiocald** » Wed Aug 12, 2020 10:40 am

his comment made perfect sense.

I was receiving about 100 new messages a day

advertisements