Thread: TPC-H Scaling Factors X PostgreSQL Cluster Command

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.

Nelson Kotowski wrote:
> 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.

Cluster will completely rewrite the table and indexes. On step 2, you
should only create the indexes you're clustering on, and create the rest
of them after clustering.

Or even better, generate and load the data in the right order to start
with, so you don't need to cluster at all.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Hi Heikki,

Thanks for answering! :)

 I don't get how creating only the indexes i cluster on would improve my cluster command perfomance. I believed that all other indexes wouldn't interfere because so far they're created in a fashionable time and they don't refer to any field/column in the orders/lineitem table. Could you explain me again?

As for the load, when you say the right order to start, you mean i should order the load file by the index field in the table before loading it?

Thanks in advance,
Nelson P Kotowski Filho.

>>> On Mon, Apr 23, 2007 at 10:52 AM, in message
<d34b24380704230852p2fe52a05qbd7397a4293ce5a9@mail.gmail.com>, "Nelson
Kotowski" <nkotowski@gmail.com> wrote:
>
>  I don't get how creating only the indexes i cluster on would improve my
> cluster command perfomance. I believed that all other indexes wouldn't
> interfere because so far they're created in a fashionable time and they
> don't refer to any field/column in the orders/lineitem table. Could you
> explain me again?

What a CLUSTER command does is to read through the table in the sequence specified by the index (using the index) and
copythe data into a new copy of the table.  It then applies all of the permissions, constraints, etc. from the original
tableto the copy and builds all the same indexes as were on the original table.  (You can't use the same indexes,
becausethe data is shifted around to new spots.)  The new copy of the table then takes the place of the original.  If
youbuild indexes and then cluster, you throw away the results of the work from the original build, and do it all over
again.

> As for the load, when you say the right order to start, you mean i should
> order the load file by the index field in the table before loading it?

If you load the rows in the same order that the index would read them during the cluster, there is no need to cluster
andno benefit from doing so. 

-Kevin

On Sat, 21 Apr 2007, Nelson Kotowski wrote:

> 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.

Have you looked in the database log files for messages?  Unless you
changed some other parameters from the defaults that you didn't mention,
I'd expect you've got a constant series of "checkpoint occuring too
frequently" errors in there, which would be a huge slowdown on your index
rebuild.  Slowdowns from checkpoints would get worse with an increase of
shared_buffers, as you report.

The default setting for checkpoint_segments of 3 is extremely low for even
a 1GB database.  Try increasing that to 30, restart the server, and
rebuild the index to see how much the 1GB case speeds up.  If it's
significantly faster (it should be), try the 5GB one again.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Greg Smith wrote:
> On Sat, 21 Apr 2007, Nelson Kotowski wrote:
>
>> 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.
>
> Have you looked in the database log files for messages?  Unless you
> changed some other parameters from the defaults that you didn't mention,
> I'd expect you've got a constant series of "checkpoint occuring too
> frequently" errors in there, which would be a huge slowdown on your
> index rebuild.  Slowdowns from checkpoints would get worse with an
> increase of shared_buffers, as you report.

Index builds don't write WAL, unless archive_command has been set. A
higher shared_buffers setting can hurt index build performance, but for
a different reason: the memory spent on shared_buffers can't be used for
sorting and caching the sort tapes.

> The default setting for checkpoint_segments of 3 is extremely low for
> even a 1GB database.  Try increasing that to 30, restart the server, and
> rebuild the index to see how much the 1GB case speeds up.  If it's
> significantly faster (it should be), try the 5GB one again.

A good advice, but it's unlikely to make a difference at load time.

BTW: With CVS HEAD, if you create the table in the same transaction (or
TRUNCATE) as you load the data, the COPY will skip writing WAL which can
give a nice speedup.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com