Re: Database restore speed

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, 2 Dec 2005, Luke Lonergan wrote:

Stephen,

On 12/2/05 12:18 PM, "Stephen Frost" <sfrost@xxxxxxxxxxx> wrote:

Just a thought, but couldn't psql be made to use the binary mode of
libpq and do at least some of the conversion on the client side?  Or
does binary mode not work with copy (that wouldn't suprise me, but
perhaps copy could be made to support it)?

Yes - I think this idea is implicit in what David suggested, and my response
as well.  The problem is that the way the client does conversions can
potentially differ from the way the backend does.  Some of the types in
Postgres are machine intrinsic and the encoding conversions use on-machine
libraries, each of which preclude the use of client conversion methods
(without a lot of restructuring).  We'd tackled this problem in the past and
concluded that the parse / convert stage really belongs in the backend.

I'll bet this parsing cost varys greatly with the data types used, I'm also willing to bet that for the data types that hae different encoding on different systems there could be a intermediate encoding that is far faster to parse then ASCII text is.

for example, (and I know nothing about the data storage itself so this is just an example), if the issue was storing numeric values on big endian and little endian systems (and 32 bit vs 64 bit systems to end up with 4 ways of holding the data) you have a substantial cost in parseing the ASCII and converting it to a binary value, but the client can't (and shouldn't) know which endian type and word size the server is. but it could create a big endian multi-precision encoding that would then be very cheap for the server to split and flip as nessasary. yes this means more work is done overall, but it's split between different machines, and the binary representation of the data will reduce probably your network traffic as a side effect.

and for things like date which get parsed in multiple ways until one is found that seems sane, there's a significant amount of work that the server could avoid.

David Lang

The other thought, of course, is that you could use PITR for your
backups instead of pgdump...

Totally - great idea, if this is actually a backup / restore then PITR plus
filesystem copy (tarball) is hugely faster than dump / restore.

- Luke



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux