I'm using mariadb 10.2, and I had a table with a structure similar to the one below, currently with 15gb, where I insert a few thousand records per minute all the time, each batch for a specific account (AccountId1).
AccountId1 bigint PK, AccountId2 bigint PK, ActionType int, ActionDate timestamp
Looking at the process list, multiple inserts were waiting in line for this table, and they were always one insert running at a time, so I decided to partition this table by hash on the AccountId1, and created 32 partitions.
The inserts now jumped from 1 or 2 a second to 8 during peak times, and that worked great. The issue is that the CPU usage jumped from 15% to 40% after the change, and even stopping the insert process completely for a few hours, the CPU won't go down.
There are a few places where we select from this table that could span all partitions, but these are processes that run every few hours and don't take that long. Other frequent queries always use the
AccountId1 field and are indeed querying a single partition by looking at the query plan.
Is there any overhead associated with partitioning, even if no sql is touching that table? What could be the reason for this increase in CPU?
Note: there are around 2000 concurrent connections at any time, but the # of connections before and after the partitioning were the same.