hi i need to know all the database overhead sizes and block header sizes etc
etc as I have a very complex database to build and it needs to be speed
tuned beyond reckoning



I have gathered some relevant information form the documentation such as all
the data type sizes and the RM block information but I don't have any
information on INDEX blocks or other general overheads



http://www.peg.com/techpapers/monogr...ace/space.html



http://www.postgresql.org/docs/8.1/static/datatype.html



I am using postgres 8.1 if anyone can post links to pages containing over
head information and index block header information it would be most
appreciated as I cannot seem to find anything



Regards

Kelvan

kelvan wrote:
> hi i need to know all the database overhead sizes and block header sizes etc
> etc as I have a very complex database to build and it needs to be speed
> tuned beyond reckoning

[snip]

> I am using postgres 8.1 if anyone can post links to pages containing over
> head information and index block header information it would be most
> appreciated as I cannot seem to find anything

I'd look to the source if you care that strongly. Don't rely on any info
found on the internet unless it explicitly mentions 8.1 - these things
change. Have a look in "backend/storage/" and "backend/access/" I'd
guess (not a hacker myself).


Some thoughts though:
1. If you care that strongly about performance, start building it with 8.3

2. Does your testing show that index storage overheads are/will be a
problem? If not, I'd concentrate on the testing to make sure you've
identified the bottlenecks first.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Fri, 2007-12-07 at 12:45 +1200, kelvan wrote:

> hi i need to know all the database overhead sizes and block header sizes etc
> etc as I have a very complex database to build and it needs to be speed
> tuned beyond reckoning

If your need-for-speed is so high, I would suggest using 8.3 or at least
looking at the 8.3 documentation.

This release is very nearly production and is much faster than 8.1 or
8.2. You may not have realised that Postgres dot releases are actually
major releases and have significant speed differences.

There's not much to be done about the overheads you mention, so best to
concentrate your efforts on index planning for your most frequently
executed queries.

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


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

"Simon Riggs" <simon@2ndquadrant.com> wrote in message
news:1197016760.4255.474.camel@ebony.site...
> On Fri, 2007-12-07 at 12:45 +1200, kelvan wrote:
>
>> hi i need to know all the database overhead sizes and block header sizes
>> etc
>> etc as I have a very complex database to build and it needs to be speed
>> tuned beyond reckoning
>
> If your need-for-speed is so high, I would suggest using 8.3 or at least
> looking at the 8.3 documentation.
>
> This release is very nearly production and is much faster than 8.1 or
> 8.2. You may not have realised that Postgres dot releases are actually
> major releases and have significant speed differences.
>
> There's not much to be done about the overheads you mention, so best to
> concentrate your efforts on index planning for your most frequently
> executed queries.
>
> --
> Simon Riggs
> 2ndQuadrant http://www.2ndQuadrant.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>



"Simon Riggs" <simon@2ndquadrant.com> wrote in message
news:1197016760.4255.474.camel@ebony.site...
> On Fri, 2007-12-07 at 12:45 +1200, kelvan wrote:
>
>> hi i need to know all the database overhead sizes and block header sizes
>> etc
>> etc as I have a very complex database to build and it needs to be speed
>> tuned beyond reckoning
>
> If your need-for-speed is so high, I would suggest using 8.3 or at least
> looking at the 8.3 documentation.
>
> This release is very nearly production and is much faster than 8.1 or
> 8.2. You may not have realised that Postgres dot releases are actually
> major releases and have significant speed differences.
>
> There's not much to be done about the overheads you mention, so best to
> concentrate your efforts on index planning for your most frequently
> executed queries.
>
> --
> Simon Riggs
> 2ndQuadrant http://www.2ndQuadrant.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


ok heres the thing i dont have a choice i just have to work with whats given
whether it is good or not why i need these overheads is for block
calculations and and tablespace calculations i have to keep everything in a
very very small area on the hdd for head reading speed as the server i am
forced to use is a peice of crap so i need to do my calculations to resolve
this

it is not that i dont know how to do my job i understand effective indexing
materlized views and all other effects of database tuning is was my major
aspect in my study i just need to know the numbers to do what i have to do.

i am new to postgres i have used many other database management systems i
know the over heads for all of them just not this one if someone could
please be of assisstance.

let me give a breef outlay of what i have without breaking my confidentality
agreement

mac server mac os 10.x
postgres 8.2.5 (appologies i just got updated documentation with errors
fixed in it)
70gig hdd
5 gig ram
4 cpus (not that it matters as postgres is not multi threading)

and i have to support approxmatally anywhere from 5000 - 30000 users all
using it concurentally

as you can see this server wouldnt be my first choice (or my last choice)
but as i said i have not choice at this time.
the interface programmer and i have come up with ways to solve certian
problems in preformance that this server produces but i still need to tune
the database

if you need any other information for someone to give me the overheads then
please ask but i may not be able to tell you

regards
kelvan

kelvan wrote:
> ok heres the thing i dont have a choice i just have to work with whats given

Ah well, it happens to all of us.

> whether it is good or not why i need these overheads is for block
> calculations and and tablespace calculations i have to keep everything in a
> very very small area on the hdd for head reading speed as the server i am
> forced to use is a peice of crap so i need to do my calculations to resolve
> this

Out of curiosity, how are you planning to keep the relevant parts of
PostgreSQL's files at a particular physical location on the disk? I
wasn't aware of any facilities in Mac-OS X for this.

> it is not that i dont know how to do my job i understand effective indexing
> materlized views and all other effects of database tuning is was my major
> aspect in my study i just need to know the numbers to do what i have to do.

Fair enough. See the source-code for full details - start with those
directories I mentioned before.

> i am new to postgres i have used many other database management systems i
> know the over heads for all of them just not this one if someone could
> please be of assisstance.
>
> let me give a breef outlay of what i have without breaking my confidentality
> agreement
>
> mac server mac os 10.x
> postgres 8.2.5 (appologies i just got updated documentation with errors
> fixed in it)
> 70gig hdd
> 5 gig ram
> 4 cpus (not that it matters as postgres is not multi threading)

Hmm - Not enough RAM or disks, too many cpus but you knew that anyway.
Oh, and PG *will* use all 4 CPUs, just one per backend - not all 4 for a
single query. Not a problem in your case.

> and i have to support approxmatally anywhere from 5000 - 30000 users all
> using it concurentally

Hmm 30,000 concurrent users, 5GB RAM = 175kB per user. Not going to
work. You'll want more than that for each connection even if it's
basically idle.

Even if you don't run out of RAM, I can't see how a single disk could
keep up with even a moderate rate of updates from that many users.
Presumably largely read-only?

Maybe you mean 30,000 web-users behind a connection-pool?

How many users have you reached in your testing?

> as you can see this server wouldnt be my first choice (or my last choice)
> but as i said i have not choice at this time.
> the interface programmer and i have come up with ways to solve certian
> problems in preformance that this server produces but i still need to tune
> the database

I don't think it's clear as to how you intend to tune the database with
index page-layout details, particularly since you say you are new to
PostgreSQL.

For example, with your above requirements, I'd be particularly concerned
about four things:
1. shared_buffers
2. work_mem
3. Trading off 1+2 vs the risk of swap
4. WAL activity / checkpointing impacting on my single disk

It would be interesting to see what conclusions you reached on these,
given that you're pushing the hardware to its limits. Can you share the
results of your testing on these?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Dec 7, 2007 1:13 PM, kelvan <kicmcewen@windowslive.com> wrote:

> ok heres the thing i dont have a choice i just have to work with whats given
> whether it is good or not why i need these overheads is for block
> calculations and and tablespace calculations i have to keep everything in a
> very very small area on the hdd for head reading speed as the server i am
> forced to use is a peice of crap so i need to do my calculations to resolve
> this
>
> it is not that i dont know how to do my job i understand effective indexing
> materlized views and all other effects of database tuning is was my major
> aspect in my study i just need to know the numbers to do what i have to do.
>
> i am new to postgres i have used many other database management systems i
> know the over heads for all of them just not this one if someone could
> please be of assisstance.
>
> let me give a breef outlay of what i have without breaking my confidentality
> agreement
>
> mac server mac os 10.x
> postgres 8.2.5 (appologies i just got updated documentation with errors
> fixed in it)
> 70gig hdd
> 5 gig ram
> 4 cpus (not that it matters as postgres is not multi threading)

Uh, yeah it matters, postgresql can use multiple backends just fine.
But this will be the least of your problems.

> and i have to support approxmatally anywhere from 5000 - 30000 users all
> using it concurentally

You are being set up to fail. No matter how you examine things like
the size of individual fields in a pg database, this hardware cannot
possibly handle that kind of load. period. Not with Postgresql, nor
with oracle, nor with teradata, nor with any other db.

If you need to have 30k users actually connected directly to your
database you most likely have a design flaw somewhere. If you can use
connection pooling to get the number of connections to some fraction
of that, then you might get it to work. However, being forced to use
a single 70G hard drive on an OSX machine with 5 Gigs ram is sub
optimal.

> as you can see this server wouldnt be my first choice (or my last choice)
> but as i said i have not choice at this time.

Then you need to quit. Now. And find a job where you are not being
setup to fail. Seriously.

> the interface programmer and i have come up with ways to solve certian
> problems in preformance that this server produces but i still need to tune
> the database

You're being asked to take a school bus and tune it to compete at the indy 500.

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

""Scott Marlowe"" <scott.marlowe@gmail.com> wrote in message
news:dcc563d10712100858j7b55e68co5d0da0f8b82c19b1@ mail.gmail.com...
> On Dec 7, 2007 1:13 PM, kelvan <kicmcewen@windowslive.com> wrote:
>
>> ok heres the thing i dont have a choice i just have to work with whats
>> given
>> whether it is good or not why i need these overheads is for block
>> calculations and and tablespace calculations i have to keep everything in
>> a
>> very very small area on the hdd for head reading speed as the server i am
>> forced to use is a peice of crap so i need to do my calculations to
>> resolve
>> this
>>
>> it is not that i dont know how to do my job i understand effective
>> indexing
>> materlized views and all other effects of database tuning is was my major
>> aspect in my study i just need to know the numbers to do what i have to
>> do.
>>
>> i am new to postgres i have used many other database management systems i
>> know the over heads for all of them just not this one if someone could
>> please be of assisstance.
>>
>> let me give a breef outlay of what i have without breaking my
>> confidentality
>> agreement
>>
>> mac server mac os 10.x
>> postgres 8.2.5 (appologies i just got updated documentation with errors
>> fixed in it)
>> 70gig hdd
>> 5 gig ram
>> 4 cpus (not that it matters as postgres is not multi threading)
>
> Uh, yeah it matters, postgresql can use multiple backends just fine.
> But this will be the least of your problems.
>
>> and i have to support approxmatally anywhere from 5000 - 30000 users all
>> using it concurentally
>
> You are being set up to fail. No matter how you examine things like
> the size of individual fields in a pg database, this hardware cannot
> possibly handle that kind of load. period. Not with Postgresql, nor
> with oracle, nor with teradata, nor with any other db.
>
> If you need to have 30k users actually connected directly to your
> database you most likely have a design flaw somewhere. If you can use
> connection pooling to get the number of connections to some fraction
> of that, then you might get it to work. However, being forced to use
> a single 70G hard drive on an OSX machine with 5 Gigs ram is sub
> optimal.
>
>> as you can see this server wouldnt be my first choice (or my last choice)
>> but as i said i have not choice at this time.
>
> Then you need to quit. Now. And find a job where you are not being
> setup to fail. Seriously.
>
>> the interface programmer and i have come up with ways to solve certian
>> problems in preformance that this server produces but i still need to
>> tune
>> the database
>
> You're being asked to take a school bus and tune it to compete at the indy
> 500.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


look i know this wont work hell i knew that from day one in all regards this
is a temporary stand point after things start getting off i am going to blow
up that mac and burn postgres as i need a more powerful dbms one that can
handle multi threading.

as i have said not my choice i know 5 gigs of ram wouldnt start a hot air
balloon let alone support the user base i will have this is for me not a
perminate job but i take high regards in my work and want to do the best job
possible that and the money is good as i am in between jobs as it stands

for now i only need to support a few thousand and they are going to be
behind a web interface as it stands we cannot configure postgres on a mac to
go over 200 connections for god knows what reason but we have found ways
around that using the mac

i have already calculated that the hdd is no where up to what we need and
will die after about 6 months but in that time the mac server is going to be
killed and we will then have a real server ill do some data migration and
then a different dbms but until then i have to make a buffer to keep things
alive -_-

the 30000 is just the number of queries that the web interface will be
sending at its high point when there are many users in the database by users
i mean at the point of the web interface not the back end so treat them as
queries.

so as you can see ill need as fast a read time for every query as possible.
i am using alot of codes using small int and bit in my database and
de-normalising everying to keep the cnnections down and the data read
ammount down but that can only do so much.we have no problem supporting that
many users form a web stand point
my problem is read time which is why i want to compact the postgres blocks
as much as possible keeping the data of the database in as small a location
as possible.

regards
kelvan

>>> On Mon, Dec 10, 2007 at 6:29 PM, in message <fjki5a$uf1$1@news.hub.org>,
"kelvan" <kicmcewen@windowslive.com> wrote:

> i need a more powerful dbms one that can
> handle multi threading.

If you're looking to handle a lot of concurrent users, PostgreSQL
has the power. The threading issues really only impact the ability
to spread the work for a single large query over the processors.
For multiple users the work is spread over the processors just fine.

> as i have said not my choice i know 5 gigs of ram wouldnt start a hot air
> balloon let alone support the user base i will have

We've run a web site with two million hits per day, running 10
million SELECT queries and 1 million DML database transactions
(averaging over 10 million DML statements) per day on a machine
with 6 MB of RAM under PostgreSQL, so you might be surprised.
Your biggest problem is the single disk drive. RAID not only
is critical for data integrity, it helps performance when your
data is not fully cached.

> we cannot configure postgres on a mac to
> go over 200 connections for god knows what reason but we have found ways
> around that using the mac

Well, with four processors there's no point to going above about
15 or 20 database connections. Use one of the many excellent
options for connection pooling for better results.

> i am using alot of codes using small int and bit in my database and
> de-normalising everying to keep the cnnections down and the data read
> ammount down but that can only do so much.

Denormalization almost always requires more disk space. That's
exactly what you should be trying to avoid.

> my problem is read time which is why i want to compact the postgres blocks
> as much as possible keeping the data of the database in as small a location
> as possible.

A much bigger issue from that regard will probably be dead space
from updated and deleted rows (plus from any rollbacks). Have
you figured out what your VACUUM strategy will be?

Without knowing more, it's hard to say for sure, but you might do
just fine if you can get a few more drives hooked up through a
decent RAID controller, and funnel your connection through a
connection pool.

I hope this helps.

-Kevin



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

>>> On Mon, Dec 10, 2007 at 6:15 PM, in message
<475D8257.EE98.0025.0@wicourts.gov>, Kevin Grittner wrote:

> with 6 MB of RAM

Obviously a typo -- that should read 6 GB of RAM.



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Tue, 11 Dec 2007, kelvan wrote:

> i am going to blow up that mac and burn postgres as i need a more
> powerful dbms one that can handle multi threading.

Someone pointed this out already, but I'll repeat: PostgreSQL has a
multi-process architecture that's fully capable of taking advantage of
multiple CPUs. Whether a multi-process model is better or worse than a
multi-threaded one is a popular subject to debate, but it's certainly not
true that switching to threads will always give a performance benefit, and
you shouldn't expect a large one--processes and threads are not that
different. As a simple example benchmarks usually show the multi-process
PostgreSQL scales better to high client loads than the multi-threaded
MySQL.

The only spot where PostgreSQL has a clear performance limitation is that
no single query can be split among multiple processors usefully. Since
you seem to be working for many users doing small tasks rather than a
single large one, I wouldn't expect the scalability of the core database
code to be your problem.

> as it stands we cannot configure postgres on a mac to go over 200
> connections for god knows what reason but we have found ways around that
> using the mac

In a web application environment, there is no good reason to have that
many individual database connections. You should consider the fact that
you had trouble getting more than 200 going a warning sign. The right way
to deal with this is not to work around it, but to use some sort of
connection pooling software instead. You might use something that does
PostgreSQL-level pooling like PgBouncer
https://developer.skype.com/SkypeGar...ects/PgBouncer or you could
do higher level caching with something like memcached
http://www.danga.com/memcached/

> so as you can see ill need as fast a read time for every query as
> possible. i am using alot of codes using small int and bit in my
> database and de-normalising everying to keep the cnnections down and the
> data read ammount down but that can only do so much.

What you should be worried about here is how much of the database you can
cram into memory at once. Have you looked into using large amounts of
memory for shared_buffers? In your situation you should consider putting
multiple GB worth of memory there to hold data. Particularly with a
single disk, if you even get to the point where you need to read from disk
regularly you're not going to get anywhere close to your performance
goals.

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly