Thanks Paolo,

I'll see how i can enhance this setup.

Kind regards,

Luc

On Fri, Mar 3, 2017 at 8:23 AM, Paolo Lucente <pa...@pmacct.net> wrote:

>
> Hi Luc,
>
> At a glance it looks a case where you are overwhelming the RDBMS 1) you
> write data to a static table, ie. acct, likely making the table and its
> index(es) big; 2) you make use of UPDATE queries, which are expensive;
> whereas you should try to aim at an INSERT-only environment (*); 3) the
> aggregation key, even in its current definition ie. the 5-tuple, is long
> and combined with #1 and #2 can only drive to negative performances.
>
> You should either try to address these issues or look at a different
> kind of backend for your data, ie. ElasticSearch, InfluxDB or similars.
>
> Paolo
>
> (*) https://github.com/pmacct/pmacct/wiki/Implementation-notes
>
> On Thu, Mar 02, 2017 at 05:19:42PM +0800, Luc Perreau wrote:
> > Hi All,
> >
> > I am having problems with my mysql db staying in the UPDATE stage even
> if i
> > set sql_locking_style: row or not.
> >
> > My processes reach 10 as shown here:
> >
> > Feb 27 05:59:01 WARN ( total/mysql ): Maximum number of SQL writer
> > processes reached (10).
> > Feb 27 06:00:01 WARN ( total/mysql ): Maximum number of SQL writer
> > processes reached (10).
> > Feb 27 06:01:01 WARN ( total/mysql ): Maximum number of SQL writer
> > processes reached (10).
> > Feb 27 06:02:01 WARN ( total/mysql ): Maximum number of SQL writer
> > processes reached (10).
> > Feb 27 06:03:01 WARN ( total/mysql ): Maximum number of SQL writer
> > processes reached (10).
> > Feb 27 06:04:01 WARN ( total/mysql ): Maximum number of SQL writer
> > processes reached (10).
> > Feb 27 06:05:01 WARN ( total/mysql ): Maximum number of SQL writer
> > processes reached (10).
> > Feb 27 06:06:01 WARN ( total/mysql ): Maximum number of SQL writer
> > processes reached (10).
> > Feb 27 06:07:01 WARN ( total/mysql ): Maximum number of SQL writer
> > processes reached (10).
> > Feb 27 06:08:01 WARN ( total/mysql ): Maximum number of SQL writer
> > processes reached (10).
> > Feb 27 06:09:01 WARN ( total/mysql ): Maximum number of SQL writer
> > processes reached (10).
> > Feb 27 06:10:01 WARN ( total/mysql ): Maximum number of SQL writer
> > processes reached (10).
> > Feb 27 06:11:01 WARN ( total/mysql ): Maximum number of SQL writer
> > processes reached (10).
> >
> > MySQL shows this:
> >
> > mysql>  SHOW FULL PROCESSLIST;
> > +-----+--------+-----------+--------+---------+------+------
> ----+-------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------
> --------------------------------------------------------------------+
> > | Id  | User   | Host      | db     | Command | Time | State    |
> > Info
> > |
> > +-----+--------+-----------+--------+---------+------+------
> ----+-------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------
> --------------------------------------------------------------------+
> > |  52 | root   | localhost | pmacct | Query   |    0 | NULL     | SHOW
> FULL
> > PROCESSLIST
> > |
> > | 157 | pmacct | localhost | pmacct | Query   |   25 | Updating | UPDATE
> > `acct` SET packets=packets+45, bytes=bytes+54114, stamp_updated=NOW()
> WHERE
> > FROM_UNIXTIME(1488444600) = stamp_inserted AND ip_src='23.48.48.81' AND
> > ip_dst='103.233.74.99' AND src_port=443 AND dst_port=17964 AND
> > ip_proto='tcp'          |
> > | 158 | pmacct | localhost | pmacct | Query   |   35 | Updating | UPDATE
> > `acct` SET packets=packets+563, bytes=bytes+831942, stamp_updated=NOW()
> > WHERE FROM_UNIXTIME(1488444600) = stamp_inserted AND
> > ip_src='117.18.232.151' AND ip_dst='103.233.74.99' AND src_port=80 AND
> > dst_port=11624 AND ip_proto='tcp'      |
> > | 159 | pmacct | localhost | pmacct | Query   |   46 | Updating | UPDATE
> > `acct` SET packets=packets+553, bytes=bytes+110600, stamp_updated=NOW()
> > WHERE FROM_UNIXTIME(1488444600) = stamp_inserted AND
> > ip_src='103.233.74.132' AND ip_dst='125.254.48.7' AND src_port=46776 AND
> > dst_port=19410 AND ip_proto='udp'    |
> > | 160 | pmacct | localhost | pmacct | Query   |   56 | Updating | UPDATE
> > `acct` SET packets=packets+494, bytes=bytes+98800, stamp_updated=NOW()
> > WHERE FROM_UNIXTIME(1488444600) = stamp_inserted AND
> > ip_src='103.233.74.132' AND ip_dst='125.254.48.7' AND src_port=46754 AND
> > dst_port=10590 AND ip_proto='udp'     |
> > | 161 | pmacct | localhost | pmacct | Query   |   46 | Updating | UPDATE
> > `acct` SET packets=packets+76, bytes=bytes+107623, stamp_updated=NOW()
> > WHERE FROM_UNIXTIME(1488444600) = stamp_inserted AND
> > ip_src='104.27.158.251' AND ip_dst='103.233.74.99' AND src_port=443 AND
> > dst_port=3249 AND ip_proto='tcp'       |
> > | 162 | pmacct | localhost | pmacct | Query   |   15 | Updating | UPDATE
> > `acct` SET packets=packets+1192, bytes=bytes+1583233, stamp_updated=NOW()
> > WHERE FROM_UNIXTIME(1488444600) = stamp_inserted AND
> > ip_src='103.233.74.125' AND ip_dst='47.88.136.147' AND src_port=32808 AND
> > dst_port=39522 AND ip_proto='udp' |
> > | 163 | pmacct | localhost | pmacct | Query   |   29 | Updating | UPDATE
> > `acct` SET packets=packets+795, bytes=bytes+1053171, stamp_updated=NOW()
> > WHERE FROM_UNIXTIME(1488444600) = stamp_inserted AND
> > ip_src='103.233.74.125' AND ip_dst='47.88.136.147' AND src_port=32808 AND
> > dst_port=39522 AND ip_proto='udp'  |
> > +-----+--------+-----------+--------+---------+------+------
> ----+-------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------
> --------------------------------------------------------------------+
> > 8 rows in set (0.01 sec)
> >
> > And that process ID 157 never finishes.
> >
> > And i keep seeing more processes:
> >
> > root@log-syslog:/home/logic# ps -aux | grep nfacctd
> > root     32436  0.0  0.5  32388  5392 ?        Ss   16:52   0:01 nfacctd:
> > Core Process [default]
> > root     32437  0.0  7.7 105720 78928 ?        S    16:52   0:01 nfacctd:
> > MySQL Plugin [total]
> > root     32439  0.0  7.2 181020 73820 ?        S    16:53   0:00 nfacctd:
> > mysql Plugin -- DB Writer [total]
> > root     32443  0.0  7.2 181152 74064 ?        S    16:54   0:00 nfacctd:
> > mysql Plugin -- DB Writer [total]
> > root     32447  0.0  7.2 181284 74132 ?        S    16:55   0:00 nfacctd:
> > mysql Plugin -- DB Writer [total]
> > root     32453  0.0  7.2 181284 74168 ?        S    16:56   0:00 nfacctd:
> > mysql Plugin -- DB Writer [total]
> > root     32471  0.0  7.2 181284 74168 ?        S    16:57   0:00 nfacctd:
> > mysql Plugin -- DB Writer [total]
> > root     32479  0.0  7.2 181416 74296 ?        S    16:58   0:00 nfacctd:
> > mysql Plugin -- DB Writer [total]
> > root     32483  0.0  7.2 181680 74472 ?        S    16:59   0:00 nfacctd:
> > mysql Plugin -- DB Writer [total]
> > root     32489  0.0  7.2 181680 74500 ?        S    17:00   0:00 nfacctd:
> > mysql Plugin -- DB Writer [total]
> > root     32493  0.0  7.2 181680 74592 ?        S    17:01   0:00 nfacctd:
> > mysql Plugin -- DB Writer [total]
> > root     32498  0.0  7.3 181680 75096 ?        S    17:02   0:00 nfacctd:
> > mysql Plugin -- DB Writer [total]
> > root     32506  0.0  0.0   8172   140 pts/1    T    17:03   0:00 less
> > /var/log/nfacctd.log
> > root     32550  0.0  0.1  12732  2000 pts/1    S+   17:20   0:00 grep
> > nfacctd
> >
> > Here how i have my nfacct set up:
> >
> >  GNU nano 2.2.6                                             File:
> > /etc/pmacct/nfacctd.conf
> >
> >
> > ! nfacctd configuration
> > !
> > !
> > !
> > daemonize:true
> > pidfile: /var/run/nfacctd.pid
> > syslog: daemon
> > plugins: mysql[total]
> > !
> > ! interested in in and outbound traffic
> > !aggregate: src_host,dst_host
> > !aggregate:
> > src_host,dst_host,src_port,dst_port,proto,tos,peer_src_
> as,peer_dst_as,in_iface,out_iface,vlan
> > !aggregate[total]:
> > src_host,dst_host,src_port,dst_port,proto,in_iface,out_iface,tag
> > aggregate[total]: src_host,dst_host,src_port,dst_port,proto
> > !aggregate[total]: src_host,dst_host
> > !nfacctd_ip: 10.100.254.10
> > nfacctd_port: 5679
> > !networks_file: /etc/pmacct/nfacctd.networks
> > !pre_tag_map: /etc/pmacct/pretag.map
> > !pre_tag_filter[total]: 0-2
> > interface: eth0
> > sql_host: localhost
> > sql_db: pmacct
> > sql_user: pmacct
> > sql_passwd: arealsmartpwd
> > sql_refresh_time: 60
> > sql_history: 5m
> > sql_history_roundoff: d
> > sql_cache_entries: 250000
> > !sql_table_version: 8
> > sql_optimize_clauses: true
> > sql_table[total]: acct
> > sql_preprocess: minp=5, minb=20000
> > sql_preprocess_type: all
> > sql_locking_style: row
> > logfile: /var/log/nfacctd.log
> >
> > !
> > ! storage methods
> > ! refresh the db every minute
> > !sql_refresh_time: 60
> > ! reduce the size of the insert/update clause
> > !sql_optimize_clauses: true
> > ! accumulate values in each row for up to an hour
> > !sql_history: 1h
> > ! create new rows on the minute, hour, day boundaries
> > !sql_history_roundoff: mhd
> > ! in case of emergency, log to this file
> > !sql_recovery_logfile: /var/log/nfacctd_recovery_log
> >
> >
> > Can anyone help me fix this please?
> >
> > Luc
>
> > _______________________________________________
> > pmacct-discussion mailing list
> > http://www.pmacct.net/#mailinglists
>
>
> _______________________________________________
> pmacct-discussion mailing list
> http://www.pmacct.net/#mailinglists
>
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists

Reply via email to