Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 5B00A9F9F08 for ; Fri, 8 Dec 2006 21:24:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 43857-03 for ; Fri, 8 Dec 2006 21:24:10 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0102.google.com (nz-out-0506.google.com [64.233.162.235]) by postgresql.org (Postfix) with ESMTP id E15F19F9EED for ; Fri, 8 Dec 2006 21:24:14 -0400 (AST) Received: by nz-out-0102.google.com with SMTP id i11so585643nzh for ; Fri, 08 Dec 2006 17:24:13 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=A9eJtmx92sDUIVGIYmVcknE24wDctNXk0VLuRqSTm+8Q9So7GiSyfkcfZkciSCiYt+LQs2uOzUX3VQGLEiqw2hAcYcyiyCOe/9qEBI5vmUJRt4rvqejgQR/u7aiZjKEBZOAgbmZPjDJhTHUa31/YhmzmzU7ijog9YNN2dDtCBa0= Received: by 10.64.10.2 with SMTP id 2mr6807944qbj.1165627453391; Fri, 08 Dec 2006 17:24:13 -0800 (PST) Received: by 10.64.209.11 with HTTP; Fri, 8 Dec 2006 17:24:13 -0800 (PST) Message-ID: <5e66c6e90612081724m57293ffdy21d6b45697e69bd4@mail.gmail.com> Date: Sat, 9 Dec 2006 02:24:13 +0100 From: "Axel Waggershauser" To: pgsql-performance@postgresql.org Subject: Re: Low throughput of binary inserts from windows to linux In-Reply-To: <5e66c6e90612080417r23ecaea4p210905f868c26c7f@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_66889_2637840.1165627453074" References: <5e66c6e90612080417r23ecaea4p210905f868c26c7f@mail.gmail.com> X-Google-Sender-Auth: 07ba625d3eac44eb X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/188 X-Sequence-Number: 22244 ------=_Part_66889_2637840.1165627453074 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi, I have written my own 'large object'-like feature using the following table: ---- CREATE TABLE blob ( id bigint NOT NULL, pageno integer NOT NULL, data bytea, CONSTRAINT blob_pkey PRIMARY KEY (id, pageno) ) WITHOUT OIDS; ALTER TABLE blob ALTER COLUMN data SET STORAGE EXTERNAL; CREATE SEQUENCE seq_key_blob; ---- One blob consist of many rows, each containing one 'page'. I insert pages with PQexecPrepared with the format set to binary. This works quite well for the following setups: client -> server ----------------- linux -> linux linux -> windows windows -> windows but pretty bad (meaning about 10 times slower) for this setup windows -> linux The used postgresql versions are 8.1.5 for both operating system. A (sort of) minimal code sample exposing this problem may be found appended to this e-mail. Any ideas? Thanks, Axel ------=_Part_66889_2637840.1165627453074 Content-Type: text/x-csrc; name=my_lo.c; charset=ANSI_X3.4-1968 Content-Transfer-Encoding: base64 X-Attachment-Id: f_evgkd365 Content-Disposition: attachment; filename="my_lo.c" I2luY2x1ZGUgPHN0ZGlvLmg+DQojaW5jbHVkZSA8c3RkbGliLmg+DQoNCiNpbmNsdWRlIDxzeXMv dHlwZXMuaD4NCiNpbmNsdWRlIDxzeXMvc3RhdC5oPg0KI2luY2x1ZGUgPGZjbnRsLmg+DQoNCiNp ZmRlZiBXSU4zMg0KI2luY2x1ZGUgPGlvLmg+DQojaW5jbHVkZSA8d2luc29jazIuaD4NCiNlbHNl DQojaW5jbHVkZSA8dW5pc3RkLmg+DQojZW5kaWYNCg0KI2luY2x1ZGUgImxpYnBxLWZlLmgiDQoj aW5jbHVkZSAibGlicHEvbGlicHEtZnMuaCINCg0KI2RlZmluZSBCVUZTSVpFICgxMDI0KjY0KQ0K DQoNCnN0YXRpYyB2b2lkIGV4aXRfbmljZWx5KFBHY29ubiAqY29ubikNCnsNCglQUWZpbmlzaChj b25uKTsNCglleGl0KDEpOw0KfQ0KDQojZGVmaW5lIENIRUNLX1BHX05PX1JFUyguLi4pCQkJCQkJ CQkJCQkJCVwNCmRvewkJCQkJCQkJCQkJCQkJCQkJCQkJXA0KICAgICAgICBQR3Jlc3VsdCogcmVz ID0gX19WQV9BUkdTX187CQkJCQkJCQkJCQlcDQogICAgICAgIGlmKCAhcmVzIHx8IChQUXJlc3Vs dFN0YXR1cyhyZXMpICE9IFBHUkVTX1RVUExFU19PSwkJCQkJCVwNCiAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICYmIFBRcmVzdWx0U3RhdHVzKHJlcykgIT0gUEdSRVNfQ09NTUFORF9P SykgKQlcDQogICAgICAgIHsJCQkJCQkJCQkJCQkJCQkJCQlcDQogICAgICAgICAgICAgICAgcHJp bnRmKCIlcyBmYWlsZWQ6ICVzIiwgI19fVkFfQVJHU19fLCBQUWVycm9yTWVzc2FnZShjb25uKSk7 CVwNCiAgICAgICAgICAgICAgICBleGl0X25pY2VseSggY29ubiApOwkJCQkJCQkJCQkJXA0KICAg ICAgICB9CQkJCQkJCQkJCQkJCQkJCQkJXA0KICAgICAgICBQUWNsZWFyKCByZXMgKTsJCQkJCQkJ CQkJCQkJCQlcDQp9IHdoaWxlKDApDQoNCg0KI2RlZmluZSBDSEVDS19QR1JFUyguLi4pCQkJCQkJ CQkJCQkJCQlcDQpkbyB7CQkJCQkJCQkJCQkJCQkJCQkJCVwNCiAgICAgICAgcmVzID0gX19WQV9B UkdTX187CQkJCQkJCQkJCQkJCQlcDQogICAgICAgIGlmKCAhcmVzIHx8IChQUXJlc3VsdFN0YXR1 cyhyZXMpICE9IFBHUkVTX1RVUExFU19PSwkJCQkJCVwNCiAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICYmIFBRcmVzdWx0U3RhdHVzKHJlcykgIT0gUEdSRVNfQ09NTUFORF9PSykgKQlc DQogICAgICAgIHsJCQkJCQkJCQkJCQkJCQkJCQlcDQogICAgICAgICAgICAgICAgcHJpbnRmKCIl cyBmYWlsZWQ6ICVzIiwgI19fVkFfQVJHU19fLCBQUWVycm9yTWVzc2FnZShjb25uKSk7CVwNCiAg ICAgICAgICAgICAgICBleGl0X25pY2VseSggY29ubiApOwkJCQkJCQkJCQkJXA0KICAgICAgICB9 CQkJCQkJCQkJCQkJCQkJCQkJXA0KfSB3aGlsZSgwKQ0KDQoNCi8qDQogKiB1cGxvYWQgMTAgTUIg ZHVtbXkgZGF0YSwgcmV0dXJuIGJsb2ItaWQNCiAqLw0Kc3RhdGljIGludCB1cGxvYWQoUEdjb25u ICpjb25uKQ0Kew0KCWNoYXIgYnVmW0JVRlNJWkVdOw0KCWludCBpZDsNCglpbnQgaWRfbjsgLy8g bmV0d29yayBieXRlb3JkZXIgb2YgaWQNCglpbnQgcGFnZW5vOw0KCWludCBwYWdlbm9fbjsNCglp bnQgTiA9IDEwKjEwMjQqMTAyNC9CVUZTSVpFOw0KDQoJY29uc3QgY2hhciogdmFsdWVzW10gPSB7 IChjb25zdCBjaGFyKikmaWRfbiwgKGNvbnN0IGNoYXIqKSZwYWdlbm9fbiwgYnVmIH07DQoJY29u c3QgaW50IGZvcm1hdHNbXSA9IHsxLCAxLCAxfTsNCgljb25zdCBpbnQgbGVuZ3Roc1tdID0ge3Np emVvZihpbnQpLCBzaXplb2YoaW50KSwgQlVGU0laRX07DQoNCgkvKg0KCSAqIGdldCAoZnJlZSkg YmxvYiBpZA0KCSAqLw0KDQoJUEdyZXN1bHQqIHJlczsNCglDSEVDS19QR1JFUyggUFFleGVjKCBj b25uLCAic2VsZWN0IG5leHR2YWwoJ3NlcV9rZXlfYmxvYicpIiApICk7DQoJaWQgPSBhdG9pKCBQ UWdldHZhbHVlKHJlcywgMCwgMCkgKTsNCglQUWNsZWFyKCByZXMgKTsNCg0KCWlkX24gPSBodG9u bChpZCk7DQoNCgkvKg0KCSAqIHByZXBhcmUgaW5zZXJ0IHN0YXRlbWVudA0KCSAqLw0KCQ0KCUNI RUNLX1BHX05PX1JFUyggUFFwcmVwYXJlKCBjb25uLCAiIiwgImluc2VydCBpbnRvIGJsb2IgKGlk LCBwYWdlbm8sIGRhdGEpICINCgkJCQkJCQkJInZhbHVlcyAoJDE6OmludDQsICQyOjppbnQ0LCAk Mzo6Ynl0ZWEpOyIsDQoJCQkJCQkJCTAsIDAgKSApOw0KDQoJcHJpbnRmKCJpbnNlcnRpbmcgJWQg cGFnZXMgKGVhY2ggJWQga0IpOiAiLCBOLCBCVUZTSVpFLzEwMjQpOw0KDQoJLyoNCgkgKiB1cGxv YWQgcGFnZXMNCgkgKi8NCglmb3IoIHBhZ2VubyA9IDA7IHBhZ2VubyA8IE47ICsrcGFnZW5vICkN Cgl7DQoJCXBhZ2Vub19uID0gaHRvbmwocGFnZW5vKTsNCg0KCQlDSEVDS19QR19OT19SRVMoDQoJ CQlQUWV4ZWNQcmVwYXJlZCggY29ubiwgIiIsIDMsIHZhbHVlcywgbGVuZ3RocywgZm9ybWF0cywg MSApICk7DQoNCgkJcHJpbnRmKCIuIik7DQoJCWZmbHVzaChzdGRvdXQpOw0KCX0NCg0KCXByaW50 ZigiXG4iKTsNCgkNCglyZXR1cm4gaWQ7DQp9DQoNCi8qDQogKiBkb3dubG9hZCBibG9iIHdpdGgg Z2l2ZW4gaWQNCiAqLw0Kc3RhdGljIHZvaWQgZG93bmxvYWQoUEdjb25uICpjb25uLCBpbnQgaWQp DQp7DQoJY2hhciBidWZbQlVGU0laRV07DQoJaW50IGlkX24gPSBodG9ubChpZCk7IC8vIG5ldHdv cmsgYnl0ZW9yZGVyIG9mIGlkDQoJaW50IHBhZ2VubzsNCglpbnQgcGFnZW5vX247DQoNCgljb25z dCBjaGFyKiB2YWx1ZXNbXSA9IHsgKGNvbnN0IGNoYXIqKSZpZF9uLCAoY29uc3QgY2hhciopJnBh Z2Vub19uIH07DQoJY29uc3QgaW50IGxlbmd0aHNbXSA9IHtzaXplb2YoaW50KSwgc2l6ZW9mKGlu dCl9Ow0KCWNvbnN0IGludCBmb3JtYXRzW10gPSB7MSwgMX07DQoNCg0KCS8qDQoJICogZGV0ZXJt aW5lIHNpemUgb2YgYmxvYg0KCSAqLw0KDQoJUEdyZXN1bHQqIHJlczsNCglDSEVDS19QR1JFUygg UFFleGVjUGFyYW1zKCBjb25uLCAic2VsZWN0IGNvdW50KGlkKSBmcm9tIGJsb2Igd2hlcmUgaWQg PSAkMTo6aW50NDsiLA0KCQkJCQkJCSAgIDEsIDAgLyp0eXBlKi8sIHZhbHVlcywgbGVuZ3Rocywg Zm9ybWF0cywgMCApICk7DQoJaW50IHBhZ2VzID0gYXRvaSggUFFnZXR2YWx1ZShyZXMsIDAsIDAp ICk7DQoJUFFjbGVhciggcmVzICk7DQoNCglwcmludGYoInNlbGVjdGluZyAlZCBwYWdlcyAoZWFj aCAlZCBrQik6ICIsIHBhZ2VzLCBCVUZTSVpFLzEwMjQpOw0KCQ0KCS8qDQoJICogcHJlcGFyZSBz ZWxlY3Qgc3RhdGVtZW50DQoJICovDQoNCglDSEVDS19QR19OT19SRVMoIFBRcHJlcGFyZSggY29u biwgIiIsICJzZWxlY3QgZGF0YSBmcm9tIGJsb2IgIg0KCQkJCQkJCQkid2hlcmUgaWQgPSAkMTo6 aW50NCBhbmQgcGFnZW5vID0gJDI6OmludDQ7IiwNCgkJCQkJCQkJMCwgMCApICk7DQoNCg0KCS8q DQoJICogZG93bmxvYWQgcGFnZXMNCgkgKi8NCg0KCWZvciggcGFnZW5vID0gMDsgcGFnZW5vIDwg cGFnZXM7ICsrcGFnZW5vICkNCgl7DQoJCXBhZ2Vub19uID0gaHRvbmwocGFnZW5vKTsNCg0KCQlD SEVDS19QR1JFUygNCgkJCVBRZXhlY1ByZXBhcmVkKCBjb25uLCAiIiwgMiwgdmFsdWVzLCBsZW5n dGhzLCBmb3JtYXRzLCAxICkgKTsNCg0KDQoJCS8qDQoJCSAqIHdlIGFyZSBub3QgaW50ZXJlc3Rl ZCBpbiB0aGUgYWN0dWFsIGRhdGEgaW4gdGhpcyBkZW1vDQoJCSAqCW4gPSBQUWdldGxlbmd0aCgg cmVzLCAwLCAwICk7DQoJCSAqICB2ID0gUFFnZXR2YWx1ZShyZXMsIDAsIDApOw0KCQkgKi8NCg0K CQlQUWNsZWFyKCByZXMgKTsNCg0KCQlwcmludGYoIi4iKTsNCgkJZmZsdXNoKHN0ZG91dCk7DQoJ fQ0KDQoJcHJpbnRmKCJcbiIpOw0KDQoJcmV0dXJuOw0KfQ0KDQoNCmludCBtYWluKGludCBhcmdj LCBjaGFyICoqYXJndikNCnsNCglpbnQgaWQ7DQoJUEdjb25uICpjb25uOw0KCVBHcmVzdWx0ICpy ZXM7DQoNCglpZiAoYXJnYyA8IDUpDQoJew0KCQlmcHJpbnRmKHN0ZGVyciwgIlVzYWdlOiAlcyBo b3N0IGRhdGFiYXNlIHVzZXIgcGFzc3dvcmRcbiIsIGFyZ3ZbMF0pOw0KCQlleGl0KDEpOw0KCX0N Cg0KCS8qDQoJICogc2V0IHVwIHRoZSBjb25uZWN0aW9uDQoJICovDQoJY29ubiA9IFBRc2V0ZGJM b2dpbihhcmd2WzFdLCAiNTQzMiIsIE5VTEwsIE5VTEwsIGFyZ3ZbMl0sIGFyZ3ZbM10sIGFyZ3Zb NF0pOw0KDQoJLyogY2hlY2sgdG8gc2VlIHRoYXQgdGhlIGJhY2tlbmQgY29ubmVjdGlvbiB3YXMg c3VjY2Vzc2Z1bGx5IG1hZGUgKi8NCglpZiAoUFFzdGF0dXMoY29ubikgIT0gQ09OTkVDVElPTl9P SykNCgl7DQoJCWZwcmludGYoc3RkZXJyLCAiQ29ubmVjdGlvbiB0byBkYXRhYmFzZSBmYWlsZWQ6 ICVzIiwNCgkJCQlQUWVycm9yTWVzc2FnZShjb25uKSk7DQoJCWV4aXRfbmljZWx5KGNvbm4pOw0K CX0NCg0KCWlkID0gdXBsb2FkKCBjb25uICk7DQoNCglkb3dubG9hZCggY29ubiwgaWQgKTsNCg0K DQoJUFFmaW5pc2goY29ubik7DQoNCglyZXR1cm4gMDsNCn0NCg== ------=_Part_66889_2637840.1165627453074--