(also posted to the 10gen blog: here)
I'm excited to report that Dex (github) is now equipped with his first planned upgrade. For those of you who haven't met him, Dex is an open-source python tool that suggests indexes for your MongoDB database. The initial release of Dex supported logfile-based analysis only. Now, Dex can be run remotely by leveraging MongoDB's built-in database profiler. This new feature is invoked with the -p or --profile option. When you run Dex with -p, it connects to the specified MongoDB database and analyzes the queries already logged to your system.profile collection.
As the diagram below shows, this is particularly good news for MongoLab's shared plan customers and anyone who does not have direct terminal access to their database machine.
Here's a quick set of steps to get you started.
- If you haven't already, get Dex:
sudo pip install dex
- Or, if you already have Dex:
sudo pip install dex --upgrade
- Log into your database through the mongo shell and run db.setProfilingLevel(1). If your MongoDB is hosted with us at MongoLab, you can also enable profiling through our UI.
- Let your app run for a while. With profiling enabled, MongoDB deposits documents into system.profile. By default, each of these documents represents a database operation that took more than 100ms to complete. For apps that perform specific operation at specific times, you will need to profile during those times. Once you feel that your profile collection is populated with a representative set of data, you're ready to run Dex!
- Run Dex with --profile or -p (instead of -f):
dex <mongodb-uri> -p
Where <mongodb-uri> is your database connection string (ex: mongodb://me:firstname.lastname@example.org:27227/mydb)
Note: If you use a Sandbox plan on MongoLab (or do not have an admin URI for other reasons) you must provide a -n/--namespace filter to narrow your request, or your Dex attempt will fail for authentication reasons. (ex: -n "mydb.*")
> dex mongodb://me:email@example.com:27227/mydb -p -n "mydb.*"
- Dex outputs index recommendations and corresponding creation syntax. Because Dex relies on heuristics that don't take your data into account, you'll want to validate and sanity check Dex's suggestions before implementing.
- Run db.setProfilingLevel(0) to disable profiling when you're done. Profiling requires a small bit of overhead and is entirely diagnostic, so you don't need to leave it running. If you like, you can also drop the system.profile collection afterwards.
As always: if you have any questions, bug reports, or feature requests, please email us at firstname.lastname@example.org.
Until next time, good luck out there!
(updates 2012-07-16: fixed missing URI; 2012-07-17: added 10gen cross-post URL)