This project is archived and is in readonly mode.

#717 ✓invalid
mcarr

Production mode makes many unnecessary SHOW TABLES calls

Reported by mcarr | July 29th, 2008 @ 02:07 AM | in 2.x

When running rails in production mode (on rails 2.0.2) there are unnecessary calls to SHOW TABLES. If you view a production log file that has SQL printed out you will notice that SHOW TABLES is called multiple times per action.

E.g.

07/28/08 06:01:06 PM: SHOW TABLES

7/28/08 06:01:06 PM: SHOW FIELDS FROM `users`

7/28/08 06:01:06 PM: SHOW TABLES

7/28/08 06:01:06 PM: SHOW FIELDS FROM `photosets`

7/28/08 06:01:06 PM: SHOW TABLES

7/28/08 06:01:06 PM: SHOW TABLES

The offending code is located in

activerecord/lib/active_record/connection_adapters/mysql_adapter.rb in def tables(name - nil) ...

If you patch mysql_adapter.rb with the diff attached... SHOW TABLES will only show up once.

Comments and changes to this ticket

  • Barry Robison

    Barry Robison August 21st, 2008 @ 10:45 AM

    I believe that should be

       def tables(name = nil) #:nodoc:
         tables = []
    
    •  execute("SHOW TABLES", name).each { |field| tables << field[0] }
      
    •  execute("SHOW TABLES", name).each { |field| tables << field[0] } unless @tables
      
    •  @tables ||= tables
      
    •  tables
      
    •  @tables
      
      end

    at least that works better for me =0

  • Pratik

    Pratik August 21st, 2008 @ 11:12 AM

    • State changed from “new” to “invalid”

    Can't reproduce. Could you please also attach a failing test case ?

    Thanks.

  • Barry Robison

    Barry Robison August 21st, 2008 @ 11:21 AM

    Sorry I don't really know Ruby, just trying to performance analyze an app. The last statement in the block is the return value right? In the original patch that would be "tables", which is initialized to an empty array. It's only populated if @tables is not yet cached, so after the first run, you'll be returning an empty list instead of the cached one.

    Unless I'm missing something about ruby, which I probably am. And my revised patch got de-formated by the comment system =(

    ta, Barry

  • Barry Robison

    Barry Robison August 21st, 2008 @ 11:28 AM

    here's a similar method for not calling SHOW FIELDS every time a table is accessed, also seem to help speed.

    
          def columns(table_name, name = nil)#:nodoc:
            sql = "SHOW FIELDS FROM #{table_name}"
            columns = []
            @columnCache ||= {}
            execute(sql, name).each { |field| columns << MysqlColumn.new(field[0], field[4], field[1], field[2] == "YES") } unless @columnCache[table_name]
            @columnCache[table_name] ||= columns
            @columnCache[table_name]
          end
    

    hth, Barry

  • Fris

    Fris February 20th, 2011 @ 11:02 AM

    Steps to repro:

    1. Install will_paginate gem.
    2. Make some valid code that displays a index of records from some model.
    3. Include a call to <%= will_paginate %> in the ERB view.
    4. DO NOT call paginate in the controller.

    Result:
    You will get an error like:

    You have a nil object when you didn't expect it!
    You might have expected an instance of Array.
    The error occurred while evaluating nil.each
    

    and there will be many SHOW TABLES calls in the log file (each one taking about 4ms in my case).

  • Fris

    Fris February 20th, 2011 @ 11:18 AM

    I could not edit my post to add more info, so here it is (the stack trace of where the SHOW TABLES is being called from, I believe):

        ...rails303/gems/activesupport-3.0.3/lib/active_support/log_subscriber.rb:93:in `send`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/log_subscriber.rb:93:in `call`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/notifications/fanout.rb:47:in `publish`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/notifications/fanout.rb:25:in `publish`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/notifications/fanout.rb:25:in `each`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/notifications/fanout.rb:25:in `publish`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/notifications/instrumenter.rb:26:in `instrument`
        ...rails303/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract_adapter.rb:195:in `log`
        ...rails303/gems/mysql2-0.2.6/lib/active_record/connection_adapters/mysql2_adapter.rb:314:in `execute`
        ...rails303/gems/mysql2-0.2.6/lib/active_record/connection_adapters/mysql2_adapter.rb:432:in `tables`
        ...rails303/gems/activerecord-3.0.3/lib/active_record/connection_adapters/abstract/schema_statements.rb:21:in `table_exists?`
        ...rails303/gems/activerecord-3.0.3/lib/active_record/base.rb:673:in `table_exists?`
        ...rails303/gems/activerecord-3.0.3/lib/active_record/base.rb:795:in `inspect`
        ...rails303/gems/actionpack-3.0.3/lib/action_dispatch/middleware/templates/rescues/_request_and_response.erb:16:in `debug_hash`
        ...rails303/gems/actionpack-3.0.3/lib/action_dispatch/middleware/templates/rescues/_request_and_response.erb:16:in `map`
        ...rails303/gems/actionpack-3.0.3/lib/action_dispatch/middleware/templates/rescues/_request_and_response.erb:16:in `debug_hash`
        ...rails303/gems/actionpack-3.0.3/lib/action_dispatch/middleware/templates/rescues/_request_and_response.erb:27:in `__home_base__rvm_gems_ruby_______p____rails____gems_actionpack_______lib_action_dispatch_middleware_templates_rescues__request_and_response_erb__147464093__612719698_0`
        ...rails303/gems/actionpack-3.0.3/lib/action_view/template.rb:135:in `send`
        ...rails303/gems/actionpack-3.0.3/lib/action_view/template.rb:135:in `render`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/notifications.rb:54:in `instrument`
        ...rails303/gems/actionpack-3.0.3/lib/action_view/template.rb:127:in `render`
        ...rails303/gems/actionpack-3.0.3/lib/action_view/render/rendering.rb:59:in `_render_template`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/notifications.rb:52:in `instrument`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/notifications/instrumenter.rb:21:in `instrument`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/notifications.rb:52:in `instrument`
        ...rails303/gems/actionpack-3.0.3/lib/action_view/render/rendering.rb:56:in `_render_template`
        ...rails303/gems/actionpack-3.0.3/lib/action_view/render/rendering.rb:26:in `render`
        ...rails303/gems/actionpack-3.0.3/lib/action_dispatch/middleware/templates/rescues/template_error.erb:21:in `__home_base__rvm_gems_ruby_______p____rails____gems_actionpack_______lib_action_dispatch_middleware_templates_rescues_template_error_erb__42385148__612684748_0`
        ...rails303/gems/actionpack-3.0.3/lib/action_view/template.rb:135:in `send`
        ...rails303/gems/actionpack-3.0.3/lib/action_view/template.rb:135:in `render`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/notifications.rb:54:in `instrument`
        ...rails303/gems/actionpack-3.0.3/lib/action_view/template.rb:127:in `render`
        ...rails303/gems/actionpack-3.0.3/lib/action_view/render/rendering.rb:59:in `_render_template`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/notifications.rb:52:in `instrument`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/notifications/instrumenter.rb:21:in `instrument`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/notifications.rb:52:in `instrument`
        ...rails303/gems/actionpack-3.0.3/lib/action_view/render/rendering.rb:56:in `_render_template`
        ...rails303/gems/actionpack-3.0.3/lib/action_view/render/rendering.rb:26:in `render`
        ...rails303/gems/actionpack-3.0.3/lib/action_dispatch/middleware/show_exceptions.rb:88:in `rescue_action_locally`
        ...rails303/gems/actionpack-3.0.3/lib/action_dispatch/middleware/show_exceptions.rb:68:in `render_exception`
        ...rails303/gems/actionpack-3.0.3/lib/action_dispatch/middleware/show_exceptions.rb:59:in `call`
        ...rails303/gems/railties-3.0.3/lib/rails/rack/logger.rb:13:in `call`
        ...rails303/gems/rack-1.2.1/lib/rack/runtime.rb:17:in `call`
        ...rails303/gems/activesupport-3.0.3/lib/active_support/cache/strategy/local_cache.rb:72:in `call`
        ...rails303/gems/rack-1.2.1/lib/rack/lock.rb:11:in `call`
        ...rails303/gems/rack-1.2.1/lib/rack/lock.rb:11:in `synchronize`
        ...rails303/gems/rack-1.2.1/lib/rack/lock.rb:11:in `call`
        ...rails303/gems/actionpack-3.0.3/lib/action_dispatch/middleware/static.rb:30:in `call`
        ...rails303/gems/railties-3.0.3/lib/rails/application.rb:168:in `call`
        ...rails303/gems/railties-3.0.3/lib/rails/application.rb:77:in `send`
        ...rails303/gems/railties-3.0.3/lib/rails/application.rb:77:in `method_missing`
        ...rails303/gems/railties-3.0.3/lib/rails/rack/log_tailer.rb:14:in `call`
        ...rails303/gems/rack-1.2.1/lib/rack/content_length.rb:13:in `call`
        ...rails303/gems/rack-1.2.1/lib/rack/handler/webrick.rb:52:in `service`
        ...ruby-1.8.7-p330/lib/ruby/1.8/webrick/httpserver.rb:104:in `service`
        ...ruby-1.8.7-p330/lib/ruby/1.8/webrick/httpserver.rb:65:in `run`
        ...ruby-1.8.7-p330/lib/ruby/1.8/webrick/server.rb:173:in `start_thread`
        ...ruby-1.8.7-p330/lib/ruby/1.8/webrick/server.rb:162:in `start`
        ...ruby-1.8.7-p330/lib/ruby/1.8/webrick/server.rb:162:in `start_thread`
        ...ruby-1.8.7-p330/lib/ruby/1.8/webrick/server.rb:95:in `start`
        ...ruby-1.8.7-p330/lib/ruby/1.8/webrick/server.rb:92:in `each`
        ...ruby-1.8.7-p330/lib/ruby/1.8/webrick/server.rb:92:in `start`
        ...ruby-1.8.7-p330/lib/ruby/1.8/webrick/server.rb:23:in `start`
        ...ruby-1.8.7-p330/lib/ruby/1.8/webrick/server.rb:82:in `start`
        ...rails303/gems/rack-1.2.1/lib/rack/handler/webrick.rb:13:in `run`
        ...rails303/gems/rack-1.2.1/lib/rack/server.rb:213:in `start`
        ...rails303/gems/railties-3.0.3/lib/rails/commands/server.rb:65:in `start`
        ...rails303/gems/railties-3.0.3/lib/rails/commands.rb:30
        ...rails303/gems/railties-3.0.3/lib/rails/commands.rb:27:in `tap`
        ...rails303/gems/railties-3.0.3/lib/rails/commands.rb:27", "script/rails:6:in `require`", "script/rails:6"
    
  • Fris

    Fris February 20th, 2011 @ 11:30 AM

    Just a wild guess, but maybe it's something to do with the call to inspect from debug_hash and the inspect is getting called on ActiveRecord::Base (which then calls table_exists?).

  • Fris

    Fris February 20th, 2011 @ 11:33 AM

    Ps.I am using Rails 3.0.3

  • bingbing

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Attachments

Pages