Re: 8.0.4 Super Lento

Bueno, no se si te sirva pero te dare mi experiencia con este problema y
como lo resolvi.

MIra lo pondre facil
Le asigne 1.1Gb de memoria a shared_buffers , solo tengo 2 gB
4Mb a workmen creo q pusiste demasiado recuerda q esa es la memoria de cada
sort q hagas , si haces mucho sort en todos tus usuarios se te va a ir al
suelo

SHMAX le asigne 1500mb para el sistema operativo

1500*1024*1024 =1572864000

No te levanta porq le asignaste a postgres mas de lo q localizaste en el SO
Haz esta prueba contigo a ver como te va puedes ir bajando esos valores como
desees pero a mi hasta ahora me funcionan y muy bien.
Te dire el dump de 4gb se hace en 6min.
Juanky dice q esta mal pero para mi funciona.
Pruebalo y me comentas a ver como va

>
>
>SHMMAX: Asumiendo que quiero 300 conexiones maximo
>shmmax = 1572864000
>shmmax = 1572864000

>Pasando a bytes:
>111989*1024 = 114676777 (ceiling)
>
>Verificando
>
>shiva1 napv2 # sysctl kernel.shmmax
>kernel.shmmax = 1572864000
>shiva1 napv2 # sysctl kernel.shmall
>kernel.shmall = 1572864000
>shiva1 napv2 #
>

>WORKMEM:
Demasiado por lo q te explique
>El 4% de 3 GB: 125829 KB (3145728 KB*4)/100
work_men = 125829
work_men = 8192 # y creo q es mucho solo tengo 4096
125mb imagina q 20 usuarios activen sort , te vas al suelo

>
>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 = 140000 # min 16, at least
>max_connections*2, 8KB each
>work_mem = 4096 # min 64, size in KB
>maintenance_work_mem = 65536 Fijate q este lo subi es el de los indices #
>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
>
>
>---------------------------(fin del mensaje)---------------------------
>TIP 9: el optimizador ignorará el uso de recorridos de índice si los
> tipos de datos de las columnas no coinciden

_________________________________________________________________
Latinos en EE.UU: noticias y artículos de interés para ti
http://latino.msn.com/noticias/latinoseneeuu