So I'm having this table that logs all errors happened in our production code. It looks like this:
id | date | type | message | file | line
----+------+--------------+---------------+--------------+-----------
INT | DATE | VARCHAR(128) | VARCHAR(4096) | VARCHAR(255) | SMALLINT
Now, in most cases errors will be reported via a simple HTTP-API, issued from the client's server running our software. Naturally, when something happens, it happens often. So most errors get logged between 10 and 200 times, in rare cases up to 1k times.
So I end up with many records having the same value for the message
field (and for type
and file
as well). Since I could not find any details via Google, I'm now wondering whether it would be wise to manually store the strings in another table like this:
hash | string
------------+----------
VARCHAR(32) | VARCHAR(4096)
Assuming I would hash the values of the three fields with MD5, I could shrink their space usage down to VARCHAR(34)
, using the scheme
l$I'm a literal value. for fields < 32 chars
h$md5(field) for fields > 32 chars
I did some benchmarking and it seems to save about 15 to 20% from my error table. To handle the concurrency with transaction, my tables use InnoDB. What is the difference between MySQL VARCHAR and TEXT data types? points out that InnoDB will store string values always outside the normal table, but is it pooling or storing each string for itself?
Additionally, I plan on using PostgreSQL, too. rolleyes :-)
Any suggestions? Is it wise to go the extra mile and do string pooling?