Java – manage MySQL partitions using hibernate

We are currently evaluating the use of MySQL partitions for our small applications The application is basically at the end of the message queue and uses hibernate to record our API requests (including timestamp) to the database Unfortunately, we received a lot of requests and querying the database became very slow

What we want to do is partition the table by timestamp (monthly), because our general query mode is similar to "get some requests between times a and B" If a and B continue for two months, most of this is true, then this will only reach two partitions

Since the range partition of MySQL must be created manually, I want to add this maintenance task to our Java application, which can be completed automatically The idea is this:

>There is a utility thread running regularly (using scheduledexecutorservice or something) > in the thread, check whether there is a partition next month > if not, create it

All this is good, but I have to try to use hibernate to get the partition information of MySQL and create a partition What is the best way (if this will be MySQL specific, I'm fine)?

>Is there a specific API in hibernate to obtain the MySQL partition information of the table and create partitions? > Should I use the original SQL (show create table..., alter table... Add partition) and parse the output myself?

Edit:

The table looks like this (I deleted some columns unrelated to the problem):

CREATE TABLE `request` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,`apikey` varchar(32) NOT NULL,`timestamp` datetime NOT NULL,`rows` int(11) DEFAULT NULL,`user_id` varchar(15) DEFAULT NULL
  PRIMARY KEY (`id`),KEY `apikey_idx` (`apikey`),KEY `timestamp_idx` (`timestamp`),KEY `apikey_timestamp_rows_idx` (`apikey`,`timestamp`,`rows`)
) ENGINE=InnoDB AUTO_INCREMENT=2190385211 DEFAULT CHARSET=utf8

Slow queries (apparently generated by doctrine):

SELECT 
  r0_.user_id AS user_id0,COUNT(r0_.id) AS sclr1
FROM
  request r0_
WHERE
  r0_.apikey = 'XXX' AND r0_.rows > 0 AND r0_.timestamp >= '2015-09-15 00:00:00' AND r0_.timestamp < '2015-10-15 00:00:00'
GROUP BY r0_.user_id
HAVING sclr1 > 0
ORDER BY sclr1 DESC
LIMIT 500

When parsing the query mysql, it says it is using apikey_ timestamp_ rows_ IDX index

A small context: for a given API key, we want to know how many rows of requests > 0.0 sent by each user in a given time period

The table currently has about 2.2 billion rows

Solution

I don't know any hibernate API that handles table partitioning

I think you have no choice but to use native SQL You can use SQL in Java code (I think you recommend it) or put it in stored procedures

You can schedule this operation using Java or mysql If you use a thread in the application server to perform this operation, you will encounter the problem that each application server has such a scheduled job This makes it difficult to control how often jobs are actually executed In this case, this may not be a big problem because the partition related queries are not very heavy

You can also schedule it in MySQL (see how to schedule a MySQL query?) This option provides more visibility to jobs (for example, to your DBA) and is easier to manage and monitor