Re: [pmacct-discussion] No Sqlite3 logging
Hi JF, On Mon, Nov 09, 2009 at 05:09:40AM -0500, JF Cliche wrote: debug: true syslog: kern plugin_buffer_size: 10240 plugin_pipe_size: 10240 interface: egiga0 daemonize: false promisc: false sql_cache_entries: 2 plugins: sqlite3[in] sql_max_writers: 2 pidfile: /vol1/pmacct.pid sql_db: /vol1/pmacct.db aggregate[in]: dst_host aggregate_filter[in]: dst net 11.0.3.0/24 sql_table[in]: acct sql_table_version: 1 sql_refresh_time: 5 From the debug output you posted, it looks everything is OK (database exists, file permissions are fine, etc.) - except pmacct doesn't have any data to report. Can you please comment the 'aggregate_filter' out? Another chance is that the daemon is bound to an interface with almost no traffic and hence the plugin_buffer_size/plugin_pipe_size couple is buffering and hence hiding traffic out. While here, i would suggest to review the following things from the above config - perhaps good if/when you go in production: * remove the 'sql_cache_entries: 2' line or perhaps explain the goal you want to achieve. This sets the number of buckets of the hashed SQL cache structure and can lead to sustained CPU utilization, if configured incorrectly. * remove the 'sql_max_writers: 2' line to at least 10; or increase sql_refresh_time * increase sql_refresh_time to something more sensible 60-120 secs; unless indeed this is done on purpose. * Try to keep plugin_pipe_size in the 1000*plugin_buffer_size area Cheers, Paolo ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] No Sqlite3 logging
Hello Paolo, I had played with many options in order to force the buffer to flush into the database file or test other hypotheses, but that did not help. But I admit I do not have a deep understanding of what every option does. In any case, I cleaned- up my config file and made sure I filter nothing (see config below). I rechecked pmacctd using the memory plugin and data is being gathered. Then I relaunched with the sqlite3 plugin. 'pmacct -s' still generate an error: the pipe file is not created. And a large number of sqlite3 writer processes are created. To be sure I have traffic, I copied a 2 GB file on the server while logging. What I got is: ... ( in/sqlite3 ) *** Purging cache - START *** ( in/sqlite3 ) *** Purging cache - END (QN: 0, ET: 0) *** WARN ( in/sqlite3 ): Maximum number of SQL writer processes reached (10). ( in/sqlite3 ) *** Purging cache - START *** ( in/sqlite3 ) *** Purging cache - END (QN: 0, ET: 0) *** ... Which is not surprising given the writer processes never terminate. And also: ERROR ( in/sqlite3 ): We are missing data. If you see this message once in a while, discard it. Otherwise some solutions follow: - increase shared memory size, 'plugin_pipe_size'; now: '3669904'. - increase buffer size, 'plugin_buffer_size'; now: '112'. - increase system maximum socket size. Which may be normal due to the low power of that little CPU. But I wonder about the 'plugin_buffer_size`of 112: is seems small. This makes me wonder if maybe there is a lack of memory somewhere. I have 1.5 MB of RAM free, and about 1 MB of RAMdisk where the system files are located (/var, /tmp etc...). Just in case use 'imt_path' to put the pipe file on the hard disk (1 TB free...) but that did not help. Maybe there are other files I should force there? I wished there were a verbose mode on the sqlite3 plugin so we had a better idea on what is happening. Is there a compilation option for that? Thanks, JF Config and log data follows: ! debug: true !syslog: kern interface: egiga0 daemonize: false promisc: false plugins: sqlite3[in] imt_path[in]: /vol1/collect.pipe pidfile: /vol1/pmacct.pid sql_db: /vol1/pmacct.db aggregate: dst_host, src_host, dst_port, src_port sql_table: acct sql_table_version: 1 sql_refresh_time: 10 ! Log message on launch: INFO ( in/sqlite3 ): 131070 bytes are available to address shared memory segment; buffer size is 112 bytes. INFO ( in/sqlite3 ): Trying to allocate a shared memory segment of 3669904 bytes. OK ( default/core ): link type is: 1 ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] No Sqlite3 logging
Hi JF, On Mon, Nov 09, 2009 at 10:26:35AM -0500, JF Cliche wrote: In any case, I cleaned- up my config file and made sure I filter nothing (see config below). I rechecked pmacctd using the memory plugin and data is being gathered. Then I relaunched with the sqlite3 plugin. 'pmacct -s' still generate an error: the pipe file is not created. And a large number of sqlite3 writer processes are created. The pmacct client tool is only useful to interact with the memory plugin; when you are using only the sqlite3 plugin, it tries to see if a memory plugin is listening on the pipe file - but nothing is there and hence it triggers the error. I would say this is all OK. ... ( in/sqlite3 ) *** Purging cache - START *** ( in/sqlite3 ) *** Purging cache - END (QN: 0, ET: 0) *** WARN ( in/sqlite3 ): Maximum number of SQL writer processes reached (10). ( in/sqlite3 ) *** Purging cache - START *** ( in/sqlite3 ) *** Purging cache - END (QN: 0, ET: 0) *** ... Now, this part is interesting. As you said, writer processes seem to not be replenished - while they should be shortly after each END debug message you see above; and also it seems the writer process is unable to dump the SQL cache content into the database (as the query number, QN, stays at zero while you produce some traffic; moreover such traffic is reaching the sqlite3 plugin no problems as you can get the buffering error ERROR ( in/sqlite3 ): We are missing data from the plugin). To rule out some configuration issue, i've copy/pasted your config on a testbed - and i see it working fine. This and the fact that the memory plugin works fine (it has a different way of working compared the SQL plugins) suggests me the issue could be at a lower level. I can propose some solutions: 1) allow me to troubleshoot the issue; this would require an access to the NAS (and feel free to bring this over privately); 2) if the above is not possible a clean alternative might be let pmacct to write to a remote SQL database, either MySQL or PostgreSQL; it has to be checked you don't run in the same SQLite issue first; 3) check whether what you want to achieve, can be done with the memory plugin. Cheers, Paolo ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] No Sqlite3 logging
Hello Paolo, Testing MySQL was my next step, but I'll need to learn how to set it up first. So I'll do my homeworks and I'll keep you posted. Thanks, JF On Mon, Nov 9, 2009 at 12:38 PM, Paolo Lucente pa...@pmacct.net wrote: Hi JF, On Mon, Nov 09, 2009 at 10:26:35AM -0500, JF Cliche wrote: In any case, I cleaned- up my config file and made sure I filter nothing (see config below). I rechecked pmacctd using the memory plugin and data is being gathered. Then I relaunched with the sqlite3 plugin. 'pmacct -s' still generate an error: the pipe file is not created. And a large number of sqlite3 writer processes are created. The pmacct client tool is only useful to interact with the memory plugin; when you are using only the sqlite3 plugin, it tries to see if a memory plugin is listening on the pipe file - but nothing is there and hence it triggers the error. I would say this is all OK. ... ( in/sqlite3 ) *** Purging cache - START *** ( in/sqlite3 ) *** Purging cache - END (QN: 0, ET: 0) *** WARN ( in/sqlite3 ): Maximum number of SQL writer processes reached (10). ( in/sqlite3 ) *** Purging cache - START *** ( in/sqlite3 ) *** Purging cache - END (QN: 0, ET: 0) *** ... Now, this part is interesting. As you said, writer processes seem to not be replenished - while they should be shortly after each END debug message you see above; and also it seems the writer process is unable to dump the SQL cache content into the database (as the query number, QN, stays at zero while you produce some traffic; moreover such traffic is reaching the sqlite3 plugin no problems as you can get the buffering error ERROR ( in/sqlite3 ): We are missing data from the plugin). To rule out some configuration issue, i've copy/pasted your config on a testbed - and i see it working fine. This and the fact that the memory plugin works fine (it has a different way of working compared the SQL plugins) suggests me the issue could be at a lower level. I can propose some solutions: 1) allow me to troubleshoot the issue; this would require an access to the NAS (and feel free to bring this over privately); 2) if the above is not possible a clean alternative might be let pmacct to write to a remote SQL database, either MySQL or PostgreSQL; it has to be checked you don't run in the same SQLite issue first; 3) check whether what you want to achieve, can be done with the memory plugin. Cheers, Paolo -- Jean-François Cliche, Ph.D., P. Eng ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] No Sqlite3 logging
Hello Paolo, I configured mySQL and everything works like a charm now. Due to the low CPU power on my NAS I had to increase the `plugin_buffer_size` to 102400 to make sure I do not miss any packets. The memory plugin was very useful to do the tuning. Note: in DEBUG mode, I had the impression that the dropped packet message did not show, but I may be mistaken. I can now track a 2 GB transfer without losing anything. Of course, if I log my internal network transfers to my NAS I have a 50% transfer rate drop because the transfer are CPU-limited, not network- or disk access-limited (thanks to the low power ARM processor in the NAS). But my goal is to log slower extra-network trafic anyway. With proper filters, I incur only a 10-15 % performance hit on intranet transfers to the NAS caused by the minimum CPU required to realize it does not want to log these packets. Apart from what is in the FAQ, do you have any suggestions to reduce the load caused by the packet filtering? Of course that will not be a problem with anyone with a decent processor. Interesting fact: I tried the ipfm program to log everything, including internal traffic, I lose only 10% of my transfer rate instead of 50% with pmacct. But I realized it drops 80% of the packets, without telling me. So bravo pmacct. I think I will try MySQL for a while. It does not seem to be too ressource-hungry. If you still want to debug the sqlite3 problem let me know. I suspect it has something to do with the slightly non-traditional Linux setup on the DNS-323. By the way, I intend to run a cron task that will do DNS lookups my external hosts (they have dynamic addresses) and store the addresses in another SQL table so I can link traffic with dynamically addressed host names. Many thanks, Jean-François On Mon, Nov 9, 2009 at 1:03 PM, JF Cliche jfcli...@jfcliche.com wrote: Hello Paolo, Testing MySQL was my next step, but I'll need to learn how to set it up first. So I'll do my homeworks and I'll keep you posted. Thanks, JF On Mon, Nov 9, 2009 at 12:38 PM, Paolo Lucente pa...@pmacct.net wrote: Hi JF, On Mon, Nov 09, 2009 at 10:26:35AM -0500, JF Cliche wrote: In any case, I cleaned- up my config file and made sure I filter nothing (see config below). I rechecked pmacctd using the memory plugin and data is being gathered. Then I relaunched with the sqlite3 plugin. 'pmacct -s' still generate an error: the pipe file is not created. And a large number of sqlite3 writer processes are created. The pmacct client tool is only useful to interact with the memory plugin; when you are using only the sqlite3 plugin, it tries to see if a memory plugin is listening on the pipe file - but nothing is there and hence it triggers the error. I would say this is all OK. ... ( in/sqlite3 ) *** Purging cache - START *** ( in/sqlite3 ) *** Purging cache - END (QN: 0, ET: 0) *** WARN ( in/sqlite3 ): Maximum number of SQL writer processes reached (10). ( in/sqlite3 ) *** Purging cache - START *** ( in/sqlite3 ) *** Purging cache - END (QN: 0, ET: 0) *** ... Now, this part is interesting. As you said, writer processes seem to not be replenished - while they should be shortly after each END debug message you see above; and also it seems the writer process is unable to dump the SQL cache content into the database (as the query number, QN, stays at zero while you produce some traffic; moreover such traffic is reaching the sqlite3 plugin no problems as you can get the buffering error ERROR ( in/sqlite3 ): We are missing data from the plugin). To rule out some configuration issue, i've copy/pasted your config on a testbed - and i see it working fine. This and the fact that the memory plugin works fine (it has a different way of working compared the SQL plugins) suggests me the issue could be at a lower level. I can propose some solutions: 1) allow me to troubleshoot the issue; this would require an access to the NAS (and feel free to bring this over privately); 2) if the above is not possible a clean alternative might be let pmacct to write to a remote SQL database, either MySQL or PostgreSQL; it has to be checked you don't run in the same SQLite issue first; 3) check whether what you want to achieve, can be done with the memory plugin. Cheers, Paolo -- Jean-François Cliche, Ph.D., P. Eng -- Jean-François Cliche, Ph.D., P. Eng ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
