X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 18409DA97D for ; Sat, 22 Oct 2005 18:15:25 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94450-07 for ; Sat, 22 Oct 2005 21:15:22 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.192]) by svr1.postgresql.org (Postfix) with ESMTP id 140FADAA68 for ; Sat, 22 Oct 2005 18:15:22 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id 8so412989nzo for ; Sat, 22 Oct 2005 14:15:25 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=qmjPTTtJWE3Ouye540qkamDyi6hNGIn1nOZBK3c0LY0SV3J6hN248PpCVAdR8Taq97acscqJm9g0cczT9P3FQ/E7AsnMkcNcsCNmijkMJNZ7AFRdEoc1HdL4F5+cCCkelpSZZXTJkWTTLAd0FM3Kd03HhyMu4NY5ICV4N/9ijvc= Received: by 10.36.140.13 with SMTP id n13mr1929083nzd; Sat, 22 Oct 2005 14:15:25 -0700 (PDT) Received: by 10.36.61.1 with HTTP; Sat, 22 Oct 2005 14:15:25 -0700 (PDT) Message-ID: <42567e060510221415r2516523l4c3f9b42dee91a09@mail.gmail.com> Date: Sun, 23 Oct 2005 02:45:25 +0530 From: Kishore B To: pgsql-performance@postgresql.org Subject: Need help in setting optimal configuration for a huge database. MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_20960_98211.1130015725464" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.307 required=5 tests=[AWL=0.187, HTML_50_60=0.095, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200510/305 X-Sequence-Number: 15073 ------=_Part_20960_98211.1130015725464 Content-Type: multipart/alternative; boundary="----=_Part_20961_6465372.1130015725464" ------=_Part_20961_6465372.1130015725464 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi All, I am Kishore doing freelance development of J2EE applications. We switched to use Postgresql recently because of the advantages it has over other commercial databases. All went well untill recently, untill we began working on an application that needs to maintain a huge database. I am describing the problem we are facing below. Can you please take a loo= k at the case, and help me in configuring the PostgreSQL. We have only two tables, one of which contains 97% of the data and the other table which contains 2.8% of the data. All other contain only the remaining 0.2% of data and are designed to support these two big tables. Currently we have 9 million of records in the first table and 0.2 million o= f records in the second table. We need to insert into the bigger table almost for every second , through out the life time. In addition, we receive at least 200,000 records a day a= t a fixed time. We are facing a* critical situation because of the performance of the ** database**.* Even a basic query like select count(*) from bigger_table is taking about 4 minutes to return. The following is the system configuration. Database *:* Postgresql 7.3 OS : Redhat Linux Processor : Athlon, Memory : 2 GB We are expecting that at least 200 active connections need to be maintained through out the day. Can any you please suggest the best configuration to satisfy the above requirements? Thanks in advance. Thank you, Kishore. ------=_Part_20961_6465372.1130015725464 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

Hi All,

 

I am Kishore doing freelance development of J= 2EE applications.

 

We switched to use Postgresql recently becaus= e of the advantages it has over other commercial databases. All went well u= ntill recently, untill we began working on an application that needs to mai= ntain  a huge database.=20

 

I am describing the problem we are facing bel= ow. Can you please take a look at the case, and help me in configuring the = PostgreSQL.

 

We have only two tables, one of which contain= s 97% of the data and the other table which contains 2.8% of the data. All = other contain only the remaining=20 0.2% of data and are designed to support these two big tables. Currently we= have 9 million of records in the first table and 0.2 million of records in= the second table.

We need to insert into the bigger table almost fo= r every second , through out the life time. In addition, we receive at leas= t 200,000 records a day at a fixed time.

We are facing a critical= situation because of the performance of the databaseDatabase :
Postgresql 7.3
OS : Redhat Linux
Processor : Athlon= ,
Memory : 2 GB

We are expecting that at least 200 active connect= ions need to be maintained through out the day.

Can any yo= u please suggest the best configuration to satisfy the above requirements?

Thanks in advance.

 

Thank  you,

Kishore.

 

------=_Part_20961_6465372.1130015725464-- ------=_Part_20960_98211.1130015725464 Content-Type: application/octet-stream; name="postgresql.conf" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="postgresql.conf" Iw0KIyBQb3N0Z3JlU1FMIGNvbmZpZ3VyYXRpb24gZmlsZQ0KIyAtLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLQ0KIw0KIyBUaGlzIGZpbGUgY29uc2lzdHMgb2YgbGluZXMgb2YgdGhlIGZvcm06 DQojDQojICAgbmFtZSA9IHZhbHVlDQojDQojIChUaGUgJz0nIGlzIG9wdGlvbmFsLikgV2hpdGUg c3BhY2UgbWF5IGJlIHVzZWQuIENvbW1lbnRzIGFyZSBpbnRyb2R1Y2VkDQojIHdpdGggJyMnIGFu eXdoZXJlIG9uIGEgbGluZS4gVGhlIGNvbXBsZXRlIGxpc3Qgb2Ygb3B0aW9uIG5hbWVzIGFuZA0K IyBhbGxvd2VkIHZhbHVlcyBjYW4gYmUgZm91bmQgaW4gdGhlIFBvc3RncmVTUUwgZG9jdW1lbnRh dGlvbi4gVGhlDQojIGNvbW1lbnRlZC1vdXQgc2V0dGluZ3Mgc2hvd24gaW4gdGhpcyBmaWxlIHJl cHJlc2VudCB0aGUgZGVmYXVsdCB2YWx1ZXMuDQojDQojIEFueSBvcHRpb24gY2FuIGFsc28gYmUg Z2l2ZW4gYXMgYSBjb21tYW5kIGxpbmUgc3dpdGNoIHRvIHRoZQ0KIyBwb3N0bWFzdGVyLCBlLmcu ICdwb3N0bWFzdGVyIC1jIGxvZ19jb25uZWN0aW9ucz1vbicuIFNvbWUgb3B0aW9ucw0KIyBjYW4g YmUgY2hhbmdlZCBhdCBydW4tdGltZSB3aXRoIHRoZSAnU0VUJyBTUUwgY29tbWFuZC4NCiMNCiMg VGhpcyBmaWxlIGlzIHJlYWQgb24gcG9zdG1hc3RlciBzdGFydHVwIGFuZCB3aGVuIHRoZSBwb3N0 bWFzdGVyDQojIHJlY2VpdmVzIGEgU0lHSFVQLiBJZiB5b3UgZWRpdCB0aGUgZmlsZSBvbiBhIHJ1 bm5pbmcgc3lzdGVtLCB5b3UgaGF2ZSANCiMgdG8gU0lHSFVQIHRoZSBwb3N0bWFzdGVyIGZvciB0 aGUgY2hhbmdlcyB0byB0YWtlIGVmZmVjdCwgb3IgdXNlIA0KIyAicGdfY3RsIHJlbG9hZCIuDQoN Cg0KIz09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09 PT09PT09PT09PT09PT09PT09PQ0KDQoNCiMNCiMJQ29ubmVjdGlvbiBQYXJhbWV0ZXJzDQojDQp0 Y3BpcF9zb2NrZXQgPXRydWUNCiNzc2wgPSBmYWxzZQ0KDQptYXhfY29ubmVjdGlvbnMgPTUwMA0K I3N1cGVydXNlcl9yZXNlcnZlZF9jb25uZWN0aW9ucyA9IDINCg0KcG9ydCA9NTIyMyANCiNob3N0 bmFtZV9sb29rdXAgPSBmYWxzZQ0KI3Nob3dfc291cmNlX3BvcnQgPSBmYWxzZQ0KDQojdW5peF9z b2NrZXRfZGlyZWN0b3J5ID0gJycNCiN1bml4X3NvY2tldF9ncm91cCA9ICcnDQojdW5peF9zb2Nr ZXRfcGVybWlzc2lvbnMgPSAwNzc3CSMgb2N0YWwNCg0KI3ZpcnR1YWxfaG9zdCA9ICcnDQoNCiNr cmJfc2VydmVyX2tleWZpbGUgPSAnJw0KDQoNCiMNCiMJU2hhcmVkIE1lbW9yeSBTaXplDQojDQpz aGFyZWRfYnVmZmVycyA9NDgwMDAgICAgICAjIG1pbiBtYXhfY29ubmVjdGlvbnMqMiBvciAxNiwg OEtCIGVhY2gNCiNtYXhfZnNtX3JlbGF0aW9ucyA9IDEwMDAJIyBtaW4gMTAsIGZzbSBpcyBmcmVl IHNwYWNlIG1hcCwgfjQwIGJ5dGVzDQojbWF4X2ZzbV9wYWdlcyA9IDEwMDAwCQkjIG1pbiAxMDAw LCBmc20gaXMgZnJlZSBzcGFjZSBtYXAsIH42IGJ5dGVzDQojbWF4X2xvY2tzX3Blcl90cmFuc2Fj dGlvbiA9IDY0CSMgbWluIDEwDQojd2FsX2J1ZmZlcnMgPSA4CQkjIG1pbiA0LCB0eXBpY2FsbHkg OEtCIGVhY2gNCg0KIw0KIwlOb24tc2hhcmVkIE1lbW9yeSBTaXplcw0KIw0Kc29ydF9tZW0gPTIw NDgJCSMgbWluIDY0LCBzaXplIGluIEtCDQojdmFjdXVtX21lbSA9IDgxOTIJCSMgbWluIDEwMjQs IHNpemUgaW4gS0INCg0KDQojDQojCVdyaXRlLWFoZWFkIGxvZyAoV0FMKQ0KIw0KI2NoZWNrcG9p bnRfc2VnbWVudHMgPSAzCSMgaW4gbG9nZmlsZSBzZWdtZW50cywgbWluIDEsIDE2TUIgZWFjaA0K I2NoZWNrcG9pbnRfdGltZW91dCA9IDMwMAkjIHJhbmdlIDMwLTM2MDAsIGluIHNlY29uZHMNCiMN CiNjb21taXRfZGVsYXkgPSAwCQkjIHJhbmdlIDAtMTAwMDAwLCBpbiBtaWNyb3NlY29uZHMNCiNj b21taXRfc2libGluZ3MgPSA1CQkjIHJhbmdlIDEtMTAwMA0KIw0KZnN5bmMgPWZhbHNlDQojd2Fs X3N5bmNfbWV0aG9kID0gZnN5bmMJIyB0aGUgZGVmYXVsdCB2YXJpZXMgYWNyb3NzIHBsYXRmb3Jt czoNCiMJCQkJIyBmc3luYywgZmRhdGFzeW5jLCBvcGVuX3N5bmMsIG9yIG9wZW5fZGF0YXN5bmMN CiN3YWxfZGVidWcgPSAwCQkJIyByYW5nZSAwLTE2DQoNCg0KIw0KIwlPcHRpbWl6ZXIgUGFyYW1l dGVycw0KIw0KI2VuYWJsZV9zZXFzY2FuID0gdHJ1ZQ0KI2VuYWJsZV9pbmRleHNjYW4gPSB0cnVl DQojZW5hYmxlX3RpZHNjYW4gPSB0cnVlDQojZW5hYmxlX3NvcnQgPSB0cnVlDQojZW5hYmxlX25l c3Rsb29wID0gdHJ1ZQ0KI2VuYWJsZV9tZXJnZWpvaW4gPSB0cnVlDQojZW5hYmxlX2hhc2hqb2lu ID0gdHJ1ZQ0KDQplZmZlY3RpdmVfY2FjaGVfc2l6ZSA9MTAwMDAwCSMgdHlwaWNhbGx5IDhLQiBl YWNoDQpyYW5kb21fcGFnZV9jb3N0ID0gMwkJIyB1bml0cyBhcmUgb25lIHNlcXVlbnRpYWwgcGFn ZSBmZXRjaCBjb3N0DQojcmFuZG9tX3BhZ2VfY29zdCA9IDQJCSMgdW5pdHMgYXJlIG9uZSBzZXF1 ZW50aWFsIHBhZ2UgZmV0Y2ggY29zdA0KI2NwdV90dXBsZV9jb3N0ID0gMC4wMQkJIyAoc2FtZSkN CiNjcHVfaW5kZXhfdHVwbGVfY29zdCA9IDAuMDAxCSMgKHNhbWUpDQojY3B1X29wZXJhdG9yX2Nv c3QgPSAwLjAwMjUJIyAoc2FtZSkNCg0KI2Zyb21fY29sbGFwc2VfbGltaXQgPSA4DQojam9pbl9j b2xsYXBzZV9saW1pdCA9IDgJIyAxIGRpc2FibGVzIGNvbGxhcHNpbmcgb2YgZXhwbGljaXQgSk9J TnMNCg0KI2RlZmF1bHRfc3RhdGlzdGljc190YXJnZXQgPSAxMAkjIHJhbmdlIDEtMTAwMA0KDQoj DQojCUdFUU8gT3B0aW1pemVyIFBhcmFtZXRlcnMNCiMNCiNnZXFvID0gdHJ1ZQ0KI2dlcW9fc2Vs ZWN0aW9uX2JpYXMgPSAyLjAJIyByYW5nZSAxLjUtMi4wDQojZ2Vxb190aHJlc2hvbGQgPSAxMQ0K I2dlcW9fcG9vbF9zaXplID0gMAkJIyBkZWZhdWx0IGJhc2VkIG9uIHRhYmxlcyBpbiBzdGF0ZW1l bnQsIA0KCQkJCSMgcmFuZ2UgMTI4LTEwMjQNCiNnZXFvX2VmZm9ydCA9IDENCiNnZXFvX2dlbmVy YXRpb25zID0gMA0KI2dlcW9fcmFuZG9tX3NlZWQgPSAtMQkJIyBhdXRvLWNvbXB1dGUgc2VlZA0K DQoNCiMNCiMJTWVzc2FnZSBkaXNwbGF5DQojDQojc2VydmVyX21pbl9tZXNzYWdlcyA9IG5vdGlj ZQkjIFZhbHVlcywgaW4gb3JkZXIgb2YgZGVjcmVhc2luZyBkZXRhaWw6DQoJCQkJIyAgIGRlYnVn NSwgZGVidWc0LCBkZWJ1ZzMsIGRlYnVnMiwgZGVidWcxLA0KCQkJCSMgICBpbmZvLCBub3RpY2Us IHdhcm5pbmcsIGVycm9yLCBsb2csIGZhdGFsLA0KCQkJCSMgICBwYW5pYw0KI2NsaWVudF9taW5f bWVzc2FnZXMgPSBub3RpY2UJIyBWYWx1ZXMsIGluIG9yZGVyIG9mIGRlY3JlYXNpbmcgZGV0YWls Og0KCQkJCSMgICBkZWJ1ZzUsIGRlYnVnNCwgZGVidWczLCBkZWJ1ZzIsIGRlYnVnMSwNCgkJCQkj ICAgbG9nLCBpbmZvLCBub3RpY2UsIHdhcm5pbmcsIGVycm9yDQojc2lsZW50X21vZGUgPSBmYWxz ZQ0KDQojbG9nX2Nvbm5lY3Rpb25zID0gZmFsc2UNCiNsb2dfcGlkID0gZmFsc2UNCiNsb2dfc3Rh dGVtZW50ID0gZmFsc2UNCiNsb2dfZHVyYXRpb24gPSBmYWxzZQ0KI2xvZ190aW1lc3RhbXAgPSBm YWxzZQ0KDQojbG9nX21pbl9lcnJvcl9zdGF0ZW1lbnQgPSBwYW5pYyAjIFZhbHVlcyBpbiBvcmRl ciBvZiBpbmNyZWFzaW5nIHNldmVyaXR5Og0KCQkJCSAjICAgZGVidWc1LCBkZWJ1ZzQsIGRlYnVn MywgZGVidWcyLCBkZWJ1ZzEsDQoJCQkJICMgICBpbmZvLCBub3RpY2UsIHdhcm5pbmcsIGVycm9y LCBwYW5pYyhvZmYpDQoNCiNkZWJ1Z19wcmludF9wYXJzZSA9IGZhbHNlDQojZGVidWdfcHJpbnRf cmV3cml0dGVuID0gZmFsc2UNCiNkZWJ1Z19wcmludF9wbGFuID0gZmFsc2UNCiNkZWJ1Z19wcmV0 dHlfcHJpbnQgPSBmYWxzZQ0KDQojZXhwbGFpbl9wcmV0dHlfcHJpbnQgPSB0cnVlDQoNCiMgcmVx dWlyZXMgVVNFX0FTU0VSVF9DSEVDS0lORw0KI2RlYnVnX2Fzc2VydGlvbnMgPSB0cnVlDQoNCg0K Iw0KIwlTeXNsb2cNCiMNCiNzeXNsb2cgPSAwCQkJIyByYW5nZSAwLTINCiNzeXNsb2dfZmFjaWxp dHkgPSAnTE9DQUwwJw0KI3N5c2xvZ19pZGVudCA9ICdwb3N0Z3JlcycNCg0KDQojDQojCVN0YXRp c3RpY3MNCiMNCiNzaG93X3BhcnNlcl9zdGF0cyA9IGZhbHNlDQojc2hvd19wbGFubmVyX3N0YXRz ID0gZmFsc2UNCiNzaG93X2V4ZWN1dG9yX3N0YXRzID0gZmFsc2UNCiNzaG93X3N0YXRlbWVudF9z dGF0cyA9IGZhbHNlDQoNCiMgcmVxdWlyZXMgQlRSRUVfQlVJTERfU1RBVFMNCiNzaG93X2J0cmVl X2J1aWxkX3N0YXRzID0gZmFsc2UNCg0KDQojDQojCUFjY2VzcyBzdGF0aXN0aWNzIGNvbGxlY3Rp b24NCiMNCiNzdGF0c19zdGFydF9jb2xsZWN0b3IgPSB0cnVlDQojc3RhdHNfcmVzZXRfb25fc2Vy dmVyX3N0YXJ0ID0gdHJ1ZQ0KI3N0YXRzX2NvbW1hbmRfc3RyaW5nID0gZmFsc2UNCiNzdGF0c19y b3dfbGV2ZWwgPSBmYWxzZQ0KI3N0YXRzX2Jsb2NrX2xldmVsID0gZmFsc2UNCg0KDQojDQojCUxv Y2sgVHJhY2luZw0KIw0KI3RyYWNlX25vdGlmeSA9IGZhbHNlDQoNCiMgcmVxdWlyZXMgTE9DS19E RUJVRw0KI3RyYWNlX2xvY2tzID0gZmFsc2UNCiN0cmFjZV91c2VybG9ja3MgPSBmYWxzZQ0KI3Ry YWNlX2x3bG9ja3MgPSBmYWxzZQ0KI2RlYnVnX2RlYWRsb2NrcyA9IGZhbHNlDQojdHJhY2VfbG9j a19vaWRtaW4gPSAxNjM4NA0KI3RyYWNlX2xvY2tfdGFibGUgPSAwDQoNCg0KIw0KIwlNaXNjDQoj DQojYXV0b2NvbW1pdCA9IHRydWUNCiNkeW5hbWljX2xpYnJhcnlfcGF0aCA9ICckbGliZGlyJw0K I3NlYXJjaF9wYXRoID0gJyR1c2VyLHB1YmxpYycNCiNkYXRlc3R5bGUgPSAnaXNvLCB1cycNCiN0 aW1lem9uZSA9IHVua25vd24JCSMgYWN0dWFsbHksIGRlZmF1bHRzIHRvIFRaIGVudmlyb25tZW50 IHNldHRpbmcNCiNhdXN0cmFsaWFuX3RpbWV6b25lcyA9IGZhbHNlDQojY2xpZW50X2VuY29kaW5n ID0gc3FsX2FzY2lpCSMgYWN0dWFsbHksIGRlZmF1bHRzIHRvIGRhdGFiYXNlIGVuY29kaW5nDQoj YXV0aGVudGljYXRpb25fdGltZW91dCA9IDYwCSMgMS02MDAsIGluIHNlY29uZHMNCiNkZWFkbG9j a190aW1lb3V0ID0gMTAwMAkjIGluIG1pbGxpc2Vjb25kcw0KI2RlZmF1bHRfdHJhbnNhY3Rpb25f aXNvbGF0aW9uID0gJ3JlYWQgY29tbWl0dGVkJw0KI21heF9leHByX2RlcHRoID0gMTAwMDAJCSMg bWluIDEwDQojbWF4X2ZpbGVzX3Blcl9wcm9jZXNzID0gMTAwMAkjIG1pbiAyNQ0KI3Bhc3N3b3Jk X2VuY3J5cHRpb24gPSB0cnVlDQojc3FsX2luaGVyaXRhbmNlID0gdHJ1ZQ0KI3RyYW5zZm9ybV9u dWxsX2VxdWFscyA9IGZhbHNlDQojc3RhdGVtZW50X3RpbWVvdXQgPSAwCQkjIDAgaXMgZGlzYWJs ZWQsIGluIG1pbGxpc2Vjb25kcw0KI2RiX3VzZXJfbmFtZXNwYWNlID0gZmFsc2UNCiANCg0KDQoj DQojCUxvY2FsZSBzZXR0aW5ncw0KIw0KIyAoaW5pdGlhbGl6ZWQgYnkgaW5pdGRiIC0tIG1heSBi ZSBjaGFuZ2VkKQ0KTENfTUVTU0FHRVMgPSAnZW5fVVMnDQpMQ19NT05FVEFSWSA9ICdlbl9VUycN CkxDX05VTUVSSUMgPSAnZW5fVVMnDQpMQ19USU1FID0gJ2VuX1VTJw0K ------=_Part_20960_98211.1130015725464--