Calculadora de ajuste de memoria

Adónde va la memoria de una base de datos, y el parámetro que se multiplica en silencio

Las bases gestionadas llegan con parámetros de memoria derivados del tamaño de la instancia, y casi todos son valores por defecto sensatos que conviene dejar en paz. Uno no lo es, porque no se comporta como los demás: work_mem se concede por nodo de ordenación o de hash, así que su coste real se multiplica por el número de conexiones y por el número de esos nodos en cada consulta. Esta herramienta deriva los parámetros estándar de la memoria de la instancia y luego muestra ese producto de forma explícita.

Cómo funciona

• Deriva shared_buffers, effective_cache_size y maintenance_work_mem de la memoria de la instancia, con las proporciones que usa el propio RDS.
• Muestra los equivalentes de MySQL — innodb_buffer_pool_size y los búferes por conexión — al cambiar de motor.
• Multiplica work_mem por tu número de conexiones y por las ordenaciones por consulta, que es la cifra que de verdad tiene que caber.
• Avisa cuando la asignación base más ese peor caso supera la memoria de la instancia.

PostgreSQL, tal como RDS fija los valores por defecto
  shared_buffers        = {DBInstanceClassMemory/32768}  -> 25% de la RAM
  effective_cache_size  ~ 75% de la RAM (pista para el planificador, no una reserva)
  maintenance_work_mem  ~ 5%, con tope en torno a 2 GiB

MySQL
  innodb_buffer_pool_size = {DBInstanceClassMemory*3/4} -> 75% de la RAM

el que se multiplica
  peor caso = work_mem x conexiones x ordenaciones_por_consulta

Ejemplo resuelto

Una instancia PostgreSQL de 32 GiB con los valores por defecto, y qué pasa cuando se sube un ajuste.

1. shared_buffers = 25% de 32 GiB = 8 GiB
2. effective_cache_size = 75% = 24 GiB, pero no reserva nada — solo le dice al planificador qué está cacheando probablemente el sistema
3. maintenance_work_mem = 5% = 1,6 GiB, bajo el tope de 2 GiB
4. work_mem = 16 MiB, con 200 conexiones y 2 nodos de ordenación por consulta
5. peor caso = 16 MiB x 200 x 2 = 6,25 GiB
6. 8 GiB + 6,25 GiB = 14,25 GiB de 32 — holgado
7. ahora sube work_mem a 64 MiB para arreglar un informe lento, y crece hasta 1.000 conexiones con 4 ordenaciones
8. peor caso = 64 MiB x 1.000 x 4 = 250 GiB en una instancia de 32 GiB

Los dos últimos pasos son la ruta habitual hacia una muerte por falta de memoria. Nada parecía temerario: alguien cuadruplicó work_mem para acelerar una consulta, y el tráfico creció. La multiplicación hizo el resto, y ocurre bajo carga y no al cambiar el parámetro, que es por lo que rara vez se detecta en revisión.

Cómo leer el resultado

• effective_cache_size no reserva nada. Es la estimación que le das al planificador de cuánta memoria dedica probablemente el sistema a cachear tus datos, y cambia qué planes parecen baratos. Ponerlo alto no consume memoria; ponerlo mal produce planes malos.
• shared_buffers al 25% es el punto de partida convencional y el valor por defecto de RDS. Subirlo no es gratis: PostgreSQL se apoya en la caché de páginas del sistema como segundo nivel, así que la memoria que va a shared_buffers se le quita a esa caché. Valores muy grandes pueden hacerlo todo más lento.
• MySQL tiene la forma opuesta. innodb_buffer_pool_size vale 75% por defecto porque InnoDB no se apoya en la caché del sistema como hace PostgreSQL: quiere los datos en su propio pool. No traslades aquí la intuición de PostgreSQL.
• Sube work_mem para la sesión y no para el servidor cuando solo un informe lo necesita. SET work_mem en esa conexión da el plan que quieres sin conceder la misma dotación a todas las demás conexiones de la instancia.
• Una consulta con varios nodos de ordenación o hash puede tomar work_mem varias veces dentro de una sola conexión. Los workers paralelos lo multiplican otra vez. El deslizador de ordenaciones por consulta existe porque suponer una es la forma más común de que esta estimación salga demasiado baja.

Preguntas frecuentes

¿El peor caso es realista o pura aritmética?

Es un techo, no un pronóstico: todas las conexiones tendrían que estar ejecutando a la vez una consulta glotona de memoria. Pero es el techo que importa, porque las bases no fallan con suavidad al alcanzarlo: las mata el OOM killer, y los picos de tráfico son justo cuando todas las conexiones trabajan a la vez.

Mi base gestionada no me deja cambiar shared_buffers. ¿Por qué?

En RDS y Azure se fija mediante un grupo de parámetros como una fórmula sobre DBInstanceClassMemory, no como un número fijo, y cambiarlo exige reiniciar porque el segmento de memoria compartida se reserva al arrancar. Cloud SQL bloquea algunas marcas por completo. El valor por defecto suele ser correcto; el parámetro que merece tu atención es work_mem.

¿Cómo sé cuánta work_mem necesita de verdad una consulta?

Ejecuta EXPLAIN (ANALYZE, BUFFERS) y busca mezcla externa o hash en disco. Si una ordenación se volcó a disco, el plan lo dice y da el tamaño. Esa cifra — algo más que el volcado — es lo que quiere la consulta, y te indica si merece la pena subir el ajuste globalmente o si conviene arreglar la consulta.

¿Y los búferes por conexión de MySQL?

Se multiplican igual. sort_buffer_size, join_buffer_size y read_rnd_buffer_size se reservan por conexión cuando hacen falta, además del buffer pool. Rige la misma disciplina: mantenlos modestos y súbelos para la sesión que realmente los necesita.

Fuentes

• PostgreSQL — parámetros de consumo de recursos
• Amazon RDS — buenas prácticas
• MySQL — configuración del buffer pool de InnoDB