La forma óptima de guardar direcciones IP en MySQL
Hace unos días vía menéame el artículo 10 things you (probably) didn’t know about PHP. El #1 recomienda almacenar las direcciones IP como enteros en vez de strings, como hacíamos en el menéame.
Aunque ya me lo habían dicho, no dí demasiada importancia, ya que prefería la “claridad” antes que ahorrar un poco de espacio. Pero ese artículo me hizo dudar, así que probé que pasaría cambiando la BBDD y el código para trabajar de esta manera. Sobre todo teniendo en cuenta que la tabla de votos del menéame ya tiene casi dos millones de filas y varios índices múltiples que ocupaban en total 600 MB.
Hice las pruebas en casa y quedé sorprendido, de 600 MB de espacio se pasó a menos de 150MB (con índices incluidos). Además las búsqueda por direcciones IP son muchos más rápidas –en el menéame, cada lector/usuario genera una consulta por votos/IP por cada noticia o comentario que se visualiza–.
Pero además la inserción de nuevos votos se hace mucho más rápida, seguramente debido a la complejidad mucho menor para generar una nueva entrada en los índices.
Dadas las diferencias, este mediodía “detuve” unos minutos el servidor e hice los cambios y actualizaciones (ver los cambios). Es impresionante la diferencia.
Hace un mes estábamos casi desesperados porque necesitábamos casi urgente un servidor nuevo mucho más potente. Como no nos podían entregar uno con Debian me dediqué a optimizar código. Con estos cambios la bajada es aún más notable, de pasar de casi 2 de carga media, ahora no llegamos siquiera a 1 en las horas pico, a pesar que se hacen más cosas, como el voto de comentarios.
Encontré un problema importante con el PHP y que afecta a toda la arquitectura de 32 bits: no trata bien los enteros sin signo que devuelve el ip2long. El inicializar el sql para la consulta pone el valor erróneo, lo que hacía que en la BBDD se guarde siempre cero, así que antes hay que convertir es entero sin signo en un string. Lo hago una sola vez al inicio con el sprintf.
$globals['user_ip_int'] = sprintf("%u", ip2long($globals['user_ip']));
En fin, que nunca se acaba de aprender todo, y es notable cómo se puede optimizar aún el código y las BBDD aunque en apariencia ya no sea posible.
¡Impresionante!
Todo sea por la eficiencia de Menéame.
Espero que esto sirva para que puedan seguir sumándose usuarios sin gran impacto para el sistema.
¡Buenas noticias para el sitio!
Un saludo.
Comment by Demian — Saturday 28/10/2006 @ 21:14
Hola ricardo, no sé si es aquí donde se te pueden hacer las sugerencias/peticiones, supongo que no. Pero hasta que sepa donde es, lo dejo aquí.
Te cuento lo que me pasa:
Yo sigo menéame a través de los rss en Netvibes.com, y me pasa una cosa curiosa, cuando quiero leer el comentario de la noticia, me aparece el link correcto, pero el comentario que me aparece es de otra noticia. No sé si esto pasa en otros entornos, y no sé si usas netvibes tb como pagina de inicio, pero estaría bien arreglarlo.
Un saludo.
Comment by Imparable — Saturday 28/10/2006 @ 22:01
Vale, olvídalo. Parece que es un problema del netvibes, ya que le pasa con mas sitios.
Les voy a mandar un mail y me voy de copas que hoy es sabado.
chao.
Comment by Imparable — Saturday 28/10/2006 @ 22:05
Ricardo ya estan disponibles estas mejoras en: http://meneame.net/archives/meneame-src.tgz ?
saludos!
Comment by drbit — Saturday 28/10/2006 @ 22:19
#4, sí, ya están disponibles
Comment by gallir — Saturday 28/10/2006 @ 23:20
Me haces sentir culpable. Yo estuve trasteando una de las primeras versiones de Meneame, y como me dedico a programar bases de datos, por pura deformación profesional, esta zona fue la que más atendí. Hice cambios en el código para usar PostGreSQL, y testee varias cosas (esta incluida). Pero tú corres muy de prisa :), y para cuando tenía algo listo, ya habian cambiado muchas cosas y lo dejé para mañana, mañana, mañana,….
Comment by René Guerra — Sunday 29/10/2006 @ 1:47
Y me alegro mucho de que hayas resuelto esto. Suerte
Comment by René Guerra — Sunday 29/10/2006 @ 1:48
http://mnm.uib.es/gallir/posts/2006/05/27/747/
“17#: RBA, deja de comerte el coco con las IP, porque cuando tengas que guardar una IPv6 (el servidor de menéame ya es ipv6) se te joderá todo el invento.
Por otro lado, así como está no hay que hacer ninguna conversión, lo que hay en la bbdd y lo que se ve es lo mismo, y los hashes de string son igual de rápido (casi O(1) si interesa eso, en árboles balanceados son también similares).
Y por supuesto, la ventaja y facilidad de las búsquedas parciales con el “LIKE”. ”
Como cambian las cosas…
Comment by a — Sunday 29/10/2006 @ 14:04
#8: pues sí, eso lo digo al principio del apunte.
“Aunque ya me lo habían dicho, no dí demasiada importancia ya que prefería la “claridad” antes que ahorrar un poco de espacio.”
Tampoco me cuesta tanto admitir cuando estaba equivocado, o cuando los demás proponen soluciones mejores (y lo que no era un problema entonces, podría serlo ahora).
Comment by gallir — Sunday 29/10/2006 @ 16:54
Pos nada Ricardo, a mandar. :)))
Disclaimer;
No tengo nada que ver con ningún rollo ni paranoia conocida hasta el momento o por conocer.
Comment by Peanut — Sunday 29/10/2006 @ 22:13
Ricardo, tal vez sería buena idea montar un acceso SVN al código para incorporar las mejoras a los clones y, si nos dejas, aportar alguna mejora al código original. Si quieres te paso por mail configuraciones para montarlo en un periquete! :_)
Comment by coder — Monday 30/10/2006 @ 10:34
Ricardo, ¡otra cosa! Si quieres terminar de optimizar ese código, haz lo siguiente:
1) No te preocupes de utilizar ip2long ni sprintf al inicio del script.
2) En el momento de hacer la consulta (ya sea INSERT o SELECT), utiliza las funciones nativas de MySQL inet_aton() e inet_ntoa(), que agilizarán algo el trámite y la CPU lo notará menos que si lo haces desde PHP. En Saca La Basura lo hemos hecho y, aunque ni de lejos tiene la carga que Meneame, se aprecia. Código SQL de ejemplo:
mysql> select inet_aton(’127.0.0.1′);
+————————+
| inet_aton(’127.0.0.1′) |
+————————+
| 2130706433 |
+————————+
1 row in set (0.00 sec)
mysql> select inet_ntoa(’2130706433′);
+————————-+
| inet_ntoa(’2130706433′) |
+————————-+
| 127.0.0.1 |
+————————-+
1 row in set (0.00 sec)
3) En algunas tablas, quizá puedas hacer uso de INSERT ON .. DUPLICATE KEY UPDATE
Comment by coder — Monday 30/10/2006 @ 11:23
Jejej que cosas… tengo ganas de ver que pasará cuando implementes consultas parametrizadas…
Comment by DN — Monday 30/10/2006 @ 11:23
Recuerdo la bronca que tú y otro par de comentaristas me echasteis cuando sugerí lo de usar enteros para las IPs. Joder si la recuerdo. A ti te lo perdono pero me gustaria saber ahora la opinion del jdeveloper que casi me ponia de tonto para arriba (y tú, para que negarlo, un poco tambien). Y no es solo espacio, Ricardo…
Comment by RBA — Monday 30/10/2006 @ 11:34
/me está sólo en una habitación cerrada con un gran látigo en la mano derecha. /me se está flagelando.
Comment by gallir — Monday 30/10/2006 @ 12:20
Seguro que más de uno sonrio maleficamente, mientras leía la entrada en meneame pensando en esa entrada de la bítacora.
Hombre, podías haber puesto algún enlace. Para dar a RBA el reconocimiento que merece.
De resto, enhorabuena por el cambio. Lo único realmente doliente, es que tuviera que ser después de un artículo en inglés que te plantearás tal cambio. Simplemente porque meses antes ya te lo habían dicho.
Comment by ^_^ — Monday 30/10/2006 @ 17:42
#16: A ver, el cambio no lo hice por necesidades críticas, ni porque lo haya visto en inglés, sino porque era la enésima vez que lo leía/escuchaba y me dio ganas de probarlo. Podría haber seguido así sin problemas.
Lo de “enésima” significa que varios me lo habían dicho, en diversos comentarios y correos, y la verdad, ni me acordaba ni de qué había sido la discusión con RBA (aunque sí recordaba una discusión, y no tengo porqué no citarlo, hasta tiene contratado publicidad en el menéame… algunos creen que me muevo por “odios”, cuando puede ser simple estupidez mía :-P, el hecho que un lector lo haya recordado y haya puesto el enlace lo confirma, los lectores siempre saben más que el que escribe, y estoy de acuerdo ).
Por otro lado, sigo con lo misma idea que antes-
Es una putada usar ahora los enteros, además de los sql que solía usar para detectar “clones” ahora se han complicado bastante comparado al simple like “x.y.z.%” que usaba antes, y me putea que cualquier cosa que quiera hacer desde el PHP deba hacerlo con mucho cuidado del problema de signos. Tiene su coste también, no ha salido gratis, aunque al menos en este momento los beneficios fueron bastantes “importantes” como para no asumir los “costes”.
Creo que ya escribí antes sobre este tema, “there is no silver bullet”.
Comment by gallir — Monday 30/10/2006 @ 17:57
Si no lo hiciste por necesidades críticas, pues con más razón. Que este tipo de cosas son básicas, y si encima son varias las voces que te indican el error pues… No lo digo porque sea un artículo en inglés, sino más bien porque te tienen que hacer reflexionar un artículo en otro idioma. Cuando ya varias personas te lo habían hecho notar.
192.168.69.0 = 3232253184
192.168.69.255 = 3232253439
hombre no parece excesivamente complicado un $globals[’user_ip_int’] BETWEEN 3232253184 AND 3232253439
y para costes es evidente que lo costoso es la solución anterior.
Comment by ^_^ — Monday 30/10/2006 @ 18:36
> hombre no parece excesivamente complicado un $globals[’user_ip_int’] BETWEEN 3232253184 AND 3232253439
No sé si has leído lo que he dicho o me expreso mal, dije “sentencia sql”, esas que ejecuto manualmente cuando quiera averiguar algo. Y manualmente significa que no se hace con PHP sino directamente en sql del mysql, que me queda así:
select distinct user_login, user_email, vote_ip_int from votes, users where user_id=vote_user_id and vote_type in (’links’,'comments’) and vote_date > date_sub(now(), interval 90 day) and vote_ip_int between inet_aton(’62.x.y.0′) and inet_aton(’62.x.y.0′) + 255;
o
select distinct user_login, user_email, vote_ip_int from votes, users where user_id=vote_user_id and vote_type in (’links’,'comments’) and vote_date > date_sub(now(), interval 90 day) and vote_ip_int between inet_aton(’62.x.y.0′) and inet_aton(’62.x.y.255′);
versus la más simple:
select distinct user_login, user_email, vote_ip from votes, users where user_id=vote_user_id and vote_type in (’links’,'comments’) and vote_date > date_sub(now(), interval 90 day) and vote_ip like ‘62.x.y.%’;
Pero no entiendo a qué viene tanta manía, sí, lo repito, tenían razón, se ahorra mucho espacio. Por otro lado, lo curioso es que el que modifica el código, lo prueba y publica los números a favor de las tesis contrarias a la mía he sido yo, no tú, que también podrías haberlo explicado, hecho antes, y hasta enviado parches si estabas tan interesado en demostrar mi error de apreciación ¿no?.
Comment by gallir — Monday 30/10/2006 @ 18:53
Jejeje pues no, ni siquiera se me paso por la cabeza que hicieras ese tipo de sentencias a mano.
Tampoco me debí expresar bien cuando te daba la enhorabuena y te decía lo sangrante del asunto. Ese cambio era evidente que llegaría. Es el como ha llegado despues de un artículo, y me repito, en inglés. Simplemente, es que estoy hartito de esa marca de cultura hispana donde lo de fuera es mejor y lo de dentro se menosprecia.
En cuanto a la complejidad del sql. Hombre un “_int” que añades a las apariciones a “vote_ip” y escribir reescribir el like con un BETWEEN … AND. Pues no me parece que añada una complejidad a la sentencia, más bien añade caracteres a esta pero no complejidad. No se trata del espacio, se trata de la eficacia y la eficiencia que aporta una solución con respecto a la otra.
Comment by ^_^ — Monday 30/10/2006 @ 19:15
Pues sí, hago muchas a mano, sobre todo para controla cuando acusan de spam, “endogamias” o trolleos de la misma persona usando diferentes usuarios.
Pero lo hago sobre un segundo servidor, donde replicamos la BBDD en tiempo real (con la replicación del mysql) porque en el principal bloquea los accesos webs en cuanto la consulta es algo compleja y requiere muchos accesos (como en la de los ejemplos). Ahora que el servidor tiene el doble de memoria y con esta reducción de los índices, volveré a probar (aunque el servidor donde está replicado es idéntico y toma unos 5-9 segundos).
Comment by gallir — Monday 30/10/2006 @ 19:41
La forma óptima de guardar direcciones IP en MySQL
Ricardo Galli explica porque motivos es mejor guardar las direcciones ip de un usuario como enteros en vez de como strings y pone ejemplos de lo que le sucedió a Menéame cuando aplicó esta optimización
Trackback by aldead.com — Sunday 5/11/2006 @ 11:18
Joder, de lo que se entera uno…
Comment by Sin acritud — Tuesday 7/11/2006 @ 10:20