random observations while testing with a 1,8B row table

Stefan,

On 3/10/06 9:40 AM, "Stefan Kaltenbrunner" <stefan@kaltenbrunner.cc> wrote:

I will summarize some of the just in case somebody is interested:

I am!

-> table used has 5 integer columns non-indexed during the loads
-> hardware is a Dual Opteron 280 with 4 cores@2,4GHz and 16GB RAM, data
is on a multipathed (busy) SAN with different (RAID 10) Arrays for WAL
and data.

How many connections out of the machine? How many disks behind each LUN?

So - about 20 Bytes per row (5*4) unless those are int8, but on disk it's
108GB/1.8B = 60 Bytes per row on disk. I wonder what all that overhead is?

1. data loading - I'm using COPY with batches of 300M rows it takes

*) with one copy running it takes about 20minutes/batch to load the data
(~250k rows/sec) and virtually no context switches.

*) with two copys running concurrently it takes a bit less then 30
minutes/batch and a steady 40k context switches/sec (~340k rows/sec overall)

*) with three copy it takes about 40min/batch at 140k context
switches/sec (380k rows/sec overall)

So, from 15 MB/s up to about 20 MB/s.

while the amount of IO going on is quite a lot it looks like we are
still mostly CPU-bound for COPY.

It's what we see almost always. In this case if your I/O configuration is
capable of performing at about 3x the 20MB/s max parsing rate, or 60MB/s,
you will be CPU limited.

The 3x is approximate, and based on observations, the reasoning underneath
it is that Postgres is writing the data several times, once to the WAL, then
from the WAL to the heap files.

2. updating all of the rows in the table:

I updated all of the rows in the table with a simple UPDATE testtable
set a=a+1;
this took about 2,5 hours (~200rows/sec)

Ugh. This is where Bizgres MPP shines, I'll try to recreate your test and
post results. This scales linearly in Bizgres MPP with the number of disks
and CPUs available, but I would hope for much more than that.

3. vacuuming this table - it turned out that VACUUM FULL is completly
unusable on a table(which i actually expected before) of this size not
only to the locking involved but rather due to a gigantic memory
requirement and unbelievable slowness.

Simple vacuum should be enough IMO.

- Luke

Luke Lonergan wrote:

Stefan,

On 3/10/06 9:40 AM, "Stefan Kaltenbrunner" <stefan@kaltenbrunner.cc> wrote:

I will summarize some of the just in case somebody is interested:

I am!

heh - not surprised :-)

-> table used has 5 integer columns non-indexed during the loads
-> hardware is a Dual Opteron 280 with 4 cores@2,4GHz and 16GB RAM, data
is on a multipathed (busy) SAN with different (RAID 10) Arrays for WAL
and data.

How many connections out of the machine? How many disks behind each LUN?

2 HBAs in the server, 2x2 possible paths to each LUN.
6 disks for the WAL and 12 disks for the data

So - about 20 Bytes per row (5*4) unless those are int8, but on disk it's
108GB/1.8B = 60 Bytes per row on disk. I wonder what all that overhead is?

1. data loading - I'm using COPY with batches of 300M rows it takes

*) with one copy running it takes about 20minutes/batch to load the data
(~250k rows/sec) and virtually no context switches.

*) with two copys running concurrently it takes a bit less then 30
minutes/batch and a steady 40k context switches/sec (~340k rows/sec overall)

*) with three copy it takes about 40min/batch at 140k context
switches/sec (380k rows/sec overall)

So, from 15 MB/s up to about 20 MB/s.

while the amount of IO going on is quite a lot it looks like we are
still mostly CPU-bound for COPY.

It's what we see almost always. In this case if your I/O configuration is
capable of performing at about 3x the 20MB/s max parsing rate, or 60MB/s,
you will be CPU limited.

the IO-System I use should be capable of doing that if pushed hard
enough :-)

The 3x is approximate, and based on observations, the reasoning underneath
it is that Postgres is writing the data several times, once to the WAL, then
from the WAL to the heap files.

2. updating all of the rows in the table:

I updated all of the rows in the table with a simple UPDATE testtable
set a=a+1;
this took about 2,5 hours (~200rows/sec)

Ugh. This is where Bizgres MPP shines, I'll try to recreate your test and
post results. This scales linearly in Bizgres MPP with the number of disks
and CPUs available, but I would hope for much more than that.

interesting to know, but still I'm testing/playing with postgresql here
not bizgres MPP ...

3. vacuuming this table - it turned out that VACUUM FULL is completly
unusable on a table(which i actually expected before) of this size not
only to the locking involved but rather due to a gigantic memory
requirement and unbelievable slowness.

Simple vacuum should be enough IMO.

sure, that was mostly meant as an experiment, if I had to do this on a
production database I would most likely use CLUSTER to get the desired
effect (which in my case was purely getting back the diskspace wasted by
dead tuples)

Stefan

Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:

3. vacuuming this table - it turned out that VACUUM FULL is completly
unusable on a table(which i actually expected before) of this size not
only to the locking involved but rather due to a gigantic memory
requirement and unbelievable slowness.

sure, that was mostly meant as an experiment, if I had to do this on a
production database I would most likely use CLUSTER to get the desired
effect (which in my case was purely getting back the diskspace wasted by
dead tuples)

Yeah, the VACUUM FULL algorithm is really designed for situations where
just a fraction of the rows have to be moved to re-compact the table.
It might be interesting to teach it to abandon that plan and go to a
CLUSTER-like table rewrite once the percentage of dead space is seen to
reach some suitable level. CLUSTER has its own disadvantages though
(2X peak disk space usage, doesn't work on core catalogs, etc).

regards, tom lane

Stefan,

On 3/10/06 11:48 AM, "Stefan Kaltenbrunner" <stefan@kaltenbrunner.cc> wrote:

2 HBAs in the server, 2x2 possible paths to each LUN.
6 disks for the WAL and 12 disks for the data

So - you have 18 disks worth of potential bandwidth, not factoring loss due
to RAID. That's roughly 18 * 60 = 1,080 MB/s. If we organized that into
four banks, one for each CPU and made each one RAID5 and left two disks for
spares, you'd have 12 disks working for you at 720MB/s, which is possibly
double the number of active FC channels you have, unless they are all
active, in which case you have a nicely matched 800MB/s of FC.

So, from 15 MB/s up to about 20 MB/s.

Gee - seems a long distance from 700 MB/s potential :-)

the IO-System I use should be capable of doing that if pushed hard
enough :-)

I would expect some 10x this if configured well.

interesting to know, but still I'm testing/playing with postgresql here
not bizgres MPP ...

Sure. Still, what I'd expect is something like 10x this update rate using
the parallelism buried in your hardware.

If you configure the same machine with 4 Bizgres MPP segments running on 4
LUNs I think you'd be shocked at the speedups.

- Luke

On Mar 10, 2006, at 11:54 AM, Tom Lane wrote:

Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:

3. vacuuming this table - it turned out that VACUUM FULL is
completly
unusable on a table(which i actually expected before) of this
size not
only to the locking involved but rather due to a gigantic memory
requirement and unbelievable slowness.

sure, that was mostly meant as an experiment, if I had to do this
on a
production database I would most likely use CLUSTER to get the
desired
effect (which in my case was purely getting back the diskspace
wasted by
dead tuples)

Yeah, the VACUUM FULL algorithm is really designed for situations
where
just a fraction of the rows have to be moved to re-compact the table.
It might be interesting to teach it to abandon that plan and go to a
CLUSTER-like table rewrite once the percentage of dead space is
seen to
reach some suitable level. CLUSTER has its own disadvantages though
(2X peak disk space usage, doesn't work on core catalogs, etc).

I get bitten by this quite often (customer machines, one giant table,
purge out a lot of old data).

CLUSTER is great for that, given the headroom, though I've often
resorted to a dump and restore because I've not had the headroom
for cluster, and it's a lot less downtime than a full vacuum.

While the right fix there is to redo the application engine side to use
table partitioning, I keep wondering whether it would be possible
to move rows near the end of the table to the beginning in one, non-
locking
phase (vacuum to populate FSM with free space near beginning of table,
touch rows starting at end of table, repeat) and then finish off with a
vacuum full to tidy up the remainder and truncate the files (or a
simpler
"lock the table and truncate anything unused at the end").

Cheers,
Steve

Luke Lonergan wrote:

Stefan,

On 3/10/06 11:48 AM, "Stefan Kaltenbrunner" <stefan@kaltenbrunner.cc> wrote:

2 HBAs in the server, 2x2 possible paths to each LUN.
6 disks for the WAL and 12 disks for the data

So - you have 18 disks worth of potential bandwidth, not factoring loss due
to RAID. That's roughly 18 * 60 = 1,080 MB/s. If we organized that into
four banks, one for each CPU and made each one RAID5 and left two disks for
spares, you'd have 12 disks working for you at 720MB/s, which is possibly
double the number of active FC channels you have, unless they are all
active, in which case you have a nicely matched 800MB/s of FC.

wrong(or rather extremely optimistic) the array itself only has two
(redundant) FC-loops(@2GB )to the attached expansion chassis. The array
has 2 active/active controllers (with a failover penalty) with two host
interfaces each, furthermore it has write-cache mirroring(to the standby
controller) enabled which means the traffic has to go over the internal
FC-loop too.
beside that the host(as I said) itself only has two HBAs @2GB which are
configured for failover which limits the hosts maximum available
bandwith to less than 200MB/S per LUN.

So, from 15 MB/s up to about 20 MB/s.

Gee - seems a long distance from 700 MB/s potential :-)

well the array is capable of about 110MB/s write per controller head (a
bit more half the possible due to write mirroring enabled which uses
delta-syncronisation).
WAL and data are on different controllers though by default.

the IO-System I use should be capable of doing that if pushed hard
enough :-)

I would expect some 10x this if configured well.

see above ...

interesting to know, but still I'm testing/playing with postgresql here
not bizgres MPP ...

Sure. Still, what I'd expect is something like 10x this update rate using
the parallelism buried in your hardware.

If you configure the same machine with 4 Bizgres MPP segments running on 4
LUNs I think you'd be shocked at the speedups.

that might be true, though it might sound a bit harsh I really prefer to
spend the small amount of spare time I have with testing(and helping to
improve if possible) postgresql than playing with a piece of commercial
software I'm not going to use anyway ...

Stefan

Stefan,

On 3/10/06 12:23 PM, "Stefan Kaltenbrunner" <stefan@kaltenbrunner.cc> wrote:

wrong(or rather extremely optimistic) the array itself only has two
(redundant) FC-loops(@2GB )to the attached expansion chassis. The array
has 2 active/active controllers (with a failover penalty) with two host
interfaces each, furthermore it has write-cache mirroring(to the standby
controller) enabled which means the traffic has to go over the internal
FC-loop too.
beside that the host(as I said) itself only has two HBAs @2GB which are
configured for failover which limits the hosts maximum available
bandwith to less than 200MB/S per LUN.

Wow - the ickiness of SAN fro a performance / value standpoint never ceases
to astound me.

Gee - seems a long distance from 700 MB/s potential :-)

well the array is capable of about 110MB/s write per controller head (a
bit more half the possible due to write mirroring enabled which uses
delta-syncronisation).
WAL and data are on different controllers though by default.

So - you're getting 20MB/s on loading from a potential of 200MB/s?

I would expect some 10x this if configured well.

see above ...

OTOH - configured well could include taking the disks out of the smart (?)
chassis, plugging them into a dumb chassis and deploying 2 dual channel U320
SCSI adapters - total cost of about $3,000.

that might be true, though it might sound a bit harsh I really prefer to
spend the small amount of spare time I have with testing(and helping to
improve if possible) postgresql than playing with a piece of commercial
software I'm not going to use anyway ...

No problem - that's our job anyway - to make the case for Postgres' use in
typical large scale use-cases like the one you describe.

- Luke

Luke Lonergan wrote:

Stefan,

On 3/10/06 12:23 PM, "Stefan Kaltenbrunner" <stefan@kaltenbrunner.cc> wrote:

wrong(or rather extremely optimistic) the array itself only has two
(redundant) FC-loops(@2GB )to the attached expansion chassis. The array
has 2 active/active controllers (with a failover penalty) with two host
interfaces each, furthermore it has write-cache mirroring(to the standby
controller) enabled which means the traffic has to go over the internal
FC-loop too.
beside that the host(as I said) itself only has two HBAs @2GB which are
configured for failover which limits the hosts maximum available
bandwith to less than 200MB/S per LUN.

Wow - the ickiness of SAN fro a performance / value standpoint never ceases
to astound me.

Well while make it sound a bit like that, performance is not everything.
One has to factor manageability,scalability (in terms of future upgrades
using the same platform and such) and high-availability features in too.
With that in mind a SAN (or a NAS - depends on the actual usecases)
suddenly looks much more interesting than plain old DASD.

Gee - seems a long distance from 700 MB/s potential :-)

well the array is capable of about 110MB/s write per controller head (a
bit more half the possible due to write mirroring enabled which uses
delta-syncronisation).
WAL and data are on different controllers though by default.

So - you're getting 20MB/s on loading from a potential of 200MB/s?

no - I can write 110MB/s on thw WAL LUN and 110MB/s on the other LUN
concurrently.

I would expect some 10x this if configured well.

see above ...

OTOH - configured well could include taking the disks out of the smart (?)
chassis, plugging them into a dumb chassis and deploying 2 dual channel U320
SCSI adapters - total cost of about $3,000.

as i said above even if that would work (it does not because the disks
have FC-connectors) I would loose a LOT of features like being able to
use the SAN for more than a single host (big one!) or doing
firmware-upgrades without downtime, using SAN-replication, having
cable-length exceeding 12m(makes it possible to place parts of the
infrastructure at remote sites),out-of-band management,scriptable(!),...

Beside that, sequential-io as you are propagating everywhere is NOT the
holy grail or the sole solution to a fast database.
While the SAN above really is not a screamer for that kind of
application it is actually a very good performer(compared with some of
the DASD based boxes) under heavy random-io and concurrent load.
This has a direct measurable influence on the overall speed of our
production applications which are mostly OLTP ;-)

that might be true, though it might sound a bit harsh I really prefer to
spend the small amount of spare time I have with testing(and helping to
improve if possible) postgresql than playing with a piece of commercial
software I'm not going to use anyway ...

No problem - that's our job anyway - to make the case for Postgres' use in
typical large scale use-cases like the one you describe.

yep

Stefan

Stefan,

On 3/11/06 12:21 AM, "Stefan Kaltenbrunner" <stefan@kaltenbrunner.cc> wrote:

So - you're getting 20MB/s on loading from a potential of 200MB/s?

no - I can write 110MB/s on thw WAL LUN and 110MB/s on the other LUN
concurrently.

The numbers you published earlier show you are getting a maximum of 20MB/s
on data loading. It's CPU limited by Postgres COPY.

Beside that, sequential-io as you are propagating everywhere is NOT the
holy grail or the sole solution to a fast database.
While the SAN above really is not a screamer for that kind of
application it is actually a very good performer(compared with some of
the DASD based boxes) under heavy random-io and concurrent load.
This has a direct measurable influence on the overall speed of our
production applications which are mostly OLTP ;-)

The same DASD can be configured with RAID10 and will far surpass the
external FC SAN configuration you describe at the same price.

The DASD story is not all about sequential I/O. The main limitation is the
number of devices you can make available using DASD, and that's a Postgres
limitation that we solve.

For OLTP apps, you may be fast enough with the limited bandwidth of your FC
SAN, but it would still be faster with hundreds of channels and disks.

- Luke

Tom Lane wrote:

Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:

3. vacuuming this table - it turned out that VACUUM FULL is completly
unusable on a table(which i actually expected before) of this size not
only to the locking involved but rather due to a gigantic memory
requirement and unbelievable slowness.

sure, that was mostly meant as an experiment, if I had to do this on a
production database I would most likely use CLUSTER to get the desired
effect (which in my case was purely getting back the diskspace wasted by
dead tuples)

Yeah, the VACUUM FULL algorithm is really designed for situations where
just a fraction of the rows have to be moved to re-compact the table.
It might be interesting to teach it to abandon that plan and go to a
CLUSTER-like table rewrite once the percentage of dead space is seen to
reach some suitable level. CLUSTER has its own disadvantages though
(2X peak disk space usage, doesn't work on core catalogs, etc).

hmm very interesting idea, I for myself like it but from what i have
seen people quite often use vacuum full to get their disk usage down
_because_ they are running low on space (and because it's not that well
known that CLUSTER could be much faster) - maybe we should add a
note/hint about this to the maintenance/vacuum docs at least ?

Stefan

Ühel kenal päeval, R, 2006-03-10 kell 12:23, kirjutas Steve Atkins:

I get bitten by this quite often (customer machines, one giant table,
purge out a lot of old data).

CLUSTER is great for that, given the headroom, though I've often
resorted to a dump and restore because I've not had the headroom
for cluster, and it's a lot less downtime than a full vacuum.

While the right fix there is to redo the application engine side to use
table partitioning, I keep wondering whether it would be possible
to move rows near the end of the table to the beginning in one, non-
locking
phase (vacuum to populate FSM with free space near beginning of table,
touch rows starting at end of table, repeat) and then finish off with a
vacuum full to tidy up the remainder and truncate the files (or a
simpler
"lock the table and truncate anything unused at the end").

At some point I had to compress a very busily updated table. I used the
following approach:

1) VACUUM buzytable; (lazy not full)

2) SELECT primary_key_value, ctid FROM buzytable;

3) Extract N last records from there and for each keep repeating

3A) UPDATE buzytable
SET primary_key_value = primary_key_value
WHERE primary_key_value = extracted_value

3B) SELECT ctid FROM buzytable
WHERE primary_key_value = extracted_value

until the tuple is moved to another pages, hopefully nearer to
the beginning of table

repeat from 1) until the page for last row (extracted from ctid) is
smaller than some thresold.

This was the only way I was able to get a table back to small enough
size without service interruption.

--------------
Hannu

Hannu Krosing <hannu@skype.net> writes:

At some point I had to compress a very busily updated table. I used the
following approach:
[ move a few rows at a time ]

We could possibly do something similar with VACUUM FULL as well: once
maintenance_work_mem is filled, start discarding per-page data to stay
under the memory limit. This would mean that some area near the middle
of the table remains uncompacted, but it would allow putting an upper
bound on the time and space used by any one pass of VACUUM FULL ...

regards, tom lane

On Sat, Mar 11, 2006 at 10:21:43PM +0200, Hannu Krosing wrote:

table partitioning, I keep wondering whether it would be possible
to move rows near the end of the table to the beginning in one, non-
locking
phase (vacuum to populate FSM with free space near beginning of table,
touch rows starting at end of table, repeat) and then finish off with a
vacuum full to tidy up the remainder and truncate the files (or a
simpler
"lock the table and truncate anything unused at the end").

At some point I had to compress a very busily updated table. I used the
following approach:

1) VACUUM buzytable; (lazy not full)

2) SELECT primary_key_value, ctid FROM buzytable;

3) Extract N last records from there and for each keep repeating

3A) UPDATE buzytable
SET primary_key_value = primary_key_value
WHERE primary_key_value = extracted_value

3B) SELECT ctid FROM buzytable
WHERE primary_key_value = extracted_value

until the tuple is moved to another pages, hopefully nearer to
the beginning of table

repeat from 1) until the page for last row (extracted from ctid) is
smaller than some thresold.

BTW, this is what the following TODO would hopefully fix:

Allow FSM to return free space toward the beginning of the heap file, in
hopes that empty pages at the end can be truncated by VACUUM
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461