AWS documentation also says horizontal physical partitioning of large table data is best practices instead keeping all in one physical file.
There are advantages and disadvantages to using InnoDB file-per-table tablespaces, depending on your application. To determine the best approach for your application, go to InnoDB File-Per-Table Mode in the MySQL documentation. We don't recommend allowing tables to grow to the maximum file size. In general, a better practice is to partition data into smaller tables, which can improve performance and recovery times.
One option that you can use for breaking a large table up into smaller tables is partitioning. Partitioning distributes portions of your large table into separate files based on rules that you specify. For example, if you store transactions by date, you can create partitioning rules that distribute older transactions into separate files using partitioning. Then periodically, you can archive the historical transaction data that doesn't need to be readily available to your application.
There are advantages and disadvantages to using InnoDB file-per-table tablespaces, depending on your application. To determine the best approach for your application, go to InnoDB File-Per-Table Mode in the MySQL documentation. We don't recommend allowing tables to grow to the maximum file size. In general, a better practice is to partition data into smaller tables, which can improve performance and recovery times.
One option that you can use for breaking a large table up into smaller tables is partitioning. Partitioning distributes portions of your large table into separate files based on rules that you specify. For example, if you store transactions by date, you can create partitioning rules that distribute older transactions into separate files using partitioning. Then periodically, you can archive the historical transaction data that doesn't need to be readily available to your application.
CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
;
ALTER TABLE trb3 PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
);
SELECT table_name, partition_name,table_rows,data_length
FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='trb3' AND
TABLE_SCHEMA='sumandb';
+------------+----------------+------------+-------------+
| table_name | partition_name | table_rows | data_length |
+------------+----------------+------------+-------------+
| trb3 | p0 | 0 | 16384 |
| trb3 | p1 | 0 | 16384 |
| trb3 | p2 | 0 | 16384 |
| trb3 | p3 | 0 | 16384 |
+------------+----------------+------------+-------------+
4 rows in set (0.19 sec)
insert into trb3 values (1,'sdfsdfs','1989-06-06');
insert into trb3 values (2,'sdfsdfs','1989-06-06');
insert into trb3 values (3,'sdfsdfs','1990-06-06');
insert into trb3 values (4,'sdfsdfs','1990-06-06');
insert into trb3 values (5,'sdfsdfs','1990-06-06');
insert into trb3 values (6,'sdfsdfs','1997-06-06');
insert into trb3 values (7,'sdfsdfs','1997-06-06');
MySQL [sumandb]> select * from trb3;
+------+---------+------------+
| id | name | purchased
|
+------+---------+------------+
| 1 | sdfsdfs |
1989-06-06 |
| 2 | sdfsdfs |
1989-06-06 |
| 3 | sdfsdfs |
1990-06-06 |
| 4 | sdfsdfs |
1990-06-06 |
| 5 | sdfsdfs |
1990-06-06 |
| 6 | sdfsdfs |
1997-06-06 |
| 7 | sdfsdfs |
1997-06-06 |
+------+---------+------------+
7 rows in set (0.19 sec)
MySQL [sumandb]> SELECT table_name,
partition_name,table_rows,data_length FROM INFORMATION_SCHEMA.PARTITIONS WHERE
TABLE_NAME='trb3' AND TABLE_SCHEMA='sumandb';
+------------+----------------+------------+-------------+
| table_name | partition_name | table_rows | data_length |
+------------+----------------+------------+-------------+
| trb3 | p0 | 2 | 16384 |
| trb3 | p1 | 3 | 16384 |
| trb3 | p2 | 2 | 16384 |
| trb3 | p3 | 0 | 16384 |
+------------+----------------+------------+-------------+
4 rows in set (0.19 sec)
Thanks for reading
I really enjoyed while reading your article and it is good to know the latest updates. Do post more.
ReplyDeleteAmazon web services Training in Chennai
AWS Certification in Chennai
DevOps course in Chennai
Best devOps Training in Chennai
Data Analytics Courses in Chennai
Big Data Analytics Courses in Chennai
AWS Training in Anna Nagar
AWS Training in T Nagar
ReplyDeleteI preview this blog. Most of the points are very interesting to read. its help me to study also. Thanks for your help.
core java training in chennai
core java classes
core javaTraining in Tambaram
clinical sas training in chennai
Spring Training in Chennai
QTP Training in Chennai
Manual Testing Training in Chennai