Re: [PERFORM] 8.x index insert performance
On Fri, 2005-11-11 at 18:02 -0500, Tom Lane wrote: There very well could be a pattern in the data which could affect things, however, I'm not sure how to identify it in 100K rows out of 100M. I conjecture that the problem areas represent places where the key sequence is significantly more random than it is elsewhere. Hard to be more specific than that though. OK, I understand the pattern now. My two tables hold orders, and order state transitions. Most orders have two transitions: creation and termination. The problem happens when there is a significant number of orders where termination is happening a long time after creation, causing order_transition rows with old ord_id values to be inserted. This is valid, so I have to figure out a way to accomodate it. You mentioned playing with checkpointing and bgwriter earlier in this thread. I experimented with the bgwriter through the weekend, but I don't have a good idea what sensible parameter changes are... Re: checkpointing, currently my checkpoints are happening every 5 minutes (if I turn on fsync, the graph shows checkpoints dramatically). If I increase the checkpoint_timeout, could that be beneficial? Or would I just have more time between larger spikes? -K ---(end of broadcast)--- TIP 1: 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] 8.x index insert performance
At 09:43 AM 11/14/2005, Kelly Burkhart wrote: On Fri, 2005-11-11 at 18:02 -0500, Tom Lane wrote: There very well could be a pattern in the data which could affect things, however, I'm not sure how to identify it in 100K rows out of 100M. I conjecture that the problem areas represent places where the key sequence is significantly more random than it is elsewhere. Hard to be more specific than that though. OK, I understand the pattern now. My two tables hold orders, and order state transitions. Most orders have two transitions: creation and termination. The problem happens when there is a significant number of orders where termination is happening a long time after creation, causing order_transition rows with old ord_id values to be inserted. This is valid, so I have to figure out a way to accomodate it. Perhaps a small schema change would help? Instead of having the order state transitions explicitly listed in the table, why not create two new tables; 1 for created orders and 1 for terminated orders. When an order is created, its ord_id goes into the CreatedOrders table. When an order is terminated, its ord_id is added to the TerminatedOrders table and then deleted from the CreatedOrders table. Downsides to this approach are some extra complexity and that you will have to make sure that system disaster recovery includes making sure that no ord_id appears in both the CreatedOrders and TerminatedOrdes tables. Upsides are that the insert problem goes away and certain kinds of accounting and inventory reports are now easier to create. Ron ---(end of broadcast)--- TIP 1: 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] slow queries after ANALYZE
DW wrote: Hello, I'm perplexed. I'm trying to find out why some queries are taking a long time, and have found that after running analyze, one particular query becomes slow. This query is based on a view that is based on multiple left outer joins to merge data from lots of tables. If I drop the database and reload it from a dump, the query result is instaneous (less than one second). But after I run analyze, it then takes much longer to run -- about 10 seconds, give or take a few depending on the hardware I'm testing it on. Earlier today, it was taking almost 30 seconds on the actual production server -- I restarted pgsql server and the time got knocked down to about 10 seconds -- another thing I don't understand. I've run the query a number of times before and after running analyze, and the problem reproduces everytime. I also ran with explain, and saw that the costs go up dramatically after I run analyze. I'm fairly new to postgresql and not very experienced as a db admin to begin with, but it looks like I'm going to have to get smarter about this stuff fast, unless it's something the programmers need to deal with when constructing their code and queries or designing the databases. I've already learned that I've commited the cardinal sin of configuring my new database server with RAID 5 instead of something more sensible for databases like 0+1, but I've been testing out and replicating this problem on different hardware, so I know that this issue is not the direct cause of this. Thanks for any info. I can supply more info (like config files, schemas, etc.) if you think it might help. But I though I would just describe the problem for starters. -DW Well, for whatever it's worth, on my test box, I upgraded from postgreql 7.4.9 to 8.1, and that seems to make all the difference in the world. These complex queries are instantaneous, and the query planner when I run EXPLAIN ANALYZE both before and after running ANALYZE displays results more in line with what is expected ( 60ms). Whatever changes were introduced in 8.x seems to make a huge improvment in query performance. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Postgres recovery time
Title: Postgres recovery time Does anyone know what factors affect the recovery time of postgres if it does not shutdown cleanly? With the same size database I've seen times from a few seconds to a few minutes. The longest time was 33 minutes. The 33 minutes was after a complete system crash and reboot so there are a lot of other things going on as well. 125 seconds was the longest time I could reproduce by just doing a kill -9 on postmaster. Is it the size of the transaction log? The dead space in files? I'm running postges 7.3.4 in Red Hat 8.0. Yes, yes I know it's crazy but for a variety of reasons upgrading is not currently feasible. Jim
[PERFORM] Help speeding up delete
We've got an older system in production (PG 7.2.4). Recently one of the users has wanted to implement a selective delete, but is finding that the time it appears to take exceeds her patience factor by several orders of magnitude. Here's a synopsis of her report. It appears that the WHERE id IN ... is resulting in a seq scan that is causing the problem, but we're not SQL expert enough to know what to do about it. Can someone point out what we're doing wrong, or how we could get a (much) faster delete? Thanks! Report: This command yields results in only a few seconds: # SELECT at.id FROM tmp_table2 at, tmp_tabl2e a # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'; However, the following command does not seen to want to ever complete (the person running this killed it after 1/2 hour). # DELETE FROM tmp_table2 WHERE id IN # (SELECT at.id FROM tmp_table2 at, tmp_table2 a # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'); == The table has four columns. There are 6175 rows satifying the condition given, and the table itself has 1539688 entries. Layout is: lab.devel.configdb=# \d tmp_table2 Table tmp_table2 Column | Type | Modifiers +--+--- id | character varying(64)| name | character varying(64)| units | character varying(32)| value | text | time | timestamp with time zone | == lab.devel.configdb=# EXPLAIN DELETE FROM tmp_table2 WHERE id IN lab.devel.configdb-# (SELECT at.id FROM tmp_table2 at, tmp_table2 a lab.devel.configdb(# WHERE at.id=a.id AND a.name='obsid' AND a.value='oid080505'); NOTICE: QUERY PLAN: Seq Scan on tmp_table2 (cost=0.00..154893452082.10 rows=769844 width=6) SubPlan - Materialize (cost=100600.52..100600.52 rows=296330 width=100) - Hash Join (cost=42674.42..100600.52 rows=296330 width=100) - Seq Scan on tmp_table2 at (cost=0.00..34975.88 rows=1539688 width=50) - Hash (cost=42674.32..42674.32 rows=38 width=50) - Seq Scan on tmp_table2 a (cost=0.00..42674.32 rows=38 width=50) EXPLAIN lab.devel.configdb=# EXPLAIN (SELECT at.id FROM tmp_table2 at, tmp_table2 a lab.devel.configdb(#WHERE at.id=a.id AND a.name='obsid' AND a.value='oid080505'); NOTICE: QUERY PLAN: Hash Join (cost=42674.42..100600.52 rows=296330 width=100) - Seq Scan on tmp_table2 at (cost=0.00..34975.88 rows=1539688 width=50) - Hash (cost=42674.32..42674.32 rows=38 width=50) - Seq Scan on tmp_table2 a (cost=0.00..42674.32 rows=38 width=50) EXPLAIN -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Help speeding up delete
On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote: # SELECT at.id FROM tmp_table2 at, tmp_tabl2e a # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'; Isn't this equivalent? select id from tmp_table2 where name = 'obsid' and value = 'oid080505'; # DELETE FROM tmp_table2 WHERE id IN # (SELECT at.id FROM tmp_table2 at, tmp_table2 a # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'); and this? delete from tmp_table2 where name = 'obsid' and value = 'oid080505'; Why are you doing a self-join using id, which I assume is a primary key? -- Scott Lamb http://www.slamb.org/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Help speeding up delete
Steve Wampler [EMAIL PROTECTED] writes: We've got an older system in production (PG 7.2.4). Recently one of the users has wanted to implement a selective delete, but is finding that the time it appears to take exceeds her patience factor by several orders of magnitude. Here's a synopsis of her report. It appears that the WHERE id IN ... is resulting in a seq scan that is causing the problem, but we're not SQL expert enough to know what to do about it. Can someone point out what we're doing wrong, or how we could get a (much) faster delete? Thanks! Update to 7.4 or later ;-) Quite seriously, if you're still using 7.2.4 for production purposes you could justifiably be accused of negligence. There are three or four data-loss-grade bugs fixed in the later 7.2.x releases, not to mention security holes; and that was before we abandoned support for 7.2. You *really* need to be thinking about an update. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??
Joost, I've got experience with these controllers and which version do you have. I'd expect to see higher than 50MB/s although I've never tried RAID 5 I routinely see closer to 100MB/s with RAID 1+0 on their 9000 series I would also suggest that shared buffers should be higher than 7500, closer to 3, and effective cache should be up around 200k work_mem is awfully high, remember that this will be given to each and every connection and can be more than 1x this number per connection depending on the number of sorts done in the query. fsync=false ? I'm not even sure why we have this option, but I'd never set it to false. Dave On 6-Nov-05, at 8:30 AM, Joost Kraaijeveld wrote: Hi, I am experiencing very long update queries and I want to know if it reasonable to expect them to perform better. The query below is running for more than 1.5 hours (5500 seconds) now, while the rest of the system does nothing (I don't even type or move a mouse...). - Is that to be expected? - Is 180-200 tps with ~ 9000 KB (see output iostat below) not low, given the fact that fsync is off? (Note: with bonnie++ I get write performance 50 MB/sec and read performace 70 MB/sec with 2000 read/write ops /sec? - Does anyone else have any experience with the 3Ware RAID controller (which is my suspect)? - Any good idea how to determine the real botleneck if this is not the performance I can expect? My hard- and software: - PostgreSQL 8.0.3 - Debian 3.1 (Sarge) AMD64 - Dual Opteron - 4GB RAM - 3ware Raid5 with 5 disks Pieces of my postgresql.conf (All other is default): shared_buffers = 7500 work_mem = 260096 fsync=false effective_cache_size = 32768 The query with explain (amount and orderbedrag_valuta are float8, ordernummer and ordernumber int4): explain update prototype.orders set amount = odbc.orders.orderbedrag_valuta from odbc.orders where ordernumber = odbc.orders.ordernummer; QUERY PLAN -- --- Hash Join (cost=50994.74..230038.17 rows=1104379 width=466) Hash Cond: (outer.ordernumber = inner.ordernummer) - Seq Scan on orders (cost=0.00..105360.68 rows=3991868 width=455) - Hash (cost=48233.79..48233.79 rows=1104379 width=15) - Seq Scan on orders (cost=0.00..48233.79 rows=1104379 width=15) Sample output from iostat during query (about avarage): Device:tpskB_read/skB_wrtn/skB_readkB_wrtn hdc 0.00 0.00 0.00 0 0 sda 0.00 0.00 0.00 0 0 sdb 187.1323.76 8764.36 24 8852 -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 1: 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 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Help speeding up delete
Scott Lamb wrote: On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote: # SELECT at.id FROM tmp_table2 at, tmp_tabl2e a # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'; Isn't this equivalent? select id from tmp_table2 where name = 'obsid' and value = 'oid080505'; Probably, the user based the above on a query designed to find all rows with the same id as those rows that have a.name='obsid' and a.value='oid080505'. However, I think the above would work to locate all the ids, which is all we need for the delete (see below) # DELETE FROM tmp_table2 WHERE id IN # (SELECT at.id FROM tmp_table2 at, tmp_table2 a # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'); and this? delete from tmp_table2 where name = 'obsid' and value = 'oid080505'; Why are you doing a self-join using id, which I assume is a primary key? Because I think we need to. The above would only delete rows that have name = 'obsid' and value = 'oid080505'. We need to delete all rows that have the same ids as those rows. However, from what you note, I bet we could do: DELETE FROM tmp_table2 WHERE id IN (SELECT id FROM temp_table2 WHERE name = 'obsid' and value= 'oid080505'); However, even that seems to have a much higher cost than I'd expect: lab.devel.configdb=# explain delete from tmp_table2 where id in (select id from tmp_table2 where name='obsid' and value = 'oid080505'); NOTICE: QUERY PLAN: Seq Scan on tmp_table2 (cost=0.00..65705177237.26 rows=769844 width=6) SubPlan - Materialize (cost=42674.32..42674.32 rows=38 width=50) - Seq Scan on tmp_table2 (cost=0.00..42674.32 rows=38 width=50) EXPLAIN And, sure enough, is taking an extrordinarily long time to run (more than 10 minutes so far, compared to 10seconds for the select). Is this really typical of deletes? It appears (to me) to be the Seq Scan on tmp_table2 that is the killer here. If we put an index on, would it help? (The user claims she tried that and it's EXPLAIN cost went even higher, but I haven't checked that...) Thanks! -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Hardware/OS recommendations for large databases (5TB)
Does anyone have recommendations for hardware and/or OS to work with around 5TB datasets? The data is for analysis, so there is virtually no inserting besides a big bulk load. Analysis involves full-database aggregations - mostlybasic arithmetic and grouping. In addition, much smallersubsets of data would be pulled and stored to separate databases. I havebeen workingwith datasets no bigger than around 30GB, and that (I'm afraidto admit) has been in MSSQL. Thanks, Adam
Re: [PERFORM] Postgres recovery time
Piccarello, James (James) wrote: Postgres recovery time Does anyone know what factors affect the recovery time of postgres if it does not shutdown cleanly? With the same size database I've seen times from a few seconds to a few minutes. The longest time was 33 minutes. The 33 minutes was after a complete system crash and reboot so there are a lot of other things going on as well. 125 seconds was the longest time I could reproduce by just doing a kill -9 on postmaster. Is it the size of the transaction log? The dead space in files? I don't know much about postgresql, but typically WAL mechanisms will exhibit recovery times that are bounded by the amount of log record data written since the last checkpoint. The 'worst' case will be where you have continuous writes to the database and a long checkpoint interval. In that case many log records must be replayed into the data files upon recovery. The 'best' case would be zero write transactions since the last checkpoint. In that case recovery would be swift since there are no live records to recover. In your tests you are probably exercising this 'best' or near best case.
Re: [PERFORM] Help speeding up delete
Tom Lane wrote: Steve Wampler [EMAIL PROTECTED] writes: We've got an older system in production (PG 7.2.4). Recently one of the users has wanted to implement a selective delete, but is finding that the time it appears to take exceeds her patience factor by several orders of magnitude. Here's a synopsis of her report. It appears that the WHERE id IN ... is resulting in a seq scan that is causing the problem, but we're not SQL expert enough to know what to do about it. Can someone point out what we're doing wrong, or how we could get a (much) faster delete? Thanks! Update to 7.4 or later ;-) I was afraid you'd say that :-) I'm not officially involved in this project anymore and was hoping for a fix that wouldn't drag me back in. The security issues aren't a concern because this DB is *well* hidden from the outside world (it's part of a telescope control system behind several firewalls with no outside access). However, the data-loss-grade bugs issue *is* important. We'll try to do the upgrade as soon as we get some cloudy days to actually do it! Is the performance behavior that we're experiencing a known problem with 7.2 that has been addressed in 7.4? Or will the upgrade fix other problems while leaving this one? Quite seriously, if you're still using 7.2.4 for production purposes you could justifiably be accused of negligence. There are three or four data-loss-grade bugs fixed in the later 7.2.x releases, not to mention security holes; and that was before we abandoned support for 7.2. You *really* need to be thinking about an update. Thanks! Steve -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Help speeding up delete
On 11/14/05, Steve Wampler [EMAIL PROTECTED] wrote: However, even that seems to have a much higher cost than I'd expect: lab.devel.configdb=# explain delete from tmp_table2 where id in(select id from tmp_table2 where name='obsid' and value = 'oid080505'); NOTICE:QUERY PLAN: Seq Scan on tmp_table2(cost=0.00..65705177237.26 rows=769844 width=6) SubPlan -Materialize(cost=42674.32..42674.32 rows=38 width=50) -Seq Scan on tmp_table2(cost=0.00..42674.32 rows=38 width=50) For one reason or the other, the planner things a sequential scan is the best solution. Try turning off seq_scan before the query and see if it changes the plan (set enable_seqscan off;). I've seen this problem with sub queries and that usually solves it. -- This E-mail is covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521 and is legally privileged.This information is confidential information and is intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.
Re: [PERFORM] Help speeding up delete
On Nov 14, 2005, at 3:52 PM, Steve Wampler wrote: Scott Lamb wrote: On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote: # SELECT at.id FROM tmp_table2 at, tmp_tabl2e a # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'; Isn't this equivalent? select id from tmp_table2 where name = 'obsid' and value = 'oid080505'; Probably, the user based the above on a query designed to find all rows with the same id as those rows that have a.name='obsid' and a.value='oid080505'. Well, this indirection is only significant if those two sets can differ. If (A) you meant tmp_table2 when you wrote tmp_tabl2e, so this is a self-join, and (B) there is a primary key on id, I don't think that can ever happen. It appears (to me) to be the Seq Scan on tmp_table2 that is the killer here. If we put an index on, would it help? On...tmp_table2.id? If it is a primary key, there already is one. If not, yeah, I expect it would help. -- Scott Lamb http://www.slamb.org/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Help speeding up delete
Scott Lamb wrote: On Nov 14, 2005, at 3:52 PM, Steve Wampler wrote: Scott Lamb wrote: On Nov 14, 2005, at 2:07 PM, Steve Wampler wrote: # SELECT at.id FROM tmp_table2 at, tmp_tabl2e a # WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'; Isn't this equivalent? select id from tmp_table2 where name = 'obsid' and value = 'oid080505'; Probably, the user based the above on a query designed to find all rows with the same id as those rows that have a.name='obsid' and a.value='oid080505'. Well, this indirection is only significant if those two sets can differ. If (A) you meant tmp_table2 when you wrote tmp_tabl2e, so this is a self-join, and (B) there is a primary key on id, I don't think that can ever happen. I wasn't clear. The original query was: SELECT at.* FROM tmp_table2 at, tmp_table2 a WHERE at.id=a.id and a.name='obsid' and a.value='oid080505'; which is significantly different than: SELECT * FROM tmp_table2 WHERE name='obsid' and value='oid080505'; The user had adapted that query for her needs, but it would have been better to just use the query that you suggested (as the subselect in the DELETE FROM...). Unfortunately, that only improves performance slightly - it is still way too slow on deletes. -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 6: explain analyze is your friend
