[MDEV-20669] Increase concurrency/speed of AES_ENCRYPT Created: 2019-09-25  Updated: 2022-11-07

Status: Open
Project: MariaDB Server
Component/s: Server
Fix Version/s: None

Type: Task Priority: Major
Reporter: Kyle Joiner (Inactive) Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: None

Attachments: File MDEV-20669.svg    

 Description   

In testing with multiple tables in InnoDB performing a SELECT INTO OUTFILE on the following example table(s) with from a single client to multiple clients the throughput is reduced from 1.8M rows/sec to 85K rows/sec total with 5 tables:

CREATE TABLE my_test<NUM> ( -- <NUM> 01 to 10
 
myid1 BIGINT(20) NOT NULL,
 
myid2 BIGINT(20) NOT NULL,
 
myotherid TINYINT(4) NOT NULL,
 
myflag TINYINT(4) NOT NULL DEFAULT 0,
 
insert_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
 
update_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
 
PRIMARY KEY (myid1,myid2),
 
KEY ak_my_test1 (myid2)
 
) ENGINE=INNODB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
 
 
 
SELECT
 
AES_ENCRYPT(myid2,6954f9fbe878b7999720bc443e47e78d88164386ba6bf8c56e5c65a6e44eb1fb9f6053329981ba4f10a6db5d6ac41c9253c09711d2e7ac5aa1ee9a6eb9fd084e),
 
AES_ENCRYPT(myid2,6954f9fbe878b7999720bc443e47e78d88164386ba6bf8c56e5c65a6e44eb1fb9f6053329981ba4f10a6db5d6ac41c9253c09711d2e7ac5aa1ee9a6eb9fd084e),
 
myotherid,
 
myflag,
 
insert_date,
 
update_date
 
INTO OUTFILE '<Separate SSD from tables>'
 
FIELDS TERMINATED BY ','
 
ENCLOSED BY '\"'
 
ESCAPED BY '\\\\'
 
FROM my_test01 FORCE INDEX (PRIMARY);



 Comments   
Comment by Sergei Golubchik [ 2019-09-26 ]

I doubt it has something to do with AES_ENCRYPT. What happens if you replace it with, say, CONCAT ?

Comment by Kyle Joiner (Inactive) [ 2019-10-01 ]

Replacing with CONCAT was only a minor impact in comparison. When running with 30 threads dumping from 30 tables, it still dumped at roughly 13 million rows / second.

Comment by Daniel Black [ 2022-07-27 ]

Testing containers in their default configuration:

$ for a in m55 m104 m105 m106 m107 m108 m109 m1010-preview-misc my55 my56 my57 my80; 
do
 
   echo $a ;
 
   podman exec $a mysql -e "SET @n=NOW();SELECT BENCHMARK(100000000,AES_ENCRYPT('hello','goodbye')); select version(), timediff(now(), @n)";
 
done

Version Time (seconds) ssl library
mariadb-5.5.64 9 1.0.1f
mariadb-10.4.25 13 1.1.1f
mariadb-10.5.16 13 1.1.1f
mariadb-10.6.8 15 1.1.1f
mariadb-10.7.4 14 1.1.1f
mariadb-10.8.3 47 3.0.2
mariadb-10.9.1 48 3.0.2
mariadb-10.10-preview-misc 47 3.0.2
mariadb-10.10 (fc36) 45 3.0.5
mysql-5.5.62 9 staticly linked
mysql-5.6.51 23 staticly linked
mysql-5.7.39 15 1.0.2k
mysql-8.0.29 17 1.1.k
Comment by Daniel Black [ 2022-07-27 ]

Flamegraph of openssl-3.0.5 shows a high amount of time in openssl auxilary functions related to startup and shutdown.

Generated at Thu Feb 08 09:01:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.