Re: [PERFORM] scaling up postgres
Hi, Fzied, [EMAIL PROTECTED] wrote: I'm using httperf/autobench for measurments and the best result I can get is that my system can handle a trafiic of almost 1600 New con/sec. Are you using connection pooling or persistent connections between PostgreSQL and the Apaches? Maybe it simply is the network latency between the two machines - as the database is read-only, did you think about having both PostgreSQL and Apache on both machines, and then load-balancing ingoing http requests between them? I cannot scale beyond that value and the funny thing, is that none of the servers is swapping, or heavy loaded, neither postgres nor apache are refusing connexions. And for measuring, are you really throwing parallel http connections to the server? This sounds like you measure request latency, but the maximum throughput might be much higher. my database is only 58M it's a read only DB and will lasts only for a month. I guess it is a simple table with a single PK (some subscription numer) - no joins or other things. For this cases, a special non-RDBMS like MySQL, SQLite, or even some hancrafted thingy may give you better results. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Big array speed issues
Von: Merlin Moncure [mailto:[EMAIL PROTECTED] An: Merkel Marcel (CR/AEM4) Cc: pgsql-performance@postgresql.org Betreff: Re: [PERFORM] Big array speed issues On 6/20/06, Merkel Marcel (CR/AEM4) [EMAIL PROTECTED] wrote: I use libpqxx to access the database. This might be another bottleneck, but I assume my query and table setup is the bigger bottleneck. Would it make sense to fetch the whole array ? (Select map from table where ... and parse the array manually) have you tried similar approach without using arrays? Merlin Not yet. I would first like to know what is the time consuming part and what is a work around. If you are sure individual columns for every entry of the array solve the issue I will joyfully implement it. The downsize of this approch is that the array dimensions are not always the same in my scenario. But I have a workaround in mind for this issue. Cheers Marcel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] scaling up postgres
Hi, Zydoon, Zydoon wrote: Now I'm trying to make my tests, and I'm not that sure I will make the switch to the PSeries, since my dual xeon with 4 G RAM can handle 3500 concurrent postmasters consuming 3.7 G of the RAM. I cannot reach this number on the PSeries with 2 G. This sounds like you want to have one postgresql backend per apache frontend. Did you try running pgpool on the Apache machine, and have only a few (hundred) connections to the backend? Maybe http://en.wikipedia.org/wiki/Memcached could be helpful, too. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Some queries starting to hang
Hi, Chris, Chris Beecroft wrote: Query is now returning with results on our replicated database. Will vacuum analyze production now. So it seems to have done the trick. Now the question is has our auto vacuum failed or was not set up properly... A question for my IT people. Most of the cases when we had database bloat despite running autovacuum, it was due to a low free_space_map setting. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Help tuning autovacuum - seeing lots of relation bloat
Hey - I am running into a data relation bloat problem which I believe is causing fairly significant slowdown of my updates. I am using version version - PostgreSQL 8.1.4 on i586-trustix-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 After about 12 hours of running, my updates are causing lots of reads and iowait (45%) slowing everything down. The DB bloats from 259MB to 2.4 - 3.4GB. The primary table which is troubled is called target and reaches a size of in mb of 834MB from its freshly 'vacuum full analyze' size of 39MB. qradar=# select * from q_table_size; tablename| size +- target | 834.496 My configuration includes. shared_buffers = 32767 work_mem = 20480 maintenance_work_mem = 32768 max_fsm_pages = 4024000 max_fsm_relations = 2000 fsync = false wal_sync_method = fsync wal_buffers = 4096 checkpoint_segments = 32 checkpoint_timeout = 1200 checkpoint_warning = 60 commit_delay = 5000 commit_siblings = 5 effective_cache_size = 175000 random_page_cost = 2 autovacuum = true autovacuum_naptime = 60 autovacuum_vacuum_threshold = 500 autovacuum_analyze_threshold = 250 autovacuum_vacuum_scale_factor = 0.08 autovacuum_analyze_scale_factor = 0.08 #autovacuum_vacuum_cost_delay=100 #autovacuum_vacuum_cost_limit=100 default_statistics_target = 40 For the particular table I have pg_autovacuum overrides as app=# select * from pg_autovacuum where vacrelid = 16603; vacrelid | enabled | vac_base_thresh | vac_scale_factor | anl_base_thresh | anl_scale_factor | vac_cost_delay | vac_cost_limit --+-+-+--+-+--++ 16603 | t | 200 | 0.01 | 200 | 0.01 | 0 |400 What I am seeing is, after about 12 hours an update of a few thousand records takes about 2+ minutes as opposed the 100ms it used to take. I can restore performance only be stopping everything, perform a vacuum full analyze and restarting. After the vacuum full, my table returns to the expected 250+ MB from the previous size. qradar=# select * from q_table_size ; tablename| size +- target | 841.536 I can see autovacuum in top every 60 seconds as configured, but it is there and gone in the 1 second refresh. My table grows consistent every transaction to no avail. To stop the growth, I had to perform a manual vacuum analyze. But at this point, performance is so poor I have to perform vacuum analyze full. Anyway, I am totally confused. My first cut at changing the autovacuum configuration was using Jim Nasby' advice by cutting all values in half leaving my tables at roughly 20% dead space, for this table, that would be just over 50k tuples. This however yields the same results as the above configuration with continous bloat. So, I was WAY more aggressive as shown above with no improvment. By calculation, Jims advice would suffice for our system. I just checked a production box which is running 8.1.1 and it is behaving as expected. This configuration only specifies autovacuum = true, everything else is left to the defaults. Is there something whacked about my configuration? Is there a way I can troubleshoot what autovacuum is doing or why it is not performing the work? Here is the output for the vacuum full of target... qradar=# vacuum full analyze verbose target; INFO: vacuuming public.target INFO: target: found 5048468 removable, 266778 nonremovable row versions in 96642 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 140 to 144 bytes long. There were 1696 unused item pointers. Total free space (including removable row versions) is 730074628 bytes. 89347 pages are or will become empty, including 0 at the end of the table. 95261 pages containing 730030436 free bytes are potential move destinations. CPU 2.31s/1.27u sec elapsed 6.46 sec. INFO: index target_pkey now contains 266778 row versions in 18991 pages DETAIL: 5048468 index row versions were removed. 40 index pages have been deleted, 40 are currently reusable. CPU 0.91s/5.29u sec elapsed 6.24 sec. INFO: index target_network_key now contains 266778 row versions in 15159 pages DETAIL: 5048468 index row versions were removed. 30 index pages have been deleted, 30 are currently reusable. CPU 0.45s/4.96u sec elapsed 5.43 sec. INFO: index target_tulu_idx now contains 266778 row versions in 19453 pages DETAIL: 5048468 index row versions were removed. 17106 index pages have been deleted, 17106 are currently reusable. CPU 0.79s/3.31u sec elapsed 4.10 sec. INFO: target: moved 266719 row versions, truncated 96642 to 4851 pages DETAIL: CPU 5.19s/8.86u sec
Re: [PERFORM] Help tuning autovacuum - seeing lots of relation
Csaba Nagy [EMAIL PROTECTED] writes: So, it appears my autovacuum is just NOT working... I must have screwed something up, but I cannot see what. Is it possible that you have long running transactions ? The other question I was wondering about is if autovacuum is actually choosing to vacuum the target table or not. The only way to check that in 8.1 is to crank log_min_messages up to DEBUG2 and then trawl through the postmaster log looking for autovac messages. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Speeding up query, Joining 55mil and 43mil records.
Hello People,
I'm trying to solve a 'what i feel is a'
performance/configuration/query error on my side. I'm fairly new to
configuring PostgreSQL so, i might be completely wrong with my
configuration.
My database consists of 44 tables, about 20GB. Two of those tables are
'big/huge'. Table src.src_faktuur_verricht contains 43million records
(9GB) and table src.src_faktuur_verrsec contains 55million records
(6GB).
Below is the 'slow' query.
INSERT INTO rpt.rpt_verrichting
(verrichting_id
,verrichting_secid
,fout_status
,patientnr
,verrichtingsdatum
,locatie_code
,afdeling_code
,uitvoerder_code
,aanvrager_code
,verrichting_code
,dbcnr
,aantal_uitgevoerd
,kostenplaats_code
,vc_patientnr
,vc_verrichting_code
,vc_dbcnr
)
SELECT t1.id
, t0.secid
, t1.status
, t1.patientnr
, t1.datum
, t1.locatie
, t1.afdeling
, t1.uitvoerder
, t1.aanvrager
, t0.code
, t1.casenr
, t0.aantal
, t0.kostplaats
, null
, null
, null
FROM src.src_faktuur_verrsec t0 JOIN
src.src_faktuur_verricht t1 ON
t0.id = t1.id
WHERE substr(t0.code,1,2) not in ('14','15','16','17')
AND (substr(t0.correctie,4,1) '1' OR t0.correctie is null)
AND EXTRACT(YEAR from t1.datum) 2004;
Output from explain
Hash Join (cost=1328360.12..6167462.76 rows=7197568 width=118)
Hash Cond: (("outer".id)::text = ("inner".id)::text)
- Seq Scan on src_faktuur_verrsec t0 (cost=0.00..2773789.90
rows=40902852 width=52)
Filter: ((substr((code)::text, 1, 2) '14'::text) AND
(substr((code)::text, 1, 2) '15'::text) AND
(substr((code)::text, 1, 2) '16'::text) AND
(substr((code)::text, 1, 2) '17'::text) AND
((substr((correctie)::text, 4, 1) '1'::text) OR (correctie IS
NULL)))
- Hash (cost=1188102.97..1188102.97 rows=8942863 width=80)
- Bitmap Heap Scan on src_faktuur_verricht t1
(cost=62392.02..1188102.97 rows=8942863 width=80)
Recheck Cond: (date_part('year'::text, datum)
2004::double precision)
- Bitmap Index Scan on src_faktuur_verricht_idx1
(cost=0.00..62392.02 rows=8942863 width=0)
Index Cond: (date_part('year'::text, datum)
2004::double precision)
The db server runs PostgreSQL 8.1.4 on FreeBSD 6.1-Stable. 2GB of RAM.
It contains two SATA150 disks, one contains PostgreSQL and the rest of
the operating system and the other disk holds the pg_xlog directory.
Changed lines from my postgresql.conf file
shared_buffers = 8192
temp_buffers = 4096
work_mem = 65536
maintenance_work_mem = 1048576
max_fsm_pages = 4
fsync = off
wal_buffers = 64
effective_cache_size = 174848
The query above takes around 42 minutes.
However, i also have a wimpy desktop machine with 1gb ram. Windows with
MSSQL 2000 (default installation), same database structure, same
indexes, same query, etc and it takes 17 minutes. The big difference
makes me think that i've made an error with my PostgreSQL
configuration. I just can't seem to figure it out.
Could someone perhaps give me some pointers, advice?
Thanks in advance.
Nicky
Re: [PERFORM] Speeding up query, Joining 55mil and 43mil records.
Title: Message
Could
you post an explain analyze of the query? Just FYI, if you do an explain
analyze of the insert statement, it will actually do the insert. If you
don't want that just post an explain analyze of the select
part.
To me
it would be interesting to compare just the select parts of the query between
Postgres and MSSQL. That way you would know if your Postgres install is
slower at the query or slower at the insert.
-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
nickySent: Wednesday, June 21, 2006 8:47 AMTo:
pgsql-performance@postgresql.orgSubject: [PERFORM] Speeding up
query, Joining 55mil and 43mil records. Hello People, I'm trying to solve a 'what i feel is a'
performance/configuration/query error on my side. I'm fairly new to
configuring PostgreSQL so, i might be completely wrong with my configuration.
My database consists of 44 tables, about 20GB. Two of those tables are
'big/huge'. Table src.src_faktuur_verricht contains 43million records (9GB)
and table src.src_faktuur_verrsec contains 55million records (6GB).
Below is the 'slow' query. INSERT INTO
rpt.rpt_verrichting(verrichting_id,verrichting_secid,fout_status,patientnr,verrichtingsdatum,locatie_code,afdeling_code,uitvoerder_code,aanvrager_code,verrichting_code,dbcnr,aantal_uitgevoerd,kostenplaats_code,vc_patientnr,vc_verrichting_code,vc_dbcnr)SELECT
t1.id,
t0.secid,
t1.status,
t1.patientnr,
t1.datum,
t1.locatie,
t1.afdeling,
t1.uitvoerder,
t1.aanvrager,
t0.code,
t1.casenr,
t0.aantal,
t0.kostplaats,
null,
null, nullFROM
src.src_faktuur_verrsec t0 JOIN
src.src_faktuur_verricht t1 ON
t0.id = t1.idWHERE substr(t0.code,1,2) not in
('14','15','16','17')AND (substr(t0.correctie,4,1)
'1' OR t0.correctie is null)AND
EXTRACT(YEAR from t1.datum) 2004;Output from
explainHash Join (cost=1328360.12..6167462.76 rows=7197568
width=118) Hash Cond: (("outer".id)::text =
("inner".id)::text) - Seq Scan on src_faktuur_verrsec
t0 (cost=0.00..2773789.90 rows=40902852
width=52) Filter:
((substr((code)::text, 1, 2) '14'::text) AND (substr((code)::text, 1,
2) '15'::text) AND (substr((code)::text, 1, 2) '16'::text)
AND (substr((code)::text, 1, 2) '17'::text) AND
((substr((correctie)::text, 4, 1) '1'::text) OR (correctie IS
NULL))) - Hash (cost=1188102.97..1188102.97
rows=8942863 width=80)
- Bitmap Heap Scan on src_faktuur_verricht t1
(cost=62392.02..1188102.97 rows=8942863
width=80)
Recheck Cond: (date_part('year'::text, datum) 2004::double
precision)
- Bitmap Index Scan on src_faktuur_verricht_idx1
(cost=0.00..62392.02 rows=8942863
width=0)
Index Cond: (date_part('year'::text, datum) 2004::double
precision)The db server runs PostgreSQL 8.1.4 on FreeBSD
6.1-Stable. 2GB of RAM. It contains two SATA150 disks, one contains
PostgreSQL and the rest of the operating system and the other disk holds the
pg_xlog directory.Changed lines from my postgresql.conf
fileshared_buffers = 8192temp_buffers = 4096work_mem =
65536maintenance_work_mem = 1048576max_fsm_pages = 4fsync =
offwal_buffers = 64effective_cache_size = 174848The query
above takes around 42 minutes. However, i also have a wimpy desktop
machine with 1gb ram. Windows with MSSQL 2000 (default installation), same
database structure, same indexes, same query, etc and it takes 17 minutes. The
big difference makes me think that i've made an error with my PostgreSQL
configuration. I just can't seem to figure it out. Could someone
perhaps give me some pointers, advice?Thanks in advance.
Nicky
Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat
Our application is broken down quite well. We have two main writing processes writing to two separate sets of tables. No crossing over, nothign to prohibit the vacuuming in the nature which you describe. My longest transaction on the tables in question are typically quite short until of course they begin to bloat. On Wednesday 21 June 2006 11:08, Csaba Nagy wrote: So, it appears my autovacuum is just NOT working... I must have screwed something up, but I cannot see what. Is it possible that you have long running transactions ? If yes, VACUUM is simply not efficient, as it won't eliminate the dead space accumulated during the long running transaction. In that case VACUUM FULL won't help you either as it also can't eliminate dead space still visible by old transactions, but from what you say I guess you really stop everything before doing VACUUM FULL so you might as well stopped the culprit transaction too... that's why the VACUUM FULL worked (if my assumption is correct). To check if this is the case, look for idle in transaction in your process listing (ps auxww|grep idle in transaction). If you got one (or more) of that, you found your problem. If not, hopefully others will help you :-) Cheers, Csaba. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Speeding up query, Joining 55mil and 43mil records.
Hi Nicky,
I guess, you should try to upgrade the memory setting of PostgreSQL first.
work_mem = 65536
Is a bit low for such large joins.
Did you get a change to watch the directory
PGDATA/base/DBOID/pgsql_tmp to see how large the temporary file is
during this query. I'm sure that there is large file.
Anyhow, you can upgrade 'work_mem' to 100 which is 1 GB. Please note
that the parameter work_mem is per backend process. You will get
problems with multiple large queries at the same time.
You may move (link) the directory 'pgsql_tmp' to a very fast file system
if you still get large files in this directory.
You also can try to increase this settings:
checkpoint_segments = 256
checkpoint_timeout = 3600 # range 30-3600, in seconds
checkpoint_warning = 0 # 0 is off
Please read the PostgreSQL documentation about the drawbacks of this
setting as well as your setting 'fsync=off'.
Cheers
Sven.
nicky schrieb:
Hello People,
I'm trying to solve a 'what i feel is a' performance/configuration/query
error on my side. I'm fairly new to configuring PostgreSQL so, i might
be completely wrong with my configuration.
My database consists of 44 tables, about 20GB. Two of those tables are
'big/huge'. Table src.src_faktuur_verricht contains 43million records
(9GB) and table src.src_faktuur_verrsec contains 55million records (6GB).
Below is the 'slow' query.
INSERT INTO rpt.rpt_verrichting
(verrichting_id
,verrichting_secid
,fout_status
,patientnr
,verrichtingsdatum
,locatie_code
,afdeling_code
,uitvoerder_code
,aanvrager_code
,verrichting_code
,dbcnr
,aantal_uitgevoerd
,kostenplaats_code
,vc_patientnr
,vc_verrichting_code
,vc_dbcnr
)
SELECT t1.id
, t0.secid
, t1.status
, t1.patientnr
, t1.datum
, t1.locatie
, t1.afdeling
, t1.uitvoerder
, t1.aanvrager
, t0.code
, t1.casenr
, t0.aantal
, t0.kostplaats
, null
, null
, null
FROMsrc.src_faktuur_verrsec t0 JOIN
src.src_faktuur_verricht t1 ON
t0.id = t1.id
WHERE substr(t0.code,1,2) not in ('14','15','16','17')
AND (substr(t0.correctie,4,1) '1' OR t0.correctie is null)
AND EXTRACT(YEAR from t1.datum) 2004;
Output from explain
Hash Join (cost=1328360.12..6167462.76 rows=7197568 width=118)
Hash Cond: ((outer.id)::text = (inner.id)::text)
- Seq Scan on src_faktuur_verrsec t0 (cost=0.00..2773789.90
rows=40902852 width=52)
Filter: ((substr((code)::text, 1, 2) '14'::text) AND
(substr((code)::text, 1, 2) '15'::text) AND (substr((code)::text, 1,
2) '16'::text) AND (substr((code)::text, 1, 2) '17'::text) AND
((substr((correctie)::text, 4, 1) '1'::text) OR (correctie IS NULL)))
- Hash (cost=1188102.97..1188102.97 rows=8942863 width=80)
- Bitmap Heap Scan on src_faktuur_verricht t1
(cost=62392.02..1188102.97 rows=8942863 width=80)
Recheck Cond: (date_part('year'::text, datum)
2004::double precision)
- Bitmap Index Scan on src_faktuur_verricht_idx1
(cost=0.00..62392.02 rows=8942863 width=0)
Index Cond: (date_part('year'::text, datum)
2004::double precision)
The db server runs PostgreSQL 8.1.4 on FreeBSD 6.1-Stable. 2GB of RAM.
It contains two SATA150 disks, one contains PostgreSQL and the rest of
the operating system and the other disk holds the pg_xlog directory.
Changed lines from my postgresql.conf file
shared_buffers = 8192
temp_buffers = 4096
work_mem = 65536
maintenance_work_mem = 1048576
max_fsm_pages = 4
fsync = off
wal_buffers = 64
effective_cache_size = 174848
The query above takes around 42 minutes.
However, i also have a wimpy desktop machine with 1gb ram. Windows with
MSSQL 2000 (default installation), same database structure, same
indexes, same query, etc and it takes 17 minutes. The big difference
makes me think that i've made an error with my PostgreSQL configuration.
I just can't seem to figure it out.
Could someone perhaps give me some pointers, advice?
Thanks in advance.
Nicky
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat
On Wed, 2006-06-21 at 17:27, jody brownell wrote: Our application is broken down quite well. We have two main writing processes writing to two separate sets of tables. No crossing over, nothign to prohibit the vacuuming in the nature which you describe. It really doesn't matter what table are you touching, as it doesn't matter if you read or write either, what matters is how long ago was the last begin without commit or rollback. VACUUM will not touch tuples which were deleted after the oldest not yet finished transaction started, regardless if that transaction touched the vacuumed table or not in any way... My longest transaction on the tables in question are typically quite short until of course they begin to bloat. Well, your application might be completely well behaved and still your DBA (or your favorite DB access tool for that matter) can leave open transactions in an interactive session. It never hurts to check if you actually have idle in transaction sessions. It happened a few times to us, some of those were bad coding on ad-hoc tools written by us, others were badly behaved DB access tools opening a transaction immediately after connect and after each successful command, effectively leaving an open transaction when leaving it open while having lunch... So it might very well be that some interactive or ad hoc tools you're using to manage the DB are your problem. Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat
That is interesting. There is one thread keeping a transaction open it appears from ps postgres: app app xxx(42644) idle in transaction however, I created a test table t not configured in pg_autovacuum. I inserted a whack of rows and saw this. Jun 21 12:38:45 vanquish postgres[1525]: [8-1] LOG: autovacuum: processing database qradar Jun 21 12:38:45 vanquish postgres[1525]: [9-1] DEBUG: autovac: will VACUUM ANALYZE t Jun 21 12:38:45 vanquish postgres[1525]: [10-1] DEBUG: vacuuming public.t Jun 21 12:38:48 vanquish postgres[1525]: [11-1] DEBUG: t: removed 8104311 row versions in 51620 pages Jun 21 12:38:48 vanquish postgres[1525]: [11-2] DETAIL: CPU 0.93s/0.70u sec elapsed 1.70 sec. Jun 21 12:38:48 vanquish postgres[1525]: [12-1] DEBUG: t: found 8104311 removable, 0 nonremovable row versions in 51620 pages Jun 21 12:38:48 vanquish postgres[1525]: [12-2] DETAIL: 0 dead row versions cannot be removed yet. followed a later (after I did a similar insert op on target) by this Jun 21 13:00:46 vanquish postgres[3311]: [12-1] LOG: autovacuum: processing database qradar Jun 21 13:00:46 vanquish postgres[3311]: [13-1] DEBUG: autovac: will VACUUM target Jun 21 13:00:46 vanquish postgres[3311]: [14-1] DEBUG: vacuuming public.target Jun 21 13:01:51 vanquish postgres[3311]: [15-1] DEBUG: index target_pkey now contains 1296817 row versions in 25116 pages Jun 21 13:01:51 vanquish postgres[3311]: [15-2] DETAIL: 5645230 index row versions were removed. Jun 21 13:01:51 vanquish postgres[3311]: [15-3] ^I116 index pages have been deleted, 60 are currently reusable. Jun 21 13:01:51 vanquish postgres[3311]: [15-4] ^ICPU 1.29s/7.44u sec elapsed 48.65 sec. Jun 21 13:02:19 vanquish postgres[3311]: [16-1] DEBUG: index target_network_key now contains 1296817 row versions in 19849 pages Jun 21 13:02:19 vanquish postgres[3311]: [16-2] DETAIL: 5645230 index row versions were removed. Jun 21 13:02:19 vanquish postgres[3311]: [16-3] ^I32 index pages have been deleted, 0 are currently reusable. Jun 21 13:02:19 vanquish postgres[3311]: [16-4] ^ICPU 0.89s/6.61u sec elapsed 27.77 sec. Jun 21 13:02:47 vanquish postgres[3311]: [17-1] DEBUG: index target_network_details_id_idx now contains 1296817 row versions in 23935 pages Jun 21 13:02:47 vanquish postgres[3311]: [17-2] DETAIL: 5645230 index row versions were removed. Jun 21 13:02:47 vanquish postgres[3311]: [17-3] ^I17814 index pages have been deleted, 0 are currently reusable. Jun 21 13:02:47 vanquish postgres[3311]: [17-4] ^ICPU 0.93s/7.52u sec elapsed 27.36 sec. Jun 21 13:03:23 vanquish postgres[3311]: [18-1] DEBUG: index target_tulu_idx now contains 1296817 row versions in 24341 pages Jun 21 13:03:23 vanquish postgres[3311]: [18-2] DETAIL: 5645230 index row versions were removed. Jun 21 13:03:23 vanquish postgres[3311]: [18-3] ^I18495 index pages have been deleted, 0 are currently reusable. Jun 21 13:03:23 vanquish postgres[3311]: [18-4] ^ICPU 1.37s/5.38u sec elapsed 36.95 sec. Jun 21 13:04:04 vanquish postgres[3311]: [19-1] DEBUG: target: removed 5645231 row versions in 106508 pages Jun 21 13:04:04 vanquish postgres[3311]: [19-2] DETAIL: CPU 3.37s/1.23u sec elapsed 40.63 sec. Jun 21 13:04:04 vanquish postgres[3311]: [20-1] DEBUG: target: found 5645231 removable, 1296817 nonremovable row versions in 114701 pages Jun 21 13:04:04 vanquish postgres[3311]: [20-2] DETAIL: 0 dead row versions cannot be removed yet. this was with the Idle in transaction though. Ah HA! Wondering, my autovacuum naptime is 60 seconds, that is also the interval which I wake up and begin persistence. Wondering if I am simply locking autovacuum out of the tables b/c they are on a similar timeline. I will try a 30 second naptime, if this is it, that should increase the likely hood of falling on the right side of the TX more often. make sense? On Wednesday 21 June 2006 12:42, Csaba Nagy wrote: On Wed, 2006-06-21 at 17:27, jody brownell wrote: Our application is broken down quite well. We have two main writing processes writing to two separate sets of tables. No crossing over, nothign to prohibit the vacuuming in the nature which you describe. It really doesn't matter what table are you touching, as it doesn't matter if you read or write either, what matters is how long ago was the last begin without commit or rollback. VACUUM will not touch tuples which were deleted after the oldest not yet finished transaction started, regardless if that transaction touched the vacuumed table or not in any way... My longest transaction on the tables in question are typically quite short until of course they begin to bloat. Well, your application might be completely well behaved and still your DBA (or your favorite DB access tool for that matter) can leave open transactions in an interactive session. It never hurts to check if you actually have idle in transaction sessions. It happened a few times to us, some of those were bad
Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat
Opps - that was confusing. The idle in transaction was from one box and the autovacuum was from another. So, one question was answered, auto vacuum is running and selecting the tables but apparently not at the same time as my app probably due to this idle in transaction. I will track it down and see what the difference is. thanks On Wednesday 21 June 2006 13:21, jody brownell wrote: That is interesting. There is one thread keeping a transaction open it appears from ps postgres: app app xxx(42644) idle in transaction however, I created a test table t not configured in pg_autovacuum. I inserted a whack of rows and saw this. Jun 21 12:38:45 vanquish postgres[1525]: [8-1] LOG: autovacuum: processing database qradar Jun 21 12:38:45 vanquish postgres[1525]: [9-1] DEBUG: autovac: will VACUUM ANALYZE t Jun 21 12:38:45 vanquish postgres[1525]: [10-1] DEBUG: vacuuming public.t Jun 21 12:38:48 vanquish postgres[1525]: [11-1] DEBUG: t: removed 8104311 row versions in 51620 pages Jun 21 12:38:48 vanquish postgres[1525]: [11-2] DETAIL: CPU 0.93s/0.70u sec elapsed 1.70 sec. Jun 21 12:38:48 vanquish postgres[1525]: [12-1] DEBUG: t: found 8104311 removable, 0 nonremovable row versions in 51620 pages Jun 21 12:38:48 vanquish postgres[1525]: [12-2] DETAIL: 0 dead row versions cannot be removed yet. followed a later (after I did a similar insert op on target) by this Jun 21 13:00:46 vanquish postgres[3311]: [12-1] LOG: autovacuum: processing database qradar Jun 21 13:00:46 vanquish postgres[3311]: [13-1] DEBUG: autovac: will VACUUM target Jun 21 13:00:46 vanquish postgres[3311]: [14-1] DEBUG: vacuuming public.target Jun 21 13:01:51 vanquish postgres[3311]: [15-1] DEBUG: index target_pkey now contains 1296817 row versions in 25116 pages Jun 21 13:01:51 vanquish postgres[3311]: [15-2] DETAIL: 5645230 index row versions were removed. Jun 21 13:01:51 vanquish postgres[3311]: [15-3] ^I116 index pages have been deleted, 60 are currently reusable. Jun 21 13:01:51 vanquish postgres[3311]: [15-4] ^ICPU 1.29s/7.44u sec elapsed 48.65 sec. Jun 21 13:02:19 vanquish postgres[3311]: [16-1] DEBUG: index target_network_key now contains 1296817 row versions in 19849 pages Jun 21 13:02:19 vanquish postgres[3311]: [16-2] DETAIL: 5645230 index row versions were removed. Jun 21 13:02:19 vanquish postgres[3311]: [16-3] ^I32 index pages have been deleted, 0 are currently reusable. Jun 21 13:02:19 vanquish postgres[3311]: [16-4] ^ICPU 0.89s/6.61u sec elapsed 27.77 sec. Jun 21 13:02:47 vanquish postgres[3311]: [17-1] DEBUG: index target_network_details_id_idx now contains 1296817 row versions in 23935 pages Jun 21 13:02:47 vanquish postgres[3311]: [17-2] DETAIL: 5645230 index row versions were removed. Jun 21 13:02:47 vanquish postgres[3311]: [17-3] ^I17814 index pages have been deleted, 0 are currently reusable. Jun 21 13:02:47 vanquish postgres[3311]: [17-4] ^ICPU 0.93s/7.52u sec elapsed 27.36 sec. Jun 21 13:03:23 vanquish postgres[3311]: [18-1] DEBUG: index target_tulu_idx now contains 1296817 row versions in 24341 pages Jun 21 13:03:23 vanquish postgres[3311]: [18-2] DETAIL: 5645230 index row versions were removed. Jun 21 13:03:23 vanquish postgres[3311]: [18-3] ^I18495 index pages have been deleted, 0 are currently reusable. Jun 21 13:03:23 vanquish postgres[3311]: [18-4] ^ICPU 1.37s/5.38u sec elapsed 36.95 sec. Jun 21 13:04:04 vanquish postgres[3311]: [19-1] DEBUG: target: removed 5645231 row versions in 106508 pages Jun 21 13:04:04 vanquish postgres[3311]: [19-2] DETAIL: CPU 3.37s/1.23u sec elapsed 40.63 sec. Jun 21 13:04:04 vanquish postgres[3311]: [20-1] DEBUG: target: found 5645231 removable, 1296817 nonremovable row versions in 114701 pages Jun 21 13:04:04 vanquish postgres[3311]: [20-2] DETAIL: 0 dead row versions cannot be removed yet. this was with the Idle in transaction though. Ah HA! Wondering, my autovacuum naptime is 60 seconds, that is also the interval which I wake up and begin persistence. Wondering if I am simply locking autovacuum out of the tables b/c they are on a similar timeline. I will try a 30 second naptime, if this is it, that should increase the likely hood of falling on the right side of the TX more often. make sense? On Wednesday 21 June 2006 12:42, Csaba Nagy wrote: On Wed, 2006-06-21 at 17:27, jody brownell wrote: Our application is broken down quite well. We have two main writing processes writing to two separate sets of tables. No crossing over, nothign to prohibit the vacuuming in the nature which you describe. It really doesn't matter what table are you touching, as it doesn't matter if you read or write either, what matters is how long ago was the last begin without commit or rollback. VACUUM will not touch tuples which were deleted after the oldest not yet finished transaction started, regardless if that
Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat
On Wed, 2006-06-21 at 18:21, jody brownell wrote: That is interesting. There is one thread keeping a transaction open it appears from ps postgres: app app xxx(42644) idle in transaction That shouldn't be a problem on itself, idle in transaction happens all the time between 2 commands in the same transaction... you only have a problem if you see the same PID always idle, that means somebody left an open transaction and left for lunch. [snip] this was with the Idle in transaction though. This probably means you don't have long running transactions currently. However, if you happen to have just one such long transaction, the dead space accumulates and normal vacuum will not be able to clean that anymore. But I guess if you didn't find one now then you should take a look at Tom's suggestion and bump up debug level to see if autovacuum picks your table at all... Ah HA! Wondering, my autovacuum naptime is 60 seconds, that is also the interval which I wake up and begin persistence. Wondering if I am simply locking autovacuum out of the tables b/c they are on a similar timeline. I will try a 30 second naptime, if this is it, that should increase the likely hood of falling on the right side of the TX more often. make sense? I don't think that's your problem... vacuum wouldn't be locked out by any activity which doesn't lock exclusively the table (and I guess you're not doing that). If your persistence finishes quickly then that's not the problem. Oh, just occured to me... in order to use autovacuum you also need to enable the statistics collector on row level: stats_start_collector = on stats_row_level = on See also: http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM This was not mentioned in the settings in your original post, so I guess you didn't touch that, and I think they are disabled by default. If this is disabled, you should enable it and pg_ctl reload , that should fix the problem. Cheers, Csaba. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat
that is exactly what I am seeing, one process, no change, always in idle while the others are constantly changing their state. looks like someone opened a tx then is blocking on a queue lock or something. dang. On Wednesday 21 June 2006 13:36, Csaba Nagy wrote: On Wed, 2006-06-21 at 18:21, jody brownell wrote: That is interesting. There is one thread keeping a transaction open it appears from ps postgres: app app xxx(42644) idle in transaction That shouldn't be a problem on itself, idle in transaction happens all the time between 2 commands in the same transaction... you only have a problem if you see the same PID always idle, that means somebody left an open transaction and left for lunch. [snip] this was with the Idle in transaction though. This probably means you don't have long running transactions currently. However, if you happen to have just one such long transaction, the dead space accumulates and normal vacuum will not be able to clean that anymore. But I guess if you didn't find one now then you should take a look at Tom's suggestion and bump up debug level to see if autovacuum picks your table at all... Ah HA! Wondering, my autovacuum naptime is 60 seconds, that is also the interval which I wake up and begin persistence. Wondering if I am simply locking autovacuum out of the tables b/c they are on a similar timeline. I will try a 30 second naptime, if this is it, that should increase the likely hood of falling on the right side of the TX more often. make sense? I don't think that's your problem... vacuum wouldn't be locked out by any activity which doesn't lock exclusively the table (and I guess you're not doing that). If your persistence finishes quickly then that's not the problem. Oh, just occured to me... in order to use autovacuum you also need to enable the statistics collector on row level: stats_start_collector = on stats_row_level = on See also: http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM This was not mentioned in the settings in your original post, so I guess you didn't touch that, and I think they are disabled by default. If this is disabled, you should enable it and pg_ctl reload , that should fix the problem. Cheers, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat
block and row are always configured on - they are my friend :) thanks On Wednesday 21 June 2006 13:44, Csaba Nagy wrote: On Wed, 2006-06-21 at 18:39, jody brownell wrote: that is exactly what I am seeing, one process, no change, always in idle while the others are constantly changing their state. looks like someone opened a tx then is blocking on a queue lock or something. dang. Don't forget to check the statistics collector settings (see below), if that is not correct then autovacuum is indeed not working correctly... I should have put that on the beginning of the mail so you won't overlook it ;-) Oh, just occured to me... in order to use autovacuum you also need to enable the statistics collector on row level: stats_start_collector = on stats_row_level = on See also: http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM This was not mentioned in the settings in your original post, so I guess you didn't touch that, and I think they are disabled by default. If this is disabled, you should enable it and pg_ctl reload , that should fix the problem. Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Speeding up query, Joining 55mil and 43mil records.
On Wed, 2006-06-21 at 08:47, nicky wrote: Hello People, SNIPPAGE The query above takes around 42 minutes. However, i also have a wimpy desktop machine with 1gb ram. Windows with MSSQL 2000 (default installation), same database structure, same indexes, same query, etc and it takes 17 minutes. The big difference makes me think that i've made an error with my PostgreSQL configuration. I just can't seem to figure it out. What is the difference between the two plans (i.e. explain on both boxes and compare) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Performance of DOMAINs
Howdy, Didn't see anything in the archives, so I thought I'd ask: has anyone done any work to gauge the performance penalty of using DOMAINs? I'm thinking of something like Elein's email DOMAIN: http://www.varlena.com/GeneralBits/ I figured that most simple domains that have a constraint check are no faster or slower than tables with constraints that validate a particular column. Is that the case? But I'm also interested in how Elein made the email domain case- insensitive, since I'd like to have/create a truly case-insensitive text type (ITEXT anyone?). The functions for the operator class there were mainly written in SQL, and if it adds a significant overhead, I'm not sure it'd be a good idea to use that approach for a case- insensitive text type, since I use it quite a lot in my apps, and often do LIKE queries against text data. Thoughts? Many TIA, David ---(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] Big array speed issues
On Wed, Jun 21, 2006 at 09:29:03AM +0200, Merkel Marcel (CR/AEM4) wrote: Von: Merlin Moncure [mailto:[EMAIL PROTECTED] An: Merkel Marcel (CR/AEM4) Cc: pgsql-performance@postgresql.org Betreff: Re: [PERFORM] Big array speed issues On 6/20/06, Merkel Marcel (CR/AEM4) [EMAIL PROTECTED] wrote: I use libpqxx to access the database. This might be another bottleneck, but I assume my query and table setup is the bigger bottleneck. Would it make sense to fetch the whole array ? (Select map from table where ... and parse the array manually) have you tried similar approach without using arrays? Merlin Not yet. I would first like to know what is the time consuming part and what is a work around. If you are sure individual columns for every entry of the array solve the issue I will joyfully implement it. The downsize of this approch is that the array dimensions are not always the same in my scenario. But I have a workaround in mind for this issue. Before mucking about with the code, I'd absolutely try 8.1. I've generally seen it double the performance of 7.4. Also, output from EXPLAIN ANALYZE would make it a lot easier to figure out what the issue is, and it would be good to try this without selecting any of the arrays. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Speeding up query, Joining 55mil and 43mil records.
On Wed, Jun 21, 2006 at 03:47:19PM +0200, nicky wrote:
WHERE substr(t0.code,1,2) not in ('14','15','16','17')
AND (substr(t0.correctie,4,1) '1' OR t0.correctie is null)
AND EXTRACT(YEAR from t1.datum) 2004;
How much data do you expect to be getting back from that where clause?
Unless you plan on inserting most of the table, some well-placed indexes
would probably help, and fixing the datum portion might as well
(depending on how far back the data goes). Specifically:
CREATE INDEX t0_code_partial ON t0(substr(code,1,2));
(yeah, I know t0 is an alias, but I already snipped the table name)
and
AND t1.datum = '1/1/2005'
(might need to cast that to a date or whatever).
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(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 tuning autovacuum - seeing lots of relation bloat
On Wed, Jun 21, 2006 at 10:52:42AM -0300, jody brownell wrote: A db wide vacuum full outputs this at the end. INFO: free space map contains 32848 pages in 159 relations DETAIL: A total of 24192 page slots are in use (including overhead). 24192 page slots are required to track all free space. Current limits are: 4024000 page slots, 2000 relations, using 23705 KB. FWIW, the tail end of a db-wide vacuum FULL doesn't provide useful info about FSM utilization, because it just made everything as compact as possible. My suspicion is that it's taking too long for autovac to get around to this database/table. Dropping the sleep time might help. I see that this table is vacuumed with a delay setting of 0, but if there are other tables with a high delay that could pose a problem. Getting detailed output of what autovac is actually doing as Tom suggested would be a good idea. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance of DOMAINs
On Wed, Jun 21, 2006 at 11:26:16AM -0700, David Wheeler wrote: Howdy, Didn't see anything in the archives, so I thought I'd ask: has anyone done any work to gauge the performance penalty of using DOMAINs? I'm thinking of something like Elein's email DOMAIN: http://www.varlena.com/GeneralBits/ I figured that most simple domains that have a constraint check are no faster or slower than tables with constraints that validate a particular column. Is that the case? Probably. Only thing that might pose a difference is if you're doing a lot of manipulating of the domain that didn't involve table access; presumably PostgreSQL will perform the checks every time you cast something to a domain. But I'm also interested in how Elein made the email domain case- insensitive, since I'd like to have/create a truly case-insensitive text type (ITEXT anyone?). The functions for the operator class there http://gborg.postgresql.org/project/citext/projdisplay.php were mainly written in SQL, and if it adds a significant overhead, I'm not sure it'd be a good idea to use that approach for a case- insensitive text type, since I use it quite a lot in my apps, and often do LIKE queries against text data. Thoughts? Many TIA, David ---(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 -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat
On Wed, Jun 21, 2006 at 01:21:05PM -0300, jody brownell wrote: Jun 21 13:04:04 vanquish postgres[3311]: [19-1] DEBUG: target: removed 5645231 row versions in 106508 pages Jun 21 13:04:04 vanquish postgres[3311]: [19-2] DETAIL: CPU 3.37s/1.23u sec elapsed 40.63 sec. Jun 21 13:04:04 vanquish postgres[3311]: [20-1] DEBUG: target: found 5645231 removable, 1296817 nonremovable row versions in 114701 pages Jun 21 13:04:04 vanquish postgres[3311]: [20-2] DETAIL: 0 dead row versions cannot be removed yet. So the table contained 5.6M dead rows and 1.3M live rows. I think you should forget about having autovacuum keep this table in-check and add manual vacuum commands to your code. Autovac is intended to deal with 99% of use cases; this is pretty clearly in the 1% it can't handle. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat
OK this was over a 12 - 16 hour period of not having anything done with it though right? I am assuming if autovacuum were active through out that period, we would be somewhat better off ...is that not accurate? On Wednesday 21 June 2006 16:38, Jim C. Nasby wrote: 5 ---(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] Help tuning autovacuum - seeing lots of relationbloat
BTW, in production with a similar load - autovacuum with default out of the box settings seems to work quite well I double checked this earlier today. On Wednesday 21 June 2006 16:38, Jim C. Nasby wrote: 5 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Big array speed issues
Not yet. I would first like to know what is the time consuming part and what is a work around. If you are sure individual columns for every entry of the array solve the issue I will joyfully implement it. The downsize of this approch is that the array dimensions are not always the same in my scenario. But I have a workaround in mind for this issue. The first thing I would try would be to completely normalize te file, aka create table data as ( id int, t timestamp, map_x int, map_y int, value float ); and go with denormalized approach only when this doesn't work for some reason. merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance of DOMAINs
David Wheeler [EMAIL PROTECTED] writes: Didn't see anything in the archives, so I thought I'd ask: has anyone done any work to gauge the performance penalty of using DOMAINs? There are some reports in the archives of particular usage patterns where they pretty much suck, because GetDomainConstraints() searches pg_constraint every time it's called. We do what we can to avoid calling that multiple times per query, but for something like a simple INSERT ... VALUES into a domain column, the setup overhead is still bad. I've been intending to try to fix things so that the search result can be cached by typcache.c, but not gotten round to it. (The hard part, if anyone wants to tackle it, is figuring out a way to clear the cache entry when needed.) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Quick question about top...
I have a really stupid question about top, what exactly is iowait CPU time?Alex
Re: [PERFORM] Quick question about top...
On Wed, Jun 21, 2006 at 04:46:15PM -0400, Alex Turner wrote: I have a really stupid question about top, what exactly is iowait CPU time? Time while the CPU is idle, but at least one I/O request is outstanding. In other words, if you're at 100% I/O-wait, you're heavily I/O-bound and your processor is bored to death. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Tuning New Server (slow function)
We just purchased a new Dell PowerEdge 2800 (dual xeon, 8GB RAM, raid 4, RHEL, postgres 8.1) and ported our old database over to it (single cpu, 2GB RAM, no raid, postgres 7.4). Our apps perform great on it, however some queries are super slow. One function in particular, which used to take 15-30 minutes on the old server, has been running now for over 12 hours: BEGIN TRUNCATE stock.datacount; FOR rec IN SELECT itemID, item, hexValue FROM stock.activeitem LOOP histdate := (SELECT updatedate FROM stock.historical s WHERE s.itemID=rec.itemID ORDER BY updatedate DESC LIMIT 1); IF histdate IS NOT NULL THEN funddate := (SELECT updatedate FROM stock.funddata s WHERE s.itemID=rec.itemID); techdate := (SELECT updatedate FROM stock.techsignals s WHERE s.itemID=rec.itemID); IF (histdate funddate) OR (histdate techdate) OR (funddate IS NULL) OR (techdate IS NULL) THEN counter := counter + 1; outrec.itemID := rec.itemID; outrec.item := rec.item; outrec.hexvalue := rec.hexvalue; RETURN NEXT outrec; END IF; END IF; END LOOP; INSERT INTO stock.datacount (itemcount) VALUES (counter); COPY stock.datacount TO ''/tmp/datacount''; RETURN; END; note: stock.activeitem contains about 75000 rows top shows: CPU states: cpuusernice systemirq softirq iowaitidle total5.8%0.6% 31.2% 0.0% 0.0%0.5% 61.6% Mem: 8152592k av, 8143012k used,9580k free, 0k shrd, 179888k buff 6342296k actv, 1206340k in_d, 137916k in_c Swap: 8385760k av, 259780k used, 8125980k free 7668624k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 17027 postgres 25 0 566M 561M 560M R24.9 7.0 924:34 1 postmaster I've likely set some parameter(s) to the wrong values, but I don't know which one(s). Here are my relevant postgresql.conf settings: shared_buffers = 7 work_mem = 9192 maintenance_work_mem = 131072 max_fsm_pages = 7 fsync = off (temporarily, will be turned back on) checkpoint_segments = 64 checkpoint_timeout = 1800 effective_cache_size = 7 [EMAIL PROTECTED] root]# cat /proc/sys/kernel/shmmax 66000 We want to put this into production soon, but this is a showstopper. Can anyone help me out with this? Thanks Ron St.Pierre ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat
On Wed, Jun 21, 2006 at 04:41:45PM -0300, jody brownell wrote: BTW, in production with a similar load - autovacuum with default out of the box settings seems to work quite well I double checked this earlier today. So what's different between production and the machine with the problem? The issue with autovac is that it will only vacuum one table at a time, so if it's off vacuuming some other table for a long period of time it won't be touching this table, which will be a problem. Now, if that's actually what's happening... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Tuning New Server (slow function)
On Wed, Jun 21, 2006 at 02:27:41PM -0700, Ron St-Pierre wrote: We just purchased a new Dell PowerEdge 2800 (dual xeon, 8GB RAM, raid 4, RHEL, postgres 8.1) and ported our old database over to it (single cpu, RAID *4*? If you do any kind of updating at all, you're likely to be real unhappy with that... 2GB RAM, no raid, postgres 7.4). Our apps perform great on it, however some queries are super slow. One function in particular, which used to take 15-30 minutes on the old server, has been running now for over 12 hours: BEGIN TRUNCATE stock.datacount; FOR rec IN SELECT itemID, item, hexValue FROM stock.activeitem LOOP histdate := (SELECT updatedate FROM stock.historical s WHERE s.itemID=rec.itemID ORDER BY updatedate DESC LIMIT 1); IF histdate IS NOT NULL THEN funddate := (SELECT updatedate FROM stock.funddata s WHERE s.itemID=rec.itemID); techdate := (SELECT updatedate FROM stock.techsignals s WHERE s.itemID=rec.itemID); IF (histdate funddate) OR (histdate techdate) OR (funddate IS NULL) OR (techdate IS NULL) THEN counter := counter + 1; outrec.itemID := rec.itemID; outrec.item := rec.item; outrec.hexvalue := rec.hexvalue; RETURN NEXT outrec; END IF; END IF; END LOOP; INSERT INTO stock.datacount (itemcount) VALUES (counter); COPY stock.datacount TO ''/tmp/datacount''; RETURN; END; note: stock.activeitem contains about 75000 rows Getting EXPLAIN ANALYZE from the queries would be good. Adding debug output via NOTICE to see how long each step is taking would be a good idea, too. Of course, even better would be to do away with the cursor... top shows: CPU states: cpuusernice systemirq softirq iowaitidle total5.8%0.6% 31.2% 0.0% 0.0%0.5% 61.6% Mem: 8152592k av, 8143012k used,9580k free, 0k shrd, 179888k buff The high system % (if I'm reading this correctly) makes me wonder if this is some kind of locking issue. 6342296k actv, 1206340k in_d, 137916k in_c Swap: 8385760k av, 259780k used, 8125980k free 7668624k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 17027 postgres 25 0 566M 561M 560M R24.9 7.0 924:34 1 postmaster I've likely set some parameter(s) to the wrong values, but I don't know which one(s). Here are my relevant postgresql.conf settings: shared_buffers = 7 work_mem = 9192 maintenance_work_mem = 131072 max_fsm_pages = 7 fsync = off (temporarily, will be turned back on) checkpoint_segments = 64 checkpoint_timeout = 1800 effective_cache_size = 7 [EMAIL PROTECTED] root]# cat /proc/sys/kernel/shmmax 66000 We want to put this into production soon, but this is a showstopper. Can anyone help me out with this? Thanks Ron St.Pierre ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Tuning New Server (slow function)
Jim C. Nasby wrote: On Wed, Jun 21, 2006 at 02:27:41PM -0700, Ron St-Pierre wrote: We just purchased a new Dell PowerEdge 2800 (dual xeon, 8GB RAM, raid 4, RHEL, postgres 8.1) and ported our old database over to it (single cpu, RAID *4*? oops, raid 5 (but we are getting good io throughput...) If you do any kind of updating at all, you're likely to be real unhappy with that... 2GB RAM, no raid, postgres 7.4). Our apps perform great on it, however some queries are super slow. One function in particular, which used to take 15-30 minutes on the old server, has been running now for over 12 hours: BEGIN TRUNCATE stock.datacount; FOR rec IN SELECT itemID, item, hexValue FROM stock.activeitem LOOP histdate := (SELECT updatedate FROM stock.historical s WHERE s.itemID=rec.itemID ORDER BY updatedate DESC LIMIT 1); IF histdate IS NOT NULL THEN funddate := (SELECT updatedate FROM stock.funddata s WHERE s.itemID=rec.itemID); techdate := (SELECT updatedate FROM stock.techsignals s WHERE s.itemID=rec.itemID); IF (histdate funddate) OR (histdate techdate) OR (funddate IS NULL) OR (techdate IS NULL) THEN counter := counter + 1; outrec.itemID := rec.itemID; outrec.item := rec.item; outrec.hexvalue := rec.hexvalue; RETURN NEXT outrec; END IF; END IF; END LOOP; INSERT INTO stock.datacount (itemcount) VALUES (counter); COPY stock.datacount TO ''/tmp/datacount''; RETURN; END; note: stock.activeitem contains about 75000 rows Getting EXPLAIN ANALYZE from the queries would be good. Adding debug output via NOTICE to see how long each step is taking would be a good idea, too. I set client_min_messages = debug2, log_min_messages = debug2 and log_statement = 'all' and am running the query with EXPLAIN ANALYZE. I don't know how long it will take until something useful returns, but I will let it run for a while. Of course, even better would be to do away with the cursor... How would I rewrite it to do away with the cursor? top shows: CPU states: cpuusernice systemirq softirq iowaitidle total5.8%0.6% 31.2% 0.0% 0.0%0.5% 61.6% Mem: 8152592k av, 8143012k used,9580k free, 0k shrd, 179888k buff The high system % (if I'm reading this correctly) makes me wonder if this is some kind of locking issue. But it's the only postgres process running. 6342296k actv, 1206340k in_d, 137916k in_c Swap: 8385760k av, 259780k used, 8125980k free 7668624k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 17027 postgres 25 0 566M 561M 560M R24.9 7.0 924:34 1 postmaster I've likely set some parameter(s) to the wrong values, but I don't know which one(s). Here are my relevant postgresql.conf settings: shared_buffers = 7 work_mem = 9192 maintenance_work_mem = 131072 max_fsm_pages = 7 fsync = off (temporarily, will be turned back on) checkpoint_segments = 64 checkpoint_timeout = 1800 effective_cache_size = 7 [EMAIL PROTECTED] root]# cat /proc/sys/kernel/shmmax 66000 We want to put this into production soon, but this is a showstopper. Can anyone help me out with this? Thanks Ron St.Pierre ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat
Well, for one we did introduce a TX leak which was preventing autovac from running. I guess that was _the_ issue. I have since fixed it and an now testing looks much better, nothing concerning (fingers crossed until morning :)). debug logs are full of vac/anal of the tables... so, for now I am back on track moving forward... Now that auto vac is actually running, the box is feeling slightly more sluggish. BTW - As soon as we deliver to QA, I will post the test case for the memory leak I was seeing the other day. (I have not forgotten, I am just swamped) Thanks for the help all. Much appreciated. Cheers. On Wednesday 21 June 2006 19:11, Jim C. Nasby wrote: On Wed, Jun 21, 2006 at 04:41:45PM -0300, jody brownell wrote: BTW, in production with a similar load - autovacuum with default out of the box settings seems to work quite well I double checked this earlier today. So what's different between production and the machine with the problem? The issue with autovac is that it will only vacuum one table at a time, so if it's off vacuuming some other table for a long period of time it won't be touching this table, which will be a problem. Now, if that's actually what's happening... ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance of DOMAINs
David, But I'm also interested in how Elein made the email domain case- insensitive, since I'd like to have/create a truly case-insensitive text type (ITEXT anyone?). The functions for the operator class there were mainly written in SQL, and if it adds a significant overhead, I'm not sure it'd be a good idea to use that approach for a case- insensitive text type, since I use it quite a lot in my apps, and often do LIKE queries against text data. Thoughts? Well, current case-insensitivity hacks definitely aren't compatible with LIKE as far as begins with indexes are concerned. Of course, floating LIKEs (%value%) are going to suck no matter what data type you're using. I created an operator for CI equality ... =~ ... which performs well on indexed columns. But it doesn't do begins with. ITEXT is a TODO, but there are reasons why it's harder than it looks. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance of DOMAINs
since I'd like to have/create a truly case-insensitive text type (ITEXT anyone?). I haven't seen it mentioned in this thread yet, but have you looked at citext? http://gborg.postgresql.org/project/citext/projdisplay.php I don't have any experience with it, but perhaps it can do what you're looking for. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Regarding ERROR: fmgr_info: function 2720768: cache lookup failed
Hello, I am getting following error while inserting a row into the "abc" table: ERROR: fmgr_info: function 2720768: cache lookup failed Table "abc" has one trigger called "abct" Definition is as follows: BEGIN; LOCK TABLE abc IN SHARE ROW EXCLUSIVE MODE; create TRIGGER abct AFTER INSERT OR DELETE on abc FOR EACH ROW EXECUTE PROCEDURE abc_function(); COMMIT; abc_function() updates entry from the "xyz" table for every insert and delete operations on table "abc". "xyz" table maintains the count of total number of rows in table "abc" Currently "abc" table contains 190 rows. And same count is available in table "xyz". But now I am not able to insert any records into the "abc" table because of above mentioned error. Please provide me some help regarding this. Thanks, Soni
