A way to improve performance using MySQL partition table in Rails _ruby topics

MySQL's partitioned table is a simple and efficient way to handle a large data table, which enables the application to achieve efficient processing of large data tables with little change, but the Rails ActiveRecord design practices may result in some data processing not taking advantage of partitioned table attributes. Instead, it becomes very slow, and you must pay more attention to the use of partitioned tables.

Here is an example to illustrate. In the light system, there is a data table is Diet_items, the main field is ID, schedule_id, meal_order food_id, weight, calory, etc., each of its records represents the user to generate a daily weight loss program (diet + Exercise program) In a diet item, the average plan has more than 10 data, the amount of data is very large, it is expected to generate more than 1 million daily data, so it did a table processing, according to SCHEDULE_ID Hash divided into 60 tables, that is, the data will be dynamically divided into 60 tables. The table-Diet_items statement after the table is as follows:

Copy Code code as follows:

CREATE TABLE ' Diet_items ' (
' id ' int (one) not NULL auto_increment,
' schedule_id ' int (one) is not NULL,
' Meals_order ' int (one) is not NULL,
' food_id ' int (one) DEFAULT NULL,
....
KEY ID (' ID '),
UNIQUE KEY ' index_diet_items_on_schedule_id_and_id ' (' schedule_id ', ' id ')
)
PARTITION by HASH (schedule_id)
Partitions 60;

After the table is Diet_items, all inquiries are required to take schedule_id, such as obtaining all diet_items of a schedule, through schedule. Diet_items, the Diet_item that gets an ID is also done by Schedule.diet_items.find (ID). Generating Diet_item is also no problem, because the generation Diet_item are all through the Schedule.diet_items.build (data) method, when the generation is brought schedule_id.

Observe the Newrelic log, found that Diet_item update and destroy related requests particularly slow, after careful analysis, found that these two operations are very busy because the ActiveRecord generated SQL does not bring schedule_id caused. The Diet_item update operation ActiveRecord generated SQL statements similar to the update diet_items set ... where id = <id>. The statement generated by Diet_item destroy is similar to the delete diet_items where id = <id> because there is no schedule_id, both statements require MySQL to scan 60 partitioned tables to complete a statement OK, very slow!

Once you know why, it's OK to change the original update and destroy call to a custom version of update and destroy calls.

Diet_item.update (attributes) changed to Dietitem.where (Id:diet_item.id, schedule_id:diet_item.schedule_id). Update_all ( Attributes

Diet_item.destroy changed into Dietitem.where (Id:diet_item.id, schedule_id:diet_item.schedule_id). Delete_all

This generates SQL with SCHEDULE_ID conditions, which eliminates the ability to scan all of the partitioned tables, and the performance boost is immediate.