Postgresql – Good encryption performance

encryptionperformanceperformance-tuningpostgresql

I'm converting a sql server database to postgresql 9.4. I'm stuck on something that is really really slow.

I use copy to bulk insert 200 files of 5000 lines each into 200 tables (one file per table).

Each table have a before trigger that use pgcrypto to encrypt two bytea column from the files and 3 default text into 3 additional column

The trigger looks like this :

select password into var_pwd from temp_table;  --The password is put into a temporary table before the copy.

new.bytea1 : = pgp_sym_encrypt_bytea(bytea1,var_pwd); 
new.bytea2 : = pgp_sym_encrypt_bytea(bytea2,var_pwd);
new.text1 : = pgp_sym_encrypt('default text1',var_pwd);
new.text2 : = pgp_sym_encrypt('default text2',var_pwd);
new.text3 : = pgp_sym_encrypt('default text3',var_pwd);

It takes 55 minutes to load everything with the trigger(3 minutes without it, so the encryption is the problem).

It takes around 7 minutes to insert and encrypt everything in Sql Server (if it is of any use, i use a symmetric key, the EncryptByKey command and an instead of trigger. Everything else is the same)

The data needs to be decrypted, so a two way encryption is needed.

Is there any alternative i could try to speed up the query (another encryption extension, some postgresql.conf settings i should look into / etc)?

Thanks in advance.

Best Answer

It's probably fastest to use COPY (or \copy) to copy the data into unencrypted tables. Disable triggers, foreign keys, indexes.

Then encrypt, and add your indexes and keys again.

Use one SQL operation to encrypt, like

update my_table set 
    bytea1 = pgp_sym_encrypt_bytea(bytea1,var_pwd),
    bytea2 : = pgp_sym_encrypt_bytea(bytea2,var_pwd)
    ....;

Or do a insert into...select into a new table, encrypting in the select.

Related Solutions

Innodb – Fastest way to copy data from MyISAM to InnoDB

There are a few solutions. First, however, I'm not sure about the process in the first place. Is this a one time copy operation? A recurring copy operation? Do you want to migrate from MyISAM to InnoDB?

What is the main reason for your desire for a quick operation?

If you're looking for migration, then why don't you use an online table alter tool, such as oak-online-alter-table (disclaimer: I'm author of this tool) or pt-online-schema-change? Both will allow you to change your schema live and online with very little disturbance.

If you're looking to a copy+paste of your data, then I would suggest using chunking: copying the data in small packets. This way you don't get that huge lock and no funny timeouts. You can use either oak-chunk-update or pt-archiver for this. This may actually make the total runtime shorter because of reduces locking, but may also take longer. Also consider that it is not an atomic operation, and changes to original table while copying is made, may not get caught, so you may get an inconsistent copy.

Otherwise (or in addition) you can use all the usual tweaks, such as

SET GLOBAL innodb_flush_log_at_trx_commit := 2;

or set

[mysqld]
innodb_doublewrite = 0

or perhaps, depending on OS and disks,

[mysqld]
innodb_flush_method = O_DIRECT

Each of the above may reduce disk I/O access. First two will also make your server less crash safe. But if for limited time, this may be OK for you.

SQL Server 2008 – Decrypting Previously Encrypted Data Returns NULL

This sounds like a case where the key_guid in the encrypted data does not match the key_guid for the symmetric key used to encrypt it.

When data is encrypted using a symmetric key and is then saved in a varbinary column, the first 16 bytes are the Key_guid of the symmetric key used to encrypt the data, which explains how DECRYPTBYKEYAUTOCERT can automatically locate the correct symmetric key.

Can you run the script below and verify that this is the case? And, did you encrypt and decrypt the data using the same database?

select 
    name,cast(key_guid as varbinary(max))
from sys.symmetric_keys
where
    name ='key_dbKeys'

select distinct table.data from table
where 
    table.data is not null

After attempting to recreate the problem, there could be a problem opening the symmetric key prior to inserting the data or, after reviewing the documentation for DECRYPTBYKEYAUTOCERT, you need to cast the return, which is varbinary, to varchar. I'm also posting the working script that is based on what you have developed. I had to use a different password.

declare  @clean_data varchar(100)

set  @clean_data='Hello World'

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword!';

CREATE CERTIFICATE cert_dbKeys
   ENCRYPTION BY PASSWORD = 'P@ssword!'
   WITH SUBJECT = 'Database encryption key', 
   EXPIRY_DATE = '20201031';

CREATE SYMMETRIC KEY key_dbKeys 
   WITH ALGORITHM = AES_128
   ENCRYPTION BY CERTIFICATE cert_dbKeys;
create table EncryptedData
( secretmessage varbinary(max))

OPEN SYMMETRIC KEY key_dbKeys
DECRYPTION BY CERTIFICATE cert_dbKeys WITH PASSWORD = 'P@ssword!'

insert into EncryptedData (secretmessage) values(EncryptByKey(Key_GUID('key_dbKeys'), @clean_data))

CLOSE SYMMETRIC KEY key_dbKeys;

SELECT cast(DecryptByKeyAutoCert(cert_id('cert_dbKeys'), N'P@ssword!', secretmessage) as varchar(100)) FROM EncryptedData

Best Answer

Related Solutions

Innodb – Fastest way to copy data from MyISAM to InnoDB

SQL Server 2008 – Decrypting Previously Encrypted Data Returns NULL

Related Question