[PERFORM] database tuning
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/monographs/space/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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] database tuning
"Simon Riggs" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 - 3 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] database tuning
""Scott Marlowe"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Dec 7, 2007 1:13 PM, kelvan <[EMAIL PROTECTED]> 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 - 3 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 3 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] database tuning
you know what you lot have left my original question this server is a temporary piece of shit my original question is what are the overheads for postgres but obviously no one knows or no one knows where a webpage containing this information is -_- overhead information i would to know is row overheads column overheads and header overheads for blocks and anything else i have missed trust me postgres and a Mac don't like working together you have no idea the amount of problems we have incurred with php trying to talk to postgres on a Mac out biggest problem is Mac tecs are incompetent and we cannot get any support for the server I know postgres connects fine just we cannot get it working on the Mac so I love your guys ideas but they don't work that's why I have had to take another approach if we were not using a Mac we would have none of the problems we have with connection issues such as php seems to want to take up 20 db connections at a time but I think we fixed that problem overall our problem is the Mac and we cannot get it support neither I nor the web app developer are Mac savvy hell as far as we have seen no Mac tec is Mac savvy either we cannot get parts of postgres to run on a Mac either such as pgagent which is necessary for us but we cannot seem to find a daemon that works on a Mac I have a list of problems a mile long and none of them are postgres it is the Mac so minus all that as the Mac is only a temporary solution can anyone just answer the original question for me if not and I mean no offence to anyone but I really don't care as I am going to re do it all later down the track as I have said your ideas sound good just not Mac oriented nor are they to do with my original question I have never had trouble finding overhead information on any other DBMS I have used this is the first time I have had to ask for it and since this DBMS is open source I have to ask a community rather than a company if anyone is wondering why I don't switch now money and time are not on my side and for those who wonder why don't I leave this job is big time just starts off small time but the potential of this job is very nice and as they say if you want something good you have to work hard for it I am not a fan of taking the easy way out as it brings no benefits for those who want to know more I cannot tell you as I am under a confidentiality agreement regards Kelvan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] database tuning
Ok thx I have got it thx to David and Scott for the links I now know why I couldn't find them as I was looking for blocks rather than page damn synonyms and to Eric thx for the criticism but yea I failed English so I know my punctuation is bad unless I concentrate and I am to busy to do that so for you Eric here is a full stop. (that was a joke not a shot at you I understand what you are saying but yeah) I have also learnt and also Richard pointed out just not in so many words the difference in support from a open source community compared to a non open source company is that the people who give support in open source are opinionated rather than concise meaning they will give you their opinion rather than answering the question Regards Kelvan ---(end of broadcast)--- TIP 6: explain analyze is your friend
