Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.