Rails:在 Oracle 上 rake db:migrate *非常* 慢 [英] Rails: rake db:migrate *very* slow on Oracle

问题描述

我将 rails 与 oracleenhanced 适配器结合使用，为遗留应用程序创建新接口.

数据库迁移工作成功，但在 rake 完成之前需要非常长的时间.数据库更改发生得非常快(1 或 2 秒)，但 db/schema.db 转储需要一个多小时才能完成.(请参阅下面的迁移示例.)

这是一个相对较大的架构(大约 150 个表)，但我确信不应该花这么长时间来转储每个表的描述.

是否可以通过仅获取最后一个 schema.db 并将迁移中指定的更改应用于它来加快速度?或者我可以完全跳过这个模式转储吗?

我知道这个 schema.db 用于每次从头开始创建测试数据库，但在这种情况下，表触发器中有大量数据库逻辑未包含在schema.rb 无论如何，所以在任何情况下，rake 测试对我们都没有好处.(这是一个完全不同的问题，我需要在其他时候解决.)

<前>dgs@dgs-laptop:~/rails/voyager$ 时间耙 db:migrate(在/home/dgs/rails/voyager 中)== 20090227012452 AddModuleActionAndControllerNames:迁移 ================-- add_column(:modules, :action_name, :text)-> 0.9619s-> 0 行-- add_column(:modules, :controller_name, :text)-> 0.1680s-> 0 行== 20090227012452 AddModuleActionAndControllerNames:已迁移(1.1304s)======真正的 87m12.961s用户 0m12.949s系统 0m2.128s

解决方案

在所有迁移应用到数据库之后，rake db:migrate 调用 db:schema:dump 任务从当前数据库模式生成 schema.rb 文件.

db:schema:dump 调用适配器的tables"方法来获取所有表的列表，然后为每个表调用indexes"方法和columns"方法.您可以在 activerecord-oracle_enhanced-adapter gem 的 oracle_enhanced_adapter.rb 文件中找到这些方法中使用的 SQL SELECT 语句.基本上它确实从 ALL% 或 USER% 数据字典表中选择以查找所有信息.

最初，当我将原始 Oracle 适配器与具有许多不同架构的数据库一起使用时，我遇到了问题(因为性能可能会受到数据库中表总数的影响 - 而不仅仅是在您的架构中)，因此我做了一些优化在 Oracle 增强型适配器中.最好找出在您的情况下哪些方法很慢(我怀疑它可能是为每个表执行的索引"或列"方法).

调试这个问题的一种方法是，如果您将一些调试消息放在 oracle_enhanced_adapter.rb 文件中，以便您可以确定哪些方法调用花费了这么长时间.

I'm using rails with the oracleenhanced adaptor to create a new interface for a legacy application.

Database migrations work successfully, but take an incredibly long amount of time before rake finishes. The database changes happen pretty quickly (1 or 2 seconds), but the db/schema.db dump takes over an hour to complete. (See example migration below.)

It's a relatively large schema (about 150 tables), but I'm sure it shouldn't be taking this long to dump out each table description.

Is there anyway to speed this up by just taking the last schema.db and applying the change specified in the migration to it? Or am I able to skip this schema dump altogether?

I understand this schema.db is used to create the test database from scratch each time, but this case, there's a large chunk of the database logic in table triggers which aren't included in the schema.rb anyway, so the rake tests are no good to us in any case. (That's a whole different issue that I need to sort out at some other point.)

dgs@dgs-laptop:~/rails/voyager$ time rake db:migrate
(in /home/dgs/rails/voyager)
== 20090227012452 AddModuleActionAndControllerNames: migrating ================
-- add_column(:modules, :action_name, :text)
   -> 0.9619s
   -> 0 rows
-- add_column(:modules, :controller_name, :text)
   -> 0.1680s
   -> 0 rows
== 20090227012452 AddModuleActionAndControllerNames: migrated (1.1304s) =======


real    87m12.961s
user    0m12.949s
sys 0m2.128s

解决方案

After all migrations are applied to database then rake db:migrate calls db:schema:dump task to generate schema.rb file from current database schema.

db:schema:dump call adapter's "tables" method to get the list of all tables, then for each table calls "indexes" method and "columns" method. You can find SQL SELECT statements that are used in these methods in activerecord-oracle_enhanced-adapter gem's oracle_enhanced_adapter.rb file. Basically it does selects from ALL% or USER% data dictionary tables to find all the information.

Initially I had issues with original Oracle adapter when I used it with databases with lot of different schemas (as performance might be affected by the total number of table in the database - not just in your schema) and therefore I did some optimizations in Oracle enhanced adapter. It would be good to find out which methods are slow in your case (I suspect that it could be either "indexes" or "columns" method which is executed for each table).

One way hoe to debug this issue would be if you would put some debug messages in oracle_enhanced_adapter.rb file so that you could identify which method calls are taking so long time.

这篇关于Rails:在 Oracle 上 rake db:migrate *非常* 慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！