Re: 8.0.4 Super Lento

Juanky Moral wrote:

>No puedes cambiar los parámetros del kernel como si estuvieras
>inventando una receta de cocina. Ni tampoco subir y bajar los
>shared_buffers a tu antojo, sin saber siquiera cual es el tamaño
>actual de los segmentos de memoria:
>$ cat /proc/sys/kernel/shmmax
>
>Sinceramente, te recomiendo que eches una mirada a este artículo:
>www.juancarlosmoral.es/hardware_tunning.html
>
>
>
Hey, se mira bueno el link pero las intrucciones no funcionan, anexo el
detalle siguiendo las recomendaciones del articulo:

SHARED BUFFERS: Asumiendo 30% del total de 3G
El 30% de 3 GB: (3145728 KB/30) = 104858 KB
shared_buffers: (104858 KB/8 KB) = 13107

SHMMAX: Asumiendo que quiero 300 conexiones maximo
shmmax = 250 kB + 8.2 kB * 13107 + 14.2 kB * 300
shmmax = 111989

Pasando a bytes:
111989*1024 = 114676777 (ceiling)

Verificando

shiva1 napv2 # sysctl kernel.shmmax
kernel.shmmax = 114676777
shiva1 napv2 # sysctl kernel.shmall
kernel.shmall = 114676777
shiva1 napv2 #

WORKMEM:
El 4% de 3 GB: 125829 KB (3145728 KB*4)/100
work_men = 125829

Mi postgresql.conf:

listen_addresses = '*' # what IP interface(s) to listen on;
# defaults to localhost, '*' = any
max_connections = 300
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from
shared_buffers
# and max_locks_per_transaction.
superuser_reserved_connections = 5
shared_buffers = 13107 # min 16, at least max_connections*2,
8KB each
work_mem = 125829 # min 64, size in KB
maintenance_work_mem = 16384 # min 1024, size in KB
max_stack_depth = 4096 # min 100, size in KB
max_fsm_pages = 900000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 5000 # min 100, ~50 bytes each
checkpoint_segments = 25 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 500 # range 30-3600, in seconds
effective_cache_size = 314573 # typically 8KB each
stats_start_collector = true
stats_command_string = true
lc_messages = 'C' # locale for system error message strings
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting

Pero Postgres no levanta:

FATAL: could not create shared memory segment: Invalid argument
DETAIL: Failed system call was shmget(key=5432001, size=120381440, 03600).
HINT: This error usually means that PostgreSQL's request for a shared
memory segment exceeded your kernel's SHMMAX parameter. You can either
reduce the request size or reconfigure the kernel with larger SHMMAX.
To reduce the request size (currently 120381440 bytes), reduce
PostgreSQL's shared_buffers parameter (currently 13107) and/or its
max_connections parameter (currently 300).
If the request size is already small, it's possible that it is
less than your kernel's SHMMIN parameter, in which case raising the
request size or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about
shared memory configuration.

---
Miguel