[SQL] Another qs 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 If the degrading performance issue is solved, are JDBC and C still expected to show similar performance in case of inserts ? I"ll probably try it out but just wanted to know if anybody already has an insight in to this. Rini __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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... > > 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/
[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/
[SQL] optimal performance for inserts
Thanks for your suggestions, though I've already considered most of them. (I have a detailed reply below, interleaved with your mail). I am considering an option but would need help from somebody who knows how the backend works to be able to figure out if any of the following options would help. Consider the scenario of a database with say 3 tables, and atleast 3 concurrent writers to all the tables inserting different records. Which of the three options would be expected to perform better ? (I am using JDBC, I dont know if that is relevant) 1. Having a different Connection per writer 2. Having a different Connection per table 3. Having a single Connection which performs the 3 transactions sequentially. I was trying out some tests to decide between option 1 & option 2 , but did not get any conclusive results. Would be helpful to get some suggestions on the same. Thanks, Rini --- Mitch Vincent <[EMAIL PROTECTED]> wrote: > Removing indexes will speed up the INSERT portion > but slow down the SELECT > portion. I cannot remove indexes since there may be other queries to these tables at the same time when I am doing the inserts. > Just an FYI, you can INSERT into table (select > whatever from another > table) -- you could probably do what you need in a > single query (but would > also probably still have the speed problem). I have not spent time on it but I could not figure out how to have an insert statement such that one of the attributes (only) is a result of a select from another table. I would be interested in knowing if there is a way to do that. > Have you EXPLAINed the SELECT query to see if index > scans are being used > where possible? Yes, the index scans are being used > -Mitch > > - Original Message - > From: "Rini Dutta" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Friday, August 25, 2000 12:20 PM > Subject: [SQL] queries and inserts > > > > Hi, > > > > I am interested in how to speed up storage. About > 1000 > > or more inserts may need to be performed at a time > , > > and before each insert I need to look up its key > from > > the reference table. So each insert is actually a > > query followed by an insert. > > > > The tables concerned are : > > CREATE TABLE referencetable(idx serial, rcol1 int4 > NOT > > NULL, rcol2 int4 NOT NULL, rcol3 varchar(20) NOT > > NULL, rcol4 varchar(20), PRIMARY KEY(idx) ... > > CREATE INDEX index_referencetable on > > referencetable(rcol1, rcol2, rcol3, rcol4); > > > > CREATE TABLE datatable ( ref_idx int4, > > start_date_offset int4 NOT NULL, stop_date_offset > int4 > > NOT NULL, dcol4 float NOT NULL, dcol5 float NOT > NULL, > > PRIMARY KEY(ref_idx, start_date_offset), > CONSTRAINT c1 > > FOREIGN KEY(ref_idx) REFERENCES > referencetable(idx) ); > > > > I need to do the following sequence n number of > times > > - > > 1. select idx (as key) from referencetable where > > col1=c1 and col2=c2 and col3=c3 and col4=c4; > (Would an > > initial 'select into temptable' help here since > for a > > large number of these queries 'c1' and 'c2' > > comnbinations would remain constant ?) > > 2. insert into datatable values(key, ); > > > > I am using JDBC interface of postgresql-7.0.2 on > > Linux. 'referencetable' has about 1000 records, it > can > > keep growing. 'datatable' has about 3 million > records, > > it would grow at a very fast rate. Storing 2000 > > records takes around 75 seconds after I vacuum > > analyze. (before that it took around 40 seconds - > ???) > > . I am performing all the inserts ( including the > > lookup) as one transaction. > > > > Thanks, > > Rini > > > > > > __ > > Do You Yahoo!? > > Yahoo! Mail - Free email you can access from > anywhere! > > http://mail.yahoo.com/ > > > __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
[SQL] queries and inserts
Hi, I am interested in how to speed up storage. About 1000 or more inserts may need to be performed at a time , and before each insert I need to look up its key from the reference table. So each insert is actually a query followed by an insert. The tables concerned are : CREATE TABLE referencetable(idx serial, rcol1 int4 NOT NULL, rcol2 int4 NOT NULL, rcol3 varchar(20) NOT NULL, rcol4 varchar(20), PRIMARY KEY(idx) ... CREATE INDEX index_referencetable on referencetable(rcol1, rcol2, rcol3, rcol4); CREATE TABLE datatable ( ref_idx int4, start_date_offset int4 NOT NULL, stop_date_offset int4 NOT NULL, dcol4 float NOT NULL, dcol5 float NOT NULL, PRIMARY KEY(ref_idx, start_date_offset), CONSTRAINT c1 FOREIGN KEY(ref_idx) REFERENCES referencetable(idx) ); I need to do the following sequence n number of times - 1. select idx (as key) from referencetable where col1=c1 and col2=c2 and col3=c3 and col4=c4; (Would an initial 'select into temptable' help here since for a large number of these queries 'c1' and 'c2' comnbinations would remain constant ?) 2. insert into datatable values(key, ); I am using JDBC interface of postgresql-7.0.2 on Linux. 'referencetable' has about 1000 records, it can keep growing. 'datatable' has about 3 million records, it would grow at a very fast rate. Storing 2000 records takes around 75 seconds after I vacuum analyze. (before that it took around 40 seconds - ???) . I am performing all the inserts ( including the lookup) as one transaction. Thanks, Rini __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
