Dave,
I noticed in your config you have "sql_refresh_time: 5" and
"sql_history: 5m". I believe these values need to be equal.
sql_refresh_time is in seconds so I think it should be 300 versus 5.
Judging from the pmacct documents by having this set to 5 seconds and
the history set to 5 minutes you are purging the data to the database
long before history time has expired.
Chris
PS With that traffic load you will want "sql_dont_try_update" set to
true unless you have major league mysql server. Database row updates are
really expensive operations. I am collecting from a nearly fully
saturated 100 Mbps ethernet link to a postgre database. The database is
on a dual 2Ghz Xeon HP Proliant with a RAID 5 array and having updates
on just killed that database server. Turning updates off, setting
sql_refresh_time 120 and sql_history to 2m allows the server to keep up
quite nicely.
The trick I found was to keep those two values in sync and watch the
postgres processes. If the insert takes longer than sql_refresh time
less 15 or 20 seconds then back off the times or beef up the database
server.
David Maple wrote:
Hi all,
I have pmacctd running on a dedicated machine listening on a gigabit
Ethernet port. It is only collecting half of the data that the router
is passing to it. For example, at this time, the 5 minute average from
the router (confirmed with ifconfig) is 136,087,000 bits/sec and pmacctd
is reporting only 70,212,327 bits/sec.
All I'm trying to do is produce 5 minute usage reports by IP address
for both source and destination traffic. When I have
sql_dont_try_update disabled, it brings the CPU on the MySQL server to
99%, so I have it enabled until I can put in a stronger MySQL server. I
have done this with and without sql_optimize_clauses with no difference.
Has anyone else gotten this working with this volume of traffic? If
so, I would appreciate knowing what you did to get it working.
Thanks,
Dave
P.S. - Here is the config for pmacctd:
debug: false
daemonize: false
aggregate: src_host,dst_host
plugins: mysql
plugin_buffer_size: 32768
plugin_pipe_size: 33554432
interface: eth0
interface_wait: true
promisc: true
syslog: local6
networks_file: /usr/local/pmacct/etc/networks.lst
network_cache_entries: 4091
sql_host: <hidden>
sql_user: <hidden>
sql_passwd: <hidden>
sql_db: pmacct
sql_table: acct_v2
sql_table_version: 2
sql_refresh_time: 5
sql_optimize_clauses: false
sql_history: 5m
sql_history_roundoff: m
sql_recovery_logfile: /usr/local/pmacct/var/log/recover
sql_cache_entries: 7919
sql_dont_try_update: true
post_tag: %%LGINSTANCE%%
sampling_rate: 1
