0

I believe I've encountered a database scaling issue. I have a table with nearly a million rows in it now and our rails application appears to be hanging whenever we try to create a new instance. I notice this also happens when we destroy a record as well. However, find and update are extremely quick.

Here's some information on the table:

| Name                  | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment              |

| instances             | InnoDB |      10 | Compact    |  972380 |           1897 |  1845493760 |               0 |    152846336 |         0 |         922976 | 2009-03-04 18:34:07 | NULL                | NULL                | latin1_swedish_ci |     NULL |                | InnoDB free: 5120 kB |

And also:

+-------------------------+--------------+------+-----+---------+----------------+
| Field                   | Type         | Null | Key | Default | Extra          |
+-------------------------+--------------+------+-----+---------+----------------+
| id                      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| card_id                 | int(11)      | YES  | MUL | NULL    |                | 
| email_id                | int(11)      | YES  | MUL | NULL    |                | 
| name                    | varchar(255) | YES  |     | NULL    |                | 
| subject                 | varchar(255) | YES  |     | NULL    |                | 
| prototype               | text         | YES  |     | NULL    |                | 
| forwards_count          | int(11)      | YES  |     | 0       |                | 
| recipients_count        | int(11)      | YES  |     | 0       |                | 
| sent_at                 | datetime     | YES  | MUL | NULL    |                | 
| created_at              | datetime     | YES  |     | NULL    |                | 
| body                    | text         | YES  |     | NULL    |                | 
| updated_at              | datetime     | YES  |     | NULL    |                | 
| original_id             | int(11)      | YES  |     | NULL    |                | 
| saved                   | tinyint(1)   | YES  |     | 1       |                | 
| notify_on_receipt       | tinyint(1)   | YES  |     | 0       |                | 
| total_views             | int(11)      | YES  |     | 0       |                | 
| total_sends             | int(11)      | YES  |     | 0       |                | 
| public                  | tinyint(1)   | YES  | MUL | 0       |                | 
| approved                | tinyint(1)   | YES  | MUL | 0       |                | 
| sender_copy             | int(11)      | YES  |     | NULL    |                | 
| recipient_base64        | varchar(255) | YES  |     | NULL    |                | 
| user_generated_video_id | int(11)      | YES  |     | NULL    |                | 
| title                   | varchar(255) | YES  |     | NULL    |                | 
| position                | int(11)      | YES  |     | NULL    |                | 
| messages                | text         | YES  |     | NULL    |                | 
+-------------------------+--------------+------+-----+---------+----------------+

I wa wondering if there is anything I can try to look into to further diagnose the issue. Or configurations I should attempt to change as possible solutions. Essentially a find or update take around 8-12ms but a create or destroy statement will take over 10 seconds to complete.

2 Answers 2

1

This is likely due to indexes having to be updated when you create or delete a row. Perhaps you could look into %wa on the results of top during slow periods and see if it is the HDD that is slowing you down. Of course, it might also be due to poor optimization of the mysql config.

0

I guess the reason might be in keys you do not show here. Updating multiple keys can take some time and they don't have to be updated on UPDATE if don't change these columns as well on read only operations.

Your index is almost 10% of table size, what needs to be rewritten on each table update and it indeed takes some time.

So try reducing the keys to some reasonable amount.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .