pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTPC-H Scaling Factors X PostgreSQL Cluster Command
6+ messages / 4 participants
[nested] [flat]
* TPC-H Scaling Factors X PostgreSQL Cluster Command
@ 2007-04-21 14:54 Nelson Kotowski <nkotowski@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Nelson Kotowski @ 2007-04-21 14:54 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TPC-H Scaling Factors X PostgreSQL Cluster Command
@ 2007-04-23 09:46 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Nelson Kotowski <nkotowski@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Heikki Linnakangas @ 2007-04-23 09:46 UTC (permalink / raw)
To: Nelson Kotowski <nkotowski@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TPC-H Scaling Factors X PostgreSQL Cluster Command
@ 2007-04-23 15:52 Nelson Kotowski <nkotowski@gmail.com>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 6+ messages in thread
From: Nelson Kotowski @ 2007-04-23 15:52 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: pgsql-performance
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 4/23/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote:
>
> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TPC-H Scaling Factors X PostgreSQL Cluster Command
@ 2007-04-23 18:26 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Nelson Kotowski <nkotowski@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2007-04-23 18:26 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; Nelson Kotowski <nkotowski@gmail.com>; +Cc: pgsql-performance
>>> 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 copy the data into a new copy of the table. It then applies all of the permissions, constraints, etc. from the original table to the copy and builds all the same indexes as were on the original table. (You can't use the same indexes, because the data is shifted around to new spots.) The new copy of the table then takes the place of the original. If you build 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 and no benefit from doing so.
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TPC-H Scaling Factors X PostgreSQL Cluster Command
@ 2007-04-24 03:39 Greg Smith <gsmith@gregsmith.com>
parent: Nelson Kotowski <nkotowski@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Greg Smith @ 2007-04-24 03:39 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: TPC-H Scaling Factors X PostgreSQL Cluster Command
@ 2007-04-24 08:52 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 0 replies; 6+ messages in thread
From: Heikki Linnakangas @ 2007-04-24 08:52 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-04-24 08:52 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-21 14:54 TPC-H Scaling Factors X PostgreSQL Cluster Command Nelson Kotowski <nkotowski@gmail.com>
2007-04-23 09:46 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-04-23 15:52 ` Nelson Kotowski <nkotowski@gmail.com>
2007-04-23 18:26 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-04-24 03:39 ` Greg Smith <gsmith@gregsmith.com>
2007-04-24 08:52 ` Heikki Linnakangas <heikki@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox