Hi Peter, It should be just a matter of configuration. Maybe a "SHOW PROCESSLIST" within the MySQL client can tell something more; you should find that those processes are lining up to obtain the LOCK to write to the SQL table. Why processes are queueing up? Because writing takes much time; basically you are throwing a lot of flows at your database - which are involving UPDATEs (which are quite heavy and should be avoided/limited; ie. look at the sql_dont_try_update option).
By looking at your configuration, i see two settings which are very short: > sql_history: 1m > sql_refresh_time: 5 I would recommend setting sql_history to 5m and sql_refresh_time to 300 seconds; these two timers will allow you to turn on safely the sql_dont_try_update option. You can have a try to the sql_multi_values aswell (given you are using MySQL) which optimizes INSERT queries. Remember SQL plugins use a cache - right to relief pressure on the database. See how it goes this way - as your way of aggregating data looks pretty solid (two plugins, one for inbound traffic the other for outbound traffic). Let me/us know! Cheers, Paolo On Mon, May 11, 2009 at 10:42:48AM +0930, Peter Adkins wrote: > Hey all, > > When running nfacctd over 4 days with about 27,000 flows per second > (max) being sent to it, I get errors from MySQL saying there are too > many open connections. Normally I wouldn't see an issue with upping the > limit, however in the process list I have the following (the daemon is > currently running as root for development.) > > Could this be a bug by any chance, or is it just my configuration? > (included below) > > root 11547 0.0 2.1 61068 11152 pts/0 S+ 09:02 0:00 > nfacctd: MySQL Plugin -- DB Writer [download] > root 11588 0.0 1.7 58924 9024 pts/0 S+ 09:03 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 11730 0.0 1.7 58924 9028 pts/0 S+ 09:06 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 11743 0.0 1.8 59564 9604 pts/0 S+ 09:06 0:00 > nfacctd: MySQL Plugin -- DB Writer [upload] > root 11829 0.0 1.7 58924 9028 pts/0 S+ 09:08 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 11889 0.0 1.7 58924 9024 pts/0 S+ 09:10 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 11955 0.0 1.7 58924 9024 pts/0 S+ 09:11 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 11985 0.0 2.1 61068 11100 pts/0 S+ 09:11 0:00 > nfacctd: MySQL Plugin -- DB Writer [download] > root 12032 0.0 1.7 58924 9024 pts/0 S+ 09:13 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12107 0.0 1.7 58924 9024 pts/0 S+ 09:15 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12140 0.0 1.7 58924 9024 pts/0 S+ 09:15 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12177 0.0 1.8 59564 9604 pts/0 S+ 09:16 0:00 > nfacctd: MySQL Plugin -- DB Writer [upload] > root 12234 0.0 1.7 58924 9024 pts/0 S+ 09:17 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12282 0.0 1.7 58924 9024 pts/0 S+ 09:18 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12314 0.0 1.7 58924 9024 pts/0 S+ 09:19 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12401 0.0 1.7 58924 9024 pts/0 S+ 09:21 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12421 0.0 1.7 58924 9024 pts/0 S+ 09:21 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12451 0.0 2.1 61068 11100 pts/0 S+ 09:22 0:00 > nfacctd: MySQL Plugin -- DB Writer [download] > root 12461 0.0 1.7 58924 9024 pts/0 S+ 09:22 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12474 0.0 1.7 58924 9024 pts/0 S+ 09:23 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12497 0.0 1.7 58924 9024 pts/0 S+ 09:23 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12563 0.0 1.7 58924 9024 pts/0 S+ 09:25 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12624 0.0 1.7 58924 9024 pts/0 S+ 09:26 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12633 0.0 1.8 59564 9604 pts/0 S+ 09:26 0:00 > nfacctd: MySQL Plugin -- DB Writer [upload] > root 12653 0.0 1.7 58924 9024 pts/0 S+ 09:26 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12686 0.0 1.7 58924 9024 pts/0 S+ 09:27 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12750 0.0 1.7 58924 9024 pts/0 S+ 09:29 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12875 0.0 1.7 58924 9024 pts/0 S+ 09:31 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 12897 0.0 2.1 61068 11100 pts/0 S+ 09:32 0:00 > nfacctd: MySQL Plugin -- DB Writer [download] > root 12959 0.0 1.7 58924 9024 pts/0 S+ 09:34 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 13082 0.0 1.7 58924 9024 pts/0 S+ 09:36 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 13087 0.0 1.8 59564 9604 pts/0 S+ 09:36 0:00 > nfacctd: MySQL Plugin -- DB Writer [upload] > root 13172 0.0 1.7 58924 9024 pts/0 S+ 09:38 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 13224 0.0 1.7 58924 9024 pts/0 S+ 09:40 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 13305 0.0 2.1 61068 11100 pts/0 S+ 09:41 0:00 > nfacctd: MySQL Plugin -- DB Writer [download] > root 13343 0.0 1.7 58924 9024 pts/0 S+ 09:42 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 13412 0.0 1.7 58924 9024 pts/0 S+ 09:44 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 13445 0.0 1.8 59564 9604 pts/0 S+ 09:45 0:00 > nfacctd: MySQL Plugin -- DB Writer [upload] > root 13564 0.0 1.7 58924 9028 pts/0 S+ 09:47 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 13584 0.0 1.8 59564 9604 pts/0 S+ 09:48 0:00 > nfacctd: MySQL Plugin -- DB Writer [upload] > root 13644 0.0 1.7 58924 9024 pts/0 S+ 09:49 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 13772 0.0 1.7 58924 9024 pts/0 S+ 09:52 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 13827 0.0 2.1 61068 11100 pts/0 S+ 09:53 0:00 > nfacctd: MySQL Plugin -- DB Writer [download] > root 13864 0.0 1.7 58924 9024 pts/0 S+ 09:54 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 13966 0.0 1.7 58924 9024 pts/0 S+ 09:56 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14058 0.0 1.7 58924 9024 pts/0 S+ 09:59 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14075 0.0 2.1 61068 11100 pts/0 S+ 09:59 0:00 > nfacctd: MySQL Plugin -- DB Writer [download] > root 14096 0.0 1.7 58924 9020 pts/0 S+ 09:59 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14139 0.0 1.7 58924 9024 pts/0 S+ 10:00 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14197 0.0 1.7 58924 9024 pts/0 S+ 10:01 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14233 0.0 1.7 58924 9024 pts/0 S+ 10:02 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14261 0.0 1.7 58924 9024 pts/0 S+ 10:03 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14283 0.0 1.8 59564 9604 pts/0 S+ 10:03 0:00 > nfacctd: MySQL Plugin -- DB Writer [upload] > root 14286 0.0 1.7 58924 9020 pts/0 S+ 10:03 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14318 0.0 1.7 58924 9024 pts/0 S+ 10:04 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14381 0.0 1.7 58924 9024 pts/0 S+ 10:05 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14419 0.0 1.8 59564 9604 pts/0 S+ 10:06 0:00 > nfacctd: MySQL Plugin -- DB Writer [upload] > root 14446 0.0 1.7 58924 9024 pts/0 S+ 10:07 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14531 0.0 1.7 58924 9028 pts/0 S+ 10:09 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14580 0.0 1.7 58924 9024 pts/0 S+ 10:10 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14661 0.0 2.1 61068 11100 pts/0 S+ 10:12 0:00 > nfacctd: MySQL Plugin -- DB Writer [download] > root 14701 0.0 1.7 58924 9024 pts/0 S+ 10:13 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14759 0.0 1.7 58924 9024 pts/0 S+ 10:14 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14800 0.0 1.7 58924 9024 pts/0 S+ 10:15 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14827 0.0 1.7 58924 9024 pts/0 S+ 10:16 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14872 0.0 2.1 61068 11100 pts/0 S+ 10:17 0:00 > nfacctd: MySQL Plugin -- DB Writer [download] > root 14920 0.0 1.7 58924 9024 pts/0 S+ 10:18 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 14974 0.0 1.7 58924 9024 pts/0 S+ 10:19 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 15064 0.0 1.8 59564 9604 pts/0 S+ 10:21 0:00 > nfacctd: MySQL Plugin -- DB Writer [upload] > root 15096 0.0 1.7 58924 9028 pts/0 S+ 10:22 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 15151 0.0 1.7 58924 9024 pts/0 S+ 10:23 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 15175 0.0 1.7 58924 9024 pts/0 S+ 10:24 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 15234 0.0 1.7 58924 9024 pts/0 S+ 10:25 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 15286 0.0 1.7 58924 9024 pts/0 S+ 10:26 0:00 > nfacctd: MySQL Plugin -- DB Writer [unmetered] > root 18024 0.7 1.0 52336 5304 pts/0 S+ May08 33:29 > nfacctd: Core Process [default] root 18025 0.1 2.1 58972 11164 > pts/0 S+ May08 6:51 nfacctd: MySQL Plugin [download] root > 18026 0.1 1.8 57468 9668 pts/0 S+ May08 6:20 nfacctd: MySQL > Plugin [upload] root 18027 0.0 1.7 56828 8988 pts/0 S+ > May08 0:09 nfacctd: MySQL Plugin [unmetered] > > Cheers for your help. > > -- Peter Adkins > > ---------------------------------------------------------------------------------------------------------------- > Configuration: > ---------------------------------------------------------------------------------------------------------------- > > !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! > ! General settings for the daemon. > !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! > > daemonize: false > sql_host: localhost > nfacctd_port: 9876 > > ! Define our buffers and pipe 'files.' > plugin_buffer_size: 5120 > plugin_pipe_size: 512000 > > ! We'll be using MySQL for storage. > plugins: mysql[download], mysql[upload], mysql[unmetered] > > ! SQL database setup information > sql_db: pmacct > sql_table: acct_v5 > sql_table_version: 5 > sql_history: 1m > sql_history_roundoff: m > sql_passwd: <secret> > sql_user: <username> > sql_refresh_time: 5 > > !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! > !! Out networks, and therefore data accounting ranges are defined below. > !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! > > ! Our list of networks (aggregate.) > networks_file: /usr/local/pmacctd/conf/nfacctd.networks > > ! Define our data 'pools.' > aggregate[download]: tag,dst_host > aggregate[upload]: tag,src_host > aggregate[unmetered]: tag,dst_host > > ! Setup our tags for differentiation. > pre_tag_map: /usr/local/pmacctd/conf/pretag.map > > ! Inbound traffic (Downloads) for customer ranges. > aggregate_filter[download]: <aggregate IP ranges> > > ! Outbound traffic (Uploads) for customer ranges. > aggregate_filter[upload]: <aggregate IP ranges> > > ! Local data (Uploads and Downloads, all counted) for customer ranges. > aggregate_filter[unmetered]: <aggregate IP ranges> > > _______________________________________________ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
