IPv4 addresses stored as String(255) instead of INT

Bug #994150 reported by aeva black
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenStack Compute (nova)
Fix Released
Wishlist
dk647

Bug Description

IPv4 addresses can be more efficiently stored in MySQL as an (UNSIGNED) INTEGER instead of as a UTF8 VARCHAR(255). The former requires only 4 bytes; the latter requires between 8 bytes (a.b.c.d) and 16 bytes (aaa.bbb.ccc.ddd) of storage, and up to 765 bytes of memory for certain operations where MySQL allocates memory based on the max row size (eg, sorts and in-memory temp tables). Changing both `fixed_ips`.`address` and `floating_ips`.`address` to UINT looks to me like it would have a positive impact for deployments with very large numbers of IP addresses. There might be other columns that could benefit from this as well.

I can't speak to the efficiency of other databases from experience, but I expect the performance difference would be similar.

Tags: db
Thierry Carrez (ttx)
Changed in nova:
importance: Undecided → Wishlist
status: New → Confirmed
dk647 (meizu647)
Changed in nova:
assignee: nobody → dk647 (meizu647)
dk647 (meizu647)
Changed in nova:
status: Confirmed → In Progress
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to nova (master)

Fix proposed to branch: master
Review: https://review.openstack.org/12557

Revision history for this message
Ian Wells (ijw-ubuntu) wrote :

It's not just working space - indexes will be smaller, rows will be smaller, and therefore index lookups and table scans should be faster. Inequalities will suddenly start to work in a useful manner, and ipv6 addresses will get normalised before being stored, if we do it right.

Revision history for this message
Sean Dague (sdague) wrote :

So we are currently doing shorter types for MySQL, and packed types for PG. Given that we have to handle the IPv6 case, I'm going to consider shorter strings "solved" for now.

Changed in nova:
status: In Progress → Fix Committed
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.