#PostgreSQL: High Privacy Data w/Encryption

In the last years at Gestiweb we’ve met several organizations that have a very special need; as they invest lots of money on I+D researching better products, they need some place to write that down, but those are recipes that are easily reproducible. So, if any competitor gets those recipes they can skip lots of I+D where they invested. So that data is very sensitive.

I’m talking about trade secrets. And we just discovered that companies are very apprehensive in that matter. I guess that’s perfectly normal indeed.

We’re used to have access from time to time to our customer servers, and in some cases to example databases to develop the new features they request in a environment as close as production as possible. The problem here is, if the database holds trade secrets, they won’t let you access the database in any way. So, at least for us, that’s a big problem when it comes for maintenance, support, development and testing of new features.

The last weeks I got an idea that may be feasible for PostgreSQL that may fix the issue if we ever get a customer with trade secrets: Encrypt the data in such a way that is impossible to decipher even with full administrator database access. As Eneboo doesn’t support encryption for its own fields and seems a fairly complicated feature to add, I was thinking about having PostgreSQL to emulate all the required behavior.

At this time I got a sketch working in my home, and as it seems we’re not going to use it in the near future, I believe it’s a good idea to share a demonstration recipe to show off the powers of PostgreSQL, and in its current state it would fit almost any program, not just our own. It just needs a bit of hacking to adapt it to specific needs.

The main idea

  • Apps connect normally to the database and should read & write as if it were clear text.
  • Almost no tweaking on the client-side.
  • Encryption & Decryption should be done in PostgreSQL
  • No keys or traces of it or the decoded data should never hit the disk in any case
  • No possibility of leaking of clear data to other clients

For the client app

  • They must call once per connection a SQL function to “enable” a key, with the corresponding password.
  • They read & write onto a view as if it were the real table. The view only lets them see those data that they have the key to decipher.
  • If data becomes corrupted, depending configuration they get an exception or all values replaced to NULL (or any other value you want).

Inside the PostgreSQL Server

  • You need a table with ciphered data on a “bytea” column. A trigger should be placed to prevent unauthorized modifications to prevent corruption or unwanted deleted from regular user. A view will transform one ciphered field into several fields using json format.
  • Anyway, you need to trust your db and server admins, even if they can’t decipher your data, they have enough power to destruct or corrupt everything.
  • Key passwords still propagate from the client to the server (obviously); but that should happen in a SSL context, so network sniffers shouldn’t work here.
  • Evil administrators of the database server still can set up a logging setting which catches the passwords sent by the clients, or they could modify the database functions in a way where the passwords get logged. Still, you should trust your admins.
  • Encryption doesn’t come free. You can’t index encrypted data. Joining tables using encrypted fields is even worser than joining two text files together. Decryption consumes lots of CPU power: with my i7 cpu, I get about 7000 rows/s from the view.
  • I tried to make this secure, but there may be bugs. Maybe it will be better to use the pgp_sym_encrypt function instead. Anyway, the intention here is to hide from prying eyes, not to make it resistant to very big attacks that would come after exposing the ciphertext publicily. Because of that, maybe a less secure but fast cipher may be secure enough.
  • A PostgreSQL C extension will probably be nicer than this, it may be faster, and it may solve some of the above problems.

How to setup

Imagine we have an application that should read & write to a “sensitive_data” table that has some encrypted data, but the app lacks the tools to do it by itself. So we place a view, call it “view_sensitive_data” that does all the work. We trick the application into thinking the view is the proper table.

We could use any cipher we want, as long PostgreSQL supports it. I’m happy with pgcrypto and AES128, but after doing my tests seems it is a bit slow; maybe other less-secure ciphers may be more interesting, but pgcrypto doesn’t seem to give access to anything interesting.

I crafted small functions for encryption and decryption. The first one adds a checksum and the other checks it afterwards. I should add there a random salt to avoid some known attacks on ciphertexts, but I think is enough for the demonstration. Also row data is usually small, and the length of the ciphertext could give the attacker a hint on what is encoded inside.

As we’re using a view, the decoded data is calculated on the fly and never stored anywhere. The problem now is, where PostgreSQL should read the encryption keys. Because if they’re in the database they will be stored on disk and probably also on the backups.

The trick I am using is very interesting; I exploit the TEMP tables of PostgreSQL and their ability to “overload” already present tables on the database.

It goes like this: I place an empty table “encryption_keys” with key & password. I also placed a CHECK constraint that avoids any real row to be never written on it accidentally. Then, in a initialization function, I create again “encryption_keys” with the same columns but as temporary table. In this temporary table is were we place the real keys for encryption.

Then our previous view joins with this temporary table for decoding, and by doing this every user decodes the data on sensitive_table using its own keys. And because they’re temporary tables, they aren’t written anywhere and they don’t get on a backup.

If you know how PostgreSQL works internally you may be worried about PostgreSQL linking the view to the actual temporary tables, and not to any table called “encryption_keys”; because that’s how PostgreSQL works. And you will be right. To avoid that, I use functions instead of joins. A special function called get_enc_key_list() returns me the rows from the table or temp table, and the decrypt function also does the same job. We force the PostgreSQL planner to rethink every time on which table we want to get. So the view is not a temporary view, and it’s SQL stored on the database permanently. If anyone executes the view without having initialized the temporary table they will see a compatible empty table and it will work assuming there aren’t any cipher keys installed.

To avoid two people trying to save with different key encryption, we place a “keys” table, where the administrator places which keys he wants to have there. To avoid two versions of the same key (which would corrupt the data) we place in public a “test_data” column so every time anyone tries to enable its key, they need to verify that the key is capable of deciphering the “test_data”. Again, this enforcement only applies to regular users of the database.

I use a json encoding to write several fields with several datatypes inside a single ciphertext; this way the ciphertext combines more data, and because of that, I feel it more convenient and also more secure.

With CREATE RULE commands it is easy to define the view behavior on writes, so whenever the client writes to the view, the database ciphers the data back and stores it on the actual table.

This is the SQL I got so far. Beware it is only for demonstration and it still has bugs and it lacks some functionality:

DROP VIEW IF EXISTS view_sensitive_data;
DROP VIEW IF EXISTS view_sensitive_data_lines;
DROP TABLE IF EXISTS sensitive_data;
DROP TABLE IF EXISTS sensitive_data_lines;
DROP TABLE IF EXISTS keys;
DROP TABLE IF EXISTS encryption_keys; -- temp table?
DROP TABLE IF EXISTS encryption_keys; -- real table!

CREATE TABLE sensitive_data
(
 id serial,
 sref varchar NOT NULL,
 sdata bytea,
 enc_key text,
 PRIMARY KEY (id)
);

CREATE TABLE sensitive_data_lines
(
 id serial,
 parentid int NOT NULL,
 sdata bytea,
 enc_key text,
 PRIMARY KEY (id)
);

CREATE TABLE keys
(
 enc_key varchar not null,
 test_data varchar,
 PRIMARY KEY (enc_key)
);

CREATE TABLE encryption_keys
(
 enc_key varchar not null,
 enc_password bytea,
 CONSTRAINT encryption_keys_readonly CHECK (enc_key is null),
 PRIMARY KEY (enc_key)
);


CREATE OR REPLACE FUNCTION init_private_key_list() RETURNS void LANGUAGE SQL AS
$$
 CREATE TEMP TABLE IF NOT EXISTS encryption_keys
 (
 enc_key varchar not null,
 enc_password bytea,
 PRIMARY KEY (enc_key)
 );

$$;

SELECT init_private_key_list();

CREATE OR REPLACE FUNCTION get_enc_key_list() RETURNS varchar[] LANGUAGE PLPGSQL AS
$$
DECLARE
 o_keylist varchar[];
BEGIN
 o_keylist := array_agg(enc_key) FROM encryption_keys;
 RETURN o_keylist;
END
$$;

CREATE OR REPLACE FUNCTION assert_identical_checkum(IN chk1 bytea, IN chk2 bytea) RETURNS boolean LANGUAGE PLPGSQL AS
$$
BEGIN
 IF chk1 = chk2 THEN
 RETURN TRUE;
 ELSE
 RAISE EXCEPTION 'Data Checksum Error!!' USING ERRCODE = 'data_exception';
 END IF;
END
$$;

DROP FUNCTION IF EXISTS encrypt_data(text,character varying); 
CREATE OR REPLACE FUNCTION encrypt_data(IN cleardata text, IN in_key varchar) RETURNS bytea LANGUAGE SQL AS
$$
 SELECT encrypt( (substring(digest(cleardata,'sha256') for 4) || cleardata)::bytea , e.enc_password::bytea,'aes')
 FROM encryption_keys e
 WHERE e.enc_key = in_key
 UNION 
 SELECT cleardata::bytea WHERE in_key IS NULL
$$;


DROP FUNCTION IF EXISTS decrypt_data(bytea,character varying); 
CREATE OR REPLACE FUNCTION decrypt_data(IN enc_data bytea, IN in_key varchar) RETURNS text LANGUAGE SQL AS
$$
 SELECT CASE WHEN assert_identical_checkum(
 SUBSTRING(d FOR 4)
 , substring(digest(substring(d from 5), 'sha256') for 4)
 ) 
 THEN substring(d from 5)::text ELSE NULL END as cleartext
 FROM (SELECT decrypt(enc_data,e.enc_password::bytea,'aes') as d 
 FROM encryption_keys e WHERE e.enc_key = in_key) x
 UNION 
 SELECT enc_data::text WHERE in_key IS NULL
$$ STABLE; 

DROP FUNCTION IF EXISTS decrypt_data2(bytea,character varying,text); 
CREATE OR REPLACE FUNCTION decrypt_data2(IN enc_data bytea, IN in_key varchar, IN return_on_bogus_ciphertext text) RETURNS text LANGUAGE SQL AS
$$
 SELECT CASE WHEN SUBSTRING(d FOR 4) = substring(digest(substring(d from 5), 'sha256') for 4)
 THEN substring(d from 5)::text ELSE return_on_bogus_ciphertext END as cleartext
 FROM (SELECT decrypt(enc_data,e.enc_password::bytea,'aes') as d 
 FROM encryption_keys e WHERE e.enc_key = in_key) x
 UNION 
 SELECT enc_data::text WHERE in_key IS NULL
$$ STABLE; 

CREATE OR REPLACE FUNCTION assert_key_is_valid(IN in_key varchar) RETURNS boolean LANGUAGE PLPGSQL AS
$$
DECLARE
 mtest_data bytea;
 decoded_data varchar;
 existing_keys integer;
BEGIN
 IF in_key IS NULL THEN
 RETURN false;
 END IF;
 mtest_data := decode(test_data, 'base64') FROM keys WHERE enc_key = in_key;
 IF LENGTH(mtest_data) < 4 THEN
 RAISE EXCEPTION 'key does not exist or hasnt test_data to validate against';
 RETURN false;
 END IF;
 
 existing_keys := COUNT(*) FROM encryption_keys WHERE enc_key = in_key;
 IF existing_keys > 0 THEN
 decoded_data := decrypt_data(mtest_data, in_key);
 RETURN true;
 END IF;
 RAISE EXCEPTION 'key % not loaded', in_key;
 RETURN false;
END
$$;


CREATE OR REPLACE FUNCTION enable_key(IN in_key varchar, IN in_passphrase varchar) RETURNS boolean LANGUAGE PLPGSQL AS
$$
DECLARE
 mtest_data bytea;
 decoded_data varchar;
 existing_keys integer;
BEGIN

 mtest_data := decode(test_data, 'base64') FROM keys WHERE enc_key = in_key;
 IF LENGTH(mtest_data) < 4 THEN
 RAISE notice 'key does not exist or hasnt test_data to validate against';
 RETURN false;
 END IF;
 
 existing_keys := COUNT(*) FROM encryption_keys WHERE enc_key = in_key;
 IF existing_keys > 0 THEN
 RAISE notice 'key already present';
 decoded_data := decrypt_data(mtest_data, in_key);
 RETURN true;
 END IF;

 PERFORM init_private_key_list();
 
 INSERT INTO encryption_keys (enc_key, enc_password)
 SELECT '__tmp_key__ ' || in_key, digest(in_passphrase,'sha256') as pass;

 decoded_data := decrypt_data(mtest_data, '__tmp_key__ ' || in_key);
 UPDATE encryption_keys SET enc_key = in_key WHERE enc_key = '__tmp_key__ ' || in_key;
 RETURN true;
EXCEPTION
 WHEN data_exception THEN
 RAISE notice 'Failure trying to enable key';
 RETURN false;
END
$$;

CREATE OR REPLACE FUNCTION keys_review_data() RETURNS trigger AS $$
 BEGIN
 IF current_user NOT IN ('postgres') THEN
 RAISE EXCEPTION 'Unauthorized user %', current_user;
 END IF;
 RETURN NEW;
 END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_keys_review_data
 BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON keys
 FOR EACH STATEMENT
 EXECUTE PROCEDURE keys_review_data();

CREATE OR REPLACE VIEW view_sensitive_data AS
SELECT s.id, s.sref, decrypt_data(s.sdata, s.enc_key) as sdata, s.enc_key FROM sensitive_data s
WHERE enc_key = ANY(get_enc_key_list()) OR enc_key IS NULL;


CREATE OR REPLACE VIEW view_sensitive_data_lines AS
SELECT s.id, s.parentid, j.date as date,j.time as time,
 j.description as description, j.price as price, j.quantity as quantity 
 , s.enc_key FROM sensitive_data_lines s
 CROSS JOIN LATERAL json_to_record( decrypt_data(s.sdata, s.enc_key)::json) as j(date date, time time, description text, price double precision, quantity double precision)
WHERE enc_key = ANY(get_enc_key_list()) OR enc_key IS NULL;



CREATE OR REPLACE FUNCTION trigger_audit_enc_change_row() RETURNS trigger AS $$
 BEGIN
 IF (TG_OP != 'INSERT') THEN
 PERFORM assert_key_is_valid(OLD.enc_key);
 END IF;
 IF (TG_OP != 'DELETE') THEN
 PERFORM assert_key_is_valid(NEW.enc_key);
 END IF;
 RETURN NEW;
 END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_audit_sensitive_data
 BEFORE INSERT OR UPDATE OR DELETE ON sensitive_data
 FOR EACH ROW
 EXECUTE PROCEDURE trigger_audit_enc_change_row();

CREATE TRIGGER trigger_audit_sensitive_data_lines
 BEFORE INSERT OR UPDATE OR DELETE ON sensitive_data_lines
 FOR EACH ROW
 EXECUTE PROCEDURE trigger_audit_enc_change_row();

CREATE RULE view_sensitive_data_INSERT AS ON INSERT TO view_sensitive_data DO INSTEAD (
 INSERT INTO sensitive_data (id,sref,sdata,enc_key)
 SELECT NEW.id,NEW.sref,encrypt_data(NEW.sdata,NEW.enc_key),NEW.enc_key;
 );

CREATE RULE view_sensitive_data_UPDATE AS ON UPDATE TO view_sensitive_data DO INSTEAD (
 UPDATE sensitive_data as s SET
 id = NEW.id,
 sref = NEW.sref,
 sdata = encrypt_data(NEW.sdata,NEW.enc_key),
 enc_key = NEW.enc_key
 WHERE s.id = OLD.id;
 );

CREATE RULE view_sensitive_data_DELETE AS ON DELETE TO view_sensitive_data DO INSTEAD (
 DELETE FROM sensitive_data WHERE sensitive_data.id = OLD.id;
 );


CREATE RULE view_sensitive_data_lines_INSERT AS ON INSERT TO view_sensitive_data_lines DO INSTEAD (
 INSERT INTO sensitive_data_lines (id,parentid,sdata,enc_key)
 SELECT NEW.id,NEW.parentid,encrypt_data(json_build_object('date',NEW.date,'time', to_json(NEW.time), 
 'description', NEW.description, 'price', NEW.price, 'quantity', NEW.quantity)::text,NEW.enc_key),NEW.enc_key;
 );

CREATE RULE view_sensitive_data_lines_UPDATE AS ON UPDATE TO view_sensitive_data_lines DO INSTEAD (
 UPDATE sensitive_data_lines as s SET
 id = NEW.id,
 parentid = NEW.parentid,
 sdata = encrypt_data(json_build_object('date',NEW.date,'time', to_json(NEW.time), 
 'description', NEW.description, 'price', NEW.price, 'quantity', NEW.quantity)::text,NEW.enc_key),
 enc_key = NEW.enc_key
 WHERE s.id = OLD.id;
 );

-- DEMO KEYS:: --- >>> 

INSERT INTO encryption_keys (enc_key, enc_password)
SELECT 'mykey1' as k, digest('mypassword','sha256') as pass;

INSERT INTO encryption_keys (enc_key, enc_password)
SELECT 'mykey2' as k, digest('otherpass','sha256') as pass;

INSERT INTO keys (enc_key, test_data)
SELECT enc_key, encode(encrypt_data(encode(digest(enc_key || rand1,'sha512'),'base64'),enc_key),'base64') as test_data
FROM (SELECT enc_key FROM encryption_keys EXCEPT SELECT enc_key FROM keys) x
 , (SELECT gen_random_bytes(32) as rand1) y;

-- From other connection:
SELECT enable_key('mykey1','mypassword');
SELECT enable_key('mykey2','otherpass');


-- <<--- DEMO KEYS: //// 


-- ENCRYPT / DECRYPT DEMO ::: --- >>>
-- Demonstration on encrypt->decrypt.
WITH enc_data as (
SELECT encrypt_data('hello, honey! how are you?','mykey1') as ciphertext
)
SELECT decrypt_data(ciphertext, 'mykey1') as plaintext, encode(ciphertext,'base64') as ciphertext FROM enc_data;

-- Demonstration of decrypt on raw data.
-- SELECT decrypt_data(decode('egeMgl3X6tzZfCk9N2amsvQ+cBn8R9z9WSiazetyjoE=','base64'),'mykey1')

--- <<<< ------- ENCRYPT / DECRYPT DEMO



--- TEST DATA TABLE:

INSERT INTO sensitive_data (sref, sdata, enc_key)
SELECT 'ref1', encrypt_data('test data 1', 'mykey1'), 'mykey1';
INSERT INTO sensitive_data (sref, sdata, enc_key)
SELECT 'ref2', encrypt_data('test data 2', 'mykey1'), 'mykey1';
INSERT INTO sensitive_data (sref, sdata, enc_key)
SELECT 'ref3', encrypt_data('test data 3', 'mykey2'), 'mykey2';
INSERT INTO sensitive_data (sref, sdata, enc_key)
SELECT 'ref4', encrypt_data('test data 4', 'mykey2'), 'mykey2';
INSERT INTO sensitive_data (sref, sdata, enc_key)
SELECT 'ref5', encrypt_data('test data 5', 'mykey1'), 'mykey1';
INSERT INTO sensitive_data (sref, sdata, enc_key)
SELECT 'ref6', encrypt_data('test data 6', 'mykey2'), 'mykey2';
INSERT INTO sensitive_data (sref, sdata, enc_key)
SELECT 'ref7', encrypt_data('clear text data 7', NULL), NULL;

------------------------------------------

--- TESTING ON INSERT RULE:
INSERT INTO view_sensitive_data (id,sref,sdata, enc_key)
VALUES(8,'ref8','my data blabla','mykey1');
UPDATE view_sensitive_data SET sdata = 'updated data 2' WHERE id = 2;

DELETE FROM view_sensitive_data WHERE id = 5 ; --- does not work???

-- VOLUME:
INSERT INTO view_sensitive_data (id,sref,sdata, enc_key) VALUES(8+generate_series(1,256*17),'ref8','my data blabla' || generate_series(1,17),'mykey1');


-- TRY JSON DATA:


INSERT INTO view_sensitive_data_lines VALUES(1,8,'2017-01-01'::date,'20:00:00'::time,'hello world', 9.56, 3, 'mykey1');
INSERT INTO view_sensitive_data_lines VALUES(2,8,'2017-01-01'::date,'20:00:00'::time,'hello world 2', 9.56, 3, 'mykey1');

UPDATE view_sensitive_data_lines SET date = now(), time = now() WHERE id = 2;

INSERT INTO view_sensitive_data_lines VALUES(3+generate_series(1,256*17),1+generate_series(1,17),'2017-01-01'::date,'20:00:00'::time,'hello world 2', 9.56, 3, 'mykey1');

SELECT * FROM view_sensitive_data_lines 
SELECT * FROM view_sensitive_data

 

 

Probando la velocidad de Japronto

Hace poco encontré por Twitter un articulo que hablaba de un nuevo framework web para Python. Hasta aquí nada nuevo, pero este framework dice que llega al millón de peticiones ¡por segundo!.

Es poco creíble, ya que en Python conseguir un simple bucle for con alguna instrucción dentro que se repita más de 20.000 iteraciones por segundo es bastante difícil. Depende de la máquina, por supuesto. Así que bueno, al principio no le dí importancia, pero al leer más a fondo encontré que realmente es un framework escrito el 99% en C, y lo que tiene es una API Python mínima. Esto ya cobra más sentido (aunque sigo dudando del millón de peticiones). Sería similar a PhalconPHP, un framework 99% en C con una API para PHP que está pensado para optimizar al máximo. en su momento probé Phalcon y la verdad no eran sólo los benchmarks. Las aplicaciones realizadas con él volaban literalmente.

Y es que el problema principal en estas cosas es que los benchmarks suelen hacerlos con pocas instrucciones de lenguaje interpretado (o ninguna) de forma que esa capa de C luzca por encima de todos los otros frameworks; pero en el trabajo real esto no es posible, por lo que al final la diferencia se difumina tanto, que tiende a ser indistinguible.

Así que me he decidido a probarlo yo mismo. Para empezar he visto varios métodos de instalación, y me quedo por el momento con la imagen de docker.

Primer momento duro, la documentación para docker, así como la imagen de docker, no está pulida y no funciona “tal cual”. He tenido que modificar la llamada de docker a:

docker run --entrypoint=/usr/local/bin/python3 --rm -i -t \
  -p 8080:8080 -v $(pwd)/tests:/tests \
  japronto/japronto /tests/hello.py

Primero de todo, el punto de montaje no se puede hacer como nos indican. Hay que compartir una carpeta completa y pasar la ruta absoluta del host. Segundo, el punto de entrada por defecto que ejecuta el comando “japronto” no consigo que funcione. Espera un módulo de Python en lugar de un fichero .py, pero me exige un fichero .py ¿Alguien lo entiende?. Usando directamente python3 para llamar al .py funciona. Para rematar, la apertura del puerto tampoco era apropiada, con la opción antigua no sabías qué puerto te abría en el anfitrión.

En fin. Funciona.

Para comprobar la velocidad usaré inicialmente “siege”, que es bastante bueno el programa para sobrecargar servidores web. Mi equipo es un Intel i7 920@ 2.67GHz con 4Gb de RAM.

deavid@debian:~/git/python/japronto-tests$ siege -b -t 1M http://127.0.0.1:8080
[alert] Zip encoding disabled; siege requires zlib support to enable it
** SIEGE 4.0.2
** Preparing 25 concurrent users for battle.
The server is now under siege…
Lifting the server siege…
Transactions: 142692 hits
Availability: 100.00 %
Elapsed time: 59.44 secs
Data transferred: 1.63 MB
Response time: 0.01 secs
Transaction rate: 2400.61 trans/sec
Throughput: 0.03 MB/sec
Concurrency: 24.62
Successful transactions: 142692
Failed transactions: 0
Longest transaction: 0.05
Shortest transaction: 0.00

El resultado son 2400 peticiones por segundo. Lejos del millón. Hay que tener en cuenta que la misma CPU que ejecuta siege es la que ejecuta Japronto.

Aún así es sorprendente, porque con PhalconPHP recuerdo unas 600 por segundo y Python es bastante más lento que PHP. Revisando los logs he encontrado que la depuración estaba activada en hello.py y eso provocaba que imprimiese en pantalla por cada petición. Imprimir por pantalla, nunca entendí porqué, pero es bastante costoso. He reiniciado la prueba con Debug=False. También he cambiado a Apache “ab” a ver si hay algo más de rendimiento:

$ ab -c250 -t 30 http://127.0.0.1:8080/
(…)
Concurrency Level: 250
Time taken for tests: 8.601 seconds
Complete requests: 50000
Failed requests: 0
Total transferred: 4600000 bytes
HTML transferred: 600000 bytes
Requests per second: 5813.35 [#/sec] (mean)
Time per request: 43.004 [ms] (mean)
Time per request: 0.172 [ms] (mean, across all concurrent requests)
Transfer rate: 522.29 [Kbytes/sec] received

Connection Times (ms)
           min mean[+/-sd] median max
Connect:     0   7   33.8   5     1032
Processing:  5  34   79.9  27     1084
Waiting:     3  28   79.8  21     1070
Total:      10  41   86.5  33     1089

Llegamos a las 5800 por segundo. Impresionante, pero lejos del millón. ¿Cómo probaron ellos? He probado su versión micro.py sin mejora alguna.

Con httperf parece que mejora más. No entiendo muy bien porqué hay menos carga de CPU ahora, pero he comprobado que está emitiendo las llamadas y que Python3 se ejecuta. Por lo que leo aprovechan fuertemente el HTTP pipelining, y httperf hace uso de él

$ httperf –hog –server=localhost –uri=/ –num-conns=200 –burst-length=200000 –num-calls=1000 –port=8080
httperf –hog –client=0/1 –server=localhost –port=8080 –uri=/ –send-buffer=4096 –recv-buffer=16384 –num-conns=200 –num-calls=1000 –burst-length=200000
httperf: warning: open file limit > FD_SETSIZE; limiting max. # of open files to FD_SETSIZE
Maximum connect burst length: 1

Total: connections 200 requests 200000 replies 200000 test-duration 2.415 s

Connection rate: 82.8 conn/s (12.1 ms/conn, <=1 concurrent connections)
Connection time [ms]: min 8.3 avg 12.1 max 23.5 median 11.5 stddev 2.8
Connection time [ms]: connect 0.1
Connection length [replies/conn]: 1000.000

Request rate: 82809.6 req/s (0.0 ms/req)
Request size [B]: 62.0

Reply rate [replies/s]: min 0.0 avg 0.0 max 0.0 stddev 0.0 (0 samples)
Reply time [ms]: response 5.2 transfer 0.0
Reply size [B]: header 79.0 content 12.0 footer 0.0 (total 91.0)
Reply status: 1xx=0 2xx=200000 3xx=0 4xx=0 5xx=0

CPU time [s]: user 0.47 system 1.94 (user 19.5% system 80.3% total 99.9%)
Net I/O: 12450.5 KB/s (102.0*10^6 bps)

Errors: total 0 client-timo 0 socket-timo 0 connrefused 0 connreset 0
Errors: fd-unavail 0 addrunavail 0 ftab-full 0 other 0

Llegamos a las 82809 peticiones por segundo. Supongo que este es el límite en mi máquina o que no sé probarlo correctamente.

Ahora realizo un pequeño cambio en la aplicación para que devuelva un dato dinámico. Simplemente que agregue “req: %d” y nos indique en cada petición el nº de petición que nos ha devuelto.

¡Sorprendentemente, sigue devolviéndome 82k reg/s!

Aunque, tengo que decir, basarse en HTTP Pipelining es “hacer trampa”. No hay ningún navegador que lo realice, ¡ni en https2! Por esto, en el momento de la verdad, en realidad entregará 8k peticiones por segundo, y no 82k.

 

He ejecutado su misma demo de nodejs y he hecho el perfil con Apache “ab”:

$ ab -i -b 1000 -k -c100 -t 30 http://127.0.0.1:8080/
This is ApacheBench, Version 2.3 <$Revision: 1757674 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 5000 requests
Completed 10000 requests
Completed 15000 requests
Completed 20000 requests
Completed 25000 requests
Completed 30000 requests
Completed 35000 requests
Completed 40000 requests
Completed 45000 requests
Completed 50000 requests
Finished 50000 requests
Server Software:
Server Hostname: 127.0.0.1
Server Port: 8080

Document Path: /
Document Length: 0 bytes

Concurrency Level: 100
Time taken for tests: 6.467 seconds
Complete requests: 50000
Failed requests: 0
Keep-Alive requests: 50000
Total transferred: 5250000 bytes
HTML transferred: 0 bytes
Requests per second: 7731.19 [#/sec] (mean)
Time per request: 12.935 [ms] (mean)
Time per request: 0.129 [ms] (mean, across all concurrent requests)
Transfer rate: 792.75 [Kbytes/sec] received

Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.2 0 6
Processing: 2 13 1.5 13 32
Waiting: 2 13 1.5 13 32
Total: 7 13 1.5 13 32

Percentage of the requests served within a certain time (ms)
50% 13
66% 13
75% 13
80% 14
90% 14
95% 14
98% 17
99% 17
100% 32 (longest request)

7731.19req/s vs 5813req/s. NodeJS se aprecia más rápido que Japronto en esta máquina. (httperf arrojó un resultado de 7914.7 req/s para NodeJS, demostrando que efectivamente Japronto está tomando provecho del pipelining para obtener un resultado 10x mejor)

¿Cómo es posible que al autor le diese resultados tan buenos? Lo único que lo explicaría sería el uso de PyPy, que lo comenta en el artículo pero no especifica que haga uso de él en el benchmark. Aún sí, eso significaría que PyPy consiguió compilar la función de Python a C puro por no tener ninguna interacción. Eso no sería posible con la mínima programación que pusiéramos. Si no podemos programar en Python entonces igual tenemos que compararlo con Nginx, ¿verdad? Y saldría perdiendo. Otra opción sería compilar la función response con Cython, pero volvemos a lo mismo. Aunque la programación web con compilación a C es interesante, no es justo compararlo con otros frameworks interpretados. (o insinuar que Python funcionaría más rápido dentro)

Probando Nginx “en crudo” (sin wsgi ni fastcgi) como dato de referencia, ab reporta 48153 req/s , siege 5604 req/s y httperf 56173 req/s.

Incluso he probado esta guía: https://tweaked.io/guide/kernel/ y la diferencia es poca (un 10%)

¿Cómo se supone que Japronto va a superar a NGINX? ¿Me he perdido algo?

 

 

 

Lateral JOIN can make a SQL 200 times faster

Today I was researching problems on one customer database server because it worked a bit slowly in the last 6 months and we don’t know exactly why. In the process, I’ve found they were trying to print every invoice of the last year 2016, and some fault on the SQL was making the query incredibly slow: 880 seconds to complete.

What’s interesting here is that this database doesn’t hold a specially high amount of data compared to other customers, so I was very suprised of this sql timings. Of course, the server also is a cheap one, but anyways that doesn’t explain that bad timing.

As we work in PostgreSQL database, this server has installed 9.4 version, used actively in production. I started PgAdmin3 and copied the SQL from the PostgreSQL log to it.

These SQL are a bit complicated since they currently have 17 tables in the FROM section. As we use Eneboo ERP (which is Free Source, GPLv2) the tables and fields are in spanish, and their names are common to any free user.

But the problem wasn’t there. The problem was in the selected fields, where there was a PGSQL function:

observaciones(facturascli.codcliente, facturascli.codserie)

This function is something we designed to let the user add texts to every invoice, using different programable rules. In the case of this customer, he had a version where it had 3 calls to the function in the SQL body. (The function we actually use is a bit larger than that)

As you can guess from the parameters, it requires one function call for each customer invoice (facturascli). The problem is, this SQL generates several rows per invoice. One per invoice line to be exact. And, because a function is a blackbox to PostgreSQL, the server ends up calling it in every output row despite that is useless. So that’s were the problem was coming from.

I usually say to my coworkers that to add extra data to the query is better to fit subqueries to the select rather to adding joins. That’s because I prefer a query to be slow rather than to be wrong. Several times we had a “row multiplication” problem, when a JOIN was doing something unwanted and we got more rows than we expected. And this can be very problematic. The donwside of abusing subqueries on SELECT is, performance downgrade. But because 95% of the time PostgreSQL manages to plan like it was a JOIN (it does some kind of transformations on its own), most of the time the performance penalty is zero. This wasn’t one of such cases, obviously.

I removed manually these functions to compare times, and I got 1.2 seconds. So the 99% of the time came from these functions.

Looking at the plan, as it is presented by the pgadmin3 tool, it was pretty clear it was doing it in every record. I tried to change the function volatility from VOLATILE to INMUTABLE in the hopes it would help the parser call it less times. But I hand’t any luck. Optimizations using volatility appear when you pass static “inplace” variables to it.

Then I tried to move the function query into a subquery, so it could only see records from invoices, and then joining those results to the main query. It was something like this:

SELECT …. FROM (
SELECT *, observaciones(facturascli.codcliente, facturascli.codserie) FROM facturascli ) facturascli
INNER JOIN lineasfacturascli USING(idfactura)

I thought I would be forcing PostgreSQL to call it once per invoice, but the plan doesn’t reflected this. And the timming was better, but not close to the 1.2 seconds I wanted.

So I started to learn LATERAL JOIN queries, and managed to test something like:

SELECT … FROM facturascli
CROSS JOIN LATERAL observaciones(facturascli.codcliente, facturascli.codserie)
INNER JOIN lineasfacturascli USING(idfactura)

And then, it worked like a charm! I placed 4 lateral joins in total, and the query timing was 2 seconds!. Also the query plan was very straightforward, and did what I wanted, do X for every row in facturascli table.

¿What does a LATERAL JOIN? It allows a subquery JOIN to refer to fields defined on a previous JOIN. Placing it exactly in the middle allows me to specify that I need facturascli rows, but not lineasfacturascli ones, at the moment of calling the function.

So now I’m a big fan of LATERAL keyword. It’s new to PostgreSQL 9.3, so it should be in the majority of servers.

I’m going to train my coworkers on the proper use of LATERAL JOINs as they seem pretty easy and they save a lot of cpu time.