Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 8FCA29FB4C8 for ; Sat, 21 Apr 2007 11:54:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 16496-03 for ; Sat, 21 Apr 2007 11:54:37 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.240]) by postgresql.org (Postfix) with ESMTP id D4D1A9FB40D for ; Sat, 21 Apr 2007 11:54:43 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so1327640ana for ; Sat, 21 Apr 2007 07:54:42 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=ZYQT7giUTpQIoWwvf43ohsq6uPtdyzIOr/WDCCYsCmRejMzCxv2qY4RUSi1UIVFca23VQyqgfJ0O/dimKWKrUzbyX7tfRJMLlja68j6IhDV/K+GkDVSKfiP79detojiyML1iL5kSCZGZ28s4QJhsGTLEdIzV9dkLqRTMNPXfWyU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=ND8kRCw9sojzXk1VqaP7ExDhaT4Zc7+VVUoTqUl1IpiOx6MW+RvvufU+PCt+CtBQ7YuYLlNoOp/PizUiO8YhzJ0mWXBhXAy/QWqx6THbznMayg5g70oTMB1VCUmCbnC/yVKHkkBWC8bakI8a/hNDLyibuCc4+XRTWuiqSfWAndw= Received: by 10.100.143.1 with SMTP id q1mr2399108and.1177167282330; Sat, 21 Apr 2007 07:54:42 -0700 (PDT) Received: by 10.100.12.6 with HTTP; Sat, 21 Apr 2007 07:54:42 -0700 (PDT) Message-ID: Date: Sat, 21 Apr 2007 11:54:42 -0300 From: "Nelson Kotowski" To: pgsql-performance@postgresql.org Subject: TPC-H Scaling Factors X PostgreSQL Cluster Command MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_162885_6122048.1177167282179" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/357 X-Sequence-Number: 24234 ------=_Part_162885_6122048.1177167282179 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello everyone, This is my first post in here, i am in need of some help... Wel, i am running PostgreSQL 8.2.4, in a single node, in this machine: Dell PowerEdge 1950 Intel Xeon 2.33 (2 CPUs) 4GB RAM Sata HD 160GB Debian Distribution Some relevant parameters: In Linux: -> SHMMAX: 32MB In postgresql.conf: -> shared_buffers: 24MB -> maintenance_work_mem: 16MB I need to: build, load and do some optimization procedures in a TPC-H benchmark database. So far, i need to do it in three different scale factors (1, 2 and 5GB databases). My build process comprehends creating the tables without any foreign keys, indexes, etc. - Running OK! Then, i load the data from the flat files generated through DBGEN software into these tables. - Running OK! Finally, i run a "optimize" script that does the following: - Alter the tables to add the mandatory foreign keys; - Create all mandatory indexes; - Cluster the orders table by the orders table index; - Cluster the lineitem table by the lineitem table index; - Vacuum the database; - Analyze statistics. This is the step which is causing me some headaches, mainly related to the 5GB database. I identified that the cluster command over the lineitem table (cluster idx_lineitem on lineitem) is the responsible. I got to this conclusion because when i run it in the 1GB and 2GB database i am able to complete this script in 10 and 30 minutes each. But when i run this command over the 5GB database, it simply seems as it won't end. I watched it running over 12 hours and nothing happened. To investigate a bit, i tried to tune these parameters and these parameters only, and re-run the script (rebooted the machine and restarted it all over): In Linux: SHMMAX -> Tuned it to 2GB via echo "..." > /proc/sys/kernel/shmmax In postgresql.conf: shared_buffers: 512MB maintenance_work_mem: 800MB I thought that this might improve the performance, but as a matter of fact, that's what happened: 1 GB database - cluster command time remains the same (more or less 10 minutes) 2 GB database - cluster command now takes 3 hours instead of 30 minutes! BAD 5 GB database - still can't complete the command in over 12 hours. To add some info, i did a top command on the machine, i saw that the postmaster consumes all the "shared_buffers" configured in the physical memory (13,3% of the RAM --> 512MB of 4GB) but the total free mem is 0% (all the 4GB is used, but not by the postmaster), and no swap is in use, CPU is around 1% busy (this 1% is for the postmaster), and this machine is dedicate, for my personal use, and there's nothing else running but PostgreSQL. Does anyone have any clues? Thanks in advance, Nelson P Kotowski Filho. ------=_Part_162885_6122048.1177167282179 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello everyone,

This is my first post in here, i am in need of some help...

Wel, i am running PostgreSQL 8.2.4, in a single node, in this machine:

Dell PowerEdge 1950
Intel Xeon 2.33 (2 CPUs)
4GB RAM
Sata HD 160GB
Debian Distribution

Some relevant parameters:

In Linux:
  -> SHMMAX: 32MB
In postgresql.conf:
  -> shared_buffers: 24MB
  -> maintenance_work_mem: 16MB

I need to: build, load and do some optimization procedures in a TPC-H benchmark database.

So far, i need to do it in three different scale factors (1, 2 and 5GB databases).

My build process comprehends creating the tables without any foreign keys, indexes, etc. - Running OK!
Then, i load the data from the flat files generated through DBGEN software into these tables. - Running OK!

Finally, i run a "optimize" script that does the following:

- Alter the tables to add the mandatory foreign keys;
- Create all mandatory indexes;
- Cluster the orders table by the orders table index;
- Cluster the lineitem table by the lineitem table index;
- Vacuum the database;
- Analyze statistics.

This is the step which is causing me some headaches, mainly related to the 5GB database. I identified that the cluster command over the lineitem table (cluster idx_lineitem on lineitem) is the responsible. I got to this conclusion because when i run it in the 1GB and 2GB database i am able to complete this script in 10 and 30 minutes each. But when i run this command over the 5GB database, it simply seems as it won't end. I watched it running over 12 hours and nothing happened.

To investigate a bit, i tried to tune these parameters and these parameters only, and re-run the script (rebooted the machine and restarted it all over):

In Linux: SHMMAX -> Tuned it to 2GB via echo "..." > /proc/sys/kernel/shmmax

In postgresql.conf:

shared_buffers: 512MB
maintenance_work_mem: 800MB

I thought that this might improve the performance, but as a matter of fact, that's what happened:

1 GB database - cluster command time remains the same (more or less 10 minutes)
2 GB database - cluster command now takes 3 hours instead of 30 minutes! BAD
5 GB database - still can't complete the command in over 12 hours.

To add some info, i did a top command on the machine, i saw that the postmaster consumes all the "shared_buffers" configured in the physical memory (13,3% of the RAM --> 512MB of 4GB) but the total free mem is 0% (all the 4GB is used, but not by the postmaster), and no swap is in use, CPU is around 1% busy (this 1% is for the postmaster), and this machine is dedicate, for my personal use, and there's nothing else running but PostgreSQL.

Does anyone have any clues?

Thanks in advance,
Nelson P Kotowski Filho.

------=_Part_162885_6122048.1177167282179--