Rubyisms

Com_show_fields as a RoR indicator (and not cached enough?)

Some platforms reveal themselves through the characteristic set of commands they issue to the database server. The JDBC driver for example makes very extensive use of the SET command, so a high and increasing value of Com_set is a good hint that JDBC is being used. RoR is also very distinctice, as the Active Record (AR) component of RoR issues "SHOW FIELDS FROM" a lot. So if your Com_show_fields counter in SHOW GLOBAL STATUS is moving a lot, you are looking at a system that is being used from AR and RoR.

In fact, AR seems to love "SHOW FIELDS FROM" a lot. One wonders why. The schema is not supposed to change a lot, especially in Ruby where there is the concept of a "migration" which is an orderly schema upgrade or downgrade. I'd expect AR to cache this information, and actually even preheat that cache. So I'd expect a number of "SHOW FIELDS FROM" when the application is being started and then no more. Instead we seem to see a lot of ongoing "SHOW FIELDS FROM", and more than necessary.

Sloppy quoting inside

For some reason, the RoR developers seem to hate quoting. It really would be better to use backticks around the #{table_name} placeholder here. Then even table names such as lines would no longer create MySQL errors. Without the quotes, irb shows me an unquoted table name, as expected: There are many more places in AR's mysql connection adapter where proper quoting is missing, just search for "#{" and check if there are backticks
around stuff.

A lot of "select *"

RoR uses AR to generate SQL and send it to the database. Because the SQL is being generated dynamically to order and we have just checked which fields are present, the usual reservation against "select *" - it breaks your code if you change the schema - does not hold.

It still transfers a lot of data that may be unnecessary, though. And what is worse, all the unnecessary data will probably become part of your tmp tables, should the query have a plan that shows "using temporary" in EXPLAIN.

That in fact is extremely bad for performance: A definition such as leaves you with If any field with "TEXT" or "BLOB" in its type name becomes part of your SELECT query and the query is using temporary tables in its execution plan, the temporary table cannot be created in memory. Instead a temporary table on disk has to be used, regardless of MySQLs settings for tmp_table_size and max_heap_table_size.

It would be much faster to select the IDs of the wanted records only (maybe adding all other fields that can easily be represented in MEMORY tables), and then retrieve the "description" TEXT field with a "SELECT id, description FROM products WHERE id IN ( idlist )" later - this is a primary key lookup and does not need a temporary table on disk. In AR, this is written as a primary key lookup of the form "find([ ... ])" where "[...]" is an array of the ids you want to retrieve.

This leaves us with the problem of avoiding the star in the "select *" that AR generates. For this as well, there is an option to find(): the find method accepts an option hash as its final parameter, and that option has not only allows for the usual :conditions, :order or :limit options, but also for ":select => [ fieldlist ]".

In fact, I believe you should override ActiveRecord::Base and generate a warning for every find call that does not contain a :select field list.

Wide temporary tables are still a problem

Even if you do not use TEXT fields at all, RoR seems to be quite wasteful: The table above has two fields "title" and "image_url", both of which are simply declared as a "string". Inside the database, this becomes a table definition with VARCHAR(255) columns of the default character set - in many MySQL installations that is utf8.

Memory tables as used in temporary tables do not have VARCHAR types. Instead, the VARCHAR is being converted into a CHAR - "VARCHAR(255) CHARSET utf8" becomes "CHAR(255) CHARSET utf8" - using 765 bytes per row and :string. With the default max_heap_table_size of 16M and for the table definition "products" as shown above, this allows for a temporary table of approximately 10.000 rows before it has to converted into a temporary table on disk (which may then be a lot smaller, because MyISAM does deal with VARCHAR in a more efficient way).

AR cannot specify the database character set in its "create_database" method. So the default value from charset_server is being used. Make sure that in your server this is set to "latin1" or any other single byte character set - never use utf8 by default. Alternatively, specify each "create_table" with an :option => "engine = innodb charset latin1" or similar to avoid using utf8 as a default.

For each string that needs utf8, you should specify that explicitly - but only for strings that actually need this. Use again an :option clause as in ":option => 'charset utf8'" to do this. You should also specify a :limit clause for each and every string, limiting the length of the string to something useful and avoiding the wasteful VARCHAR(255).

In fact, maybe you want to modify your AR in a way that it flags all :string field definitions without a proper :limit clause.

No unsigned integers and few unique constraints

For some reason, RoR seems to frown on the use of unsigned integers for auto_increment values. auto_increment on the other hand will create a lot of really interesting error messages if you manage to manually create negative values in your auto_increment sequence.

RoR really should make all values it uses as primary keys and foreign keys UNSIGNED by default.

RoR also creates a default primary key named id for each and every table. In fact, many Rails developers get so used to this that they often forget to check for natual primary keys and unique constraints where appropriate. If you for example have a relation between users and classes, where each user can be a member of multiple classes, and each class is joined by multiple users, then you will have a memberships relation table which will have user_id and class_id foreign key columns. By default, RoR will also create an id column in memberships, which is useless, and not create a unique constraint or even primary key for ( user_id, class_id) which expresses the fact that each user can only be a member in each class once.

"add_index(:memberships, [:user_id, :class_id], :unique => true)" will create such a compound unique index.

save! saves all attributes

If you are looking at the binary log, you will find a lot of queries of the form "UPDATE products SET WHERE id = ". This is the result of an AR product.save call. AR will needlessly save all attributes, even when they have not been changed.

Since all attributes in AR are always wrapped in accessor methods, it would be easy to keep change flags for all fields and generate minimal UPDATE statements. It would even be possible to forgo the statement completely if nothing has been changed.

Your query cache is probably useless

Apparently Ruby uses InnoDB by default. Apparently Ruby uses transactions a lot. That means that the query cache is of very limited use, because queries inside a transaction cannot use the query cache.

The consequences of foreign key constraints

In Locks Set by Different SQL Statements in InnoDB we explain:

If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails.

In practice that translates to a lot more locks on tables than you might expect if you happen to use foreign key constraints a lot. If you have a foreign key constraint checking that your class_id in memberships is a valid id in classes, then touching memberships.class_id will set a shared lock on your current classes.id in addition to the expected exclusive lock on memberships.class_id.

If you also implement a transaction and its inverse in a mirror symmetric way, you'll create a lot of deadlocks. For example, you might have a user join and leave a class, and you keep a membership counter in your class to be able to check quickly if a class is full or still has room. If the join updates memberships, it will set an exclusive lock on the current memberships.class_id record and a shared lock on the current classes.id, then it will increment classes.count and need an additional exclusive lock on classes.id to do this.

The opposite transaction might try to decrement classes.count first, requiring an exclusive lock on classes.id, and only then will try to touch memberships.class_id, adding an exclusive lock on memberships.class_id and the shared lock on classes.id to its current set of locks.

If you run a join and a leave for the same class and member combination at the same time (ok, bad example, but still consider it), you will have created a deadlock that will end in a rollback and a ruby exception.

This cannot happen if you implement each transaction and its inverse in the same order instead of doing it with a mirror symmetry.