Re: [GENERAL] Temporary tables and disk activity
I don't think temporary tables have any special rules regarding disk writes, so I'd expect them ot get written out like everything else. The database doesn't know you're going to delete them later. Are the tables big? On Thu, Dec 09, 2004 at 10:10:21PM +, Phil Endecott wrote: > Dear All, > > I sent a message last weekend asking about temporary tables being > written to disk but didn't get any replies. I'm sure there is someone > out there who knows something about this - please help! Here is the > question again: > > > Looking at vmstat output on my database server I have been suprised to > see lots of disk writes going on while it is doing what should be > exclusively read-only transactions. I see almost no disk reads as the > database concerned is small enough to fit into the OS disk cache. > > I suspect that it might be something to do with temporary tables. There > are a couple of places where I create temporary tables to "optimise" > queries by factoring out what would otherwise be duplicate work. The > amount of data being written is of the right order of magnitude for this > to be the cause. I fear that perhaps Postgresql is flushing these tables > to disk, even though they will be dropped before the end of the > transaction. Is this a possibility? What issues should I be aware of > with temporary tables? Are there any other common causes of lots of disk > writes within read-only transactions? Is there any debug output that I > can look at to track this down? > > Thanks in advance for any help that you can offer. > > Regards, > > Phil Endecott. > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpn91bIG0wEe.pgp Description: PGP signature
Re: [GENERAL] Temporary tables and disk activity
Martijn van Oosterhout <[EMAIL PROTECTED]> writes: > I don't think temporary tables have any special rules regarding disk > writes, so I'd expect them ot get written out like everything else. They'll be written out from PG's internal buffers, but IIRC they will never be fsync'd, and they definitely aren't WAL-logged. (These statements hold true in 8.0, but not sure how far back.) In principle, therefore, the kernel could hold temp table data in its own disk buffers and never write it out to disk until the file is deleted. In practice, of course, the kernel doesn't know the data is transient and will probably push it out whenever it has nothing else to do. One of the things on the TODO list is making the size of temp-table buffers user-configurable. (Temp table buffers are per-backend, they are not part of the shared buffer arena.) With a large temp-table arena we'd never need to write to the kernel in the first place. Right now you could manually increase the #define that sets it, but it would not pay to make it very large because the management algorithms are very stupid (linear scans). That has to be fixed first :-( regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Temporary tables and disk activity
Tom Lane wrote: They [temporary tables] > will be written out from PG's internal buffers, but IIRC they will never be fsync'd, and they definitely aren't WAL-logged. (These statements hold true in 8.0, but not sure how far back.) In principle, therefore, the kernel could hold temp table data in its own disk buffers and never write it out to disk until the file is deleted. In practice, of course, the kernel doesn't know the data is transient and will probably push it out whenever it has nothing else to do. That makes sense. I suspect that I am seeing writes every 5 seconds, which looks like bdflush / update. But my connections normally only last for a second at most. In this case, surely the table would normally have been deleted before the kernel decided to write anything. This is with 7.4.2 on linux 2.4.26. Does anyone have any experience with this type of situation? Is there any kernel-tweaking I can play with? Regards, Phil. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] relation "sql_features" does not exist
Dear Mr. Fuhr, Thank you for prompt follow up. Fitst of all, I need to remind you that presently we are working on Windows OS (Windows XP-Professional). The errors occured when installing PostgreSQL on the above OS. Upon research I found out that I "MAY" require to install "Cygwin" first and then install PostgreSQL via its environment. Is this correct? Before, getting to other details, I want to be sure which way to go. a. Can PostgreSQL be installed on Windows? Which version of Windows? b. What is the deal with "Cygwin"? Do we need it at all or not? Awaiting your advise, I remain. Regards, Elnaz - Original Message - From: "Michael Fuhr" <[EMAIL PROTECTED]> To: "Elnaz Shafipour" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, December 12, 2004 12:15 AM Subject: Re: [GENERAL] relation "sql_features" does not exist > On Tue, Dec 07, 2004 at 10:42:29AM +0330, Elnaz Shafipour wrote: > > > I want to create database in postgreSQL but I get the following error: > > relation "sql_features" does not exist. > > How are you creating the database, and is that the operation that > fails or do you get the error when doing something after you've > created the database? Please copy & paste the exact commands you're > running and the output. > > What version of the client are you running? The startup banner > should say, or you can run "psql --version" (or "createdb --version" > if that's what you're running). > > What version of the server are you running? You can find out by > executing the query "SELECT version();". > > > As I look for solving this problem I find out that I should > > add information_schema to the shema search path. > > but I don't kow how! > > Let's find out what the problem is before attempting a solution > that may or may not be appropriate or necessary. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] problem in connecting to postgreserver
Sir, I have installed postgres in my standalone pc and PGAdmin -III,i am able to start postgresql server and it is running,but when i try to connect to postgreserver from pgadmin i am geting Error connecting to server Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432. can u please tell me what the problem is Do you Yahoo!? The all-new My Yahoo! Get yours free!
Re: [GENERAL] PREPARED STATEMENT
On Sat, Dec 11, 2004 at 12:30:30PM +, NosyMan wrote: > I want to know that is a posibillity to test if a statement is prepared in > PL/PgSQL. > > I have create a function: > . > PREPARE PSTAT_SAVE_record(INTEGER, INTEGER, DATE, VARCHAR) AS INSERT INTO > table VALUES($1, $2, $3, $4); > . > > When I try to execute it second time I got an error: prepared statement > 'PSTAT_SAVE_record' already exists. How can I avoid this error? is there a > posibillity to test if a statement was prepared before? I'm not aware of a way to test for the existence of a prepared statement without trying to use it. Is there a reason the PREPARE is executed more than once? Can you DEALLOCATE the prepared statement when you're done with it so subsequent PREPAREs won't raise an error? If you're using PostgreSQL 8.0 then you could trap the error and ignore it: BEGIN PREPARE ...; EXCEPTION WHEN duplicate_prepared_statement THEN NULL; END; While I was investigating your question I found a bug that caused the backend to crash if a function that executed PREPARE was called more than once. Are you not getting that crash? If not, what version of PostgreSQL are you running? I discovered the bug in 8.0.0rc1 and it appears to exist in 7.4.6 as well. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Temporary tables and disk activity
Phil Endecott <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> In principle, therefore, the kernel could hold temp table data in its >> own disk buffers and never write it out to disk until the file is >> deleted. In practice, of course, the kernel doesn't know the data is >> transient and will probably push it out whenever it has nothing else to >> do. > That makes sense. I suspect that I am seeing writes every 5 seconds, > which looks like bdflush / update. > But my connections normally only last for a second at most. In this > case, surely the table would normally have been deleted before the > kernel decided to write anything. That does seem a bit odd, then. Can you strace a typical backend session and see if it's doing anything to force a disk write? (I'm too lazy to go check right now whether 7.4 handled temp tables exactly the same as CVS tip does. I think it's the same but I might be wrong.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] disabling OIDs?
The spam filtering package I use (dspam) had a section in their release notes recently which stated that disabling OIDs greatly increased speeds, and so they suggested that people do that on their tables. When creating new tables, you can disable OIDs with, CREATE TABLE foo (...) WITHOUT OIDS; And you can disable OIDs on existing tables by executing for each table, ALTER TABLE foo SET WITHOUT OIDS; and then running a vacuumdb (either with pg_vacuumdb or VACUUM ANALYSE;) Does anyone know of any risks or potential downsides to doing this? Thanks! -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] disabling OIDs?
Lonni J Friedman <[EMAIL PROTECTED]> writes: > The spam filtering package I use (dspam) had a section in their > release notes recently which stated that disabling OIDs greatly > increased speeds, and so they suggested that people do that on their > tables. "greatly increased"? I doubt it. Last I heard, dspam was not noted for any large amount of cluefulness WRT postgres. It was only recently that we managed to talk them out of their most egregious bits of mysql-centricity. Going to them for postgres tuning tips is about like coming to me for mysql tuning ... regards, tom lane ---(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
Re: [GENERAL] disabling OIDs?
On Sun, 12 Dec 2004 22:16:27 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Lonni J Friedman <[EMAIL PROTECTED]> writes: > > The spam filtering package I use (dspam) had a section in their > > release notes recently which stated that disabling OIDs greatly > > increased speeds, and so they suggested that people do that on their > > tables. > > "greatly increased"? I doubt it. > > Last I heard, dspam was not noted for any large amount of cluefulness > WRT postgres. It was only recently that we managed to talk them out of > their most egregious bits of mysql-centricity. Going to them for > postgres tuning tips is about like coming to me for mysql tuning ... > OK, thanks. So is there any real benefit in doing this in a generic (non-dspam) sense, or is it just a hack that wouldn't be noticable? Any risks or potential problems down the line? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] using inheritance in production application.
Larry White wrote: > How 'ready for prime-time' is the table inheritance feature? I've > seen some postings about particular issues (lack of full FK support, > for example), but would like to get an overall sense of the stability > and robustness of the feature. > > Also, is there a performance hit in using inheritance? For example if > I have a sub-table that adds a couple of columns to a base table, > would it be slower to query that structure than it would to represent > the extra columns as a separate table that would be joined with the > base table? All inheritance problems are mentioned on the TODO list, and none are performance-related. They all revolve around the inability of an index to span multiple tables. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Temporary tables and disk activity
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> One of the things on the TODO list is making the size of temp-table >> buffers user-configurable. (Temp table buffers are per-backend, they >> are not part of the shared buffer arena.) With a large temp-table arena >> we'd never need to write to the kernel in the first place. Right now >> you could manually increase the #define that sets it, but it would not >> pay to make it very large because the management algorithms are very >> stupid (linear scans). That has to be fixed first :-( > I assume you mean your TODO list because the official one has no mention > of this. Doesn't it? We've surely discussed the problem enough times, eg http://archives.postgresql.org/pgsql-hackers/2002-08/msg00380.php http://archives.postgresql.org/pgsql-hackers/2002-09/msg01368.php or for that matter here's Vadim complaining about it seven years ago: http://archives.postgresql.org/pgsql-hackers/1997-12/msg00215.php regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] High volume inserts - more disks or more CPUs?
Seeking advice on system configuration (and I have read the techdocs.) We are converting a data collection system from Oracle to PostgreSQL 8.0. We are currently getting about 64 million rows per month; data is put into a new table each month. The number of simultaneous connections is very small: one that does all these inserts, and < 5 others that read. We trying to identify a server for this. Options are a 4-way Opteron with 4 SCSI disks, or a 2-way Opteron with 6 SCSI disks. The 4-CPU box currently has 16 GB of memory and the 2-CPU 4 GB, but we can move that memory around as necessary. (1) Would we be better off with more CPUs and fewer disks or fewer CPUs and more disks? (2) The techdocs suggest starting with 10% of available memory for shared buffers, which would be 1.6 GB on the 4-way. But I've seen posts here saying that anything more than 10,000 shared buffers (80 MB) provides little or no improvement. Where should we start? (3) If we go with more disks, should we attempt to split tables and indexes onto different drives (i.e., tablespaces), or just put all the disks in hardware RAID5 and use a single tablespace? I appreciate all suggestions. -- Guy Rouillier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] problem in connecting to postgreserver
ra ghu wrote: > Sir, > > I have installed postgres in my standalone pc and PGAdmin -III,i am > able to start postgresql server and it is running,but when i try to > connect to postgreserver from pgadmin i am geting > > Error connecting to server > > Is the server running on host 127.0.0.1 and accepting TCP/IP > connections on port 5432. What operating system are you running? Are you running with the stock postgresql.conf and pg_hba.conf files? How did you configure the connection in PgAdmin III? -- Guy Rouillier ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Spanning tables
On Thursday 09 Dec 2004 10:37 am, JM wrote: > Hi ALL, > > Im wondering sooner or later my disk will be filled-up by postgres's > data.. > > Can anyone give some suggestion on how to deal with this. In oracle you > can just assign tables on a diff partition. You could use tablespaces in postgresql 8.0.. Check this http://developer.postgresql.org/docs/postgres/sql-createtablespace.html http://developer.postgresql.org/docs/postgres/sql-createtable.html HTH Shridhar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] disabling OIDs?
On Sun, 2004-12-12 at 20:25 -0800, Lonni J Friedman wrote: > OK, thanks. So is there any real benefit in doing this in a generic > (non-dspam) sense, or is it just a hack that wouldn't be noticable? > Any risks or potential problems down the line? It saves 4 bytes per row; depending on alignment and padding considerations, that may or may not equate to disk space savings. Other than the inability to use OIDs on the table, there is no real risks to doing this -- I'm planning to advocate making WITHOUT OIDS the default in PostgreSQL 8.1+. You can get this behavior in 8.0 by setting the default_with_oids config variable to "false". -Neil ---(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
Re: [GENERAL] Temporary tables and disk activity
Tom Lane wrote: > Martijn van Oosterhout <[EMAIL PROTECTED]> writes: > > I don't think temporary tables have any special rules regarding disk > > writes, so I'd expect them ot get written out like everything else. > > They'll be written out from PG's internal buffers, but IIRC they will > never be fsync'd, and they definitely aren't WAL-logged. (These > statements hold true in 8.0, but not sure how far back.) > > In principle, therefore, the kernel could hold temp table data in its > own disk buffers and never write it out to disk until the file is > deleted. In practice, of course, the kernel doesn't know the data is > transient and will probably push it out whenever it has nothing else to > do. > > One of the things on the TODO list is making the size of temp-table > buffers user-configurable. (Temp table buffers are per-backend, they > are not part of the shared buffer arena.) With a large temp-table arena > we'd never need to write to the kernel in the first place. Right now > you could manually increase the #define that sets it, but it would not > pay to make it very large because the management algorithms are very > stupid (linear scans). That has to be fixed first :-( I assume you mean your TODO list because the official one has no mention of this. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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
Re: [GENERAL] disabling OIDs?
Lonni J Friedman <[EMAIL PROTECTED]> writes: > OK, thanks. So is there any real benefit in doing this in a generic > (non-dspam) sense, or is it just a hack that wouldn't be noticable? > Any risks or potential problems down the line? OIDs increase the storage requirements so they do slow postgres somewhat. About (exactly?) the same impact as adding another integer column. That will have a bigger impact on a narrow table than wider tables. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
