impressionist
impressionist copied to clipboard
charlotte-ruby
Index data size for impressions table
A couple of months ago the project I am working on required a way to track views on some of the product pages and integrate those directly in some of those. Impressionist came our way and we thought that it would be simple enough to plug in so we did.
Around that time our traffic started to go up and we doubled our user base in a couple of months. So a constant increase in our DB size was expected. Yet lately we have been worrying as the growth seemed a bit odd ... on the indexes. A lot of writes happened there. A lot.
So we checked the status of the tables and here is the line for the impressions 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 |
+--------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| impressions | InnoDB | 10 | Compact | 7835332 | 330 | 2586836992 | 0 | 6377897984 | 5242880 | 22031446 | 2014-09-02 22:52:22 | NULL | NULL | utf8_general_ci | NULL | | |
Size are in bytes if I am not mistaking, so yes 6377897984 -> 5.9GB for about 2.4GB of data. There is obviously a couple of indexes needed for the requests to be fast etc .. but that difference seems a bit big. And there is not much rows either, 7M is not very big. Yet that explains the growth and the increased write rate.
Is that a known behaviour or just an issue we might have triggered ourselves by adding some indexes of our own ?
mysql> SHOW INDEX IN impressions;
+-------------+------------+-----------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-----------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| impressions | 0 | PRIMARY | 1 | id | A | 7835996 | NULL | NULL | | BTREE | | |
| impressions | 1 | impressionable_type_message_index | 1 | impressionable_type | A | 656 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | impressionable_type_message_index | 2 | message | A | 656 | 255 | NULL | YES | BTREE | | |
| impressions | 1 | impressionable_type_message_index | 3 | impressionable_id | A | 1567199 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | poly_request_index | 1 | impressionable_type | A | 250 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | poly_request_index | 2 | impressionable_id | A | 1305999 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | poly_request_index | 3 | request_hash | A | 7835996 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | poly_ip_index | 1 | impressionable_type | A | 2 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | poly_ip_index | 2 | impressionable_id | A | 1958999 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | poly_ip_index | 3 | ip_address | A | 7835996 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | poly_session_index | 1 | impressionable_type | A | 350 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | poly_session_index | 2 | impressionable_id | A | 1305999 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | poly_session_index | 3 | session_hash | A | 7835996 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | controlleraction_request_index | 1 | controller_name | A | 2 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | controlleraction_request_index | 2 | action_name | A | 252 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | controlleraction_request_index | 3 | request_hash | A | 7835996 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | controlleraction_ip_index | 1 | controller_name | A | 2 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | controlleraction_ip_index | 2 | action_name | A | 486 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | controlleraction_ip_index | 3 | ip_address | A | 2611998 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | controlleraction_session_index | 1 | controller_name | A | 2 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | controlleraction_session_index | 2 | action_name | A | 388 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | controlleraction_session_index | 3 | session_hash | A | 7835996 | NULL | NULL | YES | BTREE | | |
| impressions | 1 | index_impressions_on_user_id | 1 | user_id | A | 412420 | NULL | NULL | YES | BTREE | | |
+-------------+------------+-----------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
23 rows in set (0.00 sec)
