Re: R: DB on mSATA SSD - Mailing list pgsql-general

On 4/23/15 8:36 AM, 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?

Certainly disable atime updates if you haven't already.

Having a long checkpoint period is somewhat similar to running in a ram
disk and doing periodic backups. BTW, if you want to try using backups,
I recommend you setup actual PITR archiving to the SSD. That will write
data sequentially and in larger chunks, which should help the SSD better
deal with the writes. This will give you more control over how much data
you lose during a crash. Though, if all you do is a single large update
once a day you're probably better off just taking a backup right after
the update. I would also look at the backup size and recovery time of
pg_dump vs PITR or a filesystem snapshot; it could be significantly
smaller. It might take longer to restore though.

BTW, if you go the ramdisk route you should turn off fsync; there's no
point in the extra calls to the kernel. Only do that if the ENTIRE
database is in a ramdisk though.

> 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..

That depends on how much data has actually changed. If most of the data
in the table is changed then truncate and load will be your best bet.
OTOH if relatively little of the data has changed you'll probably get
much better results by loading the data into a loading table and then
updating changed data, deleting data that shouldn't be there anymore,
and inserting new data.

You definitely want the loading table to not be on SSD, and to be
unlogged. That means it needs to go in a tablespace on a ram disk. True
temporary tables (CREATE TEMP TABLE) are not logged, but I don't know
that that will work well with pg_bulk. You can use a real table with the
unlogged option to the same effect (though, I'm not sure if unlogged is
available in 8.4).

You also need to consider the indexes. First, make absolutely certain
you need all of them. Get rid of every one that isn't required. Second,
you need to test the amount of data that's written during an update with
the indexes in place *and doing a subsequent VACCUM* compared to
dropping all the indexes and re-creating them.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com