CODEWITHSUNDEEP
mysqlquery-optimizationamazon-aurora
Sudarshan kumar
Sudarshan kumar

Reputation: 1585

MySQL Operation overhead and dealing with large table

This is my current table DDL. Its a big table 7TB where 6 TB is data and 1TB is index.

The number of records in this table is 3 billion.

There are currently 10 million inserts on table and this is append only table no update or deletes.

From the application we do 50 million selects per day.

CREATE TABLE `app_uses` (
`ID` varchar(36) NOT NULL,
`ACTION` varchar(255) NOT NULL,
`EVENT` varchar(255) NOT NULL,
`CUST_ID` varchar(36) DEFAULT NULL,
`VALUE` longtext,
`OBJECT` varchar(255) NOT NULL,
`DATE_TIME` datetime(6) DEFAULT NULL,
`GROUP` varchar(36) DEFAULT NULL,
`DISPLAY_NAME` varchar(2001) DEFAULT NULL,
`OBJ_ID` varchar(36) DEFAULT NULL,
`USER_DISPLAY` varchar(1500) DEFAULT NULL,
`USER_ID` varchar(36) DEFAULT NULL,
`NOTICE` varchar(1000) DEFAULT NULL,
`ALERT` varchar(4000) DEFAULT NULL,

PRIMARY KEY (`ID`),
KEY `IDX_APP_USES_CID_OT` (`CUST_ID`,`OBJECT`) USING BTREE,
KEY `IDX_APP_USES_OBJ_ID` (`OBJ_ID`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

App performance if fine and there is no issue except one operational issue. we are not able to perform alter table (adding new null able column) and we have to use percona tool that take more than 1 week.

Also VALUE and ALERT column which stores XML file has 3 TB storage contribution.

Now discussion is if we can store this two columns to another table and by doing that the original table will be 3 TB lighter but still we will not be able to perform Alter table very fast.

So the question is we should do this split?

The migration and breaking this table is going to take very long time and very difficult so before attempting this we are thinning if this worth this much effort.

we are using Aurora parallel query so we can not partition this table because Aurora parallel query does not work on partitioned table

We will also have to make changes in our application to accommodate broken table change.

Is there any benefit we if keep all data in same table?

Upvotes: 0

Views: 1043

Answers (2)

Rick James
Rick James

Reputation: 142296

UUID inefficiency

You are about to encounter the performance disaster that comes when mixing UUIDs with huge tables.

Even a simple that fetches one row by the primary key will usually involve a disk hit. This adds up to a lot of I/O -- probably too much to tolerate.

50M selects/day = 600/second. Can your disk system handle nearly 600 reads/second?

Off-record

Another issue -- There are a lot of 'large' columns. And, with an average of 2KB per row, there are probably many rows that use "off-record" storage. This involves another disk hit. What is the ROW_FORMAT?

A partial mitigation of this problem is to be sure to avoid SELECT * and specify only the columns that you actually need. This may avoid some of the extra disk hits.

This suggestion applies especially to VALUE and ALERT.

Lazy evaluate

If the selects have filtering (WHERE, LIMIT, etc) that is not adequately indexed, you may be fetching those bulky columns, only to discard them when filtering.

It is sometimes possible to avoid extra I/O by building a derived table that does the filtering, leaving the outer SELECT to fetch only the minimal number of columns. (Show us a non-trivial query you are using; we can discuss further.)

Shrink UUIDs

What type UUIDs are you using? If it is "Type 1", like MySQL uses, the bits of the id can be rearranged to make them roughly chronological; this helps dramatically with certain queries.

89f7eecd-a2ac-11eb-a9c1-5c80b6213dd8
              ^   This digit is the "type"

Details: http://mysql.rjweb.org/doc.php/uuid

Even without that, the 36-byte UUIDs can easily be shrunk to 16 bytes for putting in a BINARY(16) instead of 38 bytes for what you have (36 for the string, 2 for the unnecessary VAR.)

The above link discusses this shrinkage. Also, 8.0 has the necessary functions built in.

MariaDB 10.7 "will have" a UUID datatype that obviates most of what is discussed in this Answer.

Compress

(I am not in favor of InnoDB's compression, so I won't mention it. Anyway, it is not likely to provide more than 2x compression.)

If you compress those XML strings, they will shrink by a factor of (about) 3. That would save about 2TB.

But do the compression (and uncompression) in the client; this offloads the server and decreases the bandwidth between client and server.

XML is a bulky way to represent data, but it may be non-trivial to reformat it. (Hence, I only mention compression.)

Note, after compression, the column should be VARBINARY(...) or ...BLOB, not a text type.

Ditto for other columns that are "text" and typically "big".

Split table

I do not forsee any significant benefit or drawbacks from splitting off the two bulky columns. The fact that they are "off-record" means that InnoDB is already providing most of the benefit that you propose. (MyISAM would benefit from your split. But don't use that engine.)

This is some benefit from splitting the table, but possibly not enough benefit (in your case) to warrant the change. This is especially the case if the new table also had a uuid as the PK, even if it is the same as the current ID.

10M Inserts

10 million 1-row INSERT statements? That would be something like 30M disk hits with your current design. How many writes per second can your disk support?

I would suggest things like "batching" the inserts. A single INSERT with 100 rows normally runs 10 times as fast as 100 single-row inserts. But there are 3 random hits -- One for the PK, one for each secondary index. So, I don't know if this 10x speedup will really happen in your case.

RAM

The more RAM the better. With, say, 7TB of RAM (25TB in the future), much of what I have said goes away. But that is not practical today. Hence, I am pushing for shrinking the table size, avoiding UUIDs or making them chronological (if useful), etc.

If 5% of a uuid-based index can fit in the buffer_pool, then 95% of selects will need to hit the disk. This is the principle behind much of my discussion.

Note: the PK is an index, but includes all the data.

Note: A lookup via a secondary index involves two BTree lookups. If each is based on a uuid, then there is a good chance of two disk hits.

Note: Your two-table approach would involve 2 lookups. Each one might be less than the "95%" above, but still.

Partitioning and parallel query

Aurora is ahead of MySQL (and MariaDB) in this area. But still, there is not much benefit to be had.

The effort to each into a different partition eliminates the benefit of a shallower BTree. (It may even slow things down.)

Parallel query helps if you are CPU-bound. But I predict that you are I/O-bound and will meltdown at 25TB. Each of the parallel queries will spend most of their time waiting for a block to be read from disk.

I assume the 50M Selects/day are coming from separate connections? And lots of them are happening "at the same time"? That gives you "parallel" execution of queries. I think Aurora's "parallel query" is aimed at a single, complex, SELECT that can benefit from multiple threads doing parts of the task simultaneously.

A way that PARTITIONing can benefit is when you need a 2-dimensional index. For example: WHERE some date range AND some other test. By partitioning on date while having the PRIMARY KEY helping with the 'other test', "partition pruning" picks which partition to look in, then the PK more quickly reaches for the rows desired. (This does not seem to be your use case.)

Your main queries would not benefit from any form of PARTITIONing. So, I recommend against partitioning.

Indexes

Given that nearly all the SELECTs are these two:

select * from app_uses where ID='5labcvnaxvb11egw4w0or0wq4';
SELECT * FROM app_uses where OBJ_ID = '5ldfjkhgdfkjhg631exlwu9tkrsmv'
     ORDER BY DATE_TIME DESC;

These are optimal:

PRIMARY KEY(ID) -- as you have

INDEX(OBJ_ID, DATE_TIME) -- replace key(obj_id) with this

The suggested change in the second case avoids the need to sort the result since the desired rows can simply be fetched in the desired order. (This is not likely to make much performance difference unless it is a lot of rows. The UUID issues dominate performance issues.)

Upvotes: 4

O. Jones
O. Jones

Reputation: 108686

Your table is now too big to reorganize without, as you say, days of downtime. So add another table.

To add attributes (columns) to an entity (table) like this without totally reorganizing the table, make another table, a key-value table. It should have the same PK as this table, and it will hold the new data.

CREATE TABLE app_uses_attributes (
  ID varchar(36) NOT NULL,
  NAME varchar(255) NOT NULL,
  VALUE varchar(4000),
  PRIMARY KEY (ID, NAME),
  INDEX app_uses_attributes_byvalue (NAME, VALUE(400)),  
  INDEX app_uses_attributes_byid (NAME, ID, VALUE(400))  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Now, instead of inserting your new attribute data into your existing, monster, table, you can insert it into this new table.

Suppose you have two new attributes. Let's call them SENDER and RECIPIENT for example. You can insert them like this, when you know the ID value.

INSERT INTO app_uses_attributes (ID, NAME, VALUE) VALUES(?, 'SENDER', 'OJones')
INSERT INTO app_uses_attributes (ID, NAME, VALUE) VALUES(?, 'RECIPIENT', 'SUDARSHAN')

Then when you want to retrieve them use some LEFT JOINs.

SELECT app_uses.*, sender.VALUE SENDER, recip.VALUE RECIPIENT
  FROM app_uses
  LEFT JOIN app_uses_attributes sender 
           ON app_uses.ID = sender.ID AND sender.NAME = 'SENDER'
  LEFT JOIN app_uses_attributes recip ON app_uses.ID = recip.ID
           ON app_uses.ID = sender.ID AND sender.NAME = 'RECIPIENT'
 WHERE whatever filters you need

To search you can do stuff like this. It will work efficiently due to the (NAME, VALUE(400)) index on the new table.

SELECT app_uses.*, recip.VALUE RECIPIENT
  FROM app_uses
  LEFT JOIN app_uses_attributes recip ON app_uses.ID = recip.ID
           ON app_uses.ID = recip.ID AND recip.NAME = 'RECIPIENT'
 WHERE recip.VALUE LIKE 'SUDAR%'

This key-value setup is a good idea for adding attributes on the entities in vast tables like yours.

  1. You can add attributes without reorganizing the main table.
  2. You can add new attributes whenever you wish. Simply use a new NAME for each new one. You will not need to ALTER this table to add new attributes in future.
  3. You have no data for the new attributes for your old rows. It costs you nothing in space. Nor does it cost you lots of time to set this up. An absent row from the new table simply means NULL data (that's what LEFT JOIN does for you).
  4. Your existing software need not change. Only software that uses new attributes is affected.

The queries are certainly more complex, but you can create views to encapsulate them.

This approach is well-known to be common and robust: WordPress uses it.

Upvotes: 0

Related Questions