You have not said that performance is the potential problem, but I am guessing that it will be. There are several red flags:
* big PRIMARY KEY
* lots of big secondary keys
* primary key not chronologically ordered, yet actions tend to be
Using a GUID (UUID) as the PK in InnoDB on a large table will lead to lots of disk hits, hence poor performance. This is whether you partition it or not.
It sounds like the archiving depends on something too complicated be the 'partition key'.
Using a GUID as a PK, plus having several secondary keys, means a lot of space is taken for the indexing. Changing the PK to a new field, INT UNSIGNED AUTO_INCREMENT, would probably decrease the size of the indexes. It could also somewhat diminish the disk hits if the rows were inserted in roughly chronological order. As a retrofit:
ALTER TABLE PI
ADD COLUMN new_id INT NOT NULL AUTO_INCREMENT
DROP PRIMARY KEY,
ADD PRIMARY KEY(new_id),
ADD INDEX(ID)
ORDER BY START_TIME;
With that, the rows to archive will tend to be clumped at one end of the table; rows inserted will be entirely at the other end of the table, and each secondary index will shrink by 32 (36-4) bytes per row.
GUID/UUID should not be utf8. They are restricted to hex, correct? So ascii would be better. Or, if you are willing to be consistent about case folding, BINARY(36).
innodb_buffer_pool_size should be 70% of available ram. That is the one tunable that can make a significant performance diff.
Do you usually reach into the table via `ORGANIZATION`? Do you look for only one ORGANIZATION in a query (but possibly multiple rows)? Then it could be beneficial to "normalize" that field out. That is, create another table with a mapping between an INT and ORGANIZATION, then change PI to have that INT. This will significantly shrink the indexes, without changing performance noticeably.
Current, each insert a _random_ hit on the table (where the PK is) and on the P_ID index. Assuming you have a limited number of ORGANIZATIONs, those hits will be less random. Each "random" hit is a disk hit when the table is big enough (that is, much bigger than the buffer pool). You can do only about 100 disk hits per second on a typical system. (RAID helps some; SSDs help a bunch.)
OTOH, if you are creating GUIDs at remote sites and don't have the opportunity of getting a AUTO_INCREMENT value, you are stuck with the current, inefficient, design.