3

if i have a table with, lets say 500 million rows, and among others there are two main individual indexes on that table. the table looks like:

CREATE TABLE `t1` (
    id_1 bigint unsigned not null,
    id_2 bigint unsigned not null,
    col3, col4 ... colN ...
    KEY `index1` (`id_1`),
    KEY `index2` (`id_2`),
    ...
) ENGINE=InnoDB;

100% of the queries on the table are using WHERE clause on index1 OR index2.

Taking this into account, and assuming it is now very reasonable time to partition that table, into 100 partitions, I would like to ask your help to understand these issues:

  1. Is it possible to create a partition expression in such a way that after the partitioning, for any query that using one of these indexes:

    SELECT * FROM `t1` WHERE id_1 = 123;
    -- or
    SELECT * FROM `t1` WHERE id_2 = 456;
    

    the engine will scan one partition only?

  2. issue 1 must happen without changing the indexes. I mean, without making back references in them, because it will be too much RAM consuming. So, this must not happen:

    KEY `index1` (`id_1`,`id_2`),
    KEY `index1` (`id_2`,`id_1`),
    
  3. Am I thinking in a wrong direction? How would you solve this issue with Amazon RDS 7.5 GB RAM instance ?

feel free to edit this question, to make it more possible to be answered, thanks

reference to stackoverflow.com

6
  • What is the primary key of the table? – ypercubeᵀᴹ Aug 12 '14 at 7:47
  • Consider also using TokuDB engine that offers multiple clustered indexes. Your case seems ideal for that engine. You could have 2 clustered indexes on the table, one using id1 and the other id2. – ypercubeᵀᴹ Aug 12 '14 at 7:51
  • @ypercube primary key is auto_increment id, but i can change it. both of these two ids are unique but i am not using uniqueness on them currently – arty Aug 12 '14 at 7:51
  • @ypercube I don't familiar with TokuDB, I will read about it later, thanks. But currently it must be amazon RDS only. any other suggestions? – arty Aug 12 '14 at 7:53
  • Both answers below are good, but let's be clear: the answer is no, you cannot partition a single table by two different keys. Can I separate my jellybeans by color and also by size? No -- at best you can do subpartitioning for the second condition. But that won't allow you to search only one partition when you search for jellybeans by size. – Bill Karwin Aug 15 '14 at 16:16
1
+50

I had written an interesting alternative of implementing your own manual hash indexes for your table, and then I made maths and realised your constraints:

Having into memory 3 bigints will cost you 500*10^6*(8*8*8)/(1024*1024*1024) = 11.17GB that you do not have. RDS is simply not adequate for you anymore, as it is not flexible enough to try some alternative engines -other than InnoDB (you need an engine that works well with indexes on disk/clustering on several keys/hash indexes)- and probably too costly to handle a large table like that.

You need either a higher-end instance or migrate to EC2 to deploy an alternative engine.

Best recommendation that I could give you for your current constraints (7GB ram, InnoDB):

Use your most frequently accessed keys as your primary key, partition by RANGE on that (lets call it id_1). Do not create any other secondary keys:

CREATE TABLE `t1` (
    id_1 bigint unsigned PRIMARY KEY,
    id_2 bigint unsigned not null,
    col3, col4 ... colN ...
) ENGINE=InnoDB
PARTITION BY RANGE (id_1) (
    PARTITION p0 VALUES LESS THAN (n),
    PARTITION p1 VALUES LESS THAN (m),
    ...
);

Create a separate table with (id_2, id_1):

CREATE TABLE `t1_id2_index` (
    id_2 bigint unsigned PRIMARY KEY,
    id_1 bigint unsigned not null,
) ENGINE=InnoDB
PARTITION BY RANGE (id_2) (
    PARTITION p0 VALUES LESS THAN (n),
    PARTITION p1 VALUES LESS THAN (m),
    ...
);

Obviously you will have to insert on this second table each time you insert on the first one. You may think this is worse, but it will not be that bad as you are getting rid of huge merging processes of the secondary keys and minimising memory usage (which is your goal, afterwards).

This will only access 1 partition on access by id_1 and 2 partitions (one on each separate table) on access through id_2:

SELECT * FROM `t1` WHERE id_1 = 123;
-- or
SELECT STRAIGHT_JOIN t1.* 
FROM `t1_id2_index` 
JOIN `t1`
ON t1_id2_index.id_2 = 456 
   and 
   t1.id_1 = t2.id_1;

If your most frequent accesses are on the latest partitions, you will get the desired improvements -make sure you partition with that in mind. You can check partition pruning by using EXPLAIN PARTITIONS. Of course, if access patterns are completely random, you will not get any advantage. The goal is to maintain everything on disk except for a small set of primary keys for both id_1 and id_2 and selected rows.

You may want to minimise read ahead caching and tune innodb_old_blocks_pct and innodb_old_blocks_time for more effective caching/eviction on the buffer pool. I hope you are also using SSDs.

This is not beautiful, but please refer to my initial suggestion of migrating away from SAAS for custom requirements.

1
  • thanks for the help! I will accept this answer because of given arguments related to AWS and providing a possible new tables structure including PARTITION BY statements – arty Aug 16 '14 at 22:45
0

MySQL partitioning is handled as follows:

  • MyISAM : Two file handles per partition (.MYD,.MYI)
  • InnoDB : One file handle per partition (.ibd)
  • Subpartitions simply create separate files, possibly multiplying the partitions

What you are trying to achieve can only be accomplished with a simply change

Create a column called part which forms a partition

CREATE TABLE `t1` (
    id_1 bigint unsigned not null,
    id_2 bigint unsigned not null,
    col3, col4 ... colN ...
    part int unsigned not null,
    KEY `index1` (`id_1`),
    KEY `index2` (`id_2`),
    KEY `index3` (part,`id_1`),
    KEY `index4` (part,`id_2`),
    ...
) ENGINE=InnoDB;

If you know all the keys that belong to a partition, just INSERT them.

INSERT INTO t1 (part,...) VALUES (127,...)

Also note that I made indexed to help the Query Optimizer choose the partition. Thus, your queries may look something like

SELECT * FROM t1 WHERE part = 127;

You can isolate 123 with

SELECT * FROM t1 WHERE part = 127 and id_1 = 123;

I have discussed this use of indexes on partitions before : MySQL Read Speed and Partitioning on Separate Drives

Without the compound indexes having part key, this would make mysqld scan all partitions.

If you are willing, you have a lot of work ahead of you to reorganize the table.

2
  • hi. May i ask from you to add the 'partition by' expression in the create statement in the answer? Thanks for taking the time to help! – arty Aug 14 '14 at 21:58
  • What is the part field? How should i calculate it? Based on what? – arty Aug 14 '14 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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