sorry... duplicate post

service pmacctd stop && yum remove pmacct

If I were Sheldon from Big Bang Theory, that would be a...

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.

No, it never got resolved and the app was withdrawn from the marketplace. The best advice is to remove the app

Can folk confirm this never got resolved? Ive just seen that I'm getting the same problem on Pef 6.6

Professional 6.5+ all of them.

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)...

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.

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

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

It's depressing. After all, we are talking about ClearOS Professional Edition and running the gateway in the enterprise.

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.

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 ...

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:

Remove the report!

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.

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.

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.

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

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.

.... and round about 18h00 today networkdetail2db began failing to complete again.

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?

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.

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:


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 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.

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

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.

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...


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

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.

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!

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!)

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)

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

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 |

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 |

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

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

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

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

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`);