SQLite versus MySQL performance

[emory]

I don't know if there are optimizations that can be done to risu or with sqlite, but there is a large discrepancy between the two when it comes to report generation, importing of data, and other operations involving stored data.

Anecdotally I have a 9.5MB .nessus archive that I fed to risu.

 sqlite: 44 minutes
 mysql: 84 seconds

Workstation is a MacMini5,3 (i7 quad-core) with 8GB of RAM running Mac OS X. It doesn't appear to be CPU-bound or io-bound so I'm not sure why it's slow, but it is much faster on MySQL than SQLite.

I'm curious if some of this isn't in risu itself, doing a "template list" risu -l takes 9 seconds. Nine very long seconds. I'm using ruby 1.9.2p290 via rvm.

[hammackj]

I use a macbook pro with less stats than that, and I have not had sqlite times over 150 seconds. Most of my test data is in the 6-9mb .nessus files. I use Sqlite for all my testing and production work with risu.

MySQL should be faster but 44 minutes for a SQLite file doesn't seem right.

On my machine risu -l is about 1 second and some change, faster each time i run it.

[hammackj@taco:~/Projects/private/hammackjllc/assessments/2012/valverde]$ time ~/Projects/public/risu/bin/risu -l Available Templates assets - Generates a Assets Summary Report cover_sheet - Generates a coversheet with a logo (Example Template) exec_summary - Generates a simple executive summary. exec_summary_detailed - Generates a detailed executive summary report finding_statistics - Generates report finding statistics findings_host - Generates a findings report by host findings_summary - Generates a findings summary report findings_summary_with_pluginid - Geneates a Findings Summary with Nessus Plugin ID graphs - Generates a report with all the graphs in it host_summary - Generates a Host Summary Report ms_patch_summary - Generates a Microsoft Patch Summary Report ms_update_summary - Generates a Microsoft Update Summary Report notable - Notable Vulnerabilities notable_detailed - Notable Vulnerabilities Detailed pci_compliance - Generates a PCI Compliance Overview Report technical_findings - Generates a Technical Findings Report

real 0m1.723s user 0m1.041s sys 0m0.230s

[hammackj@taco:~/Projects/private/hammackjllc/assessments/2012/valverde]$ ruby -v ruby 1.9.2p290 (2011-07-09 revision 32553) [x86_64-darwin11.0.0]

Could you post the following

ruby -v gem -v sqlite3 -version

If you are able to share the .nessus file, could you email that to jacob.hammack@hammackj.com and I will test that on my dev machine.

[emory]

I can give you the version information:

~/Projects/reporting % gem -v 1.8.10 ~/Projects/reporting % ruby -v ruby 1.9.2p290 (2011-07-09 revision 32553) [x86_64-darwin11.3.0] ~/Projects/reporting % sqlite3 -version 3.7.7 2011-06-25 16:35:41 8f8b373eed7052e6e93c1805fc1effcf1db09366

Possible caveat: I use a python script to merge .nessus files, this is so I can queue different groups of hosts in the Nessus console and then group them together later. It's possible that introduces a possible issue though I'm not sure what it would be.

while doing risu -l ruby will run with two threads, get 100% CPU and take about 90MB of virtual memory and build up to ~48MB of memory.

[hammackj]

What are you using to calculate 90mb of virtual memory? For me the process is so quick that it doesn't show up in top/ps/activity monitor.

Does the python script do any kind of processing of the hosts or change the data?

[emory]

Just watching it in Activity Monitor. I haven't dusted off my copy of Truss for Dummies yet but probably going to set aside my rvm environment and install it again. I noticed fs_usage showing an inordinate amount of activity when I run risu.

It's a very simple script, I didn't write it. It hunts for Reports and ReportHosts and stitches them into one .nessus file. (It's a real life-saver though I-tell-you-what)

https://gist.github.com/1717437

[hammackj]

If you want multiple nessus files to be in the same db, you can just parse all the files into the same database and then filter from there using sql.

Using the --console option i get 74mb virtual private memory usage with a 2.7mb database(sqlite) parsed from a 9.8mb .nessus file, as an example Metasploit in msfconsole uses 150mb. I wouldn't worry about the memory usage its mostly ruby.

[emory]

This isn't an issue on another system, I'm not willing to find out where my issue is on the system that is being slow, so I'm closing it out.