Re: [pmacct-discussion] nfacctd: mysql Plugin -- DB Writer [total]

2017-03-02 Thread Luc Perreau 
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  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 |

Re: [pmacct-discussion] nfacctd: mysql Plugin -- DB Writer [total]

2017-03-02 Thread Paolo Lucente 

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