Storage space usage

Re: Storage space usage

On Fri, Feb 01, 2008 at 02:14:18PM +0530, Gangadharan S.A. wrote:
> Hi,
> I have 2 questions regarding the storage optimization done by Postgres:
> 1) Is a NULL value optimized for storage. If I have a timestamp (or some
> such) field that I set to default NULL, will it still use up the full space
> for the data type.

Null values are indicated via a NULL bitmap. A null field is not stored,
it is just indicated in the bitmap.

> 2) Similarly, if I have a text array, is an empty array optimized for
> storage?

Arrays are stored as varlenas. I'm pretty sure than an empty array is
considered to be NULL; as such the comments above would apply.
--

-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel <at> decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

slow 8.2.6 with 50 connections

Hello

I am testing one server and I found strange behave of 8.2.6. My
configuration is:

Linux Orbisek 2.6.18-xeonsmp #1 SMP Thu Jan 31 14:09:15 CET 2008 i686
GNU/Linux, 4 x Intel(R) Xeon(R) CPU E5335   <at>  2.00GHz, 6G RAM

pgbench on 8.3 puts 1600-1700tps without dependency on number of
connections or transactions.

pgbench on 8.2 is similar only for 10 connections and doesn't depend
on number of transactions:
postgres <at> Orbisek:/root$ /usr/local/pgsql/bin/pgbench -c10 -t 50000 test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 10
number of transactions per client: 50000
number of transactions actually processed: 500000/500000
tps = 1747.662768 (including connections establishing)
tps = 1747.758538 (excluding connections establishing)

but is half with 50 connections:
10 (1780), 20 (1545), 30 (1400), 40 (1145) 50c (987tps)

postgres <at> Orbisek:/root$ /usr/local/pgsql/bin/pgbench -c50 -t 100 test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 50
number of transactions per client: 100
number of transactions actually processed: 5000/5000
tps = 1106.484286 (including connections establishing)
tps = 1126.062214 (excluding connections establishing)
postgres <at> Orbisek:/root$ /usr/local/pgsql/bin/pgbench -c50 -t 1000 test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 50
number of transactions per client: 1000
number of transactions actually processed: 50000/50000
tps = 975.009227 (including connections establishing)
tps = 976.521036 (excluding connections establishing)

all time load is less than 3 and cpu us 16%, cpu sys 5% (8.3 used
procs about 18%us and 7% sy)

shared_buffers = 160MB
work_mem = 10MB
maintenance_work_mem = 256MB
wal_buffers = 128kB
checkpoint_segments = 100
bgwriter_lru_percent = 20.0
bgwriter_lru_maxpages = 200
bgwriter_all_percent = 10
bgwriter_all_maxpages = 600
autovacuum_vacuum_cost_delay = 20

postgres82=# select mode, count(*) from pg_locks group by mode;
           mode           | count
--------------------------+-------
 ShareLock                |    40
 ShareUpdateExclusiveLock |     1
 AccessShareLock          |    99
 ExclusiveLock            |    62
 RowExclusiveLock         |   215
(5 rows)

postgres83=# select mode, count(*) from pg_locks group by mode;
           mode           | count
--------------------------+-------
 ShareLock                |    43
 ShareUpdateExclusiveLock |     2
 AccessShareLock          |   101
 ExclusiveLock            |   116
 RowExclusiveLock         |   218
(5 rows)
postgres <at> Orbisek:/root/postgresql-8.2.6/src/tools/fsync$ ./test_fsync
-f /usr/local/pgsql/data/aa
Simple write timing:
        write                    0.005241

Compare fsync times on write() and non-write() descriptor:
(If the times are similar, fsync() can sync data written
 on a different descriptor.)
        write, fsync, close      0.152853
        write, close, fsync      0.152203

Compare one o_sync write to two:
        one 16k o_sync write     0.298571
        two 8k o_sync writes     0.295349

Compare file sync methods with one 8k write:

        (o_dsync unavailable)
        write, fdatasync         0.151626
        write, fsync,            0.150524

Compare file sync methods with 2 8k writes:
        (o_dsync unavailable)
        open o_sync, write       0.340511
        write, fdatasync         0.182257
        write, fsync,            0.177968

any ideas are welcome

Regards
Pavel Stehule

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate

Re: slow 8.2.6 with 50 connections

On Feb 3, 2008 10:02 AM, Pavel Stehule <pavel.stehule <at> gmail.com> wrote:
> Hello
>
> I am testing one server and I found strange behave of 8.2.6. My
> configuration is:

Note that with a scaling factor that's < the number of clients, your
test isn't gonna be very useful.  scaling factor should always be >=
number of clients.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: slow 8.2.6 with 50 connections

On 03/02/2008, Scott Marlowe <scott.marlowe <at> gmail.com> wrote:
> On Feb 3, 2008 10:02 AM, Pavel Stehule <pavel.stehule <at> gmail.com> wrote:
> > Hello
> >
> > I am testing one server and I found strange behave of 8.2.6. My
> > configuration is:
>
> Note that with a scaling factor that's < the number of clients, your
> test isn't gonna be very useful.  scaling factor should always be >=
> number of clients.
>

I use it only for orientation. And reported behave signalize some problem.

Pavel

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: slow 8.2.6 with 50 connections

On Sun, 3 Feb 2008, Pavel Stehule wrote:

> postgres <at> Orbisek:/root$ /usr/local/pgsql/bin/pgbench -c10 -t 50000 test
> scaling factor: 1

If you're running with the number of clients much greater than the scaling 
factor, it's unsurprising transactions are suffering from lock issues at 
higher client loads.  It's good news that situation is much improved in 
8.3 but I'm not sure how much you can conclude from that.

Increasing scale will make the database bigger and drive down your results 
dramatically though, as it will get more disk-bound.  Consider running 
pgbench with "-N", which removes updates to the branches/tellers table 
where the worse locking issues are at, and see if that changes how 8.2 and 
8.3 compare.  But try the things below first.

The other thing to try is running the pgbench client on another system 
from the server itself.  I've seen resultings showing this curve before 
(sharp dive at higher TPS) that flattened out considerably once that was 
done.

> bgwriter_lru_percent = 20.0
> bgwriter_lru_maxpages = 200 
> bgwriter_all_percent = 10
> bgwriter_all_maxpages = 600

Hmm, isn't that the set that Sun was using in their benchmarks?  Unless 
you have more CPUs than your system does, these are way more aggressive 
than make sense--the percentages moreso than the pages.  For pgbench 
tests, you'll probably find performance improves in every way if you just 
turn the background writer off in 8.2.  I suspect that part of your 8.2 
vs. 8.3 difference here is that the way you're 8.2 background writer is 
configured here is wasting all sorts of CPU and I/O resources doing 
unproductive things.  8.3 took away most of these parameters specifically 
to keep that from happening.

--
* Greg Smith gsmith <at> gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Benchmark Data requested

Can I ask for some help with benchmarking?

There are some results here that show PostgreSQL is slower in some cases
than Monet and MySQL. Of course these results were published immediately
prior to 8.2 being released, plus run out-of-the-box, so without even
basic performance tuning.

Would anybody like to repeat these tests with the latest production
versions of these databases (i.e. with PGSQL 8.3), and with some
sensible tuning settings for the hardware used? It will be useful to get
some blind tests with more sensible settings.

http://monetdb.cwi.nl/projects/monetdb//SQL/Benchmark/TPCH/

Multiple runs from different people/different hardware is useful since
they help to iron-out differences in hardware and test methodology. So
don't worry if you see somebody else doing this also.

Thanks,

--

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com 

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Benchmark Data requested

Hi Simon,

Note that MonetDB/X100 does not have a SQL optimizer, they ran raw
hand-coded plans.  As a consequence, these comparisons should be taken as an
"executor-executor" test and we/you should be sure that the PG planner has
generated the best possible plan.

That said, we've already done the comparisons internally and they've got a
good point to make about L2 cache use and removal of unnecessary
abstractions in the executor.  We've been aware of this since 2005/6 and
have been slowly working these ideas into our/PG executor.

Bottom line: it's a good thing to work to get close to the X100/Monet
executor with a more general purpose DB.  PG is a looong way from being
comparable, mostly due to poor L2 D-cache locality and I-cache thrashing in
the executor.  The only way to cure this is to work on more rows than one at
a time.

- Luke 

On 2/4/08 10:37 AM, "Simon Riggs" <simon <at> 2ndquadrant.com> wrote:

> Can I ask for some help with benchmarking?
> 
> There are some results here that show PostgreSQL is slower in some cases
> than Monet and MySQL. Of course these results were published immediately
> prior to 8.2 being released, plus run out-of-the-box, so without even
> basic performance tuning.
> 
> Would anybody like to repeat these tests with the latest production
> versions of these databases (i.e. with PGSQL 8.3), and with some
> sensible tuning settings for the hardware used? It will be useful to get
> some blind tests with more sensible settings.
> 
> http://monetdb.cwi.nl/projects/monetdb//SQL/Benchmark/TPCH/
> 
> Multiple runs from different people/different hardware is useful since
> they help to iron-out differences in hardware and test methodology. So
> don't worry if you see somebody else doing this also.
> 
> Thanks,

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

Re: Benchmark Data requested

> There are some results here that show PostgreSQL is slower in some cases
> than Monet and MySQL. Of course these results were published immediately
> prior to 8.2 being released, plus run out-of-the-box, so without even
> basic performance tuning.
>
> Would anybody like to repeat these tests with the latest production
> versions of these databases (i.e. with PGSQL 8.3), and with some
> sensible tuning settings for the hardware used? It will be useful to get
> some blind tests with more sensible settings.
>
> http://monetdb.cwi.nl/projects/monetdb//SQL/Benchmark/TPCH/
>
> Multiple runs from different people/different hardware is useful since
> they help to iron-out differences in hardware and test methodology. So
> don't worry if you see somebody else doing this also.

Here is another graph: http://tweakers.net/reviews/649/7

Without monetdb though.

--

-- 
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Benchmark Data requested

On Mon, 2008-02-04 at 10:47 -0800, Luke Lonergan wrote:

> Note that MonetDB/X100 does not have a SQL optimizer, they ran raw
> hand-coded plans.  As a consequence, these comparisons should be taken as an
> "executor-executor" test and we/you should be sure that the PG planner has
> generated the best possible plan.

If it doesn't then I'd regard that as a performance issue in itself.

> That said, we've already done the comparisons internally and they've got a
> good point to make about L2 cache use and removal of unnecessary
> abstractions in the executor.  We've been aware of this since 2005/6 and
> have been slowly working these ideas into our/PG executor.
>
> Bottom line: it's a good thing to work to get close to the X100/Monet
> executor with a more general purpose DB.  PG is a looong way from being
> comparable, mostly due to poor L2 D-cache locality and I-cache thrashing in
> the executor.  

You maybe right, but I want to see where it hurts us the most.

> The only way to cure this is to work on more rows than one at a time.

Do you have any results to show that's true, or are you just referring
to the Cray paper? (Which used fixed length tuples and specific vector
hardware).

(With regard to benchmarks, I'd rather not download Monet at all. Helps
avoid legal issues around did-you-look-at-the-code questions.)

--

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com 

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match