Re: R: DB on mSATA SSD

On 04/23/15 15:36, Job wrote:
> Hello, thank you first of all for your wonder help!
>
> Tomas, regarding:
>
>> There are ways to make the writes less frequent, both at the database
>> and OS level. We don't know what's your PostgreSQL config, but making
>> the checkpoints less frequent and tuning the kernel/mount options may
>> help a lot.
>
> We can raise up checkpoints, at kernel-side which options do you
> suggest to tune for minimize disk writing?

You may make the pdflush configuration less aggressive, but that may not
help with your workload. You should use TRIM (of fstrim regularly), move
/tmp into a tmpfs and don't put swap on the SSD.

> We have a table, about 500Mb, that is updated and written every day.
> When machines updates, table is truncated and then re-populated with
> pg_bulk. But i think we strongly writes when importing new data tables..

In that case the checkpoint optimizations or kernel tuning probably
won't help much. But if you can easily recreate the database, and it
fits into RAM, then you can just place it into a tmpfs.

> Here is why we tought putting some tables into ramdrive...

Well, technically you can do that, but don't expect the database to work
after a crash or a reboot. You might keep a snapshot of the database
(e.g. using pg_basebackup), and use it to 'seed' the database after a
server restart.

But don't expect the database to start without a tablespace that got
lost because of being placed in a tmpfs or something.

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services