On 2/22/16 8:40 AM, Moreno Andreo wrote:
Il 18/02/2016 21:33, Jim Nasby ha scritto:
Depending on your needs, could could use synchronous replication as
part of that setup. You can even do that at a per-transaction level,
so maybe you use sync rep most of the time, and just turn it off when
inserting or updating BLOBS.
This sounds good, and when everything is OK we have I/O operation split
across the two servers; a small delay in synchronizing blobs should not
be a big deal, even if something bad happens (because of XLOG), right?
It all depends on what you can tolerate. You also don't have to use
synchronous replication; normal streaming replication is async, so if
you can stand to lose some data if one of the servers dies then you can
do that.
Last thing: should blobs (or the whole database directory itself) go in
a different partition, to optimize performance, or in VM environment
this is not a concern anymore?
First: IMO concerns about blobs in the database are almost always
overblown.
In many places I've been they say, at last, "BLOBs are slow". So I
considered this as another point to analyze while designing server
architecture. If you say "don't mind", then I won't.
It all depends. They're certainly a lot slower than handling a single
int, but in many cases the difference just doesn't matter.
30GB of blobs on modern hardware really isn't a big deal, and there's
a *lot* to be said for not having to write the extra code to manage
all that by hand.
What do you mean? Extra code?
If the blob is in the database then you have nothing extra to do. It's
handled just like all your other data.
If it's a file in a file system then you need to:
- Have application code that knows how and where to get at the file
- Have a way to make those files available on all your webservers
- Have completely separate backup and recovery plans for those files
That's a lot of extra work. Sometimes it's necessary, but many times
it's not.
When it comes to your disk layout, the first things I'd look at would be:
- Move the temporary statistics directory to a RAM disk
- Move pg_xlog to it's own partition
So I need another vDisk, not that big, for pg_xlog?
Yeah, but note that with virtualization that may or may not help.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
