On Sat, Apr 12, 2008 at 11:00 PM, Dawid Kuroczko <qnex42@gmail.com> wrote:

>
> Not quite workable. Remember that table data is not always available on
> the block device -- there are pages modified in the buffer cache (shared
> memory), and other machines have no access to the other's shared memory
> (and it would be a lot of work to do it efficiently). Remember also about the
> MVCC -- if your "read only copy machine" starts a complicated query on
> some big_table, and in the meanwhile "read-write machine" decides the
> big_table's pages can be reused... well your "read-only" machine doesn't
> even have a way of knowing its returning garbage data. ;-)
>

I am not suggesting one read-write and many read-only architecture. I am
rather suggesting all read-only systems. I would be interested in this
setup if I run large read-only queries on historical data and need easy
scalability. With read-only setup, you can easily add another machine to
increase computing power. Also, we may come up with cache-sharing
systems so that if a buffer is cached on some other node, that can
be transfered on a high speed interconnect, rather than reading from a
relatively slower disk.

> Noow, if you really really want a read-only copy of the read write data
> available over the network, many NAS/SAN devices will allow you to
> make a snapshot of the database -- and you can use that snapshot as
> a read-only copy of the database. But then again, if you want a read-only
> copy of a days/weeks old database, there are chaper and better ways of
> doing it.
>
>

Yes. I was mostly assuming read-only scalability. What are the other
better ways to do so ?

>
> A known implementation of such a set up would be Oracle RAC, where
> you have a shared storage and N machines using it.
>

Oracle RAC is a multi-master kind of architecture where each node has
access to the shared storage and can directly read/write data.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Apr 12, 2008 at 8:11 PM, Pavan Deolasee
<pavan.deolasee@gmail.com> wrote:
> On Sat, Apr 12, 2008 at 11:00 PM, Dawid Kuroczko <qnex42@gmail.com> wrote:
> > Not quite workable. Remember that table data is not always available on
> > the block device -- there are pages modified in the buffer cache (shared
> > memory), and other machines have no access to the other's shared memory
> > (and it would be a lot of work to do it efficiently). Remember also about the
> > MVCC -- if your "read only copy machine" starts a complicated query on
> > some big_table, and in the meanwhile "read-write machine" decides the
> > big_table's pages can be reused... well your "read-only" machine doesn't
> > even have a way of knowing its returning garbage data. ;-)
> I am not suggesting one read-write and many read-only architecture. I am
> rather suggesting all read-only systems. I would be interested in this
> setup if I run large read-only queries on historical data and need easy
> scalability. With read-only setup, you can easily add another machine to
> increase computing power. Also, we may come up with cache-sharing
> systems so that if a buffer is cached on some other node, that can
> be transfered on a high speed interconnect, rather than reading from a
> relatively slower disk.

For example, it coulde be done by an ability to start a cleanly shutdown
database in read-only mode. I would see it as a very helpful companion
for a PITR recovery.

So in recover.conf you could say you want a recovery done until '10:00',
and then do some read-only queries, decide you want to recover until '10:15',
and so on until you find a place where someone did a big mistake.

Hmm, would be helpful to ask recovery process to create "write before logs",
i.e. logs which would allow you to 'roll back' whole recovery of the database
to '10:05', should you decide '10:15' is too late.

Possible TODO entry? ;-)

[...]
> Yes. I was mostly assuming read-only scalability. What are the other
> better ways to do so ?

I was thinking you were saying... Let's drop the issue, I misunderstood. :-)

Regards,
Dawid

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Pavan Deolasee wrote:
[...]
>>
>
> I am not suggesting one read-write and many read-only architecture. I am
> rather suggesting all read-only systems. I would be interested in this
> setup if I run large read-only queries on historical data and need easy
> scalability. With read-only setup, you can easily add another machine to
> increase computing power. Also, we may come up with cache-sharing
> systems so that if a buffer is cached on some other node, that can
> be transfered on a high speed interconnect, rather than reading from a
> relatively slower disk.
>
You can have infinite scalability of a read-only database simply by copying the
database to each system. If it's historical data it's not "up to the minute". If
you want to periodically update the read-only databases then that's pretty
straightforward - with various options trading speed against ease - depending on
your system requirements.

>>
>>
>
> Yes. I was mostly assuming read-only scalability. What are the other
> better ways to do so ?
>
>> A known implementation of such a set up would be Oracle RAC, where
>> you have a shared storage and N machines using it.
>>
>
> Oracle RAC is a multi-master kind of architecture where each node has
> access to the shared storage and can directly read/write data.
>
> Thanks,
> Pavan
>

"Pavan Deolasee" <pavan.deolasee@gmail.com> writes:

> I am not suggesting one read-write and many read-only architecture. I am
> rather suggesting all read-only systems. I would be interested in this
> setup if I run large read-only queries on historical data and need easy
> scalability. With read-only setup, you can easily add another machine to
> increase computing power.

That's especially attractive if it could be done on a table-by-table basis. So
for example, if you froze all the tuples in a table and marked it read-only
then another database could mount that table be able to perform queries on it.

There are problems around making sure that all the databases mounting the
table are the same architecture and agree about the meanings of all the type
oids.

And I'm not sure how you would ever undo the situation. You would need some
way to indicate the read-only table is "locked" by the other databases so the
master database would know it couldn't be made read-write until they "unlock"
it.

> On Sat, Apr 12, 2008 at 11:00 PM, Dawid Kuroczko <qnex42@gmail.com> wrote:

>> Noow, if you really really want a read-only copy of the read write data
>> available over the network, many NAS/SAN devices will allow you to
>> make a snapshot of the database -- and you can use that snapshot as
>> a read-only copy of the database. But then again, if you want a read-only
>> copy of a days/weeks old database, there are chaper and better ways of
>> doing it.

Actually that's a great way of doing it. I think we're going to run into this
"problem" more and more often, that there are good lower-level solutions to
the same problems we're tackling in the application.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general