pgsql-performance@postgresql.org
At 17:14 10/23/2003, Vivek Khera wrote: > "AL" == Allen Landsidel <[EMAIL PROTECTED]> writes: AL> I recently built a rather powerful machine to be used in a heavily AL> accessed database.. the machine is a dual AthlonMP 2800+, 2GB of AL> PC2100 ECC, and a 4x18GB RAID-0 using 15k rpm fujitsu MAS drives on a AL> 4ch u160 ICP-Vortex card with 256MB of cache. The only recommendation I'd make is to switch from RAID0 to RAID10, unless you can afford the downtime (and loss of data) when one of your drives takes a vacation. Also, is your RAID card cache battery backed up? If no, then you lose the ability to use write-back and this costs *dearly* in performance. I'm planning to move it to -10 or -5 (or even -50) once we have more money to spend on drives. As it is right now though, I couldn't spare the space.. The box this was moved from was a 2x1000 P3 with a single u160 drive.. Battery backup is something I really should have gotten on the memory but I spaced out when placing the order, it'll come in the future. I'm kind of "living on the edge" here with regard to no bbu on the raid and using raid-0 I know.. but it's for a short time, and I don't think in the scheme of things this is any more failure-prone than the crummy setup it was on before. Backup and backup often, I know that mantra very well and live by it. :) AL> The box runs FreeBSD, tracking RELENG_4 (-STABLE) and PostGreSQL 7.3.4 AL> from ports (7.3.4_1) An excellent choice. :-) I recognize you from those lists.. didn't notice the Ph.D. before though.. but yes, I'm a huge FreeBSD fan.. I didn't need anyone to talk me into that particular choice. ;) AL> I run a 'vacuum analyze verbose' on the database in question every AL> hour, and a reindex on every table in the database every six hours, AL> 'vacuum full' is run manually as required perhaps anywhere from once a AL> week to once a month. I realize the analyze may not be running often AL> enough and the reindex more often than need be, but I don't think AL> these are adversely affecting performance very much; degredation over AL> time does not appear to be an issue. Personally, I don't think you need to reindex that much. And I don't think you need to vacuum full *ever* if you vacuum often like you do. Perhaps reducing the vacuum frequency may let you reach a steady state of disk usage? Well I had the vacuums running every 15 minutes for a while.. via a simple cron script I wrote just to make sure no more than one vacuum ran at once, and to 'nice' the job.. but performance on the db does suffer a bit during vacuums or so it seems. The performance doesn't degrade noticably after only an hour without a vacuum though, so I'd like to make the state of degraded performance more periodic -- not the general rule during 24/7 operation. I'll monkey around more with running the vacuum more often and see if the performance hit was more imagined than real. Depending on how many concurrent actions you process, perhaps you can use a temporary table for each, so you don't have to delete many rows when you're done. I'd love to but unfortunately the daemons that use the database are a mess, more or less 'unsupported' at this point.. thankfully they're being replaced along with a lot of performance-hurting SQL. On my busy tables, I vacuum every 6 hours. The vacuum analyze is run on the entire DB nightly. I reindex every month or so my most often updated tables that show index bloat. Watch for bloat by monitoring the size of your indexes: SELECT relname,relpages FROM pg_class WHERE relname LIKE 'some_table%' ORDER BY relname; Thanks for that tidbit.. maybe I'll cron something else to grab the values once a day or so and archive them in another table for history.. make my life easier. ;) AL> Related kernel configuration options: AL> ... AL> cpu I686_CPU AL> maxusers 256 let the system autoconfigure maxusers... Are you sure about this? I have always understood that explicitly setting this value was the best thing to do if you knew the maximum number of users you would encounter, as the kernel doesn't have to 'guess' at structure sizes and the like, or grow them later.. AL> ... AL> options MAXDSIZ="(1024UL*1024*1024)" AL> options MAXSSIZ="(512UL*1024*1024)" AL> options DFLDSIZ="(512UL*1024*1024)" above are ok at defaults. These are related to something else.. a linux developer on the system used to the way it'll always allow you access to all the memory on a machine and just kill a random process to give you memory if you allocated more than was free.. ;) He didn't know processes were getting killed, but the defaults turned out to be not high enough. This will get turned back down to default once he's done migrating everything into the new database and his app no longer needs to run there. I just mentioned them in case they could adversely affect performance as-is. AL> options SHMMAXPGS=65536 perhaps bump this and increase your
[PERFORM] PostgreSQL 7.4 beta for windows
On Friday 24 October 2003 02:21, you wrote: > Thank You Sir, > I'm very Greatfull with your answer , > 1. I don't use Cywig, my postgres is 7.4 , and I only > runs it from command prompt on windows(postmaster); Do you mean the native port from here: http://momjian.postgresql.org/main/writings/pgsql/win32.html Please be aware that this is experimental, and you might expect problems. If not, can you say where you got it from? > 2.I don't know what i must tuning on (is "PGHBACONF" > isn't it ?),and I use it in Delphi ,when I run the > program ,It runs very slow not like i run it with > mySQL ,What is The Problem , Please Help Me There are two important files: pg_hba.conf - controls access to the database postgresql.conf - config/tuning See the "performance" section at: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php There are two articles there which cover tuning and adds information about the various other settings. > My computer is : > PIII 600B, 256 MB, Postgres 7.4 Beta (without cywig), > delphi version 7, and my connection to DB with > dbexpress, OK - does dbexpress use ODBC, or does it connect directly? > Dedy Styawan > sorry my english is not so good... Your English is fine sir. If you can subscribe to the performance list, details at: http://www.postgresql.org/lists.html That way, others will be able to help too. I've CC'd this to the performance list ready for you. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] slow select
"Medora Schauer" <[EMAIL PROTECTED]> writes:
> Merge Join (cost=0.00..287726.10 rows=100221 width=58) (actual time=61.60..5975.63
> rows=100425 loops=1)
>Merge Cond: (("outer".shot_line_num = "inner".shot_line_num) AND
> ("outer".shotpoint = "inner".shotpoint))
>-> Index Scan using hsot_record_idx on shot_record r (cost=0.00..123080.11
> rows=100425 width=46) (actual time=24.15..2710.31 rows=100425 loops=1)
>-> Index Scan using shotpoint_idx on shotpoint p (cost=0.00..467924.54
> rows=290106 width=12) (actual time=37.38..1379.64 rows=100749 loops=1)
> Total runtime: 6086.32 msec
>
> So why did were the indices not used before when they yield a better plan?
There's another reason. Notice it thinks the second table will return 290k
records. In fact it only returns 100k records. So it's optimizing on the
assumption that it will have to read 3x as many records as it actually will...
I'm not clear if there's anything you can do to improve this estimate though.
--
greg
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] slow select
Vivek, > Assuming your OS will use that much RAM for the cache... the whole > world's not Linux :-) It's not? Darn! Actually, what OS's can't use all idle ram for kernel cache? I should note that in my performance docs -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] vacuum locking
Rob Nagler <[EMAIL PROTECTED]> writes: > Incorrect. If the tuples smaller, Oracle does the right thing. If > there's enough space in the page, it shifts the tuples to make room. > That's what pctfree, pctused and pctincrease allow you to control. > It's all in memory so its fast, and I don't think it has to update any > indices. Note that pctfree/pctused are a big performance drain on the usual case. Try setting them to 0/100 on a table that doesn't get updates (like a many-many relation table) and see how much faster it is to insert and scan. > > transactions that touch enough tuples to overflow your undo segment > > That's easily configured, and hasn't been a problem in the databases > I've managed. Judging by the number of FAQ lists out there that explain various quirks of rollback segment configuration I wouldn't say it's so easily configured. > > (or even just sit there for a long time, preventing you from recycling > > That's probably bad software or a batch system--which is tuned > differently. Any OLTP system has to be able to partition its problems > to keep transactions short and small. If it doesn't, it will not be > usable. Both DSS style and OLTP style databases can be accomodated with rollback segments though it seems to me that DSS style databases lose most of the advantage of rollback segments and optimistic commit. The biggest problem is on systems where there's a combination of both users. You need tremendous rollback segments to deal with the huge volume of oltp transactions that can occur during a single DSS query. And the DSS query performance is terrible as it has to check the rollback segments for a large portion of the blocks it reads. > Oracle seems to make the assumption that data changes, Arguably it's the other way around. Postgres's approach wins whenever most of the tuples in a table have been updated, in that case it just has to scan the whole table ignoring old records not visible to the transaction. Oracle has to consult the rollback segment for any recently updated tuple. Oracle's wins in the case where most of the tuples haven't changed so it can just scan the table without consulting lots of rollback segments. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] slow select
> "JB" == Josh Berkus <[EMAIL PROTECTED]> writes: JB> Vivek, >> Assuming your OS will use that much RAM for the cache... the whole >> world's not Linux :-) JB> It's not? Darn! :-) JB> Actually, what OS's can't use all idle ram for kernel cache? I JB> should note that in my performance docs FreeBSD. Limited by the value of "sysctl vfs.hibufspace" from what I understand. This value is set at boot based on available RAM and some other tuning parameters. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
pgsql-performance@postgresql.org
> "AL" == Allen Landsidel <[EMAIL PROTECTED]> writes: AL> maxusers 256 >> >> let the system autoconfigure maxusers... AL> Are you sure about this? I have always understood that explicitly AL> setting this value was the best thing to do if you knew the maximum Yes, recent freebsd kernels autosize various tables and limits based on existing RAM. It does pretty well. AL> These are related to something else.. a linux developer on the system AL> used to the way it'll always allow you access to all the memory on a Ahhh... I guess we don't believe in multi-user systems ;-) AL> options SHMMAXPGS=65536 >> >> perhaps bump this and increase your shared buffers. I find that if >> you do lots of writes, having a few more shared buffers helps. AL> Any ideas how much of a bump, or does that depend entirely on me and I AL> should just play with it? Would doubling it be too much of a bump? I use 262144 for SHMMAXPGS and SHMALL. I also use about 3 shared buffers. AL> I'll see if sysctl lets me write this value, or if it's a kernel AL> config option I missed, unless you have remembered between then and you need to bump some header file constant and rebuild the kernel. it also increases the granularity of how the buffer cache is used, so I'm not sure how it affects overall system. nothing like an experiment... AL> Given this and the above about the controllers onboard cache (not to AL> mention the per-drive cache) do you think I'll still need to lower AL> effective_cache_size? It is hard to say. If you tell PG you have more than you do, I don't know what kind of decisions it will make incorrectly. I'd rather be conservative and limit it to the RAM that the system says it will use. The RAM in the controller is not additive to this -- it is redundant to it, since all data goes thru that cache into the main memory. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
pgsql-performance@postgresql.org
> "AL" == Allen Landsidel <[EMAIL PROTECTED]> writes: AL> Well I had the vacuums running every 15 minutes for a while.. via a AL> simple cron script I wrote just to make sure no more than one vacuum AL> ran at once, and to 'nice' the job.. but performance on the db does "nice"-ing the client does nothing for the backend server that does the actual work. You need to track down the PID of the backend server running the vacuum and renice *it* to get any effect. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
pgsql-performance@postgresql.org
On Fri, 24 Oct 2003, Vivek Khera wrote: > > "AL" == Allen Landsidel <[EMAIL PROTECTED]> writes: > > AL> Well I had the vacuums running every 15 minutes for a while.. via a > AL> simple cron script I wrote just to make sure no more than one vacuum > AL> ran at once, and to 'nice' the job.. but performance on the db does > > "nice"-ing the client does nothing for the backend server that does > the actual work. You need to track down the PID of the backend server > running the vacuum and renice *it* to get any effect. Note that Tom has mentioned problems with possible deadlocks when nicing individual backends before, so proceed with caution here. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
pgsql-performance@postgresql.org
> "sm" == scott marlowe writes: sm> Note that Tom has mentioned problems with possible deadlocks when nicing sm> individual backends before, so proceed with caution here. I can see possible starvation, but if scheduling changes cause deadlocks, then there's something wrong with the design. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Tuning for mid-size server
So what is the ceiling on 32-bit processors for RAM? Most of the 64-bit vendors are pushing Athalon64 and G5 as "breaking the 4GB barrier", and even I can do the math on 2^32. All these 64-bit vendors, then, are talking about the limit on ram *per application* and not per machine? 64-bit CPU on 64-bit OS. Up to physical address limit for anything and everything. 64-bit CPU on 32-bit OS. Up to 4GB minus the kernel allocation -- which is usually 2GB on Windows and Linux. On Windows, you can up this to 3GB by using the /3GB switch. Linux requires a kernel recompile. PAE is then used to "move" the memory window to point to different areas of the physical memory. ---(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
[PERFORM] Performance Concern
Greetings. I have a table that will require 100,000 rows initially. Assume the following (some of the field names have been changed for confidentiality reasons): CREATE TABLE baz ( baz_number CHAR(15) NOT NULL, customer_id CHAR(39), foobar_id INTEGER, is_cancelled BOOL DEFAULT false NOT NULL, create_user VARCHAR(60) NOT NULL, create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, last_update_user VARCHAR(60) NOT NULL, last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, CONSTRAINT PK_baz PRIMARY KEY (baz_number) ); ALTER TABLE baz ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id); ALTER TABLE baz ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id); Using JDBC, it took approximately one hour to insert 100,000 records. I have an algorithm to generate a unique baz_number - it is a mixture of alpha and numerics. There is a purchase table; one purchase can have many associated baz records, but the baz records will be pre-allocated - baz.customer_id allows null. The act of purchasing a baz will cause baz.customer_id to be populated from the customer_id (key) field in the purchase table. If it took an hour to insert 100,000 records, I can only imagine how much time it will take if one customer were to attempt to purchase all 100,000 baz. Certainly too long for a web page. I've not had to deal with this kind of volume in Postgres before; I have my suspicions on what is wrong here (could it be using a CHAR( 15 ) as a key?) but I'd *LOVE* any thoughts. Would I be better off making the key an identity field and not indexing on baz_number? Thanks in advance for any help. __ John Pagakis Email: [EMAIL PROTECTED] "The best way to make your dreams come true is to wake up." -- Paul Valery This signature generated by ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. www.spazmodicfrog.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] Use of multipart index with "IN"
I have a reporting system that does regular queries on a table with a
multipart index. I am running version 7.3.4. Here is the table
definition:
Table "public.ds_rec_fld"
Column | Type | Modifiers
---+-+---
dsid | character varying(20) | not null
recid | integer | not null
field_name| character varying(20) | not null
option_tag| character varying(10) | not null
option_value | integer |
field_text| character varying(2000) |
field_type_cd | character varying(8)|
Indexes: ds_rf_ndx1 btree (recid, field_name, option_value)
Normally queries are done using recid and field_name, so Postgresql
returns rows very quickly as expected. Here is a sample explain
analyze output for a typical query:
db=> explain analyze
db-> select field_name, option_tag from ds_rec_fld where recid = 3000
and field_name = 'Q3A1';
QUERY PLAN
-
Index Scan using ds_rf_ndx1 on ds_rec_fld (cost=0.00..163.09 rows=40
width=38) (actual time=0.06..0.07 rows=1 loops=1)
Index Cond: ((recid = 3001) AND (field_name = 'Q3A1'::character
varying))
Total runtime: 0.12 msec
(3 rows)
The problem comes in when we are selecting multiple field_name values
in one query. The normal SQL syntax we have been using is like this:
select field_name, option_tag from ds_rec_fld where recid = 3001 and
field_name in ('Q3A1', 'Q3A9');
This is just a simplified example, at times there can be a lot of
field_name values in one query in the "in" clause. Here postgresql
refuses to use the full index, instead doing a filter based on part of
the first recid part of index. Here is the explain analyze output:
Index Scan using ds_rf_ndx1 on ds_rec_fld (cost=0.00..30425.51
rows=80 width=38) (actual time=0.18..1.08 rows=2 loops=1)
Index Cond: (recid = 3001)
Filter: ((field_name = 'Q3A1'::character varying) OR (field_name =
'Q3A9'::character varying))
Total runtime: 1.12 msec
(4 rows)
So, 10 times longer. This is an issue because at times we are
iterating through thousands of recid values.
I did a vacuum analyze, adjusted random_page_cost, etc. all to no
avail.
I also noticed that the problem goes away when I reformat the query
like this:
select field_name, option_tag from ds_rec_fld where
(recid = 3001 and field_name = 'Q3A1') or
(recid = 3001 and field_name = 'Q3A9')
Here is the explain analyze output for this:
Index Scan using ds_rf_ndx1, ds_rf_ndx1 on ds_rec_fld
(cost=0.00..326.57 rows=80 width=38) (actual time=0.07..0.10 rows=2
loops=1)
Index Cond: (((recid = 3001) AND (field_name = 'Q3A1'::character
varying)) OR ((recid = 3001) AND (field_name = 'Q3A9'::character
varying)))
Total runtime: 0.16 msec
(3 rows)
Much better. So I have partially solved my own problem, but there are
other places that this is not this simple to fix.
Therefore, my question is, is there some way to force postgresql to use
the full index and still stick with the shorter "field_name in ('...',
'...')" syntax?
If anyone has any thoughts please let me know. Also it strikes me that
perhaps the optimizer could be tweaked to treat the first case like the
second one. Thanks in advance,
Rob
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.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: [PERFORM] Tuning for mid-size server
Anjan Dave wrote: Shared_buffers (25% of RAM / 8KB)) = 8589934592 * .25 / 8192 = 262144 250,000 is probably the max you can use due to the 2GB process limit unless you recompile the Linux Kernel to use 3GB process/1GB kernel. Yes, I've got 8GB also and I started at 262144 and kept working my way down until Linux would allocate the memory. Sort_mem (4% of RAM / 1KB) = 335544. We'll take about half of that - 167772 Effective_cache_size = 262144 (same as shared_buffers - 25%) This should reflect the amount of memory available for caching. And unless you plan on running a ton of memory hogging software on the same machine, you probably will have 6GB available as cache. Top on my system confirms the 6GB number so I've got my setting at 750,000. (Left a little space for OS/programs/etc.) In the /etc/sysctl file: = kernel.shmall = 536870912 (512MB) SHMALL Total amount of shared memory available (bytes or pages) kernel.shmmax = 536870912 (512MB) SHMMAX Maximum size of shared memory segment (bytes) Ain't gonna happen unless you recompile the linux kernel to do 3/1. Through trial-and-error, I've found the largest number is: 2,147,483,648 Are the above settings ok to begin with? Are there any other parameters that I should configure now, or monitor lateron? Above is pretty good. I'd also bump up the free space map settings and maybe try to symlink the pg_xlog directory (log files) to a seperate drive. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] PostgreSQL data on a NAS device ?
I have never worked with a XEON CPU before. Does anyone know how it performs running PostgreSQL 7.3.4 / 7.4 on RedHat 9 ? Is it faster than a Pentium 4? I believe the main difference is cache memory, right? Aside from cache mem, it's basically a Pentium 4, or am I wrong? Well, see the problem is of course, there's so many flavors of P4s and Xeons that it's hard to tell which is faster unless you specify the exact model. And even then, it would depend on the workload. Would a Xeon/3GHz/2MB L3/400FSB be faster than a P4C/3GHz/800FSB? No idea as no one has complete number breakdowns on these comparisons. Oh yeah, you could get a big round number that says on SPEC or something one CPU is faster than the other but whether that's faster for Postgres and your PG app is a totally different story. That in mind, I wouldn't worry about it. The CPU is probably plenty fast for what you need to do. I'd look into two things in the server: memory and CPU expandability. I know you already plan on 4GB but you may need even more in the future. Few things can dramatically improve performance more than moving disk access to disk cache. And if there's a 2nd socket where you can pop another CPU in, that would leave you extra room if your server becomes CPU limited. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] vacuum locking
I ran into the same problem with VACUUM on my Linux box. If you are running Linux, take a look at "elvtune" or read this post: http://groups.google.com/groups?q=stephen+vacuum+linux&hl=en&lr=&ie=UTF-8&se lm=gRdjb.7484%241o2.77%40nntp-post.primus.ca&rnum=3 Regards, Stephen "Rob Nagler" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Manfred Koizar writes: > > ISTM you are VACCUMing too aggressively. You are reclaiming less than > > 1% and 0.005%, respectively, of tuples. I would increase FSM settings > > to ca. 1000 fsm_relations, 10 fsm_pages and VACUUM *less* often, > > say every two hours or so. > > I did this. We'll see how it goes. > > > ... or configure autovacuum to VACUUM a table when it has 10% dead > > tuples. > > This solution doesn't really fix the fact that VACUUM consumes the > disk while it is running. I want to avoid the erratic performance on > my web server when VACUUM is running. > > mfg, > Rob > > > ---(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 > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Performance Concern
On Thu, 2003-10-23 at 08:21, John Pagakis wrote: > Greetings. > > I have a table that will require 100,000 rows initially. > > Assume the following (some of the field names have been changed for > confidentiality reasons): > > CREATE TABLE baz ( > baz_number CHAR(15) NOT NULL, > customer_id CHAR(39), > foobar_id INTEGER, > is_cancelled BOOL DEFAULT false NOT NULL, > create_user VARCHAR(60) NOT NULL, > create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, > last_update_user VARCHAR(60) NOT NULL, > last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, > CONSTRAINT PK_baz PRIMARY KEY (baz_number) > ); > > ALTER TABLE baz > ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id); > > ALTER TABLE baz > ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id); > > > Using JDBC, it took approximately one hour to insert 100,000 records. I > have an algorithm to generate a unique baz_number - it is a mixture of alpha > and numerics. Using an int for identification is certainly suggested, however it sounds like you may be short a few indexes on the foreign key'd fields. EXPLAIN ANALYZE output is always nice.. signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Performance Concern
John, Are you treating each insertion as a separate transaction? If so the performance will suffer. I am doing the same thing in building a data warehouse using PG. I have to load millions of records each night. I do two different things: 1) If I need to keep the insertions inside the java process I turn off auto-commit and every n insertions (5000 seems to give me the best performance for my setup) issue a commit. Make sure you do a final commit in a finally block so you don't miss anything. 2) Dump all the data to a file and then use a psql COPY (columns) FROM 'file path' call to load it. Very fast. --sean John Pagakis wrote: Greetings. I have a table that will require 100,000 rows initially. Assume the following (some of the field names have been changed for confidentiality reasons): CREATE TABLE baz ( baz_number CHAR(15) NOT NULL, customer_id CHAR(39), foobar_id INTEGER, is_cancelled BOOL DEFAULT false NOT NULL, create_user VARCHAR(60) NOT NULL, create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, last_update_user VARCHAR(60) NOT NULL, last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, CONSTRAINT PK_baz PRIMARY KEY (baz_number) ); ALTER TABLE baz ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id); ALTER TABLE baz ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id); Using JDBC, it took approximately one hour to insert 100,000 records. I have an algorithm to generate a unique baz_number - it is a mixture of alpha and numerics. There is a purchase table; one purchase can have many associated baz records, but the baz records will be pre-allocated - baz.customer_id allows null. The act of purchasing a baz will cause baz.customer_id to be populated from the customer_id (key) field in the purchase table. If it took an hour to insert 100,000 records, I can only imagine how much time it will take if one customer were to attempt to purchase all 100,000 baz. Certainly too long for a web page. I've not had to deal with this kind of volume in Postgres before; I have my suspicions on what is wrong here (could it be using a CHAR( 15 ) as a key?) but I'd *LOVE* any thoughts. Would I be better off making the key an identity field and not indexing on baz_number? Thanks in advance for any help. __ John Pagakis Email: [EMAIL PROTECTED] "The best way to make your dreams come true is to wake up." -- Paul Valery This signature generated by ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. www.spazmodicfrog.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Performance Concern
[EMAIL PROTECTED] ("John Pagakis") writes:
> Greetings.
>
> I have a table that will require 100,000 rows initially.
>
> Assume the following (some of the field names have been changed for
> confidentiality reasons):
>
> CREATE TABLE baz (
> baz_number CHAR(15) NOT NULL,
> customer_id CHAR(39),
> foobar_id INTEGER,
> is_cancelled BOOL DEFAULT false NOT NULL,
> create_user VARCHAR(60) NOT NULL,
> create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL,
> last_update_user VARCHAR(60) NOT NULL,
> last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL,
> CONSTRAINT PK_baz PRIMARY KEY (baz_number)
> );
>
> ALTER TABLE baz
> ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id);
>
> ALTER TABLE baz
> ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id);
>
> Using JDBC, it took approximately one hour to insert 100,000 records. I
> have an algorithm to generate a unique baz_number - it is a mixture of alpha
> and numerics.
Question #1: How did you do the inserts?
If AUTO-COMMIT was turned on, then that would indicate that you
invoked 100,000 transactions, and that would contribute considerably
to the process being slow. Put them all in as one transaction and
you'd probably see it run in a fraction of the time.
Question #2. Do you have indices on purchase(customer_id) and on
foobar(foobar_id)?
If not, then the foreign key check would be rather inefficient.
> There is a purchase table; one purchase can have many associated baz
> records, but the baz records will be pre-allocated - baz.customer_id
> allows null. The act of purchasing a baz will cause baz.customer_id
> to be populated from the customer_id (key) field in the purchase
> table.
>
> If it took an hour to insert 100,000 records, I can only imagine how
> much time it will take if one customer were to attempt to purchase
> all 100,000 baz. Certainly too long for a web page.
I take it that each "baz" is a uniquely identifiable product, akin to
(say) an RSA certificate or the like?
By the way, if you set up a stored procedure in PostgreSQL that can
generate the "baz_number" identifiers, you could probably do the
inserts Right Well Fast...
Consider the following. I have a stored procedure, genauth(), which
generates quasi-random values. (They're passwords, sort of...)
cctld=# explain analyze insert into baz (baz_number, create_user, last_update_user)
cctld-# select substr(genauth(), 1, 15), 'cbbrowne', 'cbbrowne' from big_table;
QUERY PLAN
---
Seq Scan on big_table (cost=0.00..789.88 rows=28988 width=0) (actual
time=0.20..1713.60 rows=28988 loops=1)
Total runtime: 3197.40 msec
(2 rows)
It took about 3 seconds to insert 28988 rows into baz. (big_table,
also renamed, to protect the innocent, has 28988 rows. I didn't care
about its contents, just that it had a bunch of rows.)
And the above is on a cheap desktop PC with IDE disk.
> I've not had to deal with this kind of volume in Postgres before; I
> have my suspicions on what is wrong here (could it be using a CHAR(
> 15 ) as a key?) but I'd *LOVE* any thoughts.
> Would I be better off making the key an identity field and not
> indexing on baz_number?
That might be something of an improvement, but it oughtn't be
cripplingly different to use a text field rather than an integer.
What's crippling is submitting 100,000 queries in 100,000
transactions. Cut THAT down to size and you'll see performance return
to being reasonable.
--
"cbbrowne","@","libertyrms.info"
Re: [PERFORM] PostgreSQL data on a NAS device ?
Just an interesting comparison: I don't have the specifics, but a Dell 2 x 2.4GHZ/512KB L3 / 2GB RAM machine timed a query much faster than an older Sun E4000 with 6 x ~300MHZ CPUs / 2GB RAM. One on RH(8 or 9, don't remember) and one on Solaris 9. -anjan -Original Message- From: William Yu [mailto:[EMAIL PROTECTED] Sent: Tue 10/21/2003 12:12 PM To: [EMAIL PROTECTED] Cc: Subject: Re: [PERFORM] PostgreSQL data on a NAS device ? > I have never worked with a XEON CPU before. Does anyone know how it performs > running PostgreSQL 7.3.4 / 7.4 on RedHat 9 ? Is it faster than a Pentium 4? > I believe the main difference is cache memory, right? Aside from cache mem, > it's basically a Pentium 4, or am I wrong? Well, see the problem is of course, there's so many flavors of P4s and Xeons that it's hard to tell which is faster unless you specify the exact model. And even then, it would depend on the workload. Would a Xeon/3GHz/2MB L3/400FSB be faster than a P4C/3GHz/800FSB? No idea as no one has complete number breakdowns on these comparisons. Oh yeah, you could get a big round number that says on SPEC or something one CPU is faster than the other but whether that's faster for Postgres and your PG app is a totally different story. That in mind, I wouldn't worry about it. The CPU is probably plenty fast for what you need to do. I'd look into two things in the server: memory and CPU expandability. I know you already plan on 4GB but you may need even more in the future. Few things can dramatically improve performance more than moving disk access to disk cache. And if there's a 2nd socket where you can pop another CPU in, that would leave you extra room if your server becomes CPU limited. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] Memcache
Just thought i'd mention that on top of optimising postgres as much as possible, don't forget how much something like memcached can do for you http://www.danga.com/memcached/ we use it on www.last.fm - most pages only take one or two database hits, compared with 30 to 40 when memcache is turned off. Rich. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
pgsql-performance@postgresql.org
Vivek Khera wrote: > > "sm" == scott marlowe writes: > > > sm> Note that Tom has mentioned problems with possible deadlocks when nicing > sm> individual backends before, so proceed with caution here. > > I can see possible starvation, but if scheduling changes cause > deadlocks, then there's something wrong with the design. Yes, I think Tom's concern was priority inversion, where a low priority process holds a lock while a higher one waits for it. -- 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: [PERFORM] Performance Concern
Christopher Browne kirjutas R, 24.10.2003 kell 22:10: > That might be something of an improvement, but it oughtn't be > cripplingly different to use a text field rather than an integer. I suspect his slowness comes from not running analyze when it would be time to start using indexes for fk checks - if you run analyze on an empty table and then do 1 inserts, then all these will run their checks using seqscan, as this is the fastest way to do it on an empty table ;) > What's crippling is submitting 100,000 queries in 100,000 > transactions. Cut THAT down to size and you'll see performance return > to being reasonable. even this should not be too crippling. I 0nce did some testing for insert performance and got about 9000 inserts/sec on 4 CPU Xeon with 2GB ram and RAID-5 (likely with battery backed cache). This 9000 dropped to ~250 when I added a primary key index (to a 60.000.000 record table, so that the pk index fit only partly in memory), all this with separate transactions, but with many clients running concurrently. (btw., the clients were not java/JDBC but Python/psycopg) With just one client you are usually stuck to 1 trx/disk revolution, at least with no battery-backed write cache. even 250/sec should insert 1 in 40 sec. -- Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] vacuum locking
Stephen writes: > I ran into the same problem with VACUUM on my Linux box. If you are running > Linux, take a look at "elvtune" or read this post: The default values were -r 64 -w 8192. The article said this was "optimal". I just futzed with different values anywere from -w 128 -r 128 to -r 16 -w 8192. None of these mattered much when vacuum is running. This is a RAID1 box with two disks. Even with vacuum and one other postmaster running, it's still got to get a lot of blocks through the I/O system. Rob ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] vacuum locking
Mario Weilguni writes: > of course both approaches have advantages, it simply depends on the usage > pattern. A case where oracle really rules over postgresql are m<-->n > connection tables where each record consist of two foreign keys, the > overwrite approach is a big win here. That's usually our case. My company almost always has "groupware" problems to solve. Every record has a "realm" (security) foreign key and typically another key. The infrastructure puts the security key on queries to avoid returning the wrong realm's data. Rob ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] vacuum locking
Vivek Khera writes: > Also, how close are you to the capacity of your disk bandwidth? I > don't see that in your numbers. I know in freebsd I can run "systat > -vmstat" and it gives me a percentage of utilization that lets me know > when I'm near the capacity. The vacuum totally consumes the system. It's in a constant "D". As near as I can tell, it's hitting all blocks in the database. The problem is interactive performance when vacuum is in a D state. Even with just two processes doing "stuff" (vacuum and a select, let's say), the select is very slow. My understanding of the problem is that if a query hits the disk hard (and many of my queries do) and vacuum is hitting the disk hard, they contend for the same resource and nobody wins. The query optimizer has lots of problems with my queries and ends up doing silly sorts. As a simple example, one query goes like this: select avg(f1) from t1 group by f2; This results in a plan like: Aggregate (cost=171672.95..180304.41 rows=115086 width=32) -> Group (cost=171672.95..177427.26 rows=1150862 width=32) -> Sort (cost=171672.95..174550.10 rows=1150862 width=32) Sort Key: f2 -> Seq Scan on t1 (cost=0.00..39773.62 rows=1150862 width=32) This is of course stupid, because it sorts a 1M rows, which probably means it has to hit disk (sort_mem can only be so large). Turns out there are only about 20 different values of f2, so it would be much better to aggregate without sorting. This is the type of query which runs while vacuum runs and I'm sure the two are just plain incompatible. vacuum is read intensive and this query is write intensive. Rob ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] vacuum locking
Greg Stark writes:
> Note that pctfree/pctused are a big performance drain on the usual case. Try
> setting them to 0/100 on a table that doesn't get updates (like a many-many
> relation table) and see how much faster it is to insert and scan.
Right. You can optimize each table independently. The "usual" case
doesn't exist in most databases, I've found, which is why Oracle does
better.
> Judging by the number of FAQ lists out there that explain various quirks of
> rollback segment configuration I wouldn't say it's so easily configured.
Maybe we just got lucky. :-)
> The biggest problem is on systems where there's a combination of both users.
As is ours.
> You need tremendous rollback segments to deal with the huge volume of oltp
> transactions that can occur during a single DSS query. And the DSS query
> performance is terrible as it has to check the rollback segments for a large
> portion of the blocks it reads.
The DSS issues only come into play I think if the queries are long.
This is our problem. Postgres does a bad job with DSS, I believe. I
mentioned the select avg(f1) from t1 group by f2 in another message.
If it were optimized for "standard" SQL, such as, avg, sum, etc., I
think it would do a lot better with DSS-type problems. Our problem
seems to be that the DSS queries almost always hit disk to sort.
> Arguably it's the other way around. Postgres's approach wins whenever most of
> the tuples in a table have been updated, in that case it just has to scan the
> whole table ignoring old records not visible to the transaction. Oracle has to
> consult the rollback segment for any recently updated tuple. Oracle's wins in
> the case where most of the tuples haven't changed so it can just scan the
> table without consulting lots of rollback segments.
I see what you're saying. I'm not a db expert, just a programmer
trying to make his queries go faster, so I'll acknowledge that the
design is theoretically better.
In practice, I'm still stuck. As a simple example, this query
select avg(f1) from t1 group by f2
Takes 33 seconds (see explain analyze in another note in this thread)
to run on idle hardware with about 1GB available in the cache. It's
clearly hitting disk to do the sort. Being a dumb programmer, I
changed the query to:
select f1 from t1;
And wrote the rest in Perl. It takes 4 seconds to run. Why? The
Perl doesn't sort to disk, it aggregates in memory. There are 18 rows
returned. What I didn't mention is that I originally had:
select avg(f1), t2.name from t1, t2 where t2.f2 = t1.f2 group by t2.name;
Which is much worse:
Aggregate (cost=161046.30..162130.42 rows=8673 width=222) (actual
time=72069.10..87455.69 rows=18 loops=1)
-> Group (cost=161046.30..161479.95 rows=86729 width=222) (actual
time=71066.38..78108.17 rows=963660 loops=1)
-> Sort (cost=161046.30..161263.13 rows=86729 width=222) (actual
time=71066.36..72445.74 rows=963660 loops=1)
Sort Key: t2.name
-> Merge Join (cost=148030.15..153932.66 rows=86729 width=222)
(actual time=19850.52..27266.40 rows=963660 loops=1)
Merge Cond: ("outer".f2 = "inner".f2)
-> Sort (cost=148028.59..150437.74 rows=963660 width=58)
(actual time=19850.18..21750.12 rows=963660 loops=1)
Sort Key: t1.f2
-> Seq Scan on t1 (cost=0.00..32479.60 rows=963660
width=58) (actual time=0.06...39 rows=963660 loops=1)
-> Sort (cost=1.56..1.60 rows=18 width=164) (actual
time=0.30..737.59 rows=931007 loops=1)
Sort Key: t2.f2
-> Seq Scan on t2 (cost=0.00..1.18 rows=18 width=164)
(actual time=0.05..0.08 rows=18 loops=1)
Total runtime: 87550.31 msec
Again, there are about 18 values of f2. The optimizer even knows this
(it's a foreign key to t2.f2), but instead it does the query plan in
exactly the wrong order. It hits disk probably 3 times as much as the
simpler query judging by the amount of time this query takes (33 vs 88
secs). BTW, adding an index to t1.f2 has seriously negative effects
on many other DSS queries.
I'm still not sure that the sort problem is our only problem when
vacuum runs. It's tough to pin down. We'll be adding more memory to
see if that helps with the disk contention.
Rob
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] vacuum locking
Rob Nagler <[EMAIL PROTECTED]> writes: > Mario Weilguni writes: > > of course both approaches have advantages, it simply depends on the usage > > pattern. A case where oracle really rules over postgresql are m<-->n > > connection tables where each record consist of two foreign keys, the > > overwrite approach is a big win here. I don't understand why you would expect overwriting to win here. What types of updates do you do on these tables? Normally I found using update on such a table was too awkward to contemplate so I just delete all the relation records that I'm replacing for the key I'm working with and insert new ones. This always works out to be cleaner code. In fact I usually leave such tables with no UPDATE grants on them. In that situation I would have actually expected Postgres to do as well as or better than Oracle since that makes them both functionally equivalent. -- greg ---(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
pgsql-performance@postgresql.org
Pardon this for looking somewhat "weird" but it seems I'm not getting all the messages to the list.. I've noticed the past few days a lot of them are coming out of order as well.. So, this was copy/pasted from the web archive of the list.. Vivek Khera wrote: > "AL" == Allen Landsidel writes: AL> maxusers 256 >> let the system autoconfigure maxusers... AL> Are you sure about this? I have always understood that explicitly Yes, recent freebsd kernels autosize various tables and limits based on existing RAM. It does pretty well. I'll disable it then and see how it goes. AL> These are related to something else.. a linux developer on the system AL> used to the way it'll always allow you access to all the memory on a Ahhh... I guess we don't believe in multi-user systems ;-) No, that's a foreign concept to a lot of people it seems. As a matter of trivia, I first suggested we run this on another server instead and hit the db remotely, as it's only going to be a "run once" type of thing that converts the old system to the new one but was rebuffed. Yesterday during a test run the thing ran over the 1GB limit, failed on some new() or other and dumped core. I couldn't bring the db down at that time to update the kernel, so we ran it on another box that has MAXDSIZ set to 1.5GB and it ran ok, but took about six hours.. so I'll be upping the that value yet again for this one special run this weekend when we do the *real* switch over, then putting it back down once we're all done. I can deal with it since it's not going to be "normal" but simply a one-off type thing. FWIW the same kind of thing has happened to me with this postgres install; Occasionally large atomic queries like DELETE will fail for the same reason (out of memory) if there are a lot of rows to get removed, and TRUNCATE isn't an option since there are FKs on the table in question. This is an annoyance I'd be interested to hear how other people work around, but only a minor one. I use 262144 for SHMMAXPGS and SHMALL. I also use about 3 shared buffers. I believe I had it fairly high once before and didn't notice much of an improvement but I'll fool with numbers around where you suggest. AL> I'll see if sysctl lets me write this value, or if it's a kernel AL> config option I missed, unless you have remembered between then and you need to bump some header file constant and rebuild the kernel. it also increases the granularity of how the buffer cache is used, so I'm not sure how it affects overall system. nothing like an experiment... So far I've found a whole lot of questions about this, but nothing about the constant. The sysctl (vfs.hibufspace I believe is the one) is read only, although I should be able to work around that via /boot/loader.conf if I can't find the kernel option. AL> Given this and the above about the controllers onboard cache (not to AL> mention the per-drive cache) do you think I'll still need to lower AL> effective_cache_size? It is hard to say. If you tell PG you have more than you do, I don't know what kind of decisions it will make incorrectly. I'd rather be conservative and limit it to the RAM that the system says it will use. The RAM in the controller is not additive to this -- it is redundant to it, since all data goes thru that cache into the main memory. A very good point, I don't know why I thought they may hold different data. I think it could differ somewhat but probably most everything in the controller cache will be duplicated in the OS cache, provided the OS cache is at least as large. A separate reply concatenated here to a message I actually did get delivered via email: At 16:50 10/24/2003, Bruce Momjian wrote: Vivek Khera wrote: > > "sm" == scott marlowe writes: > > > sm> Note that Tom has mentioned problems with possible deadlocks when nicing > sm> individual backends before, so proceed with caution here. > > I can see possible starvation, but if scheduling changes cause > deadlocks, then there's something wrong with the design. Yes, I think Tom's concern was priority inversion, where a low priority process holds a lock while a higher one waits for it. 1. Vivek, you were absolutely right about the backend process not being lowered in priority by nice'ing the psql. Yet another thing that "just didn't occur" when I wrote the script. 2. Vivek and Bruce (and even Tom), "VACUUM ANALYZE (VERBOSE)" isn't supposed to lock anything though, right? I can see this being a possible problem for other queries that do lock things, but between Vivek pointing out that the nice isn't *really* affecting the vacuum (as I just run one query db-wide) and the fact that the vacuum doesn't lock, I don't think it's hurting (or helping) in this case. However, I do the same thing with the reindex, so I'll definitely be taking it out there, as that one does lock.. although I would think the worst this would do would be a making the index unavailable and forcing a seq sca
Re: [PERFORM] Performance Concern
Thanks Rod. While I was waiting for my post to make it I went ahead and made the key an int. It improved it a lot, but was still pretty slow. This is weird: I was testing in a query window thus: UPDATE baz SET customer_id = '1234' WHERE ( SELECT baz_number FROM baz WHERE customer_id IS NULL LIMIT 1000 ); In the version of the table I posted this took 3 1/2 minutes. By making baz_number not part of the key, adding a baz_key of int4 and adjusting the above query for that it dropped to 1 1/2 minutes. But, I realized that was not how my app was going to be updating, so I wrote a little simulation in JAVA that gets a list of baz_keys where the customer_ is null and then iterates through the list one at a time attempting to UPDATE baz SET customer_id = '1234' WHERE baz_key = AND customer_id IS NULL. One thousand iterations took only 37 seconds. It would appear PostgreSQL is tuned towards single updates as opposed to handing a big bunch off to the query engine. Does that seem right? Seems odd to me. Anyway thanks for your response. I'll add some indexes and see if I can't shave that time down even further. __ John Pagakis Email: [EMAIL PROTECTED] "If you can't beat them, arrange to have them beaten." -- George Carlin This signature generated by ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. www.spazmodicfrog.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Rod Taylor Sent: Friday, October 24, 2003 11:23 AM To: [EMAIL PROTECTED] Cc: Postgresql Performance Subject: Re: [PERFORM] Performance Concern On Thu, 2003-10-23 at 08:21, John Pagakis wrote: > Greetings. > > I have a table that will require 100,000 rows initially. > > Assume the following (some of the field names have been changed for > confidentiality reasons): > > CREATE TABLE baz ( > baz_number CHAR(15) NOT NULL, > customer_id CHAR(39), > foobar_id INTEGER, > is_cancelled BOOL DEFAULT false NOT NULL, > create_user VARCHAR(60) NOT NULL, > create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, > last_update_user VARCHAR(60) NOT NULL, > last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, > CONSTRAINT PK_baz PRIMARY KEY (baz_number) > ); > > ALTER TABLE baz > ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id); > > ALTER TABLE baz > ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id); > > > Using JDBC, it took approximately one hour to insert 100,000 records. I > have an algorithm to generate a unique baz_number - it is a mixture of alpha > and numerics. Using an int for identification is certainly suggested, however it sounds like you may be short a few indexes on the foreign key'd fields. EXPLAIN ANALYZE output is always nice.. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] vacuum locking
Rob Nagler <[EMAIL PROTECTED]> writes:
> Greg Stark writes:
> > Note that pctfree/pctused are a big performance drain on the usual case. Try
> > setting them to 0/100 on a table that doesn't get updates (like a many-many
> > relation table) and see how much faster it is to insert and scan.
>
> Right. You can optimize each table independently. The "usual" case
> doesn't exist in most databases, I've found, which is why Oracle does
> better.
Sorry I was unclear. By "usual case" I meant reading, as opposed to updates.
The size of the on-disk representation turns out to be a major determinant in
a lot of database applications, since the dominant resource is i/o bandwidth.
Try doing a fresh import of a large table with pctfree 0 pctuse 100 and
compare how long a select takes on it compared to the original table.
> In practice, I'm still stuck. As a simple example, this query
> select avg(f1) from t1 group by f2
>
> Takes 33 seconds (see explain analyze in another note in this thread)
> to run on idle hardware with about 1GB available in the cache. It's
> clearly hitting disk to do the sort. Being a dumb programmer, I
> changed the query to:
I didn't see the rest of the thread so forgive me if you've already seen these
suggestions.
FIrstly, that type of query will be faster in 7.4 due to implementing a new
method for doing groups called hash aggregates.
Secondly you could try raising sort_mem. Postgres can't know how much memory
it really has before it swaps, so there's a parameter to tell it. And swapping
would be much worse than doing disk sorts.
You can raise sort_mem to tell it how much memory it's allowed to use before
it goes to disk sorts. You can even use ALTER SESSION to raise it in a few DSS
sessions but leave it low the many OLTP sessions. If it's high in OLTP
sessions then you could quickly hit swap when they all happen to decide to use
the maximum amount at the same time. But then you don't want to be doing big
sorts in OLTP sessions anyways.
Unfortunately there's no way to tell how much memory it thinks it's going to
use. I used to use a script to monitor the pgsql_tmp directory in the database
to watch for usage.
> select f1 from t1;
>
> And wrote the rest in Perl. It takes 4 seconds to run. Why? The Perl doesn't
> sort to disk, it aggregates in memory. There are 18 rows returned. What I
> didn't mention is that I originally had:
Oof. I expect if you convinced 7.3 to do the sort in memory by a suitable
value of sort_mem it would be close, but still slower than perl. 7.4 should be
very close since hash aggregates would be more or less equivalent to the perl
method.
> select avg(f1), t2.name from t1, t2 where t2.f2 = t1.f2 group by t2.name;
>
> Which is much worse:
>
> Aggregate (cost=161046.30..162130.42 rows=8673 width=222) (actual
> time=72069.10..87455.69 rows=18 loops=1)
>-> Group (cost=161046.30..161479.95 rows=86729 width=222) (actual
> time=71066.38..78108.17 rows=963660 loops=1)
> -> Sort (cost=161046.30..161263.13 rows=86729 width=222) (actual
> time=71066.36..72445.74 rows=963660 loops=1)
>Sort Key: t2.name
>-> Merge Join (cost=148030.15..153932.66 rows=86729 width=222)
> (actual time=19850.52..27266.40 rows=963660 loops=1)
> Merge Cond: ("outer".f2 = "inner".f2)
> -> Sort (cost=148028.59..150437.74 rows=963660 width=58)
> (actual time=19850.18..21750.12 rows=963660 loops=1)
>Sort Key: t1.f2
>-> Seq Scan on t1 (cost=0.00..32479.60 rows=963660
> width=58) (actual time=0.06...39 rows=963660 loops=1)
> -> Sort (cost=1.56..1.60 rows=18 width=164) (actual
> time=0.30..737.59 rows=931007 loops=1)
>Sort Key: t2.f2
>-> Seq Scan on t2 (cost=0.00..1.18 rows=18 width=164)
> (actual time=0.05..0.08 rows=18 loops=1)
> Total runtime: 87550.31 msec
>
> Again, there are about 18 values of f2. The optimizer even knows this
> (it's a foreign key to t2.f2), but instead it does the query plan in
> exactly the wrong order. It hits disk probably 3 times as much as the
> simpler query judging by the amount of time this query takes (33 vs 88
> secs). BTW, adding an index to t1.f2 has seriously negative effects
> on many other DSS queries.
Well, first of all it doesn't really because you said to group by t2.name not
f1. You might expect it to at least optimize something like this:
select avg(f1),t2.name from t1 join t2 using (f2) group by f2
but even then I don't think it actually is capable of using foreign keys as a
hint like that. I don't think Oracle does either actually, but I'm not sure.
To convince it to do the right thing you would have to do either:
SELECT a, t2.name
FROM (SELECT avg(f1),f2 FROM t1 GROUP BY f2) AS t1
JOIN t2 USING (f2)
Or use a subquery:
SELECT a, (SELECT name FROM t2 WHERE t2.f2 = t1.f
Re: [PERFORM] Performance Concern
Sean - I believe auto-commit was off (not at the box right now). I'll play with the commit interval; I know commits are expensive operations. Thanks for item 2. I was toying with the notion of pre-creating 10 bazes off-loading them and then seeing if the COPY would be any faster; you saved me the effort of experimenting. Thanks for the benefit of your experience. __ John Pagakis Email: [EMAIL PROTECTED] "Oh, you hate your job? Why didn't you say so? There's a support group for that. It's called EVERYBODY, and they meet at the bar." -- Drew Carey This signature generated by ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. www.spazmodicfrog.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Sean Shanny Sent: Friday, October 24, 2003 11:31 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Performance Concern John, Are you treating each insertion as a separate transaction? If so the performance will suffer. I am doing the same thing in building a data warehouse using PG. I have to load millions of records each night. I do two different things: 1) If I need to keep the insertions inside the java process I turn off auto-commit and every n insertions (5000 seems to give me the best performance for my setup) issue a commit. Make sure you do a final commit in a finally block so you don't miss anything. 2) Dump all the data to a file and then use a psql COPY (columns) FROM 'file path' call to load it. Very fast. --sean John Pagakis wrote: >Greetings. > >I have a table that will require 100,000 rows initially. > >Assume the following (some of the field names have been changed for >confidentiality reasons): > >CREATE TABLE baz ( >baz_number CHAR(15) NOT NULL, >customer_id CHAR(39), >foobar_id INTEGER, >is_cancelled BOOL DEFAULT false NOT NULL, >create_user VARCHAR(60) NOT NULL, >create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, >last_update_user VARCHAR(60) NOT NULL, >last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, >CONSTRAINT PK_baz PRIMARY KEY (baz_number) >); > >ALTER TABLE baz >ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id); > >ALTER TABLE baz >ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id); > > >Using JDBC, it took approximately one hour to insert 100,000 records. I >have an algorithm to generate a unique baz_number - it is a mixture of alpha >and numerics. > >There is a purchase table; one purchase can have many associated baz >records, but the baz records will be pre-allocated - baz.customer_id allows >null. The act of purchasing a baz will cause baz.customer_id to be >populated from the customer_id (key) field in the purchase table. > >If it took an hour to insert 100,000 records, I can only imagine how much >time it will take if one customer were to attempt to purchase all 100,000 >baz. Certainly too long for a web page. > >I've not had to deal with this kind of volume in Postgres before; I have my >suspicions on what is wrong here (could it be using a CHAR( 15 ) as a key?) >but I'd *LOVE* any thoughts. > >Would I be better off making the key an identity field and not indexing on >baz_number? > >Thanks in advance for any help. > >__ >John Pagakis >Email: [EMAIL PROTECTED] > > >"The best way to make your dreams come true is to wake up." >-- Paul Valery > >This signature generated by > ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. > www.spazmodicfrog.com > > >---(end of broadcast)--- >TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Performance Concern
John, One other thing I forgot to mention with solution 2. If you are going to be adding a fair number of records to the table on an ongoing basis you will want to drop indexes first and re-create them after the load is complete. I have tried it both ways and dropping is faster overall. --sean John Pagakis wrote: Sean - I believe auto-commit was off (not at the box right now). I'll play with the commit interval; I know commits are expensive operations. Thanks for item 2. I was toying with the notion of pre-creating 10 bazes off-loading them and then seeing if the COPY would be any faster; you saved me the effort of experimenting. Thanks for the benefit of your experience. __ John Pagakis Email: [EMAIL PROTECTED] "Oh, you hate your job? Why didn't you say so? There's a support group for that. It's called EVERYBODY, and they meet at the bar." -- Drew Carey This signature generated by ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. www.spazmodicfrog.com -Original Message- From: Sean Shanny [mailto:[EMAIL PROTECTED] Sent: Friday, October 24, 2003 11:31 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Performance Concern John, Are you treating each insertion as a separate transaction? If so the performance will suffer. I am doing the same thing in building a data warehouse using PG. I have to load millions of records each night. I do two different things: 1) If I need to keep the insertions inside the java process I turn off auto-commit and every n insertions (5000 seems to give me the best performance for my setup) issue a commit. Make sure you do a final commit in a finally block so you don't miss anything. 2) Dump all the data to a file and then use a psql COPY (columns) FROM 'file path' call to load it. Very fast. --sean John Pagakis wrote: Greetings. I have a table that will require 100,000 rows initially. Assume the following (some of the field names have been changed for confidentiality reasons): CREATE TABLE baz ( baz_number CHAR(15) NOT NULL, customer_id CHAR(39), foobar_id INTEGER, is_cancelled BOOL DEFAULT false NOT NULL, create_user VARCHAR(60) NOT NULL, create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, last_update_user VARCHAR(60) NOT NULL, last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, CONSTRAINT PK_baz PRIMARY KEY (baz_number) ); ALTER TABLE baz ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id); ALTER TABLE baz ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id); Using JDBC, it took approximately one hour to insert 100,000 records. I have an algorithm to generate a unique baz_number - it is a mixture of alpha and numerics. There is a purchase table; one purchase can have many associated baz records, but the baz records will be pre-allocated - baz.customer_id allows null. The act of purchasing a baz will cause baz.customer_id to be populated from the customer_id (key) field in the purchase table. If it took an hour to insert 100,000 records, I can only imagine how much time it will take if one customer were to attempt to purchase all 100,000 baz. Certainly too long for a web page. I've not had to deal with this kind of volume in Postgres before; I have my suspicions on what is wrong here (could it be using a CHAR( 15 ) as a key?) but I'd *LOVE* any thoughts. Would I be better off making the key an identity field and not indexing on baz_number? Thanks in advance for any help. __ John Pagakis Email: [EMAIL PROTECTED] "The best way to make your dreams come true is to wake up." -- Paul Valery This signature generated by ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. www.spazmodicfrog.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
