Uploaded image for project: 'EJBCA'
  1. EJBCA
  2. ECA-4614

Document alternative MySQL CertificateData partitioning

    Details

    • Type: Task
    • Status: Open
    • Priority: Cosmetic
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Install-script
    • Labels:
      None
    • Issue discovered during:
      Customer

      Description

      Add the following partitioning outline to doc/sql-scripts/optimize-ejbca-mysql.sql :

      – Alternative partitioning based on yearly grouping of expireDate and issuing CA
      – We would like to do the next line, but it is not possible:
      – ALTER TABLE CertificateData PARTITION BY LINEAR KEY (issuerDN) PARTITIONS 20 SUBPARTITION BY HASH ((YEAR(FROM_UNIXTIME(expireDate/1000)))) SUBPARTITIONS 10;
      --ALTER TABLE CertificateData REMOVE PARTITIONING;
      --ALTER TABLE CertificateData ADD PRIMARY KEY(fingerprint);
      --ALTER TABLE CertificateData DROP PRIMARY KEY;
      --ALTER TABLE CertificateData DROP INDEX certificatedata_idx12;
      --ALTER TABLE CertificateData ADD INDEX certificatedata_idx12 (serialNumber, issuerDN);
      --ALTER TABLE CertificateData DROP INDEX certificatedata_idx13;
      --ALTER TABLE CertificateData ADD INDEX certificatedata_idx13 (fingerprint) USING HASH;
      --ALTER TABLE CertificateData PARTITION BY RANGE( expireDate ) SUBPARTITION BY KEY( issuerDN ) SUBPARTITIONS 10 (
      – PARTITION pExp2015 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')*1000),
      – PARTITION pExp2016 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01 00:00:00')*1000),
      – PARTITION pExp2017 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01 00:00:00')*1000),
      – PARTITION pExp2018 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-01 00:00:00')*1000),
      – PARTITION pExp2019 VALUES LESS THAN (UNIX_TIMESTAMP('2020-01-01 00:00:00')*1000),
      – PARTITION pExp2020 VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01 00:00:00')*1000),
      – PARTITION pExp2021 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01 00:00:00')*1000),
      – PARTITION pExp2022 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01 00:00:00')*1000),
      – PARTITION pExp2023 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01 00:00:00')*1000),
      – PARTITION pExp2024 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01 00:00:00')*1000),
      – PARTITION pExp2025 VALUES LESS THAN (UNIX_TIMESTAMP('2026-01-01 00:00:00')*1000),
      – PARTITION pExp2026 VALUES LESS THAN (UNIX_TIMESTAMP('2027-01-01 00:00:00')*1000),
      – PARTITION pExp2027 VALUES LESS THAN (UNIX_TIMESTAMP('2028-01-01 00:00:00')*1000),
      – PARTITION pExp2028 VALUES LESS THAN (UNIX_TIMESTAMP('2029-01-01 00:00:00')*1000),
      – PARTITION pExp2029 VALUES LESS THAN (UNIX_TIMESTAMP('2030-01-01 00:00:00')*1000),
      – PARTITION pExp2030 VALUES LESS THAN (UNIX_TIMESTAMP('2031-01-01 00:00:00')*1000),
      – PARTITION pExp2031 VALUES LESS THAN (UNIX_TIMESTAMP('2032-01-01 00:00:00')*1000),
      – PARTITION pExp2032 VALUES LESS THAN (UNIX_TIMESTAMP('2033-01-01 00:00:00')*1000),
      – PARTITION pExp2033 VALUES LESS THAN (UNIX_TIMESTAMP('2034-01-01 00:00:00')*1000),
      – PARTITION pExp2034 VALUES LESS THAN (UNIX_TIMESTAMP('2035-01-01 00:00:00')*1000),
      – PARTITION pExp2035 VALUES LESS THAN (UNIX_TIMESTAMP('2036-01-01 00:00:00')*1000),
      – PARTITION pExp2036 VALUES LESS THAN (UNIX_TIMESTAMP('2037-01-01 00:00:00')*1000),
      – PARTITION pExpLater VALUES LESS THAN (MAXVALUE)
      --);

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            johan Johan Eklund
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:

                Time Tracking

                Estimated:
                Original Estimate - 30 minutes
                30m
                Remaining:
                Remaining Estimate - 30 minutes
                30m
                Logged:
                Time Spent - Not Specified
                Not Specified