1. Store
  2. Apps
  3. Hardware
  4. Support
  5. Solutions

ClearFoundation

About Me

Basic Information

Whereabouts

Country
United States

Web Links

Peter Finch
Peter Finch
  • Karma
  • Member since
  • Sunday, 16 September 2012 18:36
  • Last online
  • 3 days ago
  • Profile views
  • 1490 views
3 days ago
Peter Finch replied to the topic Re:close to 100% CPU load in the forums.
Here it is Saturday morning with nothing planned so what do? I know, I'll play with my ClearOS firewalls some more.

Changing reports tables to INNODB showed no improvement in CPU or load. Instead of one running query and a dozen locked ones I now have about 150 running queries, some running for nearly a minute. IO Wait is very low so not a disk issue, bound up in CPU. I will revert the INNODB change.

Think it is time to just uninstall the offending modules. Or is there an easy way to just turn all the network reporting off for now so I can enable again later to test future updates?

I include below a new MySQL process list after the change to INNODB. Maybe there is a clue in here for Devs. I edited out 100 or so similar queries in the list to keep the post small.

I am surprised at all the IPV6 references since at one point I disabled IPV6 but that was apparently reverted by a subsequent ClearOS update.

Peter

Code:


mysql> select * from information_schema.processlist order by time desc;
+------+---------+-----------------+---------+---------+------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID   | USER    | HOST            | DB      | COMMAND | TIME | STATE     | INFO                                                                                                                                                                               |
+------+---------+-----------------+---------+---------+------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 6720 | reports | localhost:43670 | reports | Query   |   51 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+76, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408136400) = stamp_inserted AND ip_dst='64.34.185.196'            |
| 7404 | reports | localhost:56232 | reports | Query   |   50 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408186800) = stamp_inserted AND ip_dst='ff02::1:ff9f:bcb0'        |
| 5646 | reports | localhost:45270 | reports | Query   |   49 | Updating  | UPDATE `network_detail_external` SET packets=packets+3, bytes=bytes+216, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408053000) = stamp_inserted AND ip_dst='ff02::1:ff01:1205'       |
| 5118 | reports | localhost:53887 | reports | Query   |   48 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408009200) = stamp_inserted AND ip_dst='ff02::1:ff4b:d3c2'        |
| 4626 | reports | localhost:37050 | reports | Query   |   47 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1407969000) = stamp_inserted AND ip_dst='ff02::1:ff0b:b362'        |
| 6204 | reports | localhost:46992 | reports | Query   |   46 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408096200) = stamp_inserted AND ip_dst='ff02::1:ff86:1f62'        |
| 3439 | reports | localhost:40280 | reports | Query   |   45 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1407873000) = stamp_inserted AND ip_dst='ff02::1:ff81:5d42'        |
| 7329 | reports | localhost:50926 | reports | Query   |   44 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408182000) = stamp_inserted AND ip_dst='ff02::1:ff40:551e'        |
| 6925 | reports | localhost:36553 | reports | Query   |   43 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408152600) = stamp_inserted AND ip_dst='ff02::1:fffe:aca0'        |
| 3725 | reports | localhost:40670 | reports | Query   |   43 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1407895800) = stamp_inserted AND ip_dst='ff02::1:ffb5:4c92'        |
| 7550 | reports | localhost:41649 | reports | Query   |   42 | Updating  | UPDATE `network_detail_external` SET packets=packets+36, bytes=bytes+2176, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408196400) = stamp_inserted AND ip_dst='8.8.8.8'               |
| 7549 | reports | localhost:41648 | reports | Query   |   42 | Updating  | UPDATE `network_detail_external` SET packets=packets+5342, bytes=bytes+484555, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408196400) = stamp_inserted AND ip_src='24.99.223.144'     |
| 7063 | reports | localhost:54451 | reports | Query   |   42 | Updating  | UPDATE `network_detail_external` SET packets=packets+8377, bytes=bytes+12817799, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408162800) = stamp_inserted AND ip_src='208.111.161.254' |
| 7162 | reports | localhost:35507 | reports | Query   |   41 | Updating  | UPDATE `network_detail_external` SET packets=packets+2, bytes=bytes+120, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408170000) = stamp_inserted AND ip_src='173.194.37.73'           |
| 2501 | reports | localhost:38315 | reports | Query   |   41 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1407796200) = stamp_inserted AND ip_dst='ff02::1:ff95:b586'        |
| 7529 | reports | localhost:39270 | reports | Query   |   40 | Updating  | UPDATE `network_detail_external` SET packets=packets+12, bytes=bytes+942, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408194600) = stamp_inserted AND ip_src='69.171.248.65'          |
| 6936 | reports | localhost:37579 | reports | Query   |   38 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408153200) = stamp_inserted AND ip_dst='ff02::1:ff1d:77c0'        |
| 6129 | reports | localhost:40596 | reports | Query   |   38 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408090800) = stamp_inserted AND ip_dst='ff02::1:ff4b:f859'        |
| 7224 | reports | localhost:40203 | reports | Query   |   37 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408173000) = stamp_inserted AND ip_dst='ff02::1:ff5d:9b5a'        |
| 6249 | reports | localhost:51436 | reports | Query   |   36 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408099800) = stamp_inserted AND ip_dst='ff02::1:ff9e:4032'        |
| 2932 | reports | localhost:52159 | reports | Query   |   35 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1407831600) = stamp_inserted AND ip_dst='ff02::1:ffbf:fd2'         |
| 5583 | reports | localhost:39385 | reports | Query   |   34 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408047600) = stamp_inserted AND ip_dst='ff02::1:ff78:642c'        |
| 4098 | reports | localhost:45165 | reports | Query   |   33 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1407927000) = stamp_inserted AND ip_dst='ff02::1:ff61:b9aa'        |
| 5823 | reports | localhost:36929 | reports | Query   |   32 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408067400) = stamp_inserted AND ip_dst='ff02::1:fffe:6b8'         |
| 6384 | reports | localhost:37330 | reports | Query   |   31 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408110000) = stamp_inserted AND ip_dst='ff02::1:ff5a:d7e1'        |
| 7431 | reports | localhost:59477 | reports | Query   |   30 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408189200) = stamp_inserted AND ip_dst='ff02::1:fff4:7113'        |
| 7364 | reports | localhost:54129 | reports | Query   |   29 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408185000) = stamp_inserted AND ip_dst='ff02::1:ff97:7012'        |
. . .
. . .
| 5981 | reports | localhost:55764 | reports | Query   |    5 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408080000) = stamp_inserted AND ip_dst='ff02::1:ff50:4cf1'        |
| 6606 | reports | localhost:60888 | reports | Query   |    4 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408128000) = stamp_inserted AND ip_dst='ff02::1:ff3d:ded2'        |
| 7552 | reports | localhost:41651 | reports | Query   |    3 | Updating  | UPDATE `network_detail` SET packets=packets+4, bytes=bytes+304, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408196400) = stamp_inserted AND ip_dst='192.168.10.176'                   |
| 6981 | reports | localhost:44178 | reports | Query   |    3 | Updating  | UPDATE `network_detail_external` SET packets=packets+8, bytes=bytes+352, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408156800) = stamp_inserted AND ip_dst='17.173.254.222'          |
| 7530 | reports | localhost:39271 | reports | Query   |    2 | Updating  | UPDATE `network_detail_external` SET packets=packets+37, bytes=bytes+16825, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408194600) = stamp_inserted AND ip_dst='31.13.69.182'         |
| 7551 | reports | localhost:41650 | reports | Query   |    1 | Updating  | UPDATE `network_detail` SET packets=packets+156, bytes=bytes+28914, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408196400) = stamp_inserted AND ip_src='192.168.10.174'               |
| 6685 | reports | localhost:40176 | reports | Query   |    1 | Updating  | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+72, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408133400) = stamp_inserted AND ip_dst='ff02::1:ffec:3470'        |
| 7536 | reports | localhost       | NULL    | Query   |    0 | executing | select * from information_schema.processlist order by time desc                                                                                                                    |
+------+---------+-----------------+---------+---------+------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
147 rows in set, 1 warning (0.00 sec)

04:23 AM
4 days ago
Peter Finch replied to the topic Re:close to 100% CPU load in the forums.
Doh! I should have noticed that... I nnew it was locking at the table level from the process list but still had it in my head they were innodedb tables...

Good catch Tim! Let us know in a day or two how your load graphs look and I will alter mine as well if improvement noted. I was actually getting ready to just remove these modules this weekend in frustration.

Peter
02:43 PM

Wall

No wall post to show

My Forum Updates

Groups

No group joined by the user yet.