[GENERAL] drastic reduction in speed of inserts as the table grows
Hi, I was comparing speed of inserts in C vs JDBC and found that as the table size increases the speed differential decreases till there is no difference (no problem). However inserts kept getting slower and slower as the table size increased and the performance became quite poor. Here is the data including the table descriptions - CREATE TABLE some_table( idx serial, a_idx int4 NOT NULL, b_idx int4 NOT NULL, c_address varchar(20) NOT NULL, d_address varchar(20) NOT NULL, PRIMARY KEY(idx), CONSTRAINT a_fkey1 FOREIGN KEY(a_idx) REFERENCES a_ref(idx), CONSTRAINT b_fkey2 FOREIGN KEY(b_idx) REFERENCES b_ref(idx) ); CREATE INDEX some_index on some_table (a_idx, b_idx, c_address, d_address); Here is the performance statistics on the same table. Note the fall in performance as the test proceeds. # of inserts C (in sec) JDBC (in sec) (as 1 transaction) 500 1 1.7 1000 3 3.4 2000 6 7.5 another 6000 inserts ... then 1 70.8 1283 (ran vacuum at this point to see if it helped) 1355 1000 3637 100 3.8 3.8 I ran these tests on a Linux machine (299 MHz). I used postgres v7.0.3 but then I even tried grouping a large number of inserts in one transaction to reduce the number of hard-disk writes (it did not make a difference as shown in the above data) I am concerned about the drastic fall in performance with increase of table size. Is this expected behavior ? Would this be related to indexes existing on the table? I would expect indexes to make inserts slower but I do not see how it explains such a great fall in performance with increasing table-size. Is there a way to avoid this drop in performance ? Thanks, Rini __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ ---(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] drastic reduction in speed of inserts as the table grows
--- Tom Lane [EMAIL PROTECTED] wrote: Rini Dutta [EMAIL PROTECTED] writes: Here is the performance statistics on the same table. Note the fall in performance as the test proceeds. Try 7.1. I think you are running into the lots-of-pending-triggers problem that was found and fixed awhile back. regards, tom lane I'll try it out. Just for my understanding, is the 'lots-of-pending-triggers' problem related to indexes, or to foreign keys ? Rini __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ ---(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
[GENERAL] 'Unknown Result Type' - JDBC Driver
Hi, I am using the JDBC interface. Does anybody know what could lead to the following two errors I ran into - 1. Cannot handle multiple result groups. at org.postgresql.Connection.ExecSQL(Connection.java, Compiled Code) at org.postgresql.jdbc2.Statement.execute(Statement.java, Compiled Code) at org.postgresql.jdbc2.Statement.executeQuery(Statement.java, Compiled Code) at org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement.java, Compiled Code) This seems to have been followed by a whole lot of occurences of - 2. Unknown Response Type ^@ at org.postgresql.Connection.ExecSQL(Connection.java, Compiled Code) at java.lang.Exception.init(Exception.java, Compiled Code) at java.sql.SQLException.init(SQLException.java, Compiled Code) at org.postgresql.util.PSQLException.init(PSQLException.java, Compiled Code) at org.postgresql.Connection.ExecSQL(Connection.java, Compiled Code) at org.postgresql.jdbc2.Statement.execute(Statement.java, Compiled Code) at org.postgresql.jdbc2.Statement.executeQuery(Statement.java, Compiled Code) at org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement.java, Compiled Code) I normally do not see this happen, and do not know if I can replicate this error reliably. Any information regarding the meaning/cause of this error and whether there is a way to get around it/avoid it, would be really helpful. Thanks, Rini __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(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
[GENERAL] RE: [SQL] handling of database size exceeding physical disk space
Thanks ! I'm using JDBC to insert into the tables. Would it throw an SQLException in such a situation ? Rini --- "Diehl, Jeffrey" [EMAIL PROTECTED] wrote: I happen to know this very well... It handles things very gracefully as far as I can tell. I complains that it can't extend the table and bails out of the transaction. I just wish it didn't happen so often... grin Mike Diehl, Network Monitoring Tool Devl. 284-3137 [EMAIL PROTECTED] -Original Message- From: Rini Dutta [mailto:[EMAIL PROTECTED]] Sent: February 20, 2001 9:35 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: [SQL] handling of database size exceeding physical disk space Hi, Does anyone know how postgres/ postmaster handles the situation where the physical hard disk space is full ? Does it crash / corrupt the database, or does it cleanly exit with appopriate message so that relevant tables can be pruned (by the user) to free up disk space and get it working again ? Thanks, Rini __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
[GENERAL] handling of database size exceeding physical disk space
Hi, Does anyone know how postgres/ postmaster handles the situation where the physical hard disk space is full ? Does it crash / corrupt the database, or does it cleanly exit with appopriate message so that relevant tables can be pruned (by the user) to free up disk space and get it working again ? Thanks, Rini __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
[GENERAL] how critical is WAL ?
Hi, I happenned to come across the following in the documentation on WAL implementation in v7.1 - * Before WAL, any crash during writing could result in: 1.index tuples pointing to non-existent table rows 2.index tuples lost in split operations 3.totally corrupted table or index page content, because of partially written data pages * Does anybody know what kind of a problem this refers to ? Does this mean that incomplete transactions would be stored or does this mean that the entire table might get corrupted and unusable, implying loss of all data ? ( I am using postgresql v7.0.x , and would ideally like to migrate to v7.1 after a few months ... unless it is critical enough to do so earlier. ) Thanks, Rini __ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices! http://auctions.yahoo.com/
[GENERAL] psql - use of default editor
Is there a way to temporarily turn of the use of a default editor when using psql ? I need to do this when I try to use psql from a script/batch file. Whenever a query is executed psql displays a result, displays it - I need to hit 'q' to end the display and would like to avoid this unnecessary (when running psql from a script) user input. Thanks, Rini __ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices! http://auctions.yahoo.com/
