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
