Forums

Ronald
Ronald
Offline
Resolved
0 votes
Not sure if it is the same problem as other report, but my server Clearos 6.5 is running close to 100% now for a number of days. I am a newby on managing a server, so I have tried to replicate what I found on this forum to test where the load is coming from.

I think it is related to system-mysqld although top tells me that it is not driving the load to 100%, but it is almost continuously on top of top.

top - 23:30:31 up 3:04, 3 users, load average: 2.41, 1.99, 2.17
Tasks: 391 total, 2 running, 389 sleeping, 0 stopped, 0 zombie
Cpu(s): 11.9%us, 2.7%sy, 0.0%ni, 0.0%id, 85.4%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 1030072k total, 1016564k used, 13508k free, 1696k buffers
Swap: 2064376k total, 1418040k used, 646336k free, 101332k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2676 system-m 20 0 686m 537m 3116 S 12.6 53.5 5:45.86 system-mysqld
19078 root 20 0 2988 1240 828 R 0.7 0.1 0:00.13 top
16 root 20 0 0 0 0 R 0.3 0.0 0:24.71 kblockd/0
29 root 20 0 0 0 0 S 0.3 0.0 0:39.52 kswapd0
3860 root 20 0 22908 13m 13m S 0.3 1.3 0:15.73 pmacctd
3867 root 20 0 22100 9884 5848 S 0.3 1.0 0:07.91 pmacctd
4457 snort 20 0 297m 13m 3880 S 0.3 1.4 0:34.59 snort
4729 root 20 0 30956 1864 1460 S 0.3 0.2 0:03.47 X
4851 root 20 0 3428 200 164 S 0.3 0.0 0:31.87 snortsam
4872 clearcon 20 0 349m 9228 4108 S 0.3 0.9 0:29.38 gconsole
4937 plex 20 0 203m 6064 1536 S 0.3 0.6 0:11.08 Plex DLNA Serve
1 root 20 0 2948 892 784 S 0.0 0.1 0:01.15 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd


In var/log/mysqld.log I find this information:

140502 20:25:33 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140502 20:27:48 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140502 20:27:48 InnoDB: Initializing buffer pool, size = 8.0M
140502 20:27:48 InnoDB: Completed initialization of buffer pool
140502 20:27:49 InnoDB: Started; log sequence number 0 152806009
140502 20:27:49 [Note] Event Scheduler: Loaded 0 events
140502 20:27:49 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution

Not sure if it has anything to do with it, but the dashboard in the UI takes ages to load, in particular the charts on memory and CPU usage.

Any help to get me to the next step is appreciated.

Regards, Ronald
Friday, May 02 2014, 09:37 PM
Share this post:
Responses (77)
  • Accepted Answer

    Sunday, May 03 2015, 03:58 AM - #Permalink
    Resolved
    0 votes
    sorry... duplicate post
    The reply is currently minimized Show
  • Accepted Answer

    Sunday, May 03 2015, 03:56 AM - #Permalink
    Resolved
    0 votes
    service pmacctd stop && yum remove pmacct

    If I were Sheldon from Big Bang Theory, that would be a...https://anabbastow.files.wordpress.com/2013/01/sheldon-cooper-bazinga.jpg
    The reply is currently minimized Show
  • Accepted Answer

    Sunday, April 19 2015, 08:02 PM - #Permalink
    Resolved
    1 votes
    pmacct still has problems. We might revisit this app in the future but for now it is a distraction to getting ClearOS 7 out the door. For now, Nick's suggestion is what we are recommending. If pmacct is running on your system, please remove it. It should not be installable on ClearOS 6.6.
    The reply is currently minimized Show
  • Accepted Answer

    Sunday, April 19 2015, 07:32 AM - #Permalink
    Resolved
    0 votes
    No, it never got resolved and the app was withdrawn from the marketplace. The best advice is to remove the app
    The reply is currently minimized Show
  • Accepted Answer

    Sunday, April 19 2015, 07:20 AM - #Permalink
    Resolved
    0 votes
    Can folk confirm this never got resolved? Ive just seen that I'm getting the same problem on Pef 6.6
    The reply is currently minimized Show
  • Accepted Answer

    Intelliant
    Intelliant
    Offline
    Friday, October 31 2014, 07:28 PM - #Permalink
    Resolved
    0 votes
    Professional 6.5+ all of them.
    The reply is currently minimized Show
  • Accepted Answer

    fikse
    fikse
    Offline
    Friday, October 31 2014, 04:05 PM - #Permalink
    Resolved
    0 votes
    Sorry if this has been clarified earlier in this tread, but is this related only to the community or also professional? I got quorious since I've had this problem on 3 community but no problems on professionals yet (3 pcs professionals)...
    The reply is currently minimized Show
  • Accepted Answer

    Intelliant
    Intelliant
    Offline
    Friday, October 31 2014, 06:06 AM - #Permalink
    Resolved
    0 votes
    Same issue faced over the last 3+ months on 5 ClearOS servers that I maintain. Network detail report and pmacct removed.

    Should fix as per Nick's advise above. Will observe over the next few hours.
    The reply is currently minimized Show
  • Accepted Answer

    Tuesday, October 07 2014, 02:59 PM - #Permalink
    Resolved
    0 votes
    Hi,

    thanks for the info - my load was also peaking over 1.5 during office hours, but it was dropping down at night. I have removed the network detail reporting as prescribed and my daytime load has dropped to excellent levels again.

    Thanks, David http://www.clearfoundation.com/media/kunena/attachments/legacy/images/Clearos_load_20141007.jpg
    The reply is currently minimized Show
  • Accepted Answer

    gys
    gys
    Offline
    Sunday, September 28 2014, 05:44 PM - #Permalink
    Resolved
    0 votes
    gys wrote:
    Hello,

    since yesterday evening around 19:30 GMT+2 I've encountered the same problem.

    Any solutions found meanwhile?

    To keep the server up and running I stopped the service pmacctd, but this isn't a solution I guess

    Regards
    Guus


    This morning at 9:30 the same problem started again. After many trail & error it seems that de WAN NIC is malfunctioning. Now that I've replaced it the server starts normal and functions run smoothly. By the way I still have pmacctd removed from the system and will try later the effects of reinstalling.

    First I want to see the system stable over a longer periode

    Guus
    The reply is currently minimized Show
  • Accepted Answer

    Sunday, September 28 2014, 04:08 PM - #Permalink
    Resolved
    0 votes
    It's depressing. After all, we are talking about ClearOS Professional Edition and running the gateway in the enterprise.
    The reply is currently minimized Show
  • Accepted Answer

    Sunday, September 28 2014, 03:09 PM - #Permalink
    Resolved
    0 votes
    There are a couple of tweaks to the .conf files here. You can also try switching the database engine to innodb. Ultimately neither solved it. In your /var/log/system you will see a lot of messages saying networkdetail2db not able to start as it is already running. Making those changes and deleting stacks or records from the database stopped the errors for a while but they came back eventually.

    Peter Baldwin has said said networkdetail2db needs to be replaced with something to improve performance.

    I would honestly recommend stopping and removing pmacct and the network-detail reports.
    The reply is currently minimized Show
  • Accepted Answer

    Sunday, September 28 2014, 02:11 PM - #Permalink
    Resolved
    0 votes
    I did not understand until now, while browsing this topic.
    What can be done to reduce the load on the gateway?
    There is the exact recipe?
    This situation is starting to annoy ...

    http://www.clearfoundation.com/media/kunena/attachments/legacy/images/COS_6-20140928.jpg http://www.clearfoundation.com/media/kunena/attachments/legacy/images/COS_6-20140928-2.jpg
    The reply is currently minimized Show
  • Accepted Answer

    fikse
    fikse
    Offline
    Sunday, September 28 2014, 08:09 AM - #Permalink
    Resolved
    0 votes
    Nick Howitt wrote:
    Remove the report!


    Yea-yea-yeah :P I posted in the same second as you wrote your post ;)

    (ehh, well, not the same second, but I didnt refresh the forum before I posted. My bad)

    Thank you:

    https://dl.dropboxusercontent.com/u/67781693/COS/systemload.png
    The reply is currently minimized Show
  • Accepted Answer

    Sunday, September 28 2014, 07:59 AM - #Permalink
    Resolved
    0 votes
    Remove the report!
    The reply is currently minimized Show
  • Accepted Answer

    fikse
    fikse
    Offline
    Sunday, September 28 2014, 07:42 AM - #Permalink
    Resolved
    0 votes
    I also hav e serious trouble with this, making CPU so busy that zarafa stops working after approx 1 1/\2 day. So Ill have to restart mysql every 24 hours, this keeps the system alive.
    The reply is currently minimized Show
  • Accepted Answer

    Sunday, September 28 2014, 07:16 AM - #Permalink
    Resolved
    0 votes
    If you look a couple of posts up, the app has been withdrawn from the marketplace until they can sort it out. With 6.6 and 7 on the horizon I'd guess it is a low priority. In the meanwhile I'd suggest a complete removal of the app with:
    service pmacctd stop
    yum remove pmacct
    If you're more adventurous, you could even drop the tables from system-mysql.
    Like
    1
    The reply is currently minimized Show
  • Accepted Answer

    Saturday, September 27 2014, 10:25 PM - #Permalink
    Resolved
    0 votes
    I'd like to bump this one too. I've been watching all the system-mysqld threads on here for several weeks. I've tried all the suggestions but keep ending up with a thoroughly slammed cpu.

    I have zero torrents, so I know it isn't being caused by that on my machine at least. I did a reboot of my machine last night, and since then system-mysqld has logged over 457 minutes of cpu time....about 40x more than snort.
    The reply is currently minimized Show
  • Accepted Answer

    gys
    gys
    Offline
    Saturday, September 27 2014, 09:46 PM - #Permalink
    Resolved
    0 votes
    Hello,

    since yesterday evening around 19:30 GMT+2 I've encountered the same problem.

    Any solutions found meanwhile?

    To keep the server up and running I stopped the service pmacctd, but this isn't a solution I guess

    Regards
    Guus
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, September 10 2014, 03:38 PM - #Permalink
    Resolved
    0 votes
    Hi all,

    I think it's time to pull the report from the Marketplace (still available via yum though). The networkdetail2db should be replaced with a patch to pmacct -- that will certainly improve performance.
    Like
    1
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, September 04 2014, 08:30 PM - #Permalink
    Resolved
    0 votes
    .... and round about 18h00 today networkdetail2db began failing to complete again. :(
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, September 03 2014, 09:08 PM - #Permalink
    Resolved
    0 votes
    Hi Peter,

    Thanks for the reply. For interest here is my 7 day graph (I was away for the first 5 days, power cut on 31/08 (again!), re-installed on 1/9 and modded the config on 2/9. The system load and kernel usage tell the story. For me the report has a relatively high load on the server (which normally uses very little power and is a Core i3-4130 device) and I wonder if we are playing at this. On a busier system (small to mid-sized office) even with my config, would the report have the ability to pull the system down?

    http://www.clearfoundation.com/media/kunena/attachments/legacy/images/monitorix.png
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, September 03 2014, 07:51 PM - #Permalink
    Resolved
    0 votes
    Hi Tim and Nick,

    Thanks for diving into this! It's a crazy whirlwind at the moment (ClearOS 6.6, ClearOS 7, new web site). Flip side: the kids are back in school so there's much more time for hacking.
    The reply is currently minimized Show
  • Accepted Answer

    Tuesday, September 02 2014, 07:24 PM - #Permalink
    Resolved
    0 votes
    Reinstalled yesterday and still using innodb. The database is only (!) 556,487 records for the moment as the report was not running while I was away. From the pmacct wiki #18, in both interface conf files I've set:
    sql_refresh_time: 600
    sql_dont_try_update: true
    sql_history is still 10m so sql_refresh_time matches, and these are my loads:
    http://www.clearfoundation.com/media/kunena/attachments/legacy/images/pmacct.png

    You can see where I made the change at 18:00. It is not perfect but it is better. 15m load averages are about .5. I need to run with this for longer to see what happens.

    I have not tried packet sniffing yet. That will probably be in a few days.
    The reply is currently minimized Show
  • Accepted Answer

    Thursday, August 21 2014, 09:11 AM - #Permalink
    Resolved
    0 votes
    The odd thing about the single packets I saw in the firewall is that if they were torrents, they were on the wrong port! I'll see if I can find some time in September to give it another go but it needs some concentration.
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, August 20 2014, 10:00 PM - #Permalink
    Resolved
    0 votes
    I've installed Monitorix which is available in the clearos-epel repo :)

    Perhaps a tcpdump log would help identify what the single packets are? This is a symptom of running torrent software as other members of the 'swarm' send single packets to identify who is available long after the client may have stopped transferring. Perhaps try disabling your torrents and monitor the transmission port

    I was referring to Peters earlier post which contained many IPV6 entries too... given the almost infinite number of IPV6 addresses this could cause a very large database of traffic

    I'll have a play with the pcap_filters
    The reply is currently minimized Show
  • Accepted Answer

    Wednesday, August 20 2014, 11:44 AM - #Permalink
    Resolved
    0 votes
    I unloaded the app yesterday evening. On Saturday I deleted all single packet entries in the database but they were still building again. I'd also tried to filter all ICMP packets using both aggregate[inbound] and aggregate[outbound], and pcap_filter filtering for "not icmp" in the pmacct.conf file but this did not appear to really slow down the single packet entries. I also switched to using innodb, Even before I deleted the extra entries this switch to innodb generally (but not always) allowed networkdetail2db to complete before it tried to start again. By yesterday networkdetail2db was failing to complete more often. My 15min load averages were down from 130% before the weekend to 40-50% indicating that the system-mysql (which takes nearly all the load) was running for about 2 minutes every 5 at 100% which confirms what I was observing with "top"

    I don't think I saw any IPv6 traffic.

    BTW where are you getting your system load graph from? It is not the ClearOS one which I have.

    I tried logging everything inbound in the firewall over a 10 minute period to correlate it with the system-mysql table, but I don't think this was long enough. After eliminating all the volume traffic and identifyable traffic from the firewall dump, I could not find any of the remaining traffic in the system-mysql table. I also did not get any icmp traffic in the firewall which I find odd. I will need to do it again over a larger sample period (but at a quiet time to reduce the firewall dump!) but I will get no time before early September.

    Because I could not correlate the firewall to the system-mysql table I could not identify the single packets or the NULL entries.
    The reply is currently minimized Show
  • Accepted Answer

    Tuesday, August 19 2014, 11:10 PM - #Permalink
    Resolved
    0 votes
    Hmm the changes have helped somewhat.. but I can report that system-mysqld still eats up a lot of CPU every 5 minutes during updates

    My load trends have definitely reduced though, and it no longer seems to snowball...

    http://www.clearfoundation.com/media/kunena/attachments/legacy/images/system1z.png
    The spikes are periodic backups, I installed the app last Monday... increasing load until Saturday when I made the changes, and also restarted because of some rewiring and currently hover around the 0.5 mark

    Processing all those IPV6 and single packet entries is not going to help!

    There appear to be two things at play here... the pmacctd database updates every 10minutes (which doesn't include the IP / username / hostname) and the cron PHP script which is called every 5 minutes (which updates the existing IP / hostname / username if available).

    Both of these coincide with each other and on my fairly quiet system max out one CPU for 2-3 minutes.... note that having a CPU governor which scales the frequency to match the load will give misleading readings as the load is relative to the processor speed
    The reply is currently minimized Show
  • Accepted Answer

    Sunday, August 17 2014, 09:05 AM - #Permalink
    Resolved
    0 votes
    So much for that hunch. I have logged 18,441 single packets in the last 14 hours. :( - either that or my pmmctd filter is not working.
    The reply is currently minimized Show
  • Accepted Answer

    Saturday, August 16 2014, 07:49 PM - #Permalink
    Resolved
    0 votes
    Trial and error time now. I've added a filter to the WAN logging in /etc/pmacct/pmacctd_eth0.conf:
    pcap_filter: 'not icmp'
    and deleted all single packet records from the database (561,168 leaving 370,112 records):
     delete FROM `network_detail_external` WHERE packets = 1;
    If my hunch is correct (and if I have the filter correct) I should be logging less than half the records into a database less than half the size.

    I have no idea what the NULL entries are in the hostname column. It is not just because there is no ptr record as other entries without a ptr record show up with the IP in the hostname field. These records with a NULL hostname also have a NULL username, device_type and device_vendor as opposed to a blank one. These account for 103,048 of my remaining 370,112 records!
    The reply is currently minimized Show
  • Accepted Answer

    Saturday, August 16 2014, 04:16 PM - #Permalink
    Resolved
    0 votes
    Back home for a few days now. I don't use the proxy but I've changed the engine to innodb for the network_detail and network_detail_external and restarted system-mysqld. Initially it did nothing to help (processing a backlog?) then system-mysqld seemed to peak at about 100% for 2.5 - 3 minutes every 5 minutes then drop out of "top". This is a reasonable improvement but this server does only supports a small family. It would struggle if it were supporting an SME or college. Unfortunately after about 40 mins I was back to networkdetail2db not always completing between cron jobs (perhaps an hourly thing but I can't keep staring at the screen!)
    The reply is currently minimized Show
  • Accepted Answer

    Saturday, August 16 2014, 02:23 PM - #Permalink
    Resolved
    0 votes
    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.

    EDIT: Actually, with MyISAM I also have about 150 queries in PROCESSLIST with many locked for over a minute.

    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


    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)
    The reply is currently minimized Show
  • Accepted Answer

    Saturday, August 16 2014, 12:43 AM - #Permalink
    Resolved
    0 votes
    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
    The reply is currently minimized Show
  • Accepted Answer

    Friday, August 15 2014, 11:17 PM - #Permalink
    Resolved
    0 votes
    OK so took a look at the table status, and noticed that the network_detail, network_detail_external and proxy tables all use MyISAM engine not InnoDB... so followed Zombu2 advice on the first page, and changed the engine

    mysql> use reports;
    mysql> show table status;
    mysql> alter table network_detail_external engine=innodb;
    mysql> alter table network_detail engine=innodb;
    mysql> alter table proxy engine=innodb;
    mysql> show table status;

    Now processlist list does not show table locking between each query and hence there seems to be an overall speed increase. I'll monitor the drop in load and see how it compares...
    | 5208 | reports | localhost:34592 | reports | Query   |    1 | Updating | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+86, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408143600) = stamp_inserted AND ip_src='185.30.232.167'                           |
    | 5209 | reports | localhost:34593 | reports | Query | 1 | Updating | UPDATE `network_detail_external` SET packets=packets+1, bytes=bytes+76, stamp_updated=NOW() WHERE FROM_UNIXTIME(1408143600) = stamp_inserted AND ip_dst='87.117.251.2' |
    | 5212 | reports | localhost:34637 | reports | Query | 0 | Updating | UPDATE network_detail_external SET ip='|{?', hostname='ras.beamtele.net', username='', device_vendor='', device_type='' WHERE (ip_src='124.123.26.138' OR ip_dst='124.123.26.138') AND ip IS NULL |
    The reply is currently minimized Show
  • Accepted Answer

    Friday, August 15 2014, 11:04 PM - #Permalink
    Resolved
    0 votes
    Hmm so after a couple days the load is steadily increasing and the tips above don't seem to have alleviated the problem. The INSERT queries are generated by pmacctd, but also the UPDATE query is generated by the cron script to update the IP/username for particular hosts. The table appears to be locked between single queries...

    mysql> show full processlist;
    | 5156 | reports | localhost:34162 | reports | Query   |    1 | Locked   | INSERT INTO `network_detail_external` (stamp_updated, stamp_inserted, ip_src, packets, bytes) VALUES (FROM_UNIXTIME(1408143601), FROM_UNIXTIME(1408143000), '176.31.240.170', 1, 86)                       |
    | 5157 | reports | localhost:34163 | reports | Query | 2 | Locked | INSERT INTO `network_detail_external` (stamp_updated, stamp_inserted, ip_dst, packets, bytes) VALUES (FROM_UNIXTIME(1408143601), FROM_UNIXTIME(1408143000), '176.31.240.170', 1, 86) |
    | 5160 | reports | localhost:34172 | reports | Query | 2 | Updating | UPDATE network_detail_external SET ip='>??H', hostname='m328-mp1-cvx1b.lan.ntl.com', username='', device_vendor='', device_type='' WHERE (ip_src='62.252.169.72' OR ip_dst='62.252.169.72') AND ip IS NULL |
    The reply is currently minimized Show
  • Accepted Answer

    Tuesday, August 12 2014, 08:49 AM - #Permalink
    Resolved
    0 votes
    No problem have a good break :) perhaps Peter can help?

    From my limited observations things can be improved by tweaking the pmacctd daemon so that database INSERTS are only generated once during the timeslot period, otherwise it has to UPDATE the existing table rather than just insert new data, which is more IO intensive for large tables. The default config appears to deviate from this principle?

    Second benefit is to enable and configure the plugin_buffer_size (which appears to be disabled by default). This reduces the CPU load on high traffic but does nothing for system-mysql...on my system anyway.

    The third benefit is to add database indexes to the stamp_inserted, ip_src and ip_dst columns, which appear to be used by the UPDATE query..

    There are other optimisations with pmacct shown in the FAQ such as PF_RING / LIBPCAP but I've not investigated these. Interestingly the pmacctd FAQ's discourage the logging of all traffic to prevent DOS type problems
    The reply is currently minimized Show
  • Accepted Answer

    Tuesday, August 12 2014, 07:54 AM - #Permalink
    Resolved
    0 votes
    Hi Tim,

    My hands are a bit tied on this. I'm off today until Saturday and again later in the week for 10 days. system-mysqld was running away (up to 1.30) and there is no way my wife could fix things in my absence so I've had to pull the report for the moment. It also means that when I'm finally back and reinstall it, the database will be relatively empty as the purge routine appears to be working. I'll see if I can give it a go between the two trips.

    I have been using phpMyAdmin as I remembered a post from long ago on how to modify /etc/phpMyAdmin/config.inc.php to gain access. I can't remember how to get root access but I can get reports access from details posted in a linked thread. I'm not a coder but about 25 years ago I played around with SQL queries so I have a vague idea about what I'm doing hence my simple reports. Google helps.

    I do host 2 ClearOS torrents and torrent on and off for other things, but not, for example at the back end of last week. It could, I suppose, mean that my IP is well known and I am getting a lot of pings which is what I assume these single packet requests are.

    Did you bump into any way not to log icmp? I had a bit of a google last night but failed.

    Nick
    The reply is currently minimized Show
  • Accepted Answer

    Monday, August 11 2014, 11:43 PM - #Permalink
    Resolved
    0 votes
    P.S pmacctd daemon gobbles up CPU... on a 50Mbps download from my LAN it accounts for nearly 50% of my CPU time, cumulatively more than snort which is doing packet inspection as well!
    18403 snort     30  10  469m 174m 4152 R 43.1  3.6   6:19.41 snort
    17679 squid 30 10 158m 86m 3664 R 29.2 1.8 2:09.92 squid
    13389 root 20 0 61156 10m 9968 S 19.2 0.2 0:46.42 pmacctd
    13398 root 20 0 64692 14m 4140 S 10.6 0.3 0:25.17 pmacctd
    13394 root 20 0 64692 14m 4140 S 10.3 0.3 0:24.75 pmacctd
    30272 dansguar 30 10 128m 16m 1340 R 10.0 0.3 0:14.85 dansguardian-av
    13397 root 20 0 61156 10m 9.8m S 9.0 0.2 0:10.88 pmacctd


    EDIT: adding "plugin_buffer_size: 1024" to both pmacctd_ethX.conf files seems to have significantly reduced the usage! as per the FAQ here. Some room for tuning?
    http://wiki.pmacct.net/OfficialFAQs
    The reply is currently minimized Show
  • Accepted Answer

    Monday, August 11 2014, 11:02 PM - #Permalink
    Resolved
    0 votes
    Something else to try...

    modifying the pmacctd daemon (I incorrectly thought it was the cron job earlier but that just calls a script to update the mappings in network_detail which is by comparison much smaller than network_detail_external)

    With reference to:-
    http://wiki.pmacct.net/OfficialExamples

    You should have two generate configlets by ClearOS in /etc/pmacctd/pmacctd_ethX.conf - where ethX is your WAN and will log to network_detail_external

    In here are two parameters, sql_refresh_time: 900, sql_history: 10m... try reducing the first to say 600 so that only INSERTS and not UPDATES are carried out once per SQL timeslot...
    Alternatively drop it right down to 60, which will reduce the amount of traffic kept in memory (at the expense of more frequent but smaller database inserts) to try and reduce the size of data being dumped into system-mysql at any one time

    You may need to run 'service pmacctd restart' to implement
    The reply is currently minimized Show
  • Accepted Answer

    Monday, August 11 2014, 10:36 PM - #Permalink
    Resolved
    0 votes
    Some more poking around... I also see lots of NULL IP records

    I've been experimenting with adding a table index to the mysql query columns to improve the query performance. My hunch is that with large amounts of TCP connections the database size grows rapidly and so does the time taken to query and update the host information in it - hence my question about Torrents. The queries appear to usually use WHERE stamp_inserted, ip_dst OR ip_src... but as I don't have the symptoms I can't tell if these improve things

    If you are happy to test could you try running the following on the reports database?
    ALTER TABLE `network_detail_external` ADD INDEX(`ip_src`);
    ALTER TABLE `network_detail_external` ADD INDEX(`ip_dst`);
    ALTER TABLE `network_detail_external` ADD INDEX(`stamp_inserted`);
    The reply is currently minimized Show
Your Reply