2

I have a table like:

create table table_a
(
    id_a      mediumint unsigned   not null,
    id_b      tinyint unsigned     not null,
    id_c      char(10)             not null,
    value     tinyint unsigned     not null,
    TYPE      enum ('a', 'b', 'c') not null,
    DATE      date                 not null,
    constraint `unique`
        unique (id_b, id_c, id_a, TYPE, DATE),
    constraint fk_id_b_id_c
        foreign key (id_b, id_c) references table_b (id_b, id_c)
            on delete cascade,
    constraint id_a
        foreign key (id_a) references table_c (ID)
            on delete cascade
);

create index table_a_value_index
    on table_a (value);

create index table_a_new
    on table_a (id_b, id_c, DATE, id_a);

create index mks_date
    on table_a (DATE);

Now I would like to create partitions, but I am not sure how they will impact the RAM usage or the performance. I have tried the partitions like:

ALTER TABLE table_a
 PARTITION BY RANGE (to_days(`date`)) (
    PARTITION 2021_H1 VALUES LESS THAN (to_days('2021-07-01')),
    PARTITION 2021_H2 VALUES LESS THAN (to_days('2022-01-01')),
    PARTITION 2022_H1 VALUES LESS THAN (to_days('2022-07-01')),
    PARTITION current VALUES LESS THAN (MAXVALUE)
 ) ;

But that hadn't the expected RAM, performance improvement.

My research has shown that smaller partitions could be better, but you should not have more than 50 partitions. If I would partition after each month, I could store the last 4 years (when max. 50 partitions are recommended), which would be enough.

But how much would that impact my RAM usage and/or the performance?

As far as I understood, the partitions are treated as separated tables, does that mean, that each partition will have their own indexes? The table has a size of 20GB+, but the indexes are 40GB+. It would be beneficial to reduce the loaded index size.

The most used indexes are unique and table_a_new. The filter for date is a specific date or a range of 6 months.

It is fine, that I will lose my foreign keys.

5
  • Partitioning is not really meant to improve performance.
    – J.D.
    Commented Dec 5, 2022 at 13:19
  • @J.D. Ok, but if I would decrease the RAM usage through partitioning, wouldn't that improve the performance? If the RAM is completely occupied.
    – Zystrix
    Commented Dec 5, 2022 at 13:32
  • How much data do you have? How much RAM do you have? What kind of disk do you have? If your RAM isn't fully being used at all times, then it's just wasted hardware you paid for.
    – J.D.
    Commented Dec 5, 2022 at 13:34
  • @J.D. I set the innodb_buffer_pool_size to 96GiB and I have 128GB RAM. The size of the data is 170GB. The server has 2*1TB NVME SSDs (raid 1). The mysql data is on a LVM partition with 450G, which is used 57%.
    – Zystrix
    Commented Dec 5, 2022 at 13:54
  • Then the answer to your question "but if I would decrease the RAM usage through partitioning, wouldn't that improve the performance" is no. You already have plenty of RAM to store almost all of your data, and in the off-chance you need to load a small amount of data from Disk, you have an NVMe which is going to be very fast. There's probably no point in using partitioning here, as your hardware setup is quite optimal.
    – J.D.
    Commented Dec 5, 2022 at 13:56

1 Answer 1

0

Partitioning does not reduce RAM usage.

InnoDB tables and their indexes do not need to be fully loaded into RAM to be queried.

If your query only references part of the table, InnoDB loads the subset of pages from that table and its indexes into RAM. This is the same whether the table uses partitioning or not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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