Обсуждение: [INFO] - vm.dirty_ratio/background_ratio

Поиск
Список
Период
Сортировка

[INFO] - vm.dirty_ratio/background_ratio

От
"drum.lucas@gmail.com"
Дата:
Hi guys,

I've got a 128GB server with SATA disks.. and I'm having I/O issue....

NO! I Can't change the disks to SAS or SSD =\


Anyway... just wanna understand the parameters below:
[root@741293-03 ~]$ sysctl -a | grep dirty
vm.dirty_background_ratio = 20
vm.dirty_background_bytes = 0
vm.dirty_ratio = 25
vm.dirty_bytes = 0
vm.dirty_writeback_centisecs = 500
vm.dirty_expire_centisecs = 3000

Would be that a good value to work with?
Can you guys tell me what are you using at the moment?

cheers 

Re: [INFO] - vm.dirty_ratio/background_ratio

От
Victor Yegorov
Дата:
2016-04-05 2:27 GMT+03:00 drum.lucas@gmail.com <drum.lucas@gmail.com>:
I've got a 128GB server with SATA disks.. and I'm having I/O issue....
...
[root@741293-03 ~]$ sysctl -a | grep dirty
vm.dirty_background_ratio = 20
vm.dirty_background_bytes = 0
vm.dirty_ratio = 25
vm.dirty_bytes = 0
vm.dirty_writeback_centisecs = 500
vm.dirty_expire_centisecs = 3000

I think you should lower your `dirty_background_ratio`, 'cos currently kernel will start writing down dirty data from the cache when 20% of `Active` (as reported by /proc/vmstat) is filled,
on 128GB server this can be a lot. You should base your settings on your IO subsystem capabilities — somewhere around the size of the cache of your RAID array.

If you really have IO issues, consider checking more kernel parameters:

    sysctl -a|egrep '^vm.(dirty|swap|over)'

and also check `pg_stat_bgwriter` data, it shows accumulated data about IO performed by checkpoints, bgwriter and individual backends.


For reference, I really like the following pages:
- http://www.westnet.com/~gsmith/content/linux-pdflush.htm
- http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
- http://careers.directi.com/display/tu/Understanding+and+optimizing+Memory+utilization
- http://careers.directi.com/display/tu/Understanding+and+Optimizing+Disk+IO


--
Victor Y. Yegorov

Re: upsert example about postgres9.5

От
koff10
Дата:
Hi all,

It's known that postgres 9.5 has introduce  upsert  instruction.
Is somebody can show  me how to use this instruction ?
Do the UPSERT commande exists in  SQL ?

Thanks
koff

Re: upsert example about postgres9.5

От
"Gilberto Castillo"
Дата:
>
> Hi all,
>
> It's known that postgres 9.5 has introduce  upsert  instruction.
> Is somebody can show  me how to use this instruction ?
> Do the UPSERT commande exists in  SQL ?
>


insert into accesos_usuarios (usuarios, accesos)
values ('8kdata',1)
on conflict (usuarios)
do update set
accesos = accesos_usuarios.accesos + EXCLUDED.accesos;

insert into accesos_usuarios (usuarios, accesos)
values ('8kdata',1), ('8kdata2',1)
on conflict do nothing;

Tomado de un presentación de Alvaro Hernadez  En Cuba

--
Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba

Re: upsert example about postgres9.5

От
koff10
Дата:
Thanks for your reply.
So there is no Sql instruction like UPSERT !

koff.

Le 12/04/2016 23:23, Gilberto Castillo a écrit :
>> Hi all,
>>
>> It's known that postgres 9.5 has introduce  upsert  instruction.
>> Is somebody can show  me how to use this instruction ?
>> Do the UPSERT commande exists in  SQL ?
>>
>
> insert into accesos_usuarios (usuarios, accesos)
> values ('8kdata',1)
> on conflict (usuarios)
> do update set
> accesos = accesos_usuarios.accesos + EXCLUDED.accesos;
>
> insert into accesos_usuarios (usuarios, accesos)
> values ('8kdata',1), ('8kdata2',1)
> on conflict do nothing;
>
> Tomado de un presentación de Alvaro Hernadez  En Cuba
>