Re: [PERFORM] Tuning PostgreSQL
On Sunday 13 July 2003 10:23, Ron Johnson wrote: On Fri, 2003-07-04 at 09:49, Shridhar Daithankar wrote: On 4 Jul 2003 at 16:35, Michael Mattox wrote: [snip] On a positive note, me and Josh are finishing a bare bone performance article that would answer lot of your questions. I am counting on you to provide valuable feedback. I expect it out tomorrow or on sunday..Josh will confirm that.. Hello, Is this doc publicly available yet? Yes. See http://www.varlena.com/GeneralBits/ I thought I announved it on performance.. anyways.. Shridhar ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Pgsql - Red Hat Linux - VS MySQL VS MSSQL
On Sunday 13 July 2003 12:05, Balazs Wellisch wrote: Hi all, However, to be able to justify the move I will have to demonstrate that PostgreSQL is up to par with MSSQL and MySQL when it comes to performance. After having read through the docs and the lists it seems obvious that PostgreSQL is not configured for high performance out of the box. I don't have months to learn the ins and outs of PostgreSQL performance tuning so I looked around to see if there are any preconfigured solutions out there. If postgresql performance is going to be a concern, concurrency considerations with mysql will be even bigger concern. Postgresql can be tuned. For achieving good concurrency with mysql, you might have to redesign your app. In general, this list can help you to tune the things. Shouldn't be that big concern. I found that Red Hat Database 2.1 comes with PostgreSQL installed. However, as far as I can tell it comes with postgreSQL 7.2 and it requires Red Hat 8.0 or Red Hat Advanced Server which is based on Red Hat 7.2. Would I be better off installing Red Hat 9.0 and PostgreSQL 7.3 and try to performance tune the installation myself, or should I buy Red Hat Advanced Server and install Red Hat Database 2.1? (Let's say money is no object) I would rather vote for RH-AS with postgresql 7.4 devel. Former for it's big-app tunings out of the box and later for it's performance. Of course best way is to try it out yourself. Even vanilaa distro. on good hardware should be plenty good.. Shridhar ---(end of broadcast)--- TIP 3: 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
[PERFORM] Help disk-pages
Hi all! I'm new to Postgresql and I'm trying solve a problem: is there a way to know how many disk-pages are read during a query? Because I found out only how many disk-pages a relation has and I'd like to know if there is a system catalog or something else that stores this information thanks, Andrea Lazzarotto - Salve, il messaggio che hai ricevuto รจ stato inviato per mezzo del sistema di web mail interfree. Se anche tu vuoi una casella di posta free visita il sito http://club.interfree.it Ti aspettiamo! - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Improving a simple query?
select * from attributes_table where id in (select id from attributes where (name='obsid') and (value='oid00066')); Can you convert it into a join? 'where in' clauses tend to slow pgsql down. -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Pgsql - Red Hat Linux - VS MySQL VS MSSQL
There are many Linux and other OS distributions that will work just fine. You may need to tweak a few kernel configuration parameters, but that's not too difficult; see: http://www.postgresql.org/docs/view.php?version=7.3idoc=0file=kernel-resources.html Yes, I looked at the online documentation but found it a little too generic. Although it gives me good idea of where to look to adjust performance related parameters I need a little more specific advise. I just don't have the time to tweak and test different configurations for months to see what works and what doesn't. Ideally, I'd love to run my own benchmarks and become an expert at postgresql, but unfortunately in the real world I have deadlines to meet and clients to appease. So, I was hoping someone would have some real world experiences to share running postgresql on RH in an enterprise environment. I would *not* use the default version of Postgres shipped with any particular distribution. Use 7.3.3 because that is the latest released version. Or, as Shridhar mentioned in his post, the are a number of pretty significant performance improvements in 7.4 (which is in feature freeze and scheduled to go into beta on 21 July). If you are in an exploratory/test phase rather than production right now, I'd say use the 7.4 beta for your comparisons. Well, I could start by testing 7.4, however I'd have to go back to the stable version once we're ready to use it a production environment. So, I might as well stick with eveluating the production version. If money is truly not a problem, but time is, my advice is to hire a consultant. There are probably several people on this list that can fill that role for you. Otherwise read the archives and ask lots of specific questions. Once we're ready to go with postgresql in a production environment we may indeed need to hire a consultant. Any suggestions whom I should contact? (We're in the San Diego area) Thank you for your advice. Balazs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Pgsql - Red Hat Linux - VS MySQL VS MSSQL
On Sun, Jul 13, 2003 at 12:51:02PM -0700, Balazs Wellisch wrote: Alternatively, you simply compile 7.3.3 from source. I've upgraded most my machines that way. Unfortunatelly, compiling from source is not really an option for us. We use RPMs only to ease the installation and upgrade process. We have over a hundred servers to maintaine and having to compile and recompile software everytime a new release comes out would be way too much work. If you aren't settled on OS yet, take a look at FreeBSD, or one of the linuxes that have better app management. Keeping pgsql up-to-date using ports on FreeBSD is pretty painless (for that matter, so is keeping the OS itself up-to-date). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Improving a simple query?
At 11:31 PM 7/13/03 -0300, Chris Bowlby wrote: Woops, this might not go through via the address I used : (not subscribed with that address).. At 01:46 PM 7/13/03 -0700, Steve Wampler wrote: The following left join should work if I've done my select right, you might want to play with a left versus right to see which will give you a better result, but this query should help: SELECT * FROM attributes_table att LEFT JOIN attributes at ON (at.name = 'obsid' AND at.value = 'oid00066') WHERE att.id = at.id; On Sun, Jul 13, 2003 at 08:09:17PM +0100, Richard Huxton wrote: I'm not an SQL or PostgreSQL expert. I'm getting abysmal performance on a nested query and need some help on finding ways to improve the performance: [snip] select * from attributes_table where id in (select id from attributes where (name='obsid') and (value='oid00066')); This is the classic IN problem (much improved in 7.4 dev I believe). The recommended approach is to rewrite the query as an EXISTS form if possible. See the mailing list archives for plenty of examples. Could you not rewrite this as a simple join though? Hmmm, I don't see how. Then again, I'm pretty much the village idiot w.r.t. SQL... The inner select is locating a set of (2049) ids (actually from the same table, since 'attributes' is just a view into 'attributes_table'). The outer select is then locating all records (~30-40K) that have any of those ids. Is that really something a JOIN could be used for? -Steve -- Steve Wampler -- [EMAIL PROTECTED] Quantum materiae materietur marmota monax si marmota monax materiam possit materiari? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] How to configure the postgresql.conf files
Hi Chris, I suggest you read this tech. document: http://www.varlena.com/GeneralBits/ I think you'll it's the best place to start. Cheers Rudi. Chris_Wu wrote: Hello all! I'm a new to Postgresql , I have never used it before. I am having an issue with configure the postgresql.conf file. The machine itself is a CPU= 2.66GHz P4 w/ Memory= 2G Maybe you can tell me how to configure these parameters. shared_buffers= max_fsm_relations= max_fsm_pages= max_locks_per_transaction= wal_buffers= sort_mem= vacuum_mem= wal_files= wal_sync_method= wal_debug = commit_delay = commit_siblings = checkpoint_segments = checkpoint_timeout = fsync = true enable_seqscan = enable_indexscan = enable_tidscan = enable_sort = enable_nestloop = enable_mergejoin = enable_hashjoin = ksqo = effective_cache_size = random_page_cost = cpu_tuple_cost = cpu_index_tuple_cost = cpu_operator_cost = Would you mind to send me a copy of examples .(postgresql.conf) Thanks Sincerely, Chris.Wu ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] How to configure the postgresql.conf files
Chris, Oops - it's changed ! Here's the link's you need: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html Cheers Rudi. Chris_Wu wrote: Hello all! I'm a new to Postgresql , I have never used it before. I am having an issue with configure the postgresql.conf file. The machine itself is a CPU= 2.66GHz P4 w/ Memory= 2G Maybe you can tell me how to configure these parameters. shared_buffers= max_fsm_relations= max_fsm_pages= max_locks_per_transaction= wal_buffers= sort_mem= vacuum_mem= wal_files= wal_sync_method= wal_debug = commit_delay = commit_siblings = checkpoint_segments = checkpoint_timeout = fsync = true enable_seqscan = enable_indexscan = enable_tidscan = enable_sort = enable_nestloop = enable_mergejoin = enable_hashjoin = ksqo = effective_cache_size = random_page_cost = cpu_tuple_cost = cpu_index_tuple_cost = cpu_operator_cost = Would you mind to send me a copy of examples .(postgresql.conf) Thanks Sincerely, Chris.Wu ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
