Thread: improve performance
Hello, A) I am going to load a huge amount of data in the DBMS using JDBC and I want to reduce as much as possible the required loading time. Initially I loaded all data in one transaction. Subsequently, I increased the number of buffers and disabled fsync() (-o -F) and I loaded the data again but the performance was almost unchanged. Does it make sense?? How can I improve performance? To note that no indexes are created on the tables and that I load both "insert into" and "create table" statements. ------------------------------ B) I want to reduce the space (8 Kb) allocated by DBMS when more space is required to load the data in a table. I reduced the parameter BLCKSZ but the space allocated remains the same, that is 8 KB. How can the allocated space be reduced? If the space was reduced would loading time and query time increase? Thank you in advance for your help Sofia Alexaki
Alexaki Sofia <alexaki@ics.forth.gr> writes: > Initially I loaded all data in one transaction. Subsequently, I increased > the number of buffers and disabled fsync() (-o -F) and I loaded the > data again but the performance was almost unchanged. Does it make sense?? > How can I improve performance? To note that no indexes are created on the > tables and that I load both "insert into" and "create table" statements. If you can load the data with a COPY command, instead of individual INSERTs, it'll go a lot faster. See also http://www.postgresql.org/devel-corner/docs/postgres/populate.htm > I want to reduce the space (8 Kb) allocated by > DBMS when more space is required to load the data in a table. I reduced > the parameter BLCKSZ but the space allocated remains the same, that is 8 > KB. > How can the allocated space be reduced? If the space was reduced > would loading time and query time increase? I've heard lots of people want to increase BLCKSZ, but you're the first one who ever wanted to reduce it. You sure you want to do this? It's going to make the maximum row length uncomfortably short. Anyway, you probably forgot to do a full rebuild after changing config.h. "make clean" before "make all" is the only way to be sure the configuration change propagates to all the code. Don't forget you will have to do an initdb, also, so back up your data with the old code first. regards, tom lane
Tom Lane wrote: > > > I've heard lots of people want to increase BLCKSZ, but you're the first > one who ever wanted to reduce it. You sure you want to do this? It's > going to make the maximum row length uncomfortably short. And it may even not work, as some system tables (that are also affected by this) may need the full 8k. AFAIK it has never been tested with BLCKSZ < 8k ---------- Hannu
I need help, which is the symbol used in postgreSql to carry out the outer join. in oracle the is used (+) in sybase the is used * and in postgreSql? thank you Hipp Mauricio
On Mon, 15 Jan 2001, Alexaki Sofia wrote: > Hello, > > A) > I am going to load a huge amount of data in the DBMS using JDBC > and I want to reduce as much as possible the required loading time. > > Initially I loaded all data in one transaction. Subsequently, I increased > the number of buffers and disabled fsync() (-o -F) and I loaded the > data again but the performance was almost unchanged. Does it make sense?? Yes. Syncing is only done at the end of a transaction. The difference between one fsync() and none is not measurable. Large amounts of buffers helps in read performance, not write performance. > How can I improve performance? To note that no indexes are created on the > tables and that I load both "insert into" and "create table" statements. > ------------------------------ > B) > I want to reduce the space (8 Kb) allocated by > DBMS when more space is required to load the data in a table. I reduced > the parameter BLCKSZ but the space allocated remains the same, that is 8 > KB. > How can the allocated space be reduced? If the space was reduced > would loading time and query time increase? No. Reducing the blocksize does not sound like a good idea. Why would you want to reduce the amount of allocated space? I actually wish that Postgres would preallocate a lot of blocks in advance (ie. 100 x 8KB). That would probably improve performance, as it would not have to keep re-extending the file. The best way to improve write performance, is to get faster disks connecting to a fast interface. Also, get lots of disks (4 to 6). Pair them into RAID1 sets, then strip over them. Use a RAID controler with a battery backed cache in write-back (write cache) mode. > Thank you in advance for your help > Sofia Alexaki Tom
Mauricio Hipp Werner wrote: > > I need help, which is the symbol used in postgreSql to carry out the outer > join. > > in oracle the is used (+) > in sybase the is used * and > in postgreSql? The PostgreSQL outer join is accomplished using SQL92 syntax. You will not find real outer joins *except* in the current beta release, and beware that there may be some tweaks to the grammar to help with conformance to the standard. In any case, check the standard or try something like "select * from t1 left outer join t2 on (i)".
On Mon, Jan 15, 2001 at 06:16:00PM -0400, Mauricio Hipp Werner wrote: > I need help, which is the symbol used in postgreSql to carry out the outer > join. > > in oracle the is used (+) > in sybase the is used * and > in postgreSql? I don't really understand your question, but from src/test/regress/sql/join.sql some examples of use are: -- -- Outer joins -- Note that OUTER is a noise word -- SELECT '' AS "xxx", * FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i); SELECT '' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i); SELECT '' AS "xxx", * FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i); SELECT '' AS "xxx", * FROM J1_TBL RIGHT JOIN J2_TBL USING (i); SELECT '' AS "xxx", * FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i); SELECT '' AS "xxx", * FROM J1_TBL FULL JOIN J2_TBL USING (i); SELECT '' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1); SELECT '' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1); Cheers, Patrick
On Mon, 15 Jan 2001, Mauricio Hipp Werner wrote: MHW> in oracle the is used (+) MHW> in sybase the is used * and MHW> in postgreSql? In PostgreSQL we have no outer joins at all. But it is promised they are going to arrive soon, perhaps in 7.1? Bye Borek -- ===================================================================== BOREK LUPOMESKY Usti nad Labem, Czech Republic, Europe WWW: http://www.volny.cz/borekl/ PGP keyid: B6A06AEB ==========[ MIME/ISO-8859-2 & PGP encrypted mail welcome ]===========
Hannu Krosing wrote: > Tom Lane wrote: > > > > > > I've heard lots of people want to increase BLCKSZ, but you're the first > > one who ever wanted to reduce it. You sure you want to do this? It's > > going to make the maximum row length uncomfortably short. > > And it may even not work, as some system tables (that are also affected > by this) > may need the full 8k. AFAIK it has never been tested with BLCKSZ < 8k Except for two different sorted (but correct) results while selecting inherited tables in "misc", regression testspassed with 2K. Why shouldn't it work? All the catalogs that require really big data have toast tables now. Anyway, the 8K default BLCKSZ already restricts index tuples to 2700 bytes. So I wouldn't recommend it at all. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Thomas Lockhart wrote: > > Mauricio Hipp Werner wrote: > > > > I need help, which is the symbol used in postgreSql to carry out the outer > > join. > > > > in oracle the is used (+) > > in sybase the is used * and > > in postgreSql? > > The PostgreSQL outer join is accomplished using SQL92 syntax. You will > not find real outer joins *except* in the current beta release, and > beware that there may be some tweaks to the grammar to help with > conformance to the standard. > > In any case, check the standard or try something like "select * from t1 > left outer join t2 on (i)". To get a feel you could use MS Access visual query builder and then view the source. I have not checked it lately, but it very likely produces SQL92 compliant outer joins. ----------- Hannu
On Tue, Jan 16, 2001 at 06:44:18PM +0200, Hannu Krosing wrote: > > To get a feel you could use MS Access visual query builder and then view > the source. > I have not checked it lately, but it very likely produces SQL92 > compliant outer joins. > I fired up MS-Access 97SR1, just to see, and here's one result: SELECT Institution.InstitutionName, InstituteAssignment.PersonID FROM Institution LEFT JOIN InstituteAssignment ON Institution.InstID = InstituteAssignment.InstID; I'm surprised: looks pretty standard, to me. Ross -- Open source code is like a natural resource, it's the result of providing food and sunshine to programmers, and then staying out of their way. [...] [It] is not going away because it has utility for both the developers and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.