|
|
Joined: Oct 2007
Posts: 531 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 531 Likes: 13 |
We're having a problem that makes the site either painfully slow or completely unreachable. When I ran top, mysqld was using over 700% of CPU. Running processlist showed a number of threads Waiting for table level lock. mysql -u root -p -e "SHOW PROCESSLIST;"
Enter password:
+------+------+-----------+------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| 5172 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT t1.POST_ID,t1.POST_PARENT_ID,t1.POST_POSTED_TIME,t2.USER_DISPLAY_NAME,t1.POST_SUBJECT,t1.POST |
| 5173 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT t1.POST_ID,t1.POST_PARENT_ID,t1.POST_POSTED_TIME,t2.USER_DISPLAY_NAME,t1.POST_SUBJECT,t1.POST |
| 5175 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT t1.POST_ID,t1.POST_PARENT_ID,t1.POST_POSTED_TIME,t2.USER_DISPLAY_NAME,t1.POST_SUBJECT,t1.POST |
| 5176 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('45005') |
| 5177 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT t1.POST_ID,t1.POST_PARENT_ID,t1.POST_POSTED_TIME,t2.USER_DISPLAY_NAME,t1.POST_SUBJECT,t1.POST |
| 5180 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT t1.POST_ID,t1.POST_PARENT_ID,t1.POST_POSTED_TIME,t2.USER_DISPLAY_NAME,t1.POST_SUBJECT,t1.POST |
| 5183 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('6248') |
| 5184 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT t1.POST_ID,t1.POST_PARENT_ID,t1.POST_POSTED_TIME,t2.USER_DISPLAY_NAME,t1.POST_SUBJECT,t1.POST |
| 5185 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT t1.POST_ID,t1.POST_PARENT_ID,t1.POST_POSTED_TIME,t2.USER_DISPLAY_NAME,t1.POST_SUBJECT,t1.POST |
| 5190 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('12074') |
| 5191 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('28319') |
| 5192 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT t1.POST_ID,t1.POST_PARENT_ID,t1.POST_POSTED_TIME,t2.USER_DISPLAY_NAME,t1.POST_SUBJECT,t1.POST |
| 5193 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('1504') |
| 5194 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('15338') |
| 5195 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT t1.POST_ID,t1.POST_PARENT_ID,t1.POST_POSTED_TIME,t2.USER_DISPLAY_NAME,t1.POST_SUBJECT,t1.POST |
| 5198 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT t1.POST_ID,t1.POST_PARENT_ID,t1.POST_POSTED_TIME,t2.USER_DISPLAY_NAME,t1.POST_SUBJECT,t1.POST |
| 5199 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('4378') |
| 5203 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('7745') |
| 5204 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('12163') |
| 5206 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('13406') |
| 5207 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('23098') |
| 5208 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('1504') |
| 5209 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT t1.POST_ID,t1.POST_PARENT_ID,t1.POST_POSTED_TIME,t2.USER_DISPLAY_NAME,t1.POST_SUBJECT,t1.POST |
| 5210 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('15452') |
| 5211 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT USER_ID, USER_DISPLAY_NAME
FROM sbf_USERS
WHERE USER_ID IN ('15565') |
| 5212 | ubb | localhost | ubbthreads | Query | 13 | executing | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5213 | ubb | localhost | ubbthreads | Query | 7 | executing | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5214 | ubb | localhost | ubbthreads | Query | 5 | executing | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5215 | ubb | localhost | ubbthreads | Query | 5 | executing | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5217 | ubb | localhost | ubbthreads | Query | 5 | executing | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5220 | ubb | localhost | ubbthreads | Query | 4 | executing | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5222 | ubb | localhost | ubbthreads | Query | 4 | executing | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5223 | ubb | localhost | ubbthreads | Query | 4 | executing | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5224 | ubb | localhost | ubbthreads | Query | 4 | executing | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5225 | ubb | localhost | ubbthreads | Query | 4 | executing | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5229 | ubb | localhost | ubbthreads | Query | 3 | executing | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5230 | ubb | localhost | ubbthreads | Query | 3 | executing | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5231 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5232 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5233 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5234 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5235 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5236 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT
p.POST_ID, u.USER_DISPLAY_NAME, u.USER_IS_BANNED, p.POST_POSTED_TIME, p.POST_POSTER_IP,
|
| 5237 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | UPDATE sbf_USERS
SET USER_SESSION_ID = 'dd458505749b2941217ddd59394240e8'
WHERE USER_ID = |
| 5238 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT
t2.USER_TOPIC_VIEW_TYPE, t2.USER_TOPICS_PER_PAGE, t2.USER_POSTS_PER_TOPIC, t2.USER_SHOW_S |
| 5239 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT
t2.USER_TOPIC_VIEW_TYPE, t2.USER_TOPICS_PER_PAGE, t2.USER_POSTS_PER_TOPIC, t2.USER_SHOW_S |
| 5240 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT
t2.USER_TOPIC_VIEW_TYPE, t2.USER_TOPICS_PER_PAGE, t2.USER_POSTS_PER_TOPIC, t2.USER_SHOW_S |
| 5241 | ubb | localhost | ubbthreads | Query | 3 | Waiting for table level lock | SELECT
t1.USER_ID, t1.USER_DISPLAY_NAME, t1.USER_PASSWORD, t1.USER_SESSION_ID, t1.USER_MEMBERSHI |
| 5242 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT
t2.USER_TOPIC_VIEW_TYPE, t2.USER_TOPICS_PER_PAGE, t2.USER_POSTS_PER_TOPIC, t2.USER_SHOW_S |
| 5243 | ubb | localhost | ubbthreads | Query | 2 | Waiting for table level lock | SELECT
t2.USER_TOPIC_VIEW_TYPE, t2.USER_TOPICS_PER_PAGE, t2.USER_POSTS_PER_TOPIC, t2.USER_SHOW_S |
| 5244 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
+------+------+-----------+------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+All our tables are MyISAM. Would converting them to INNODB be beneficial?
|
|
|
|
|
Joined: Jun 2006
Posts: 16,532 Likes: 150
|
|
Joined: Jun 2006
Posts: 16,532 Likes: 150 |
I'd go check if you're being DDoSed or see where your traffic is originating from; tools like Cloudflare limit abusive bot traffic and clearly identify if you're being attacked and what countries to ban to alleviate abusive traffic. UBB.threads uses the MySQL database as a storage engine, every user accessing the forum will have multiple hits to your database while they're visiting your site; the system should clear these hits automatically; during times of traffic increases (especially on large forums with lots of bot activity) you'll continue to see large traffic hits to the database, this is where services like Cloudflare (which are free) come in handy to mitigate attacks.
|
|
|
|
|
Joined: Oct 2007
Posts: 531 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 531 Likes: 13 |
We have been having problems with excessive accesses for a while. I finally got fed up with it and setup firewalld. I'm now blocking a number of networks. rich rules:
rule family="ipv4" source address="47.84.0.0/16" drop
rule family="ipv4" source address="47.79.0.0/16" drop
rule family="ipv4" source address="57.141.0.0/24" drop
rule family="ipv4" source address="47.77.0.0/16" drop
rule family="ipv4" source address="47.80.0.0/16" drop
rule family="ipv4" source address="47.81.0.0/16" drop
rule family="ipv4" source address="34.174.0.0/16" drop
rule family="ipv4" source address="47.74.0.0/16" drop
rule family="ipv4" source address="185.55.240.0/24" drop
rule family="ipv4" source address="47.86.0.0/16" drop
rule family="ipv4" source address="47.78.0.0/16" drop
rule family="ipv4" source address="179.49.0.0/16" reject
rule family="ipv4" source address="47.85.0.0/16" drop
rule family="ipv4" source address="216.73.216.0/24" log prefix="BOT BLOCK" drop
rule family="ipv4" source address="17.241.219.0/24" drop
rule family="ipv4" source address="203.162.0.0/24" drop
rule family="ipv4" source address="159.65.0.0/24" drop
rule family="ipv4" source address="45.228.0.0/16" reject
rule family="ipv4" source address="20.171.207.0/24" drop
rule family="ipv4" source address="47.75.0.0/16" drop
rule family="ipv4" source address="57.141.4.0/24" drop
rule family="ipv4" source address="47.76.0.0/16" drop
rule family="ipv4" source address="47.83.0.0/16" drop
rule family="ipv4" source address="47.87.0.0/16" drop
rule family="ipv4" source address="47.82.0.0/16" drop
rule family="ipv4" source address="57.141.6.0/24" drop If I have to block more, I will. But, should I convert to INNODB? Or will that not really help?
|
|
|
|
|
Joined: Jun 2006
Posts: 16,532 Likes: 150
|
|
Joined: Jun 2006
Posts: 16,532 Likes: 150 |
I don't believe that it'd really help. InnoDB will write to disk faster,however MyISAM will read from the database faster (which is the majority of your traffic). InnoDB will also restrict your searches to no longer support full-text searches. We have been having problems with excessive accesses for a while. I finally got fed up with it and setup firewalld. I'm now blocking a number of networks. A Content Delivery Network such as Cloudflare would automatically mitigate most problems and give you the ability to block entire countries with just a few clicks.
|
|
|
|
|
Joined: Oct 2007
Posts: 531 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 531 Likes: 13 |
I don't know if our hosting company has that available. I'll check. They just posted this: hanks for your response.
We found that the server load was alarmingly high, ranging between 40 and 50 , primarily caused by the MySQL service utilising almost all available CPU resources .
Top resource utilising MySQL process:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5385 mysql 20 0 4969428 484784 38516 S 796.4 3.0 106:33.43 /usr/sbin/mysqld
Additionally, we noticed multiple connection hits originating from the IP ranges 45.228.0.0/16 and 179.49.0.0/16 , which appeared to resemble a DDoS-style attack targeting port 443 on your server. To mitigate this, we have blocked both IP ranges in the firewalld service on your server.
Connection hits: https://hw-screenshots.sea-proxy.wi...ac3842b3-2ab7-4c6a-a067-c557df0401e5.png
Afterwards, we restarted both the httpd and MySQL services. Upon further inspection, we observed that MySQL was executing multiple long-running queries for the database “ubbthreads ”, which were continuously looping and consuming excessive CPU resources.
SQL long-running queries: https://hw-screenshots.sea-proxy.wi...a143b5d3-c303-461b-8dcf-9c823908d36f.png
We have attached two files for your reference, named SQL_QUERY.txt and IP_HITS.txt , which contain details of the long-running SQL queries and IP connections hitting the server.
It appears that the issue may be related to application-level code looping or inefficient database queries . We recommend having your developer review and optimise the application code and database related to the domain “stovebolt.com ”.
In addition, we suggest implementing rate limiting for MySQL connections and adjusting interactive timeout values in the MySQL configuration to prevent long-running queries from continuously executing.
Please note that the server did not have the sar utility installed earlier, which made it difficult to analyse historical resource usage and traffic trends. We have now installed sar, which will start collecting system activity data to help analyse future performance and load patterns.
Regarding the conversion of MyISAM to InnoDB, since the issue is being caused by long-running SQL queries in your database, this conversion will not fix inefficient or looping queries in the application;, those queries still need to be optimised by your developer.
Additionally, the conversion can be risky on large tables, as it may lock the tables during the process and impact your live site.
We recommend focusing first on optimising the slow queries and application code loop with the help of a developer to resolve the performance issues effectively.
Last edited by Baldeagle; 10/15/2025 11:50 PM.
|
|
|
|
|
Joined: Jun 2006
Posts: 16,532 Likes: 150
|
|
Joined: Jun 2006
Posts: 16,532 Likes: 150 |
Cloudflare is a service that you place in front of your web host; your host does not have to offer it. You just plugin the IP address and MX servers of your web host into the Cloudflare configuration page and then set your DNS servers to use Cloudflare instead of your host. It acts like a firewall in front of your server: User -> Cloudflare -> Host Cloudflare does a pretty good job of identifying existing IP Addresses and MX servers when you configure it, but your web host should be able to provide any additional details if they're needed.[
|
|
|
|
|
Joined: Oct 2007
Posts: 531 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 531 Likes: 13 |
|
|
|
|
|
Joined: Oct 2007
Posts: 531 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 531 Likes: 13 |
Turns out we were under attack by a DDoS. The networks involved have been blocked at the firewall.
|
|
|
|
|
Joined: Oct 2007
Posts: 531 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 531 Likes: 13 |
I got this when I posted my last.
Gateway Timeout
The gateway did not receive a timely response from the upstream server or application.
Additionally, a 504 Gateway Timeout error was encountered while trying to use an ErrorDocument to handle the request.
|
|
|
|
|
Joined: Oct 2007
Posts: 531 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 531 Likes: 13 |
We are continuing to have problems with performance. I have asked the hosting company to investigate. This is their answer: Thank you for being patient.
We’ve reviewed the database process list, and it appears that multiple queries are waiting for table-level locks on the ubbthreads DB. This is causing temporary delays for some operations. The main reason is that several queries are accessing the same tables simultaneously, leading to contention.
https://hw-screenshots.sea-proxy.wi...77c48b4b-f997-473b-819c-134dc3f38147.png
To fix this issue, we recommend:
Improving your database queries and indexes to make them run faster.
Break large operations into smaller parts to avoid putting too much load on the database.
We appreciate your patience and understanding on this matter.
Feel free to contact us if you have further queries or concerns.
Thank you.
|
|
|
|
0 members (),
490
guests, and
102
robots. |
|
Key:
Admin,
Global Mod,
Mod
|
|
|
|
