X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 459F19DC813 for ; Mon, 23 Jan 2006 05:55:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07982-02 for ; Mon, 23 Jan 2006 05:55:30 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from matrix.gatewaynet.com (host2.dynacom.ondsl.gr [62.103.35.210]) by postgresql.org (Postfix) with ESMTP id 4801E9DC806 for ; Mon, 23 Jan 2006 05:55:25 -0400 (AST) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id k0NAvCOp018832; Mon, 23 Jan 2006 12:57:12 +0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id k0NAvBHi018828; Mon, 23 Jan 2006 12:57:12 +0200 Date: Mon, 23 Jan 2006 12:57:11 +0200 (EET) From: Achilleus Mantzios To: ssinger@navtechinc.com cc: pgsql-sql@postgresql.org Subject: DBMirror.pl performance change Message-ID: MIME-Version: 1.0 Content-Type: MULTIPART/MIXED; BOUNDARY="333001285-1175207926-1138013831=:18744" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.084 required=5 tests=[AWL=0.084] X-Spam-Score: 0.084 X-Spam-Level: X-Archive-Number: 200601/143 X-Sequence-Number: 23932 This message is in MIME format. The first part should be readable text, while the remaining parts are likely unreadable without MIME-aware tools. Send mail to mime@docserver.cac.washington.edu for more info. --333001285-1175207926-1138013831=:18744 Content-Type: TEXT/PLAIN; charset=US-ASCII Steven Hi, i hope you are ok. I discovered a problem in DBMirror.pl, performance wise. pending.c stores data in a way very similar to the PgSQL input "\" escaped format. When the field is of type bytea, and the source of data is binary, then this produces 2 additional backslashes for every unprintable char. The performance in function extractData in DBMirror.pl, really suffers from this condition, since it breaks data in chunks of "\" delimited strings. Informally speaking, performance tends to be O(n) where n is the size of the data. This can be remedied if we break data in chunks of "'" rather than "\". "'" happens much more infrequently in common binary files (bz2, tiff, jpg, pdf etc..), and if we notice that odd number of contained "\", signals an intermidiate "'", whereas even number of "\" signals the final "'", then we can make this routine run much faster. I attach the new extractData function. Please feel free for any comments. -- -Achilleus --333001285-1175207926-1138013831=:18744 Content-Type: TEXT/PLAIN; charset=US-ASCII; name="extractData.pl" Content-Transfer-Encoding: BASE64 Content-ID: Content-Description: Content-Disposition: attachment; filename="extractData.pl" c3ViIGV4dHJhY3REYXRhKCQkKSB7DQogIG15ICRwZW5kaW5nUmVzdWx0ID0g JF9bMF07DQogIG15ICRjdXJyZW50VHVwbGUgPSAkX1sxXTsNCiAgbXkgJGZu dW1iZXI7DQogIG15ICV2YWx1ZXNIYXNoOw0KICAkZm51bWJlciA9IDQ7DQog IG15ICRkYXRhRmllbGQgPSAkcGVuZGluZ1Jlc3VsdC0+Z2V0dmFsdWUoJGN1 cnJlbnRUdXBsZSwkZm51bWJlcik7DQogIG15ICRudW1vZmJzOw0KDQogIHdo aWxlKGxlbmd0aCgkZGF0YUZpZWxkKT4wKSB7DQogICAgIyBFeHRyYWN0IHRo ZSBmaWVsZCBuYW1lIHRoYXQgaXMgc3Vycm9uZGVkIGJ5IGRvdWJsZSBxdW90 ZXMNCiAgICAkZGF0YUZpZWxkID1+IG0vKFwiLio/XCIpL3M7DQogICAgbXkg JGZpZWxkTmFtZSA9ICQxOw0KICAgICRkYXRhRmllbGQgPSBzdWJzdHIgJGRh dGFGaWVsZCAsbGVuZ3RoKCRmaWVsZE5hbWUpOw0KICAgICRmaWVsZE5hbWUg PX4gcy9cIi8vZzsgI1JlbW92ZSB0aGUgc3Vycm9uZGluZyAiIHNpZ25zLg0K DQogICAgaWYoJGRhdGFGaWVsZCA9fiBtLyhePSApL3MpIHsNCiAgICAgICNN YXRjaGVkIG51bGwNCgkkZGF0YUZpZWxkID0gc3Vic3RyICRkYXRhRmllbGQg LCBsZW5ndGgoJDEpOw0KICAgICAgJHZhbHVlc0hhc2h7JGZpZWxkTmFtZX09 dW5kZWY7DQogICAgfQ0KICAgIGVsc2lmICgkZGF0YUZpZWxkID1+IG0vKF49 XCcpL3MpIHsNCiAgICAgICNIYXMgZGF0YS4NCiAgICAgIG15ICR2YWx1ZTsN CiAgICAgICRkYXRhRmllbGQgPSBzdWJzdHIgJGRhdGFGaWVsZCAsMjsgI1Nr aXAgdGhlID0nDQogICAgTE9PUDogeyAgI1RoaXMgaXMgdG8gYWxsb3cgdXMg dG8gdXNlIGxhc3QgZnJvbSBhIGRvIGxvb3AuDQoJICAgICAjUmVjb21tZW5k ZWQgaW4gcGVybHN5biBtYW5wYWdlLg0KICAgICAgZG8gew0KCW15ICRtYXRj aFN0cmluZzsNCglteSAkbWF0Y2hTdHJpbmcyOw0KCSNGaW5kIHRoZSBzdWJz dHJpbmcgZW5kaW5nIHdpdGggdGhlIGZpcnN0ICcgb3IgZmlyc3QgXA0KCSRk YXRhRmllbGQgPX4gbS8oLio/W1wnXSk/L3M7IA0KCSRtYXRjaFN0cmluZyA9 ICQxOw0KDQoJJG51bW9mYnMgPSAoJG1hdGNoU3RyaW5nID1+IHRyL1xcLy8p ICUgMjsJDQoNCglpZiAoJG51bW9mYnMgPT0gMSkgeyAjLy8gb2RkIG51bWJl ciBvZiBcLCBpLmUuIGludGVybWVkaWF0ZSAnDQoJCSRtYXRjaFN0cmluZzIg PSBzdWJzdHIgJG1hdGNoU3RyaW5nLDAsIGxlbmd0aCgkbWF0Y2hTdHJpbmcp LTI7DQoJCSRtYXRjaFN0cmluZzIgPX4gcy9cXFxcL1xcL2c7DQoJCSR2YWx1 ZSAuPSAoJG1hdGNoU3RyaW5nMiAuICJcJyIpOw0KCQkkZGF0YUZpZWxkID0g c3Vic3RyICRkYXRhRmllbGQsbGVuZ3RoKCRtYXRjaFN0cmluZyk7DQoJfQ0K CWVsc2UgeyAjLy8gZXZlbiBudW1iZXIgb2YgXCwgaS5lLiBmb3VuZCBlbmQg b2YgZGF0YQ0KCQkkbWF0Y2hTdHJpbmcyID0gc3Vic3RyICRtYXRjaFN0cmlu ZywwLCBsZW5ndGgoJG1hdGNoU3RyaW5nKS0xOw0KCQkkbWF0Y2hTdHJpbmcy ID1+IHMvXFxcXC9cXC9nOw0KCQkkdmFsdWUgLj0gJG1hdGNoU3RyaW5nMjsN CgkJJGRhdGFGaWVsZCA9IHN1YnN0ciAkZGF0YUZpZWxkLGxlbmd0aCgkbWF0 Y2hTdHJpbmcpKzE7DQoJCWxhc3Q7DQoJfQ0KDQoJICAgDQogICAgICB9IHVu dGlsKGxlbmd0aCgkZGF0YUZpZWxkKT09MCk7DQogIH0NCiAgICAgICR2YWx1 ZXNIYXNoeyRmaWVsZE5hbWV9ID0gJHZhbHVlOw0KICAgICAgDQogICAgICAN CiAgICAgIH0jZWxzZSBpZiANCgkgIGVsc2Ugew0KCSAgICANCgkgICAgbG9n RXJyb3JNZXNzYWdlICJFcnJvciBpbiBQZW5kaW5nRGF0YSBTZXF1ZW5jZSBJ ZCAiIC4NCgkJJHBlbmRpbmdSZXN1bHQtPmdldHZhbHVlKCRjdXJyZW50VHVw bGUsMCk7DQoJICAgIGRpZTsNCgkgIH0NCiAgICANCiAgICANCiAgICANCiAg fSAjd2hpbGUNCiAgcmV0dXJuICV2YWx1ZXNIYXNoOw0KICAgIA0KfQ0K --333001285-1175207926-1138013831=:18744--