Re: [pmacct-discussion] nfacctd: mysql Plugin -- DB Writer [total]
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 | UPDATE > > `acct` SET pa
Re: [pmacct-discussion] nfacctd: mysql Plugin -- DB Writer [total]
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=p