Re: [PERFORM] postgresql amd-64
Iain wrote: I'm hoping I'll have the opportunity to build a similar machine soon and am wondering about the choice of 64 bit distributions. Gentoo is obviously a possibility but I'm also condsidering Debian. There is also a 64 compile of redhat sources somewhere around, but I can't remember what they call it offhand. RedHat's community OS is now called Fedora: http://fedora.redhat.com/ There's been two AMD64 releases of this OS, Fedora Core 1 and Fedora Core 2. Core 3 is just around the corner. I've been running FC2 x86_64 with kernel 2.6 as a desktop system for quite some time now, with PostgreSQL 7.4.2 / 64bit installed. I find Fedora to be a really good Linux distro, continuing and improving upon the fine tradition of RedHat's releases. You can also get RedHat's commercial releases on AMD64; according to http://www.redhat.com/software/rhel/features/ you can also get a EM64T release. If anyone has opinions about that, I'd be happy to hear. -- Radu-Adrian Popescu CSA, DBA, Developer Aldrapay MD Aldratech Ltd. +40213212243 smime.p7s Description: S/MIME Cryptographic Signature
Re: [PERFORM] Prepared statements vs. Stored Procedures
I'm hoping someone can offer some advice here. I have a large perl script that employs prepared statements to do all its queries. I'm looking at using stored procedures to improve performance times for the script. Would making a stored procedure to replace each prepared statement be worthwhile? If not, when could I use stored procedures to improve performance? Thanks in advance. You'll definitely gain some performance if you manage to group several operations that are executed in a sequence - into a stored procedure. The principle here is that you'd be reducing the number of round-trips to the database server. As an example assume you start a transaction, lock several rows in different tables for update (thereof), update fields and then commit. If this is done in a sequencial manner - whether this is perl or java/jdbc or libpq - you'll require several round-trips to the server and also fetch several bits and pieces to the application. If this can be rewritten as a stored procedure that receives the data/parameters it needs in order to complete its work and does the whole thing in one go you'll definitely see an improvement as ther will be a single call to the database and you'll move (much) less data between the server and the application. On the other hand if you're mostly fetching data I doubt you'll be able to gain anything from changing to stored procedures. I believe a good rule of thumb is this: change data, several related operations, very simple processing involved - stored procedure. Read data as in a reporting scenario - prepared statements. Obviously if you're reading data in several steps and then aggregate it in the application then perhaps you need to make better use of SQL :) I hope this helps, Regards, -- Radu-Adrian Popescu CSA, DBA, Developer Aldrapay MD Aldratech Ltd. +40213212243 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance Tuning Article
There have been problems with Xeon processors. Can you elaborate on that please ? Thanks, -- Radu-Adrian Popescu CSA, DBA, Developer Aldrapay MD Aldratech Ltd. +40213212243 smime.p7s Description: S/MIME Cryptographic Signature
Re: [PERFORM] Performance Tuning Article
Dave Cramer wrote: My understanding is that it isn't particularly XEON processors that is the problem Any dual processor will exhibit the problem, XEON's with hyperthreading exacerbate the problem though and the good news is that it has been fixed in 8.1 Where's that ? The only information I have is a message from Tom Lane saying the buffer manager (or something like that) locking has been redone for 8.0. Any pointers ? Dave Thanks, -- Radu-Adrian Popescu CSA, DBA, Developer Aldrapay MD Aldratech Ltd. +40213212243 smime.p7s Description: S/MIME Cryptographic Signature
Re: [PERFORM] Performance - moving from oracle to postgresql
- Oracle has one particular performance enhancement that Postgres is missing. If you do a select that returns 100,000 rows in a given order, and all you want are rows 99101 to 99200, then Oracle can do that very efficiently. With Postgres, it has to read the first 99200 rows and then discard the first 99100. When I was reading up on resultset pagination on AskTom I got a clear impression that the same happens in Oracle as well. Resultset is like: 0START...STOP...END 0STOP START...END You first select all the rows from 0 to STOP and then from that select the rows from START to end (which is now STOP). This is done using ROWNUM twice and subselects. It was discussed over there that this obviously produces higher response times as you move towards the end of a very large resultset. Tom even pointed out the same effect when using google search, as you move forward through a very large (millions) search result. Regards, -- Radu-Adrian Popescu CSA, DBA, Developer Aldrapay MD Aldratech Ltd. +40213212243 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
