2

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.

cpu usage before and after partitioning

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.

  • We really need to see SHOW CREATE TABLE to address the CPU issue. Are the INSERTs done one row at a time? Or batched in some way? – Rick James Mar 15 '18 at 19:06
  • And, is each connection for only one AccountID1? – Rick James Mar 15 '18 at 19:07
2

OVERHEAD

What needs to be understood is a partitioned table's physical implementation.

For example, in my old post (Aug 31, 2014 : How does subpartitioning actually work and what physical files are created?), I displayed what a partitioned table looks like:

C:\>cd \MySQL_5.6.15\data\test

C:\MySQL_5.6.15\data\test>dir nums_comp*
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\MySQL_5.6.15\data\test

08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp1.ibd
08/31/2014  04:09 PM             8,594 nums_composite.frm
08/31/2014  04:09 PM                96 nums_composite.par
               8 File(s)        598,514 bytes
               0 Dir(s)  686,691,409,920 bytes free

C:\MySQL_5.6.15\data\test>

I have another example in Feb 16, 2015 : MySQL Create Table with Partition Slow on server machine

Each partition is treated as a separate InnoDB table with a distinct tablespace.

What overhead exists ?

While these things help explain the overhead, you must still be wondering where the CPU activity is coming from ???

CPU

There will still be some CPU activity based on the open file handles. How ?

The option innodb_open_files places a limit on the number of open file handles against all InnoDB tables through the MySQL Instance.

If table info needs to be examined by monitoring software, more queries make more CPU.

If there are any queries that do not access disk (such as SHOW PROCESSLIST; or SHOW GLOBAL STATUS;) or reads from INFORMATION_SCHEMA, more CPU. (See my old ServerFault post from 2011-08-10 1 billion mysql queries in 24 days? Can something be wrong?)

Opening and closing files handles to access certain tables and read 16K pages into the InnoDB Buffer or marking pages in the InnoDB Buffer Pool as old.

  • Thanks. I'll take a look at these points and see what I can do. – Natan Mar 13 '18 at 21:44
  • After looking into the variables and doing some math, I still though there was no reason for such a high increase. So I decided to reboot the instance, and things went back to normal. It may be a bug, I don't know, but looks like something is not cleaning up something inside mariadb. – Natan Mar 16 '18 at 8:23
  • Additional information request, please. RAM on your Host server current complete my.cnf-ini Text results of: A) SHOW GLOBAL STATUS; B) SHOW GLOBAL VARIABLES; C) SHOW ENGINE INNODB STATUS; Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, free -m for a linux/unix free memory report, complete MySQLTuner.com report if readily available. – Wilson Hauck Apr 2 '18 at 22:25
  • @natan Please consider posting the information requested Apr 2 at 22:25. Thanks – Wilson Hauck Apr 9 '18 at 15:11
  • I appreciate the attempt to find the root cause, but we've already upgraded the instance, so the current values won't match what we had before. and I don't have access to the OS to get some of the requested stats. All I can say it was a 48 CPU / 192Gb RAM RDS instance on AWS managed by them. There were 15Gb of free RAM available at the time, and 3Tb of disk available. – Natan Apr 11 '18 at 16:00

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.