Category Archives: MySQL

Latencies and sizes

This was hanging around in my Keep for quite a while – right till I realized that I need it somewhere for a quick reference, although it just messes my Keep flow. Thus posting it here in a “JFTR” fashion.

Data access latencies:

L1 cache reference ……………………. 0.5 ns
Branch mispredict ………………………. 5 ns
L2 cache reference ……………………… 7 ns
Mutex lock/unlock ……………………… 25 ns
Main memory reference …………………. 100 ns
Compress 1K bytes with Zippy …………. 3,000 ns = 3 µs
SSD random read …………………… 150,000 ns = 150 µs
Read 1 MB sequentially from memory ….. 250,000 ns = 250 µs
Round trip within same datacenter …… 500,000 ns = 0.5 ms
Read 1 MB sequentially from SSD ….. 1,000,000 ns = 1 ms
Send 1MB over 1 Gbps network ……. 10,000,000 ns = 10 ms
Disk seek ……………………… 10,000,000 ns = 10 ms
Read 1 MB sequentially from disk …. 20,000,000 ns = 20 ms
Send packet CA->Netherlands->CA …. 150,000,000 ns = 150 ms

MySQL Storage Requirement per Data Type:

TINYINT – 1 byte (BOOL is an alias for this)
SMALLINT – 2 bytes
MEDIUMINT – 3 bytes
INT, INTEGER – 4 bytes
BIGINT – 8 bytes
FLOAT(p) – 4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
FLOAT – 4 bytes
DOUBLE [PRECISION], REAL – 8 bytes
DECIMAL(M,D), NUMERIC(M,D) – Varies
BIT(M) – approximately (M+7)/8 bytes