Shiba is a tool (currently in alpha) that automatically reviews SQL queries before they cause problems in production. It uses production statistics for realistic query analysis. It catches missing indexes, overly broad indexes, and queries that return too much data.
Install in a Rails / ActiveRecord project using bundler. Note: this gem is not designed to be run on production. It should be required after minitest/rspec.
# Gemfile gem 'shiba', :group => :test, :require => 'shiba/setup'
If your application lazy loads gems, you will to manually require it.
# config/environments/test.rb or test/test_helper.rb require 'shiba/setup'
To get started, try out shiba locally. To verify shiba is actually running, you can run your tests with SHIBA_DEBUG=true.
# Install bundle # Run some tests using to generate a SQL report rake test:functional rails test test/controllers/users_controller_test.rb SHIBA_DEBUG=true ruby test/controllers/users_controller_test.rb # 1 problematic query detected # Report available at /tmp/shiba-explain.log-1550099512
Note: Postgres support is under development. For hopefully reliable results, test tables should have at least 1,000 rows.
- Integrate with Github pull requests
- Add production stats for realistic analysis
- Preview queries from the developer console
- Read more about typical query problems
- Using with languages other than Ruby
Going beyond table scans
Without more information, Shiba acts as a simple missed index detector. To catch other problems that can bring down production (or at least cause some performance issues), Shiba requires general statistics about production data, such as the number of rows in a table and how unique columns are.
This information can be obtained by running the bin/dump_stats command in production.
production$ git clone https://github.com/burrito-brothers/shiba.git cd shiba ; bundle bin/mysql_dump_stats -d DATABASE_NAME -h HOST -u USER -pPASS > ~/shiba_index.yml local$ scp production:~/shiba_index.yml RAILS_PROJECT/config
The stats file will look similar to the following:
local$ head <rails_project>/config/shiba_index.yml
users: count: 10000 indexes: PRIMARY: name: PRIMARY columns: - column: id rows_per: 1 # one row per unique `id` unique: true index_users_on_email: name: index_users_on_email columns: - column: email rows_per: 1 # one row per email address (also unique) unique: true index_users_on_organization_id: name: index_users_on_organization_id columns: - column: organization_id rows_per: 20% # each organization has, on average, 20% or 2000 users. unique: false
Automatic pull request reviews
Shiba can automatically comment on Github pull requests when code changes appear to introduce a query issue. To do this, it will need the Github API token of a user that has access to the repo. Shiba's comments will appear to come from that user, so you'll likely want to setup a bot account on Github with repo access for this. The token can be generated on Github at https://github.com/settings/tokens.
Once the token is ready, you can integrate Shiba on your CI server by following these steps:
On Travis, add this to the after_script setting:
# .travis.yml after_script: - bundle exec shiba review --submit
Add the Github API token you've generated as an environment variable named
SHIBA_GITHUB_TOKEN at https://travis-ci.com/organization/repo/settings.
To integrate with CircleCI, add this after the the test run step in
# .circleci/config.yml - run: name: Review SQL queries command: bundle exec shiba review --submit
An environment variable named
SHIBA_GITHUB_TOKEN will need to be configured on CircleCI under Project settings > Environment Variables
Custom CI Integration
To run on other servers, two steps are required:
- Ensure an environment variable named
CIis set when the tests and shiba script are run.
- Run the
shiba reviewcommand after tests are run, supplying the required arguments to
--submit, --token, --branch, and --pull-request. For example:
CI=true export CI rake test bundle exec shiba review --submit --token $MY_GITHUB_TOKEN --branch $(git rev-parse HEAD) --pull-request $MY_PR_NUMBER
--submit option tells Shiba to comment on the relevant PR when an issue is found.
Analyze queries from the developer console
For quick analysis, queries can be analyzed from the Rails console.
# rails console  pry(main)> require 'shiba/console' => true  pry(main)> shiba User.where(email: "[email protected]") Severity: high ---------------------------- Fuzzed Data: Table sizes estimated as follows -- 100000: users Table Scan: The database reads 100% (100000) of the of the rows in **users**, skipping any indexes. Results: The database returns 100000 row(s) to the client. Estimated query time: 3.02s => #<Shiba::Console::ExplainRecord:0x00007ffc154e6128>: 'SELECT `users`.* FROM `users` WHERE `users`.`email` = '[email protected]''. Call the 'help' method on this object for more info.  pry(main)>
Raw query strings are also supported, e.g.
shiba "select * from users where users.email = '[email protected]'"
Typical query problems
Here are some typical query problems Shiba can detect. We'll assume the following schema:
create_table :users do |t| t.string :name t.string :email # add an organization_id column with an index t.references :organization, index: true t. end
Full table scans
The most simple case to detect are queries that don't utilize indexes. While it isn't a problem to scan small tables, often tables will grow large enough where this can become a serious issue.
user = User.where(email: '[email protected]').limit(1)
Without an index, the database will read every row in the table until it finds one with an email address that matches. By adding an index, the database can perform a quick lookup for the record.
Non selective indexes
Another common case is queries that use an index, and work fine in the average case, but the distribution is non normal. These issues can be hard to track down and often impact large customers.
users = User.where(organization_id: 1) users.size # => 75 users = User.where(organization_id: 42) users.size # => 52,000
Normally a query like this would only become a problem as the app grows in popularity. Fixes include adding
With more data, Shiba can help detect this issue when it appears in a pull request.
Shiba commands can be used to analyze non Ruby / Rails projects when given a query log file. The log file is a list of queries with the query's backtrace as a SQL comment. The backtrace comment must begin with the word 'shiba' followed by a JSON array of backtrace lines:
SELECT `users`.* FROM `users` WHERE `users`.`email` = '[email protected]' /*shiba["test/app/app.rb:29:in `<main>'", "Rakefile:0:in `<run>'"]*/ SELECT `users`.* FROM `users` ORDER BY `users`.`id` ASC LIMIT 1 /*shiba["test/app/app.rb:30:in `<main>'", "Rakefile:0:in `<run>'"]*/
The generated log file can then be analyzed after installing Ruby.
gem install bundler git clone https://github.com/burrito-brothers/shiba.git cd shiba bundle bin/explain -f query.log --database <DB_NAME> --server mysql --json explain.log.json bin/review -f explain.log.json # When no problem queries are found, the command will exit with status 0. $ echo $? $ 0