MS SQL Support?
Hello, I'm wondering if there's any supported method for moving Inventory from sqlite to MS SQL?
The long version: After many hours of watching inventory and the sqlite database directory I've begun to notice a problem with it in my environment. Anytime there's a large number of computers being updated (1000+) by something in deploy, Inventory begins to get bogged down. What I mean by that is computers will start to take longer, and longer to complete a scan. Once I have over 300-400 being scanned at once its snowballs into 1000+ and after an hour computers will start to time out and never finish the scan. What else I've noticed, during these large number of scans, is that the Database.db-wal file (in the database directory) is never left alone to write to the database. The db-wal file grows and grows until it's several GB in size (note: my database is only 400ish MB). My understanding is that the db-wal file wants to write to the database after roughly 1000 pages or 4MB but cannot if it's being continually accessed. If I stop the Inventory service, let the db-wal file catchup, and then start the service again computers scan VERY fast (a few seconds). After a little while, when the db-wal file is 2-3 times larger than the database, again, everything has slowed way down. I would like to at least see if moving my Inventory database to MSSQL would solve this problem. I'm also open to any other idea to solve this problem.
A little more information:
- I've tried small and large number of concurrent scans. Large actually seems to be better (500-1000).
- I only have two scans being used currently: one runs every 24 hours, the other runs after a successful deployment.
- I’m using the provided Collection Libraries and Auto Deploy features for most of these large updates, so dividing them into smaller collections to spread the scanning load isn’t any easy solution.
- Deploy and Inventory run on the same box.
- Our VM environment is not limited by resources and the storage is a Nimble 460.
Thank you in advance!
Brad
Comments
I found a command this morning I can run in the SQLite console and that forces the WAL file to checkpoint. Once the WAL file checkpoints computers begin to save, again, very quickly. I can run this command without stopping any service which seems like a much better option.
I've noticed that the WAL file will sometimes continue to checkpoint successfully even with a couple hundred computers scanning. But other times it'll get locked (and grow to 1GB+) and if I run this command it will seemingly be corrected (always temporarily) and continue scanning quickly. It seems like this has to be a bug and the WAL file should be doing checkpoints without manual intervention even during large scan jobs. Also, I'm not a DBA and I have zero experience with SQLite so I don't know what side effects running this command might have on the database. I'm willing to run this against our database though since, in its current state, Inventory is practically unusable for large scanning jobs. Here's a link to the site where I got the command: https://www.sqlite.org/pragma.html#pragma_wal_checkpoint
Thanks again!
Brad
Hi Brad,
Can you do us a favor and go to Preferences -> Database
Click on SQLite console button
Run this command:
**(Note - make sure you include the semicolon)**
Attach a screenshot of what see when you run that command please.
Before you close Preferences on that same panel can you click on the Optimize Database button as well and see if that helps with the performance.
Hi, Jason
I've attached a screenshot of the results of running that command. It came back "ok".
I've run optimize database several times in the past. It does help for a short time. But the database always starts to slow down again after a large number of computers scan.
I forgot to add. The slowdown is always temporary. After a large scan job it will eventually catch up and everything will start running normal again, but this can take several hours or even over night. Like, right now, it's running fine.
Hi Brad,
Thanks for sending that back in. Can you let us know what version of Inventory you're currently running?
Hi, Jason
I'm running 7.3.0.0. The latest I believe.
*** Note *** This was resolved in an internal support ticket with the customer. Due to the nature of each users environment there is no blanket answer to solve this. If you are experiencing issues please submit them to support@adminarsenal.com.