Re: [PERFORM] VACUUM FULL vs CLUSTER
Bruno Wolff III mentioned : => If you have a proper FSM setting you shouldn't need to do vacuum fulls => (unless you have an older version of postgres where index bloat might => be an issue). What version of postgres was the last version that had the index bloat problem? ---(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] VACUUM FULL vs CLUSTER
Bruno Wolff III mentioned : => > => If you have a proper FSM setting you shouldn't need to do vacuum fulls => > => (unless you have an older version of postgres where index bloat might => > => be an issue). Thanks Alvaro and Bruno I just want to clarify something that I also couldn't find a clear cut answer for before. What is a proper fsm setting? Someone told me to set max_fsm_relations to the number of relations in pg_class plus a few more to allow for new relations. And max_fsm_pages to the number of rows in the biggest table I want to vacuum, plus a few 1000's for extra room? Where does this free space map sit? On the disk somewhere, or in memory, or both. I once set the max_fsm_pages very high by mistake, and postgres then started up and used a _lot_ of shared memory, and I had to increase shmmax. Is there abything to watch out for when bumping this setting up a lot? Kind Regards Stefan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Compression of text columns
I have a table in the databases I work with, that contains two text columns with XML data stored inside them. This table is by far the biggest table in the databases, and the text columns use up the most space. I saw that the default storage type for text columns is "EXTENDED" which, according to the documentation, uses up extra space to make possible substring functioning faster. Suppose that the data in those columns are only really ever _used_ once, but may be needed in future for viewing purposes mostly, and I cannot really change the underlying structure of the table, what can I possibly do to maximally reduce the amount of disk space used by the table on disk. (There are no indexes on these two columns.) I've thought about compression using something like : ztext http://www.mahalito.net/~harley/sw/postgres/ but I have to change the table structure a lot and I've already encountered problems unzipping the data again. The other problem with this solution, is that database dumps almost double in size, because of double compression. Any suggestions much appreciated TIA Stefan ---(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] Compression of text columns
Tino Wildenhain mentioned : => Well, text columns are automatically compressed via the toast mechanism. => This is handled transparently for you. OK, I misread the documentation, and I forgot to mention that I'm using postgres 7.3 and 8.0 It's actually the EXTERNAL storage type that is larger, not EXTENDED. What kind of compression is used in the EXTERNAL storage type? Is there any way to achieve better compression? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] simple join uses indexes, very slow
If your looking for suggestions, I would suggest updating the 8.1.x you
have installed to the latest version, as of typing this is 8.1.3 ;) Most
notable is some of the -bug- fixes that are in since 8.1.0, for example;
* Fix incorrect optimizations of outer-join conditions (Tom)
You know, minor point releases aren't adding new features or changing
basic functionality, they are pure and simple bugfixes. If I was in
-your- position, I would run (don't walk ;) and install upto 8.1.3
of course, thats jst my 2c, feel free to ignore :D
Regards
Stef
Chris wrote:
george young wrote:
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
I have a simple join on two tables that takes way too long. Can you
help
me understand what's wrong? There are indexes defined on the
relevant columns.
I just did a fresh vacuum --full --analyze on the two tables.
Is there something I'm not seeing?
[CPU is 950Mhz AMD, 256MB RAM, 15k rpm scsi disk]
-- George Young
Table sizes: parameters has 2.1512e+07 tuples, run_opsets has 211745
tuples.
explain analyze SELECT ro.run, ro.opset_num, p.step_num FROM
run_opsets ro, parameters p WHERE ro.run = p.run AND ro.opset_num =
p.opset_num and ro.run='team9';
QUERY PLAN
Nested Loop (cost=2.16..7957.40 rows=6707 width=22) (actual
time=14.986..70197.129 rows=43050 loops=1)
-> Index Scan using run_opsets_pkey on run_opsets ro
(cost=0.00..128.75 rows=71 width=18) (actual time=0.386..62.959
rows=263 loops=1)
Index Cond: (run = 'team9'::text)
-> Bitmap Heap Scan on parameters p (cost=2.16..109.93 rows=27
width=22) (actual time=1.591..266.211 rows=164 loops=263)
Recheck Cond: (('team9'::text = p.run) AND
("outer".opset_num = p.opset_num))
-> Bitmap Index Scan on parameters_idx (cost=0.00..2.16
rows=27 width=0) (actual time=1.153..1.153 rows=164 loops=263)
Index Cond: (('team9'::text = p.run) AND
("outer".opset_num = p.opset_num))
Total runtime: 70237.727 ms
(8 rows)
Table "public.run_opsets"
Column|Type |Modifiers
--+-+-
run | text| not null
opset| text|
opset_ver| integer |
opset_num| integer | not null
status | opset_status|
date_started | timestamp without time zone |
date_done| timestamp without time zone |
work_started | timestamp without time zone |
lock_user| text| default 'NO-USER'::text
lock_pid | integer |
needs_review | text|
Indexes:
"run_opsets_pkey" PRIMARY KEY, btree (run, opset_num) CLUSTER
-- Table "public.parameters"
Column | Type | Modifiers
---+-+---
run | text| not null
opset_num | integer | not null
opset | text| not null
opset_ver | integer | not null
step_num | integer | not null
step | text| not null
step_ver | integer | not null
name | text| not null
value | text|
split | boolean | not null default false
wafers| text[] | not null default '{}'::text[]
Indexes:
"parameters_idx" btree (run, opset_num, step_num, opset,
opset_ver, step, step_ver, name, split, wafers)
"parameters_opset_idx" btree (opset, step, name)
"parameters_step_idx" btree (step, name)
More for my own information (because nobody else has suggested it),
would it make a difference if 'run' was a varchar field rather than text?
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Database possible corruption , unsolvable mystery
Eric Lauzon wrote: Mabey later if this dosen't fix the problem , and as of information its 7.4.6 [i know its not the most rescent] but it is the way it is right now and we suspect the problem might have come from a power outage while there was a full vacuum and the reason why its only one table that has been affected is probably because it was the table being vacummed, but this is only an assumption right now and more info will folow if the problems persis after a full restore. Hrm, you know that you -should- upgrade to at least the latest 7.4 (7.4.13 I think is the most recent). looking from the changelogs, there are a few bugs that you could be hitting; 7.4.10 * Fix race condition in transaction log management There was a narrow window in which an I/O operation could be initiated for the wrong page, leading to an Assert failure or data corruption. 7.4.9 * Improve checking for partially-written WAL pages * Fix error that allowed VACUUM to remove ctid chains too soon, and add more checking in code that follows ctid links. This fixes a long-standing problem that could cause crashes in very rare circumstances. 7.4.8 * Repair race condition between relation extension and VACUUMThis could theoretically have caused loss of a page's worth of freshly-inserted data, although the scenario seems of very low probability. There are no known cases of it having caused more than an Assert failure and these are only the ones that appear 'notably' in the changelog. In short, I -really- -would- -strongly- -advise- you upgrading to 7.4.13. Personally, I would have made this my first step, especially if your data is important. There is no need for a dump/reload between minor point releases. Although there is a security fix in 7.4.8. Since the db is in a state of 'down' or repair, why not do it now ? two birds, one stone. Regards Stef ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Postgres function use makes machine crash.
Hi all, I've been dealing with a problem for the past two days where a certain sql statement works 2 out of 5 times, and the other 3 times, it causes the machine (quad Xeon 2.8GHz + 792543232 bytes mem, linux kernel 2.4.26-custom, pg ver 7.3.4) to slow down, and finally grind to a halt. It looks like postgres gets itself into an insane loop, because no matter how much shared memory I give it, it uses it all, and then the kernel starts swapping. I'm pretty sure it's not the kernel, because I've tried four different 2.4.2* stable kernels, and the same happens. I've attached the query, and the functions used inside the query, as well as the table structure and an explain. (I haven't been able to get explain analyze) It seems that when I replace the functions used in the query, with the actual values returned by them (one date in each case), the query runs in 10 seconds. I did vacuum analyze, and reindex seemed to work at one stage, but now it doesn't anymore. Is there some limitation in using functions that I do not know about, or is it a bug? (It seems to be hanging on the max_fpp() function call from inside the fpp_max_ms() function.) Please help. Kind Regards Stefan query.sql Description: Binary data =# EXPLAIN SELECTgroup_code::text AS group_code, -# sku::text AS sku, -# stktype_code::varchar(2) AS stktype_code, -# brn_code::textAS brn_code, -# SUM(overdue)::int4AS overdue, -# SUM(current)::int4AS current, -# SUM(future)::int4 AS future -# FROM ( (# SELECTgroup_code, (# sku, (# stktype_code, (# brn_code, (# CASE WHEN to_date <= max_fpp_ms() THEN (# SUM(out_qty) (# ELSE 0 (# END AS overdue, (# CASE WHEN to_date > max_fpp_ms() (# AND to_date <= max_fpp_me() THEN (# SUM(out_qty) (# ELSE 0 (# END AS current, (# CASE WHEN to_date > max_fpp_me() THEN (# SUM(out_qty) (# ELSE 0 (# END AS future (# FROM gir_outstanding (# GROUP BY group_code, (# sku, (# stktype_code, (# brn_code, (# to_date (# ) AS sub -# GROUP BY group_code, -# sku, -# stktype_code, -# brn_code -# ; QUERY PLAN - Aggregate (cost=15880.41..16055.62 rows=876 width=44) -> Group (cost=15880.41..15989.92 rows=8761 width=44) -> Sort (cost=15880.41..15902.31 rows=8761 width=44) Sort Key: group_code, sku, stktype_code, brn_code -> Subquery Scan sub (cost=13335.57..15306.72 rows=8761 width=44) -> Aggregate (cost=13335.57..15306.72 rows=8761 width=44) -> Group (cost=13335.57..14649.67 rows=87607 width=44) -> Sort (cost=13335.57..13554.58 rows=87607 width=44) Sort Key: group_code, sku, stktype_code, brn_code, to_date -> Seq Scan on gir_outstanding (cost=0.00..4687.07 rows=87607 width=44) (10 rows) functions.sql Description: Binary data =# \d gir_outstanding Table "public.gir_outstanding" Column |Type | Modifiers +-+--- supp_code | text| supp_name | text| supp_brn | text| ord_no | text| due_date | timestamp without time zone | to_date| timestamp without time zone | group_code | text| brn_code | text| desc_short | text| cluster_brn| text| country_code | text| req_doc_no | integer | ops_code | text| sku| text| std_descr | text| acde_code | text| req_qty| double precision| grv_qty| double precision| skul_qty | double precision| pref_date | timestamp without time zone | skul_grv_qty | double precision| out_qty| double precision| skul_or
Re: [PERFORM] Postgres function use makes machine crash.
Tom Lane mentioned : => Please try it on 7.4.2 and see if you still have a problem. Will do, and I'll post the results Thanks! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Strange problems with more memory.
Hi all, I'm running postgres 7.3.4 on a quad Xeon 2.8 GHz with Mem: 1057824768 309108736 7487160320 12242944 256413696 Swap: 518053888 8630272 509423616 on Linux version 2.4.26-custom Data directory is mounted with noatime. Nothing else but one 11GB database is running on this machine. When the database was created, I changed the following defaults : shared_buffers = 24415 sort_mem = 5120 vacuum_mem = 10240 commit_delay = 5000 commit_siblings = 100 These settings worked fine, but were not optimal, I thought, and processing stuff on this database was a bit slow. The machine is not nearly used to it's capacity, and I realized that disk IO is what's slowing me down. So I decided to give postgres more shared memory and much more sort memory, as it does a lot of "group by'"s and "order by"'s during the nightly processing. These were the new settings I tried : shared_buffers = 61035 sort_mem = 97657 I thought because it's only one process that runs queries exclusively at night, I should be able to set the sort_mem this high without worrying about running out of memory. It seems I was mistaking, as I started getting these kind of errors in dmesg : VM: killing process postmaster __alloc_pages: 0-order allocation failed (gfp=0x1d2/0) __alloc_pages: 0-order allocation failed (gfp=0x1d2/0) VM: killing process postmaster and I kept on getting these postgres errors : ERROR: Index is not a btree I systematically reduced the shared buffers back down to 24415, and this kept on happening. As soon as I reduced sort_mem back to under 1,the problem stopped. But the database is just as slow as before. (By slow I mean not as fast as it should be on such a powerful machine compared to much worse machines running the same processes) What can I do to make this database run faster on this machine. Can anyone suggest how I would go about speeding up this database. I need to prepare a database three times the size of this one, running the same processes, and I don't know what improvements I can do on hardware to make this possible. On the current machine I can easily get another 1GB or 2GB of memory, but will that help at all? Without going into the details of exactly the queries that run on this machine, what would be needed to make postgres run very fast on this machine? Please help. Kind Regards Stefan ---(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] Strange problems with more memory.
Tom Lane mentioned : => Turn off => memory overallocation in your kernel to get more stable behavior when => pushing the limits of available memory. I think this will already help a lot. Thanks!! => If your concern is with a single nightly process, then that quad Xeon is => doing squat for you, because only one of the processors will be working. => See if you can divide up the processing into several jobs that can run => in parallel. (Of course, if the real problem is that you are disk I/O => bound, nothing will help except better disk hardware. Way too many => people think they should buy a super-fast CPU and attach it to => consumer-grade IDE disks. For database work you're usually better off => spending your money on good disks...) Got 3 1 rpm SCSI raid5 on here. I doubt I will get much better than that without losing both arms and legs... I think I'll try and even out the disk IO a bit and get 4 processes running in parallel. At least I can move forward again. Thanks again! Kind Regards Stefan ---(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] Query kills machine.
Hi all,
I've attached all the query in query.sql
I'm using postgres 7.3.4 on Linux version 2.4.26-custom
( /proc/sys/vm/overcommit_memory = 0 this time )
free :
total used free sharedbuffers cached
Mem: 18102121767384 42828 0 56041663908
-/+ buffers/cache: 978721712340
Swap: 505912 131304 374608
After I rebuilt the database, the query was fast (28255.12 msec).
After one night's insertion into the tables that the query select from,
the query all of a sudden uses up all resources , and the kernel
starts swapping, and I haven't seen the query actually finish when
this happens. I did vacuum analyze AND reindex, but that doesn't
help.
I attached the explain analyze of the query before this happens, and
the explain plan from when it actually happens that the query doesn't finish.
The one noticeable difference, was that before, it used merge joins, and
after, it used hash joins.
When the query was slow, I tried to : set enable_hashjoin to off
for this query, and the query finished relatively fast again (316245.16 msec)
I attached the output of that explain analyze as well, as well as the postgres
settings.
Can anyone shed some light on what's happening here. I can't figure it out.
Kind Regards
Stefan
query.sql
Description: Binary data
Aggregate (cost=87597.84..89421.82 rows=2702 width=484) (actual
time=22727.88..28164.74 rows=12040 loops=1)
Filter: sum(qty_on_hand) + sum(qty_in_goods)) - sum(qty_dp)) -
sum(qty_out_goods)) < 0::numeric)
-> Group (cost=87597.84..88003.17 rows=27022 width=484) (actual
time=22727.45..23242.01 rows=42705 loops=1)
-> Sort (cost=87597.84..87665.40 rows=27022 width=484) (actual
time=22727.43..22756.74 rows=42705 loops=1)
Sort Key: s.sku, s.stktype_code, i.sku_descr, br.cluster_code,
br.cluster_descr
-> Merge Join (cost=84388.96..85608.78 rows=27022 width=484) (actual
time=20303.41..21814.25 rows=42705 loops=1)
Merge Cond: (("outer".group_code = "inner".group_code) AND
("outer".sku = "inner".sku))
-> Sort (cost=64472.34..64489.67 rows=6930 width=388) (actual
time=16503.56..16530.23 rows=42705 loops=1)
Sort Key: s.group_code, os.sku
-> Merge Join (cost=63006.13..64030.25 rows=6930
width=388) (actual time=14394.48..15794.71 rows=42705 loops=1)
Merge Cond: (("outer".cluster_brn =
"inner".cluster_code) AND ("outer".sku = "inner".sku))
-> Index Scan using old_sku_uidx1 on old_sku os
(cost=0.00..797.79 rows=17799 width=64) (actual time=0.02..47.66 rows=17799 loops=1)
-> Sort (cost=63006.13..63045.07 rows=15574
width=324) (actual time=14393.77..14556.50 rows=132703 loops=1)
Sort Key: br.cluster_code, s.sku
-> Merge Join (cost=61645.75..61921.64
rows=15574 width=324) (actual time=4862.56..6078.94 rows=132703 loops=1)
Merge Cond: ("outer".brn_code =
"inner".brn_code)
-> Sort (cost=61587.79..61626.73
rows=15574 width=228) (actual time=4859.23..5043.43 rows=132703 loops=1)
Sort Key: s.brn_code
-> Index Scan using stmst_sku_idx4
on stmst_sku s (cost=0.00..60503.30 rows=15574 width=228) (actual time=0.07..1078.30
rows=132703 loops=1)
Index Cond: (fpp_code =
'200408'::text)
-> Sort (cost=57.96..59.62 rows=667
width=96) (actual time=3.26..91.93 rows=133005 loops=1)
Sort Key: br.brn_code
-> Seq Scan on master_branch_descr
br (cost=0.00..26.67 rows=667 width=96) (actual time=0.02..1.13 rows=667 loops=1)
-> Sort (cost=19916.61..20306.53 rows=155968 width=96) (actual
time=3797.71..3914.26 rows=184223 loops=1)
Sort Key: i.group_code, i.sku
-> Seq Scan on master_sku_descr i (cost=0.00..6463.68
rows=155968 width=96) (actual time=0.01..293.74 rows=155968 loops=1)
SubPlan
-> Aggregate (cost=6.02..6.02 rows=1 width=8) (actual time=0.04..0.04 rows=1
loops=14456)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding
(cost=0.00..6.01 rows=1 width=8) (actual time=0.03..0.03 rows=0 loops=14456)
Index Cond: ((cluster_brn = $0) AND (sku = $1) AND (stktype_code =
$2))
-> Aggregate (cost=6.02..6.02 rows=1 width=8) (actual time=0.04..0.04 rows=1
loops=14456)
-> Index Scan using gir_oustanding_idx1 on gir_outstanding
Re: [PERFORM] Query kills machine.
Tom Lane mentioned : => Not if you haven't got the RAM to support it :-( => => Another thing you might look at is ANALYZEing the tables again after => you've loaded all the new data. The row-count estimates seem way off => in these plans. You might need to increase the statistics target, => too, to get better plans. Thanks Tom, Christopher and Magnus! I tested this, and found the correct sort_mem setting for my situation. I'm testing a new default_statistics_target setting. This is something I never considered. Kind Regards Stefan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] execute cursor fetch
Pierre-Frédéric Caillaud mentioned : => http://www.postgresql.org/docs/7.4/static/jdbc-query.html#AEN24298 My question is : Is this only true for postgres versions >= 7.4 ? I see the same section about "Setting fetch size to turn cursors on and off" is not in the postgres 7.3.7 docs. Does this mean 7.3 the JDBC driver for postgres < 7.4 doesn't support this ? Kind Regards Stefan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Performance of count(*) on large tables vs SQL Server
Hello Andrew, Everything that Shridhar says makes perfect sense, and, speaking from experience in dealing with this type of 'problem', everything you say does as well. Such is life really :) I would not be at -all- surprised if Sybase and Oracle did query re-writing behind the scene's to send un-defined count's to a temporary table which holds the row count. For an example of such done in postgreSQL (using triggers and a custom procedure) look into the 'General Bits' newsletter. Specifically http://www.varlena.com/varlena/GeneralBits/49.php I know, giving a URL as an answer 'sucks', but, well, it simply repeats my experience. Triggers and Procedures. Regards Steph On Tue, Feb 01, 2005 at 06:32:56PM +0530, Shridhar Daithankar wrote: > On Tuesday 01 Feb 2005 6:11 pm, Andrew Mayo wrote: > > PG, on the other hand, appears to do a full table scan > > to answer this question, taking nearly 4 seconds to > > process the query. > > > > Doing an ANALYZE on the table and also VACUUM did not > > seem to affect this. > > > > Can PG find a table's row count more efficiently?. > > This is not an unusual practice in commercial > > applications which assume that count(*) with no WHERE > > clause will be a cheap query - and use it to test if > > a table is empty, for instance. (because for > > Oracle/Sybase/SQL Server, count(*) is cheap). > > First of all, such an assumption is no good. It should hit concurrency under > heavy load but I know people do use it. > > For the specific question, after a vacuum analyze, you can use > > select reltuples from pg_class where relname='Foo'; > > Remember, you will get different results between 'analyze' and 'vacuum > analyze', since later actually visit every page in the table and hence is > expected to be more accurate. > > > (sure, I appreciate there are other ways of doing > > this, but I am curious about the way PG works here). > > Answer is MVCC and PG's inability use index alone. This has been a FAQ for a > loong time.. Furthermore PG has custom aggregates to complicate the matter.. > > Most of the pg developers/users think that unqualified select count(*) is of > no use. You can search the archives for more details.. > > HTH > > Shridhar > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > pgpoc1hcqAQ8G.pgp Description: PGP signature
[PERFORM] Slow loads when indexes added.
Hi all,
I suspect this problem/bug has been dealt with already, but I couldn't
find anything in the mail archives.
I'm using postgres 7.3, and I managed to recreate the problem using the
attached
files.
The database structure is in slow_structure.sql
After creating the database, using this script, I ran run_before_load__fast.sql
Then I created a load file using create_loadfile.sh (It creates a file called
load.sql)
I timed the loading of this file, and it loaded in 1 min 11.567 sec
Then I recreated the database from slow_structure.sql, ran
run_before_load__slow.sql,
and then loaded the same load.sql and it took 3 min 51.293 sec which is about 6
times slower.
I tried the same thing on postgres 8.0.0 to see if it does the same thing, but
there it
was consistently slow : 3 min 31.367 sec
The only way I got the load.sql to load fast on postgres 8.0.0, was by not
creating
any of the foreign key constraints that point to the "main" table, and then
enabling them
afterwards. This gave me the fastest time overall : 1 min 4.911 sec
My problem is that on the postgres 7.3.4 database I'm working with, a load
process that
used to take 40 minutes, now takes 4 hours, because of 3 rows data being loaded
into
a table (similar in setup to the "main" table in the example) before the
indexes were created.
(This happens automatically when you dump and re-import the database (7.3.4))
Is there a way to get it to load fast again on the 7.3 database without
dropping the foreign
key constraints (After running run_before_load_slow.sql) ?
And, if someone knows off-hand, what's happening here?
TIA
Kind Regards
Stefan#!/bin/bash
for x in one two three four five six seven eight nine ten
do
for y in eleven twelve thirteen fourteen fifteen sixteen seventeen eighteen nineteen twenty
do
for z in `seq 1 100`
do
echo "insert into main (c_text,d_text) values ('${x}','${y}');" >> load.sql
echo "insert into a(id) values (currval('public.main_id_seq'::text));" >> load.sql
echo "insert into b(id) values (currval('public.main_id_seq'::text));" >> load.sql
echo "insert into e(id) values (currval('public.main_id_seq'::text));" >> load.sql
echo "insert into f(id) values (currval('public.main_id_seq'::text));" >> load.sql
echo "insert into g(id) values (currval('public.main_id_seq'::text));" >> load.sql
echo "insert into h(id) values (currval('public.main_id_seq'::text));" >> load.sql
done
done
done
run_before_load__fast.sql
Description: Binary data
run_before_load__slow.sql
Description: Binary data
slow_structure.sql
Description: Binary data
---(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] Slow loads when indexes added.
[EMAIL PROTECTED] mentioned : => Try ANALYZE after loading the referenced tables, but before loading the main table I attached a new script for creating the load file... Analyze didn't help, it actually took longer to load. I set autocommit to off, and put a commit after every 100 inserts, chattr'd noatime atrribute off recursively on PGDATA, and set fsync to off, this improved the time from 3min 51sec to 2min 37 sec for the slow scenario. But I was already doing all these things in the app that used to take 40 minutes, but now takes four hours to load. Any other suggestions? Kind Regards Stefan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] Analyze makes queries slow...
Hi all,
I posted this problem on the sql list, and was referred to this list in stead.
I have attached an sql statement that normally runs under 3 minutes.
That is, until I vacuum analyze the database (or just the tables in the query),
then the same query runs longer than 12 hours, and I have to kill it.
However 90% of queries are faster after analyzing on this database,
there are two or three, including this one that takes for ever.
I have tried to reverse engineer the explain plan from before analyzing,
to come up with an sql statement, using proper joins, to force the planner
to do the original join, but although I came close, I never got the same
result as the original query.
I suspect that this might be caused by some of the crazy indexes that
were built on some of these tables, but I can't really do much about that,
unless I can come up with a very good reason to nuke them.
I also attached the "create table" statements for all the tables, as well
as a row count of each.
Can somebody help me with guidelines or something similar,
to understand exactly what is happening in the explain plan.
TIA
Stefan
Aggregate (cost=52.00..61.64 rows=32 width=241)
-> Group (cost=52.00..57.62 rows=321 width=241)
-> Sort (cost=52.00..52.80 rows=321 width=241)
Sort Key: m.group_code, m.sku, m.brn_code, m.stktype_code,
ss.supplier_price_curr_cost, sk.sku_price_curr_cost
-> Merge Join (cost=36.38..38.62 rows=321 width=241)
Merge Cond: (("outer".group_cde = "inner".group_cde) AND
("outer".brn_code = "inner".brn_code))
Join Filter: (("outer".price_tmpl_hdr_cde =
"inner".price_tmpl_hdr_cde) AND ("outer".price_tmpl_hdr_reg =
"inner".price_tmpl_hdr_reg))
-> Nested Loop (cost=0.00..1407212.08 rows=63 width=179)
Join Filter: (("inner".sku_mst_cde = "outer".sku) AND
("inner".group_cde = "outer".group_code))
-> Merge Join (cost=0.00..1405644.89 rows=315 width=135)
Merge Cond: ("outer".group_code = "inner".group_code)
Join Filter: ("outer".sku = "inner".sku)
-> Nested Loop (cost=0.00..4826563.70 rows=8694
width=108)
-> Index Scan using master_fpp_values_idx2 on
master_fpp_values m (cost=0.00..3766902.34 rows=215650 width=54)
Filter: (fpp_code = '200307'::text)
-> Index Scan using pk_supplier_price on
supplier_price ss (cost=0.00..4.90 rows=1 width=54)
Index Cond: ((ss.group_cde =
"outer".group_code) AND (ss.sku_mst_cde = "outer".sku) AND (ss.supplier_cde =
"outer".supplier_code))
-> Index Scan using master_sku_descr_idx3 on
master_sku_descr s (cost=0.00..2535.04 rows=10758 width=27)
Filter: (control_code = '0'::text)
-> Index Scan using idx_sku_price on sku_price sk
(cost=0.00..4.96 rows=1 width=44)
Index Cond: ((sk.group_cde = "outer".group_cde) AND
(sk.sku_mst_cde = "outer".sku_mst_cde) AND (sk.price_tmpl_hdr_cde =
"outer".price_tmpl_hdr_cde) AND (sk.price_tmpl_hdr_reg = "outer".price_tmpl_hdr_reg))
-> Sort (cost=36.38..36.87 rows=198 width=62)
Sort Key: p.group_cde, p.branch_cde
-> Hash Join (cost=18.46..28.82 rows=198 width=62)
Hash Cond: ("outer".brn_code = "inner".branch_cde)
-> Merge Join (cost=13.94..20.34 rows=198 width=33)
Merge Cond: ("outer".country_code =
"inner".from_ctry)
-> Index Scan using master_branch_descr_idx4
on master_branch_descr b (cost=0.00..33.12 rows=198 width=15)
-> Sort (cost=13.94..13.95 rows=4 width=18)
Sort Key: f.from_ctry
-> Index Scan using forex_idx1 on forex
f (cost=0.00..13.90 rows=4 width=18)
Index Cond: ((to_ctry =
'ZAF'::text) AND (fpp_code = '200307'::text))
-> Hash (cost=4.02..4.02 rows=202 width=29)
-> Seq Scan on price_tmpl_det p
(cost=0.00..4.02 rows=202 width=29)
(34 rows)
Aggregate (cost=163.58..163.61 rows=1 width=699)
-> Group (cost=163.58..163.60 rows=1 width=699)
-> Sort (cost=163.58..163.58 rows=1 width=699)
Sort Key: m.group_code, m.sku, m.brn_code, m.stktype_code,
ss.supplier_price_curr_cost, sk.sku_price_curr_cost
-> Nested Loop (cost=115.56..163.57 r
Re: [PERFORM] Analyze makes queries slow...
On Mon, 11 Aug 2003 14:25:03 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
=> set enable_mergejoin to off;
=> explain analyze ... query ...
=>
=> If it finishes in a reasonable amount of time, send the explain output.
Hi again,
I did this on the 7.3.1 database, and attached the output.
It actually ran faster after ANALYZE and 'set enable_mergejoin to off'
Thanks!
I also reloaded this database onto 7.3.4, tried the same query after
the ANALYZE, and the query executed a lot faster.
Thanks again!
I also attached the output of the EXPLAIN ANALYZE on 7.3.4
For now I'll maybe just disable mergejoin. But definitely a postgres
upgrade is what I will do.
I went through the different outputs of EXPLAIN ANALYZE a bit, and
I think I can now see where the difference is.
Thanks a lot for the help.
Regards
Stefan.
Aggregate (cost=103991.51..103999.75 rows=27 width=241) (actual
time=77907.78..83292.51 rows=125803 loops=1)
-> Group (cost=103991.51..103996.32 rows=274 width=241) (actual
time=77907.61..79449.70 rows=125803 loops=1)
-> Sort (cost=103991.51..103992.20 rows=274 width=241) (actual
time=77907.58..78149.54 rows=125803 loops=1)
Sort Key: m.group_code, m.sku, m.brn_code, m.stktype_code,
ss.supplier_price_curr_cost, sk.sku_price_curr_cost
-> Hash Join (cost=2599.45..103980.40 rows=274 width=241) (actual
time=2527.34..73353.16 rows=125803 loops=1)
Hash Cond: ("outer".country_code = "inner".from_ctry)
-> Nested Loop (cost=2585.54..103961.83 rows=12 width=223)
(actual time=2504.90..71966.16 rows=125803 loops=1)
Join Filter: (("inner".price_tmpl_hdr_cde =
"outer".price_tmpl_hdr_cde) AND ("inner".price_tmpl_hdr_reg =
"outer".price_tmpl_hdr_reg) AND ("inner".group_cde = "outer".group_cde) AND
("inner".sku_mst_cde = "outer".sku))
-> Hash Join (cost=2585.54..103611.37 rows=60 width=179)
(actual time=2411.76..46771.60 rows=125803 loops=1)
Hash Cond: ("outer".brn_code = "inner".brn_code)
-> Hash Join (cost=2575.07..103599.70 rows=60
width=164) (actual time=2410.16..44730.60 rows=125803 loops=1)
Hash Cond: ("outer".brn_code =
"inner".branch_cde)
Join Filter: (("inner".group_cde =
"outer".group_code) AND ("outer".price_tmpl_hdr_cde = "inner".price_tmpl_hdr_cde) AND
("outer".price_tmpl_hdr_reg = "inner".price_tmpl_hdr_reg))
-> Hash Join (cost=2570.54..103586.96
rows=299 width=135) (actual time=2402.43..39292.85 rows=629015 loops=1)
Hash Cond: ("outer".sku = "inner".sku)
Join Filter: (("outer".group_code =
"inner".group_code) AND ("inner".supplier_cde = "outer".supplier_code))
-> Seq Scan on master_fpp_values m
(cost=0.00..98545.54 rows=220358 width=54) (actual time=1013.70..28087.16 rows=162226
loops=1)
Filter: (fpp_code = '200307'::text)
-> Hash (cost=2542.25..2542.25
rows=11318 width=81) (actual time=1388.58..1388.58 rows=0 loops=1)
-> Hash Join
(cost=543.67..2542.25 rows=11318 width=81) (actual time=188.63..1277.34 rows=54675
loops=1)
Hash Cond:
("outer".sku_mst_cde = "inner".sku)
Join Filter:
("outer".group_cde = "inner".group_code)
-> Seq Scan on
supplier_price ss (cost=0.00..1418.75 rows=54675 width=54) (actual time=5.94..553.10
rows=54675 loops=1)
-> Hash
(cost=516.06..516.06 rows=11042 width=27) (actual time=182.53..182.53 rows=0 loops=1)
-> Index Scan using
master_sku_descr_idx11 on master_sku_descr s (cost=0.00..516.06 rows=11042 width=27)
(actual time=19.15..160.75 rows=10936 loops=1)
Index Cond:
(control_code = '0'::text)
-> Hash (cost=4.02..4.02 rows=202 width=29)
(actual time=7.51..7.51 rows=0 loops=1)
-> Seq Scan on price_tmpl_det p
(cost=0.00..4.02 rows=202 width=29) (actual time=6.46..7.16 rows=202 loops=1)
-> Hash (cost=9.98..9.98 rows=198 width=15) (actual
time=1.41..1.41 rows=0 loops=1)
-> Seq Scan on master_branch_descr b
(cost=0.00..9.98 rows=198 width=15) (actual time=0.09..1.08 rows=198 loops=1)
-
Re: [PERFORM] Analyze makes queries slow...
Hi Tom,
Thanks for responding.
I got as much info as I could :
On Mon, 11 Aug 2003 11:43:45 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
=> Could we see the results of "EXPLAIN ANALYZE", rather than just EXPLAIN,
=> for the un-analyzed case?
Attached the output of this.
=> Also, what do you see in pg_stats (after analyzing) for each of the
=> tables used in the query?
I attached a file in csv format of pg_stats after analyzing.
(With the columns selected on the top line)
It looks like cached values for (quite a lot of?) the table columns.
I would assume it stores the most commonly selected
values for every column with an index. Don't know if I'm correct.
=> And what PG version is this, exactly?
PostgreSQL 7.3.1
Kind regards
Stefan
Aggregate (cost=187.80..187.84 rows=1 width=699) (actual time=142704.64..148066.77
rows=125769 loops=1)
-> Group (cost=187.80..187.82 rows=1 width=699) (actual time=142704.48..144239.11
rows=125769 loops=1)
-> Sort (cost=187.80..187.81 rows=1 width=699) (actual
time=142704.45..142947.14 rows=125769 loops=1)
Sort Key: m.group_code, m.sku, m.brn_code, m.stktype_code,
ss.supplier_price_curr_cost, sk.sku_price_curr_cost
-> Nested Loop (cost=140.07..187.79 rows=1 width=699) (actual
time=49796.26..135679.87 rows=125769 loops=1)
Join Filter: (("outer".sku = "inner".sku) AND ("outer".group_code
= "inner".group_code))
-> Nested Loop (cost=140.07..181.76 rows=1 width=635) (actual
time=49742.50..118086.42 rows=125769 loops=1)
Join Filter: (("inner".group_cde = "outer".group_cde) AND
("inner".price_tmpl_hdr_cde = "outer".price_tmpl_hdr_cde) AND
("inner".price_tmpl_hdr_reg = "outer".price_tmpl_hdr_reg) AND ("inner".supplier_cde =
"outer".supplier_code))
-> Nested Loop (cost=140.07..176.91 rows=1 width=485)
(actual time=49741.95..90991.39 rows=125769 loops=1)
Join Filter: (("inner".price_tmpl_hdr_cde =
"outer".price_tmpl_hdr_cde) AND ("inner".price_tmpl_hdr_reg =
"outer".price_tmpl_hdr_reg) AND ("inner".group_cde = "outer".group_cde))
-> Hash Join (cost=140.07..172.07 rows=1 width=367)
(actual time=49741.16..52345.71 rows=162115 loops=1)
Hash Cond: ("outer".branch_cde =
"inner".brn_code)
Join Filter: ("inner".group_code =
"outer".group_cde)
-> Seq Scan on price_tmpl_det p
(cost=0.00..20.00 rows=1000 width=100) (actual time=0.09..9.50 rows=202 loops=1)
-> Hash (cost=140.00..140.00 rows=27
width=267) (actual time=49740.97..49740.97 rows=0 loops=1)
-> Nested Loop (cost=0.00..140.00
rows=27 width=267) (actual time=432.55..49360.23 rows=162115 loops=1)
-> Nested Loop (cost=0.00..30.79
rows=1 width=115) (actual time=154.19..184.03 rows=198 loops=1)
-> Seq Scan on forex f
(cost=0.00..25.00 rows=1 width=51) (actual time=50.86..51.12 rows=4 loops=1)
Filter: ((to_ctry =
'ZAF'::text) AND (fpp_code = '200307'::text))
-> Index Scan using
master_branch_descr_idx4 on master_branch_descr b (cost=0.00..5.78 rows=1 width=64)
(actual time=25.90..32.81 rows=50 loops=4)
Index Cond:
(b.country_code = "outer".from_ctry)
-> Index Scan using
master_fpp_values_uidx1 on master_fpp_values m (cost=0.00..108.88 rows=27 width=152)
(actual time=3.41..243.55 rows=819 loops=198)
Index Cond: ((m.fpp_code =
'200307'::text) AND (m.brn_code = "outer".brn_code))
-> Index Scan using idx_sku_price on sku_price sk
(cost=0.00..4.83 rows=1 width=118) (actual time=0.09..0.21 rows=4 loops=162115)
Index Cond: ((sk.group_cde =
"outer".group_code) AND (sk.sku_mst_cde = "outer".sku))
-> Index Scan using idx_supplier_price on supplier_price
ss (cost=0.00..4.83 rows=1 width=150) (actual time=0.11..0.16 rows=5 loops=125769)
Index Cond: (("outer".group_cde = ss.group_cde) AND
("outer".sku_mst_cde = ss.sku_mst_cde))
-> Index Scan using master_sku_descr_idx1 on master_sku_descr s
(cost=0.00..6.02 rows=1 width=64) (actual time=0.12..0.12 rows=1 loops=125769)
Index Cond: (("outer".group_cde = s.group_code) AND
("outer".sku_mst_cde = s.sku) AND (s.control_code = '0'::text))
Total runtime: 148710.78 msec
(30 ro
[PERFORM] Postgres low end processing.
Hi everyone, I've been trying to find out if some guidelines exist, somewhere, describing how postgres can possibly run on less than 8MB of RAM. (Disk space not an issue). The closest thread I could find in the list archives is : http://archives.postgresql.org/pgsql-general/2002-06/msg01343.php Is it possible to have a stripped-down version of postgres that will use an absolute minimal amount of memory? Maybe by switching off some features/options at compile time, and/or configuration tweaks? (Or anything else) This will be on very low end i386 architecture. Performance penalties are expected and will be accepted. I will need the functionality of >= 7.3.4 , at least. Any help will be much appreciated. Regards Stef 0009.mimetmp Description: PGP signature pgp0.pgp Description: PGP signature
Re: [PERFORM] Postgres low end processing.
On Fri, 03 Oct 2003 11:42:54 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: => Are you sure you want Postgres, and not something smaller? BDB, => or SQL Lite, for example? I have considered various options, including BDB and SQL Lite, but alas, it will have to be postgres if it's going to be a database. Otherwise it will be back to the original idea of flat .idx files :( => "Postgres is bloatware by design: it was built to house PhD theses." => -- J. Hellerstein (who ought to know) :o) Believe me, I've been amazed since I encountered postgres v6.3.2 in '98 => But having said that ... given virtual memory and cramped configuration => settings, Postgres would certainly run in an 8M machine. Maybe "crawl" => would be a more applicable verb than "run", but you could execute it. Crawling is ok. Won't differ much from normal operation on a machine like that. Any tips on how to achieve the most diminutive vmem an conf settings? I tried to figure this out from the docs, and played around with backend/storage , but I'm not really winning. Regards Stef pgp0.pgp Description: PGP signature
Re: [PERFORM] Postgres low end processing.
On Fri, 03 Oct 2003 12:32:00 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: => What exactly is failing? And what's the platform, anyway? Nothing is really failing atm, except the funds for better hardware. JBOSS and some other servers need to be run on these machines, along with linux, which will be a minimal RH >= 7.2 with kernel 2.4.21 (Any better suggestions here?) In this case, whatever is the least amount of memory postgres can run on, is what is needed. So this is still a kind of feasibility study. Of course, it will still be thoroughly tested, if it turns out to be possible. (Which I know it is, but not how) Regards Stef pgp0.pgp Description: PGP signature
Re: [PERFORM] Postgres low end processing.
Thanks for the replies, On Fri, 3 Oct 2003 11:08:48 -0700 Josh Berkus <[EMAIL PROTECTED]> wrote: => 1. Make sure that the WAL files (pg_xlog) are on a seperate disk from the => database files, either through mounting or symlinking. I'm not sure I understand how this helps? => 2. Tweak the .conf file for low vacuum_mem (1024?), but vacuum very => frequently, like every 1-5 minutes. Spend some time tuning your => fsm_max_pages to the ideal level so that you're not allocating any extra => memory to the FSM. => => 3. If your concern is *average* CPU/RAM consumption, and not peak load => activity, increase wal_files and checkpoint_segments to do more efficient => batch processing of pending updates as the cost of some disk space. If peak => load activity is a problem, don't do this. => => 4. Tune all of your queries carefully to avoid anything requiring a => RAM-intensive merge join or CPU-eating calculated expression hash join, or => similar computation-or-RAM-intensive operations. Thanks, I'll try some of these, and post the results. The actual machines seem to be Pentium I machines, with 32M RAM. I've gathered that it is theoretically possible, so no to go try it. Regards Stef pgp0.pgp Description: PGP signature
Re: [PERFORM] Postgres low end processing.
Hi again all, I've tested postgres 7.3.4 on Linux version 2.4.17 and this is what I found : The initial instance took up 8372K and this fluctuated between +- 8372K and 10372K, plus +- 3500K for every connection. I did quite a few transactions on both connections, plus a few vacuums and a pg_dump and the total memory usage didn't seem to go over 16M I set all the _buffers, _mem, _fsm settings to the minimum, restarted every time, but this had absolutely no noticeable increase or decrease in total memory usage. (I used a program called gmemusage to get these stats.) On the same machine , I tested postgres 7.1.2 with basically the same conf options (not _fsm) and got the following : The initial instance was 1772K and fluctuated to +- 4000K, plus +- 3400K for every connection. Doing the same transactions, vacuum + pg_dump, total memory usage didn't really go over 11M, which was exactly what I needed. Although I've lived through some of the shortcomings of 7.1.2, it is still very stable, and works perfectly for what it is going to be used for. Again, here, I was only able to restrict things a little by changing the configuration options, but no major difference in memory usage. Regards Stef On Mon, 6 Oct 2003 09:55:51 +0200 Stef <[EMAIL PROTECTED]> wrote: => Thanks for the replies, => => On Fri, 3 Oct 2003 11:08:48 -0700 => Josh Berkus <[EMAIL PROTECTED]> wrote: => => 1. Make sure that the WAL files (pg_xlog) are on a seperate disk from the => => database files, either through mounting or symlinking. => => I'm not sure I understand how this helps? => => => 2. Tweak the .conf file for low vacuum_mem (1024?), but vacuum very => => frequently, like every 1-5 minutes. Spend some time tuning your => => fsm_max_pages to the ideal level so that you're not allocating any extra => => memory to the FSM. => => => => 3. If your concern is *average* CPU/RAM consumption, and not peak load => => activity, increase wal_files and checkpoint_segments to do more efficient => => batch processing of pending updates as the cost of some disk space. If peak => => load activity is a problem, don't do this. => => => => 4. Tune all of your queries carefully to avoid anything requiring a => => RAM-intensive merge join or CPU-eating calculated expression hash join, or => => similar computation-or-RAM-intensive operations. => => Thanks, I'll try some of these, and post the results. => The actual machines seem to be Pentium I machines, => with 32M RAM. I've gathered that it is theoretically => possible, so no to go try it. => => Regards => Stef => pgp0.pgp Description: PGP signature
[PERFORM] postgres timeout.
Hi all , I'm trying to find out if there is a specific setting to make transactions time out faster in a scenario where there's an update on a table in a transaction block, and another update process tries to update the same column. It looks like the second process will wait until you end the transaction block in the first transaction. I've looked at the deadlock timeout parameter and other parameters, but I don't think I found what I'm looking for. I basically need to be able to let the second process exit with an error after waiting 5 - 10 seconds. Please can someone help? Kind Regards Stefan pgp0.pgp Description: PGP signature
Re: [PERFORM] postgres timeout. [SOLVED]
Hi all, It seems I always find a solution just after panicking a little bit. Anyway, I found that statement_timeout solved my problem. When I tested it earlier, I actually made an error, and skipped it as a possible solution. Cheers Stef Stef mentioned : => Forgot to mention that I use postgres 7.3.4 => => Stef mentioned : => => Hi all , => => => => I'm trying to find out if there is a specific setting => => to make transactions time out faster in a scenario => => where there's an update on a table in a transaction => => block, and another update process tries to update => => the same column. => => => => It looks like the second process will wait until you => => end the transaction block in the first transaction. => => => => I've looked at the deadlock timeout parameter and => => other parameters, but I don't think I found what => => I'm looking for. => => => => I basically need to be able to let the second process => => exit with an error after waiting 5 - 10 seconds. => => => => Please can someone help? => => => => Kind Regards => => Stefan => => => pgp0.pgp Description: PGP signature
Re: [PERFORM] postgres timeout.
Forgot to mention that I use postgres 7.3.4 Stef mentioned : => Hi all , => => I'm trying to find out if there is a specific setting => to make transactions time out faster in a scenario => where there's an update on a table in a transaction => block, and another update process tries to update => the same column. => => It looks like the second process will wait until you => end the transaction block in the first transaction. => => I've looked at the deadlock timeout parameter and => other parameters, but I don't think I found what => I'm looking for. => => I basically need to be able to let the second process => exit with an error after waiting 5 - 10 seconds. => => Please can someone help? => => Kind Regards => Stefan => pgp0.pgp Description: PGP signature
Re: [PERFORM] Slow deletes in 8.1 when FKs are involved
Hey there Will,
I would assume that, perhaps, jst perhaps, the FK doesn't have an
index on the field on both sides, so, your seeing a potential sequential
scan happening. Can you fling up an explain anaylze for everyone please
? Anything more will be merely shooting in the dark, and, tracer bullets
aside, I have heard that -that- can be dangerous ;p
Regards
Stef
Will Reese wrote:
> I'm preparing for an upgrade from PostgreSQL 7.4.5 to 8.1.3, and I
> noticed a potential performance issue.
>
> I have two servers, a dual proc Dell with raid 5 running PostgreSQL
> 7.4, and a quad proc Dell with a storage array running PostgreSQL 8.1.
> Both servers have identical postgresql.conf settings and were restored
> from the same 7.4 backup. Almost everything is faster on the 8.1
> server (mostly due to hardware), except one thing...deletes from
> tables with foreign keys.
>
> I have table A with around 100,000 rows, that has foreign keys to
> around 50 other tables. Some of these other tables (table B, for
> example) have around 10 million rows.
>
> On the 7.4 server, I can delete a single row from a table A in well
> under a second (as expected). On the 8.1 server, it takes over a
> minute to delete. I tried all the usual stuff, recreating indexes,
> vacuum analyzing, explain analyze. Everything is identical between
> the systems. If I hit ctrl-c while the delete was running on 8.1, I
> repeatedly got the following message...
>
> db=# delete from "A" where "ID" in ('6');
> Cancel request sent
> ERROR: canceling statement due to user request
> CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."B" x WHERE
> "A_ID" = $1 FOR SHARE OF x"
>
> It looks to me like the "SELECT ... FOR SHARE" functionality in 8.1 is
> the culprit. Has anyone else run into this issue?
>
>
> Will Reese -- http://blog.rezra.com
>
>
>
>
> ---(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 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Raid 10 chunksize
B ram, 2 x 7200rpm SATA into motherboard which I then lvm stripped together; lvcreate -n data_lv -i 2 -I 64 mylv -L 60G (expandable under lvm2). That gives me a stripe size of 64. Running pgbench with the same scaling factors; starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 24 number of transactions per client: 12000 number of transactions actually processed: 288000/288000 tps = 1398.907206 (including connections establishing) tps = 1399.233785 (excluding connections establishing) It's also running ext4dev, but, this is the 'playground' server, not the real iron (And I dread to do that on the real iron). In short, I think that chunksize/stripesize is killing you. Personally, I would go for 64 or 128 .. that's jst my 2c .. feel free to ignore/scorn/laugh as applicable ;) Regards Stef -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAknK0UsACgkQANG7uQ+9D9VK3wCeO/guLVb4K4V7VAQ29hJsmstb 2JMAmQEmJjNTQlxng/49D2/xHNw2W19/ =/rKD -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Raid 10 chunksize
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mark Kirkwood wrote: > Scott Carey wrote: >> >> A little extra info here >> md, LVM, and some other tools do not >> allow the file system to use write barriers properly So >> those are on the bad list for data integrity with SAS or SATA >> write caches without battery back-up. However, this is NOT an >> issue on the postgres data partition. Data fsync still works >> fine, its the file system journal that might have out-of-order >> writes. For xlogs, write barriers are not important, only >> fsync() not lying. >> >> As an additional note, ext4 uses checksums per block in the >> journal, so it is resistant to out of order writes causing >> trouble. The test compared to here was on ext4, and most likely >> the speed increase is partly due to that. >> >> > > [Looks at Stef's config - 2x 7200 rpm SATA RAID 0] I'm still > highly suspicious of such a system being capable of outperforming > one with the same number of (effective) - much faster - disks > *plus* a dedicated WAL disk pair... unless it is being a little > loose about fsync! I'm happy to believe ext4 is better than ext3 - > but not that much! > > However, its great to have so many different results to compare > against! > > Cheers > > Mark > Hello Mark, For the record, this is a 'base' debian 5 install (with openVZ but postgreSQL is running on the base hardware, not inside a container) and I have -explicitly- enabled sync in the conf. Eg; fsync = on# turns forced synchronization on or off synchronous_commit = on # immediate fsync at commit #wal_sync_method = fsync# the default is the first option Infact, if I turn -off- sync commit, it gets about 200 -slower- rather than faster. Curiously, I also have an intel x25-m winging it's way here for testing/benching under postgreSQL (along with a vertex 120gb). I had one of the nice lads on the OCZ forum bench against a 30gb vertex ssd, and if you think -my- TPS was crazy.. you should have seen his. postg...@rob-desktop:~$ /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 test_db starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 24 number of transactions per client: 12000 number of transactions actually processed: 288000/288000 tps = 3662.200088 (including connections establishing) tps = 3664.823769 (excluding connections establishing) (Nb; Thread here; http://www.ocztechnologyforum.com/forum/showthread.php?t=54038 ) Curiously, I think with SSD's there may have to be an 'off' flag if you put the xlog onto an ssd. It seems to complain about 'too frequent checkpoints'. I can't wait for -either- of the drives to arrive. I want to see in -my- system what the speed is like for SSD's. The dataset I have to work with is fairly small (30-40GB) so, using an 80GB ssd (even a few raided) is possible for me. Thankfully ;) Regards Stef (ps. I should note, running postgreSQL in a prod environment -without- a nice UPS is never going to happen on my watch, so, turning on write-cache (to me) seems like a no-brainer really if it makes this kind of boost possible) -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAknTfKMACgkQANG7uQ+9D9XZ7wCfdU3JDXj1f2Em9dt7GdcxRbWR eHUAn1zDb3HKEiAb0d/0R1MubtE44o/k =HXmP -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Raid 10 chunksize
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Greg Smith wrote: > On Wed, 1 Apr 2009, Stef Telford wrote: > >> I have -explicitly- enabled sync in the conf...In fact, if I turn >> -off- sync commit, it gets about 200 -slower- rather than >> faster. > > You should take a look at > http://www.postgresql.org/docs/8.3/static/wal-reliability.html > > And check the output from "hdparm -I" as suggested there. If > turning off fsync doesn't improve your performance, there's almost > certainly something wrong with your setup. As suggested before, > your drives probably have write caching turned on. PostgreSQL is > incapable of knowing that, and will happily write in an unsafe > manner even if the fsync parameter is turned on. There's a bunch > more information on this topic at > http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm > > Also: a run to run variation in pgbench results of +/-10% TPS is > normal, so unless you saw a consistent 200 TPS gain during multiple > tests my guess is that changing fsync for you is doing nothing, > rather than you suggestion that it makes things slower. > Hello Greg, Turning off fsync -does- increase the throughput noticeably, - -however-, turning off synchronous_commit seemed to slow things down for me. Your right though, when I toggled the sync_commit on the system, there was a small variation with TPS coming out between 1100 and 1300. I guess I saw the initial run and thought that there was a 'loss' in sync_commit = off I do agree that the benefit is probably from write-caching, but I think that this is a 'win' as long as you have a UPS or BBU adaptor, and really, in a prod environment, not having a UPS is .. well. Crazy ? >> Curiously, I think with SSD's there may have to be an 'off' flag >> if you put the xlog onto an ssd. It seems to complain about 'too >> frequent checkpoints'. > > You just need to increase checkpoint_segments from the tiny default > if you want to push any reasonable numbers of transactions/second > through pgbench without seeing this warning. Same thing happens > with any high-performance disk setup, it's not specific to SSDs. > Good to know, I thought it maybe was atypical behaviour due to the nature of SSD's. Regards Stef > -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com > Baltimore, MD -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAknTky0ACgkQANG7uQ+9D9UuNwCghLLC96mj9zzZPUF4GLvBDlQk fyIAn0V63YZJGzfm+4zPB9zjm8YKn42X =A6x2 -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Raid 10 chunksize
Scott Marlowe wrote: > On Wed, Apr 1, 2009 at 10:15 AM, Stef Telford wrote: > >> I do agree that the benefit is probably from write-caching, but I >> think that this is a 'win' as long as you have a UPS or BBU adaptor, >> and really, in a prod environment, not having a UPS is .. well. Crazy ? >> > > You do know that UPSes can fail, right? En masse sometimes even. > Hello Scott, Well, the only time the UPS has failed in my memory, was during the great Eastern Seaboard power outage of 2003. Lots of fond memories running around Toronto with a gas can looking for oil for generator power. This said though, anything could happen, the co-lo could be taken out by a meteor and then sync on or off makes no difference. Good UPS, a warm PITR standby, offsite backups and regular checks is "good enough" for me, and really, that's what it all comes down to. Mitigating risk and factors into an 'acceptable' amount for each person. However, if you see over a 2x improvement from turning write-cache 'on' and have everything else in place, well, that seems like a 'no-brainer' to me, at least ;) Regards Stef -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Raid 10 chunksize
Matthew Wakeling wrote: > On Wed, 1 Apr 2009, Stef Telford wrote: >>Good UPS, a warm PITR standby, offsite backups and regular checks is >> "good enough" for me, and really, that's what it all comes down to. >> Mitigating risk and factors into an 'acceptable' amount for each person. >> However, if you see over a 2x improvement from turning write-cache 'on' >> and have everything else in place, well, that seems like a 'no-brainer' >> to me, at least ;) > > In that case, buying a battery-backed-up cache in the RAID controller > would be even more of a no-brainer. > > Matthew > Hey Matthew, See about 3 messages ago.. We already have them (I did say UPS or BBU, it should have been a logical 'and' instead of logical 'or' .. my bad ;). Your right though, that was a no-brainer as well. I am wondering how the card (3ware 9550sx) will work with SSD's, md or lvm, blocksize, ext3 or ext4 .. but.. this is the point of benchmarking ;) Regards Stef -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Raid 10 chunksize
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Stef Telford wrote: > Mark Kirkwood wrote: >> Scott Carey wrote: >>> A little extra info here >> md, LVM, and some other tools do >>> not allow the file system to use write barriers properly So >>> those are on the bad list for data integrity with SAS or SATA >>> write caches without battery back-up. However, this is NOT an >>> issue on the postgres data partition. Data fsync still works >>> fine, its the file system journal that might have out-of-order >>> writes. For xlogs, write barriers are not important, only >>> fsync() not lying. >>> >>> As an additional note, ext4 uses checksums per block in the >>> journal, so it is resistant to out of order writes causing >>> trouble. The test compared to here was on ext4, and most >>> likely the speed increase is partly due to that. >>> >>> >> [Looks at Stef's config - 2x 7200 rpm SATA RAID 0] I'm still >> highly suspicious of such a system being capable of outperforming >> one with the same number of (effective) - much faster - disks >> *plus* a dedicated WAL disk pair... unless it is being a little >> loose about fsync! I'm happy to believe ext4 is better than ext3 >> - but not that much! > >> However, its great to have so many different results to compare >> against! > >> Cheers > >> Mark > > postg...@rob-desktop:~$ /usr/lib/postgresql/8.3/bin/pgbench -c 24 > -t 12000 test_db starting vacuum...end. transaction type: TPC-B > (sort of) scaling factor: 100 number of clients: 24 number of > transactions per client: 12000 number of transactions actually > processed: 288000/288000 tps = 3662.200088 (including connections > establishing) tps = 3664.823769 (excluding connections > establishing) > > > (Nb; Thread here; > http://www.ocztechnologyforum.com/forum/showthread.php?t=54038 ) Fyi, I got my intel x25-m in the mail, and I have been benching it for the past hour or so. Here are some of the rough and ready figures. Note that I don't get anywhere near the vertex benchmark. I did hotplug it and made the filesystem using Theodore Ts'o webpage directions ( http://thunk.org/tytso/blog/2009/02/20/aligning-filesystems-to-an-ssds-erase-block-size/ ) ; The only thing is, ext3/4 seems to be fixated on a blocksize of 4k, I am wondering if this could be part of the 'problem'. Any ideas/thoughts on tuning gratefully received. Anyway, benchmarks (same system as previously, etc) (ext4dev, 4k block size, pg_xlog on 2x7.2krpm raid-0, rest on SSD) r...@debian:~# /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 test_db starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 24 number of transactions per client: 12000 number of transactions actually processed: 288000/288000 tps = 1407.254118 (including connections establishing) tps = 1407.645996 (excluding connections establishing) (ext4dev, 4k block size, everything on SSD) r...@debian:~# /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 test_db starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 24 number of transactions per client: 12000 number of transactions actually processed: 288000/288000 tps = 2130.734705 (including connections establishing) tps = 2131.545519 (excluding connections establishing) (I wanted to try and see if random_page_cost dropped down to 2.0, sequential_page_cost = 2.0 would make a difference. Eg; making the planner aware that a random was the same cost as a sequential) r...@debian:/var/lib/postgresql/8.3/main# /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 test_db starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 24 number of transactions per client: 12000 number of transactions actually processed: 288000/288000 tps = 1982.481185 (including connections establishing) tps = 1983.223281 (excluding connections establishing) Regards Stef -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAknTxccACgkQANG7uQ+9D9XoPgCfRwWwh0jTIs1iDQBVVdQJW/JN CBcAn3zoOO33BnYC/FgmFzw1I+isWvJh =0KYa -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Raid 10 chunksize
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Stef Telford wrote: > Stef Telford wrote: >> Mark Kirkwood wrote: >>> Scott Carey wrote: >>>> A little extra info here >> md, LVM, and some other tools do >>>> not allow the file system to use write barriers properly >>>> So those are on the bad list for data integrity with SAS or >>>> SATA write caches without battery back-up. However, this is >>>> NOT an issue on the postgres data partition. Data fsync >>>> still works fine, its the file system journal that might have >>>> out-of-order writes. For xlogs, write barriers are not >>>> important, only fsync() not lying. >>>> >>>> As an additional note, ext4 uses checksums per block in the >>>> journal, so it is resistant to out of order writes causing >>>> trouble. The test compared to here was on ext4, and most >>>> likely the speed increase is partly due to that. >>>> >>>> >>> [Looks at Stef's config - 2x 7200 rpm SATA RAID 0] I'm still >>> highly suspicious of such a system being capable of >>> outperforming one with the same number of (effective) - much >>> faster - disks *plus* a dedicated WAL disk pair... unless it is >>> being a little loose about fsync! I'm happy to believe ext4 is >>> better than ext3 - but not that much! However, its great to >>> have so many different results to compare against! Cheers Mark >> postg...@rob-desktop:~$ /usr/lib/postgresql/8.3/bin/pgbench -c 24 >> -t 12000 test_db starting vacuum...end. transaction type: TPC-B >> (sort of) scaling factor: 100 number of clients: 24 number of >> transactions per client: 12000 number of transactions actually >> processed: 288000/288000 tps = 3662.200088 (including connections >> establishing) tps = 3664.823769 (excluding connections >> establishing) > > >> (Nb; Thread here; >> http://www.ocztechnologyforum.com/forum/showthread.php?t=54038 ) > Fyi, I got my intel x25-m in the mail, and I have been benching it > for the past hour or so. Here are some of the rough and ready > figures. Note that I don't get anywhere near the vertex benchmark. > I did hotplug it and made the filesystem using Theodore Ts'o > webpage directions ( > http://thunk.org/tytso/blog/2009/02/20/aligning-filesystems-to-an-ssds-erase-block-size/ > ) ; The only thing is, ext3/4 seems to be fixated on a blocksize > of 4k, I am wondering if this could be part of the 'problem'. Any > ideas/thoughts on tuning gratefully received. > > Anyway, benchmarks (same system as previously, etc) > > (ext4dev, 4k block size, pg_xlog on 2x7.2krpm raid-0, rest on SSD) > > r...@debian:~# /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 > test_db starting vacuum...end. transaction type: TPC-B (sort of) > scaling factor: 100 number of clients: 24 number of transactions > per client: 12000 number of transactions actually processed: > 288000/288000 tps = 1407.254118 (including connections > establishing) tps = 1407.645996 (excluding connections > establishing) > > (ext4dev, 4k block size, everything on SSD) > > r...@debian:~# /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 > test_db starting vacuum...end. transaction type: TPC-B (sort of) > scaling factor: 100 number of clients: 24 number of transactions > per client: 12000 number of transactions actually processed: > 288000/288000 tps = 2130.734705 (including connections > establishing) tps = 2131.545519 (excluding connections > establishing) > > (I wanted to try and see if random_page_cost dropped down to 2.0, > sequential_page_cost = 2.0 would make a difference. Eg; making the > planner aware that a random was the same cost as a sequential) > > r...@debian:/var/lib/postgresql/8.3/main# > /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 test_db starting > vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 > number of clients: 24 number of transactions per client: 12000 > number of transactions actually processed: 288000/288000 tps = > 1982.481185 (including connections establishing) tps = 1983.223281 > (excluding connections establishing) > > > Regards Stef Here is the single x25-m SSD, write cache -disabled-, XFS, noatime mounted using the no-op scheduler; s...@debian:~$ sudo /usr/lib/postgresql/8.3/bin/pgbench -c 24 -t 12000 test_db starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 24 number of transactions per client: 12000 number of transactions actually processed: 288000/288000 tps = 1427.781843 (including connections establishing) tps = 1428.137858 (excluding connections establishing) Regards Stef -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAknT0hEACgkQANG7uQ+9D9X8zQCfcJ+tRQ7Sh6/YQImPejfZr/h4 /QcAn0hZujC1+f+4tBSF8EhNgR6q44kc =XzG/ -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Hunting Unused Indexes .. is it this simple ?
Hey Everyone, So, I have a nice postgreSQL server (8.4) up and running our database. I even managed to get master->slave going without trouble using the excellent skytools.. however, I want to maximize speed and the hot updates where possible, so, I am wanting to prune unused indexes from the database. is it as simple as taking the output from ; select indexrelname from pg_stat_user_indexes where idx_scan = 0 and idx_tup_read = 0 and idx_tup_fetch = 0 ; And .. dropping ? The reason I ask is, well, the count on that gives me 750 indexes where-as the count on all user_indexes is 1100. About 2/3rds of them are obsolete ? I did do an ETL from mySQL -> postgreSQL but.. that's still a ridiculous amount of (potentially) unused indexes. Regards Stef -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
