服务器CPU经常占用100%

顽童不老

在服务器MYSQL环境下查询，PRE_FORUM_THREAD表读取频繁，最多的是帖子查看数加1，求高手怎么解决？谢谢。

顽童不老

求高手指教。

新化文印网

被cc攻击吧

顽童不老

怎样查看pre_forum_thread表建没有建索引？

顽童不老

新化文印网 发表于 2021-10-19 10:09
被cc攻击吧

不是的，是正常访问。

人工智能实验室

如果网站较大，mysql服务带来的资源开销远大于web服务，这时候可以将web服务和mysql分开放置在不同的服务器下（同机房的不同服务器），或者将mysql转移到云数据库，然后关闭服务器本地的mysql服务。

顽童不老

人工智能实验室 发表于 2021-10-19 14:59
如果网站较大，mysql服务带来的资源开销远大于web服务，这时候可以将web服务和mysql分开放置在不同的服务器 ...

谢谢。现在数据库10.11G，服务器是8G内存，四核CPU，应该能处理吧？

顽童不老

Id    | User | Host            | db     | Command | Time | State                        | Info                                                                                                 |
+-------+------+-----------------+--------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| 50935 | root | localhost:57572 | NULL   | Query   |    0 | starting                     | show processlist                                                                                     |
| 50946 | root | localhost:57584 | ultrax | Query   |    3 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('975363')                 |
| 50949 | root | localhost:57587 | ultrax | Query   |    3 | Creating sort index          | SELECT tid FROM pre_forum_thread WHERE fid=4 AND displayorder>=0 AND closed=0 AND lastpost>144616211 |
| 50951 | root | localhost:57589 | ultrax | Query   |    3 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('731958')                 |
| 50954 | root | localhost:57592 | ultrax | Query   |    2 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('962034')                 |
| 50956 | root | localhost:57594 | ultrax | Query   |    3 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('382332')                 |
| 50957 | root | localhost:57595 | ultrax | Query   |    3 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('643637')                 |
| 50961 | root | localhost:57599 | ultrax | Query   |    3 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('870809')                 |
| 50962 | root | localhost:57600 | ultrax | Query   |    3 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('968070')                 |
| 50963 | root | localhost:57601 | ultrax | Query   |    2 | Creating sort index          | SELECT tid FROM pre_forum_thread WHERE fid=4 AND displayorder>=0 AND closed=0 AND lastpost<159407660 |
| 50965 | root | localhost:57603 | ultrax | Query   |    3 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('926051')                 |
| 50966 | root | localhost:57604 | ultrax | Query   |    3 | Creating sort index          | SELECT tid FROM pre_forum_thread WHERE fid=4 AND displayorder>=0 AND closed=0 AND lastpost<162374567 |
| 50972 | root | localhost:57610 | ultrax | Query   |    2 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('112994')                 |
| 50973 | root | localhost:57611 | ultrax | Query   |    2 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('302205')                 |
| 50975 | root | localhost:57613 | ultrax | Query   |    3 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('303934')                 |
| 50977 | root | localhost:57615 | ultrax | Query   |    3 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('599011')                 |
| 50978 | root | localhost:57616 | ultrax | Query   |    3 | Creating sort index          | SELECT tid FROM pre_forum_thread WHERE fid=4 AND displayorder>=0 AND closed=0 AND lastpost<153872669 |
| 50979 | root | localhost:57617 | ultrax | Query   |    3 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('888190')                 |
| 50982 | root | localhost:57620 | ultrax | Query   |    2 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('32403')                  |
| 50991 | root | localhost:57629 | ultrax | Query   |    2 | Creating sort index          | SELECT tid FROM pre_forum_thread WHERE fid=4 AND displayorder>=0 AND closed=0 AND lastpost<160971512 |
| 50992 | root | localhost:57630 | ultrax | Query   |    2 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('466796')                 |
| 50993 | root | localhost:57631 | ultrax | Query   |    2 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('964261')                 |
| 51004 | root | localhost:57642 | ultrax | Query   |    2 | Creating sort index          | SELECT tid FROM pre_forum_thread WHERE fid=5 AND displayorder>=0 AND closed=0 AND lastpost<151115804 |
| 51006 | root | localhost:57644 | ultrax | Query   |    2 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('280944')                 |
| 51008 | root | localhost:57646 | ultrax | Query   |    2 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('883250')                 |
| 51009 | root | localhost:57647 | ultrax | Query   |    2 | Creating sort index          | SELECT tid FROM pre_forum_thread WHERE fid=4 AND displayorder>=0 AND closed=0 AND lastpost>152180770 |
| 51013 | root | localhost:57651 | ultrax | Query   |    2 | Creating sort index          | SELECT tid FROM pre_forum_thread WHERE fid=4 AND displayorder>=0 AND closed=0 AND lastpost>157390993 |
| 51014 | root | localhost:57652 | ultrax | Query   |    2 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('422998')                 |
| 51015 | root | localhost:57653 | ultrax | Query   |    1 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('102617')                 |
| 51018 | root | localhost:57656 | ultrax | Query   |    1 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('823133')                 |
| 51019 | root | localhost:57657 | ultrax | Query   |    2 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('309797')                 |
| 51023 | root | localhost:57661 | ultrax | Query   |    2 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('284575')                 |
| 51028 | root | localhost:57666 | ultrax | Query   |    1 | Creating sort index          | SELECT tid FROM pre_forum_thread WHERE fid=4 AND displayorder>=0 AND closed=0 AND lastpost<159619952 |
| 51029 | root | localhost:57667 | ultrax | Query   |    1 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('168227')                 |
| 51030 | root | localhost:57668 | ultrax | Query   |    1 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('912145')                 |
| 51031 | root | localhost:57669 | ultrax | Query   |    1 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('240808')                 |
| 51034 | root | localhost:57672 | ultrax | Query   |    1 | Waiting for table level lock | UPDATE LOW_PRIORITY pre_forum_thread SET `views`=`views`+'1' WHERE tid IN ('911101')                 |
+-------+------+-----------------+--------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
37 rows in set (0.00 sec)

allthebest

顽童不老 发表于 2021-10-20 11:00
Id    | User | Host            | db     | Command | Time | State                        | Info      ...

你用mysql8.0，降低一点吧！

顽童不老

allthebest 发表于 2021-10-21 06:41
你用mysql8.0，降低一点吧！

DZ论坛支持MYSQL8.0吗？