[PERFORM] Database tuning at Duke
There's two papers published recently at Duke that I just found, both of which use PostgreSQL as part of their research: Automated SQL Tuning through Trial and (Sometimes) Error: http://www.cs.duke.edu/~shivnath/papers/dbtest09z.pdf Tuning Database Configuration Parameters with iTuned: http://www.cs.duke.edu/~shivnath/papers/ituned.pdf The second has a number of interesting graphs showing how changing two postgresql.conf parameters at a time interact with one another. There's also a set of graphs comparing the default postgresql.conf performance with what you get using the guidelines suggested by an earlier version of http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and some of the documents on that section of the wiki. Check out page 10, the M column represents that manual tuning against the leftmost D which is the stock postgresql.conf settings. I was a bit confused at first about the environment because of how the paper is organized, here's the bit that clarifies it: The database size with indexes is around 4GB. The physical memory (RAM) given to the database is 1GB to create a realistic scenario where the database is 4x the amount of RAM. That RAM limit was constrained with a Solaris zone. They multiplied the 1GB x 20% to get a standard rule-based setting of shared_buffers of 200MB (based on the guidelines on the wiki at the time--that suggestion is now 25%). Note that much of the improvement shown in their better tuned versions there results from increases to shared_buffers (peaking at 40%=400MB) and work_mem beyond the recommendations given in the tuning guide. That is unsurprising as those are aimed more to be reasonable starting values rather than suggested as truly optimal. work_mem is particular is dangerous to suggest raising really high without knowing what types of queries are going to be run. There's been plenty of commentary on this list suggesting optimal shared_buffers is closer to 50% of RAM than 25% for some workloads, so their results showing peak performance at 40% fit right in the middle of community lore. I'm now in contact with the authors and asked them to let me know whey publish the entire post-optimization postgresql.conf, I'll let the list know when that's available. I'm quite curious to see what the final settings that gave the best results looked like. -- Greg Smithg...@2ndquadrant.comBaltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
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
kelvan wrote: I wonder where did all the punctuation symbols on your keyboard went. Your email is amazingly hard to read. overhead information i would to know is row overheads column overheads and header overheads for blocks and anything else i have missed As for storage overhead, see here: http://www.postgresql.org/docs/8.3/static/storage-page-layout.html -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J Siempre hay que alimentar a los dioses, aunque la tierra esté seca (Orual) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] database tuning
http://www.postgresql.org/docs/8.1/static/storage.html On Dec 11, 2007 5:18 PM, kelvan [EMAIL PROTECTED] wrote: 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 -_- So, have you looked in the docs? I go here: http://www.postgresql.org/docs/8.1/static/index.html see this down the page a bit: http://www.postgresql.org/docs/8.1/static/storage.html which takes me here: http://www.postgresql.org/docs/8.1/static/storage-page-layout.html And it seems to have that information in it. Again. You can look at the source, or find out experimentally by building tables and checking their size. Some of this is an inexact science because different architechtures have different alignment requirements. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] database tuning
On Dec 11, 2007, at 5:18 PM, kelvan wrote: 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 Kelvan, proper capitalization and punctuation are virtues when trying to communicate extensively via text mediums. I, for one, read the first couple and last couple of lines of this message after gruelingly reading your last message and I wouldn't be surprised if others with more experience and better answers at the ready simply ignored both as that much text is extremely difficult to follow in the absence those aforementioned virtues. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] database tuning
kelvan wrote: 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 You said you had most of that in your original post: 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 The index details are in the source, as I said in my first reply. It's just that nobody here thinks that'll help you much. neither I nor the web app developer are Mac savvy hell as far as we have seen no Mac tec is Mac savvy either So what on earth are you going to do with the index overhead figures? Without accurate information on usage patterns, fill-factor, vacuuming frequency etc. they aren't going to tell you anything. Even if you could get an accurate figure for database size with less effort than just generating test data, what would your next step be? as I have said your ideas sound good just not Mac oriented The only idea I've seen mentioned is connection-pooling. I'm not sure why that wouldn't work on a Mac. Other comments were warning that 30,000 connections weren't do-able, that de-normalising made poor use of your limited disk/memory and pointing out solutions other people use. Oh, and me asking for any info from your testing. 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 Again, since you said you had all the stuff from the manuals, the rest is in the source. That's what the source is there for. -- Richard Huxton Archonet Ltd ---(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
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
Re: [PERFORM] database tuning
On Wed, 12 Dec 2007, kelvan wrote: 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 -_- In addition to the documentation links people have already suggested, I'd also suggest http://andreas.scherbaum.la/blog/archives/282-table-size,-database-size.html which gives some helpful suggestions on measuring the actual size of data you've got in the database already. It's possible to make a mistake when trying to compute overhead yourself; loading a subset of the data and measuring the size is much less prone to error. 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 I can't imagine why the connection pooling links I suggested before wouldn't work perfectly fine on a Mac. You're correct to first nail down why PHP is connecting more than you expect, but eventually I suspect you'll need to wander toward pooling. 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 You might want to give some specifics and ask about this on the pgAdmin mailing list: http://www.pgadmin.org/support/list.php OS X support is relatively recent for pgAdmin and I see some other recent fixes for specific issues on that platform. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] database tuning
On Wed, Dec 12, 2007 at 12:27:39PM +1200, kelvan wrote: 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 No, the difference is that the paid support *has to* give *an* answer. It doesn't have to be a useful answer, it just has to fulfil their obligation. They will give you whatever answer you ask for to get you off the phone as quickly as possible because it makes their on-phone numbers better than arguing about it and trying to give a useful answer. Free support will tell you that what you're asking for is silly, because they don't have to give you the answer you asked for in order to get you off the phone. You seem to have already made up your mind about a whole number of things, making this whole discussion more amusing than erudite. Mike Stone ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] database tuning
Michael Stone wrote: On Wed, Dec 12, 2007 at 12:27:39PM +1200, kelvan wrote: 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 No, the difference is that the paid support *has to* give *an* answer. Good lord what a bizarre paragraph. Michael is right, paid support *has to* give *an* answer and I guarantee you that the answer will be opinionated. There are very little right and wrong in the world of software. It is mostly one pedantic opinion versus another pedantic opinion. I get paid everyday to offer my opinion :) Joshua D. Drake ---(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
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 - 3 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] database tuning
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
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
On Mon, Dec 10, 2007 at 6:29 PM, in message [EMAIL PROTECTED], kelvan [EMAIL PROTECTED] 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
Fwd: Re: [PERFORM] database tuning
On Mon, Dec 10, 2007 at 6:15 PM, in message [EMAIL PROTECTED], 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
Re: [PERFORM] database tuning
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/SkypeGarage/DbProjects/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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] database tuning
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
Re: [PERFORM] database tuning
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
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
