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
