Re: [HACKERS] Avoid memory leaks during ANALYZE's compute_index_stats() ?
Hi Tom, thanks for brilliant analysis - now we know how to avoid the problem. As a side note: from the user's point of view it would be really nice to know that the error was caused by auto-ANALYZE - at least on 8.2 it's not that obvious from the server log. It was the first message with given backend PID so it seemed to me as it's problem during backend startup - we have log_connections to on... Thanks, Kuba Dne 9.11.2010 2:04, Tom Lane napsal(a): I looked into the out-of-memory problem reported by Jakub Ouhrabka here: http://archives.postgresql.org/pgsql-general/2010-11/msg00353.php It's pretty simple to reproduce, even in HEAD; what you need is an index expression that computes a bulky intermediate result. His example is md5(array_to_string(f1, ''::text)) where f1 is a bytea array occupying typically 15kB per row. Even though the final result of md5() is only 32 bytes, evaluation of this expression will eat about 15kB for the detoasted value of f1, roughly double that for the results of the per-element output function calls done inside array_to_string, and another 30k for the final result string of array_to_string. And *none of that gets freed* until compute_index_stats() is all done. In my testing, with the default stats target of 100, this gets repeated for 30k sample rows, requiring something in excess of 2GB in transient space. Jakub was using stats target 500 so it'd be closer to 10GB for him. AFAICS the only practical fix for this is to have the inner loop of compute_index_stats() copy each index expression value out of the per-tuple memory context and into the per-index Analyze Index context. That would allow it to reset the per-tuple memory context after each FormIndexDatum call and thus clean up whatever intermediate result trash the evaluation left behind. The extra copying is a bit annoying, since it would add cycles while accomplishing nothing useful for index expressions with no intermediate results, but I'm thinking this is a must-fix. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with 8.4 stats collector high load
You might want to try setting log_autovacuum_min_duration=0 in the postgresql.conf Thanks, tried it. There is nothing in the log - the actual vacuum/analyze commands are not run (as there is no query activity). I suspect that autovacuum is checking each database if it should run - and decides not to run. See the randomly catch process in ps output/pg_stat_activity mentioned in earlier mail. I suspect that this checking generates the load. Is it possible? With this many databases and this high of a statistics target I've changed the default_statistics_target back to its default (100). No change, still stats collector generates load. You're really pushing what you can do in a VM with this many databases of this size. Yes, it's a VM but on our dedicated hardware - there are few other containers running but they are not generating any load. What's puzzling me is that there is no database activity (queries, connections) and stats collector is still eating CPU. Kuba Dne 16.2.2010 8:29, Greg Smith napsal(a): Jakub Ouhrabka wrote: I've found similar reports but with older versions of postgres: http://old.nabble.com/100--of-CPU-utilization-postgres-process-tt27302021.html Those all looked like a FreeBSD issue, doubt it's related to yours. The pgstat.stat is ~20MB. There are 650 databases, 140GB total. default_statistics_target = 1000 The system is running Proxmox linux distribution. PostgreSQL is in OpenVZ container. With this many databases and this high of a statistics target, running in a VM, suspecting autovacuum seems reasonable. You might want to try setting log_autovacuum_min_duration=0 in the postgresql.conf, restarting or signalling (pg_ctl reload) the server, and watching just what it's doing. You might need to reduce how aggressively that runs, or limit the higher target to only the tables that need it, to get this under control. You're really pushing what you can do in a VM with this many databases of this size. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with 8.4 stats collector high load
Maybe you should decrease naptime a bit. That did the trick, thanks! Yes. There were some changes that needed to be done to autovacuum so that it didn't read the stats file too often, but I don't recall if I got around to it. I looked at the strace output and there are *writes* to the file not reads. Why? Is it a consequence of this optimization? Release notes 8.4: Reduce I/O load of writing the statistics collection file by writing the file only when requested (Martin Pihlak) Was autovacuum requesting to write this 20MB file 650x per minute? Anyway, thank you all for the quick answer and precise answers. PostgreSQL is really unique in this regard! Kuba Dne 16.2.2010 15:10, Alvaro Herrera napsal(a): Jakub Ouhrabka wrote: You might want to try setting log_autovacuum_min_duration=0 in the postgresql.conf Thanks, tried it. There is nothing in the log - the actual vacuum/analyze commands are not run (as there is no query activity). I suspect that autovacuum is checking each database if it should run - and decides not to run. See the randomly catch process in ps output/pg_stat_activity mentioned in earlier mail. I suspect that this checking generates the load. Is it possible? Yes. There were some changes that needed to be done to autovacuum so that it didn't read the stats file too often, but I don't recall if I got around to it. Note that autovacuum_naptime=1min (default value) means that it's checking stats 650 times per minute (there's a throttle IIRC but still). Maybe you should decrease naptime a bit. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with 8.4 stats collector high load
Ideally, autovacuum would only request a new copy of the file if the one it got was considerably out of date. Obviously a tenth of a second is not old enough. I've tried to look at it and found that's already implemented - see autovac_refresh_stats(). STATS_READ_DELAY which is set to 1s. Am I reading the code correctly? If so then 1s is not enough for big clusters. I guess it would be feasible to crank STATS_READ_DELAY up a little bit, say to 10s. What do you think? Kuba Dne 16.2.2010 19:59, Alvaro Herrera napsal(a): Jakub Ouhrabka wrote: Maybe you should decrease naptime a bit. That did the trick, thanks! Yes. There were some changes that needed to be done to autovacuum so that it didn't read the stats file too often, but I don't recall if I got around to it. I looked at the strace output and there are *writes* to the file not reads. Why? Is it a consequence of this optimization? Release notes 8.4: Reduce I/O load of writing the statistics collection file by writing the file only when requested (Martin Pihlak) Was autovacuum requesting to write this 20MB file 650x per minute? Yes, exactly. Ideally, autovacuum would only request a new copy of the file if the one it got was considerably out of date. Obviously a tenth of a second is not old enough. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with 8.4 stats collector high load
well, my current opinion is that we should spend some nonzero amount of thought into figuring out what to do. I'd suggest to do it like this: Do autovac_refresh_stats() once per autovacuum_naptime/2 and share the result among all autovacuum workers. This would guarantee that autovacuum is fired no later than autovacuum_naptime after the condition for the run became true. If it's not that easy to code then don't share it among the workers and do it once per worker - typically there are not so many workers. And for bigger installations document that it's highly recommend to put the stats file on ramdisk. Kuba Dne 17.2.2010 0:12, Alvaro Herrera napsal(a): Tom Lane wrote: Alvaro Herreraalvhe...@commandprompt.com writes: Jakub Ouhrabka wrote: Was autovacuum requesting to write this 20MB file 650x per minute? Yes, exactly. Ideally, autovacuum would only request a new copy of the file if the one it got was considerably out of date. Obviously a tenth of a second is not old enough. Wasn't it you that insisted on a short staleness criterion for autovac in the first place? well, my current opinion is that we should spend some nonzero amount of thought into figuring out what to do. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with 8.4 stats collector high load
This would guarantee that autovacuum is fired no later than autovacuum_naptime after the condition for the run became true. Of course, this unfortunately not true... The guarantee is 1,5x autovacuum_naptime. But I'd be happy with it but I agree that's not what I'd as a user expect from this parameter. Kuba -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Problem with 8.4 stats collector high load
Hi, sorry for repost but previous message didn't get through. So I'm trying another list and sending without attachment which I can send privately upon request (strace output mentioned below). We've migrated some of our databases to 8.4 cluster (from 8.2 and older versions). These databases are archive databases, so there is no user activity - no connected users. But the stats collector generates load - 20-40% of modern 2.8GHz core all the time. I've found similar reports but with older versions of postgres: http://old.nabble.com/100--of-CPU-utilization-postgres-process-tt27302021.html Any clues what does it cause and how to investigate it? I'm attaching my findings below - I suspect autovacuum but don't know where the problem is exactly. Thanks, Kuba Detailed report: PostgreSQL 8.4.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2, 64-bit The pgstat.stat is ~20MB. There are 650 databases, 140GB total. Attached is strace output of stats collector running for 5s. Restarting postgresql and/or pg_stat_reset() doesn't help. When I do select * from pg_stat_activity, there is every 3rd try row like this: template1# select * from pg_stat_activity; datname - some database in the cluster procpid - changing number usename - postgres current_query - command string not enabled xact_start - null query_start - null backend_start - few milliseconds ago ps shows autovacuum worker: 21323 0:04 /opt/pg/bin/postmaster -D /var/lib/postgresql/8.4/data 21325 0:00 postgres: writer process 21326 0:00 postgres: wal writer process 21327 3:01 postgres: autovacuum launcher process 21328 22:30 postgres: stats collector process 21355 0:00 postgres: autovacuum worker process name of db There are only minor modifications to postgresql.conf: shared_buffers = 512MB temp_buffers = 2MB work_mem = 32MB maintenance_work_mem = 128MB max_stack_depth = 1MB fsync = off wal_buffers = 1MB checkpoint_segments = 100 effective_cache_size = 2GB default_statistics_target = 1000 The system is running Proxmox linux distribution. PostgreSQL is in OpenVZ container. The kernel is 2.6.18-2-pve. PostgreSQL data files are on local xfs filesystem. We don't have much experience with this setup yet. But we have a smaller cluster with 8.4 running without this problem on other machine. And we have a big 8.2 cluster on this setup without this problem. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Howto change db cluster locale on-the-fly
Thanks for your answer. Is there any other risk than wrong answers when running with wrong locale? So maybe the best bet would be: 1) drop all text/varchar user indexes 2) stop database, change the locale 3) in single user mode reindex shared tables and system tables in all databases and templates 4) start the database 5) create all text/varchar user indexes Sounds this about right? I'd like to minimize downtime... How to do step 2) - change the locale?? Thanks a lot, Kuba Martijn van Oosterhout napsal(a): On Mon, Feb 19, 2007 at 09:27:06AM +0100, Jakub Ouhrabka wrote: But I guess something like this would work: a) 1) drop all indexes on text/varchar columns 2) change cluster locale 3) create all indexes on text/varchar columns You're going to miss the name columns, ie. every string index in pg_catalog. Also, there are shared tables which all used in every DB. You need to log into every DB in the cluster (don't forget template[01] and reindex everything. So, REINDEX DATABASE; seems to be a safer bet. In general this doesn't actually work since changing the locale may make two strings equal that wern't before, thus possibly breaking a unique index, but it may be possible. I'd suggest single user mode at least, and make backups! Have a nice day, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Howto change db cluster locale on-the-fly
Hi, we've made mistake and initdb database cluster in wrong locale :-( Now it's full of data. I've read in the docs that it's not possible to change locale. But I guess something like this would work: a) 1) drop all indexes on text/varchar columns 2) change cluster locale 3) create all indexes on text/varchar columns or even b) 1) change cluster locale 2) reindex all indexes on text/varchar columns [I'm aware that before reindex queries on top of these indexes would return wrong answers] Is it possible/safe to do a) or b)? How to do step change cluster locale? Where is this information stored? Or the only way is to rebuild the database cluster from scratch? Thanks, Kuba ---(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: [HACKERS] Howto change db cluster locale on-the-fly
Hi Tom, Hacking pg_control would be the hard part; you'll never get the CRC right if you do it manually. Possibly pg_resetxlog could be adapted to the purpose. thanks for your valuable answer! I looked at pg_resetxlog.c but I'm no pg internals' expert - would something like this work? 1) normally shut down database 2) hack pg_resetxlog to set locale to wanted value 3) run pg_resetxlog -f (rewrite pg_control - everything would be guessed with the exception of overloaded locale) 4) start database We won't miss any transactions and there won't be any inconsistency in data because server was normally shut down, right? Thanks, Kuba Tom Lane napsal(a): Martijn van Oosterhout kleptog@svana.org writes: But I guess something like this would work: 1) drop all indexes on text/varchar columns 2) change cluster locale 3) create all indexes on text/varchar columns You're going to miss the name columns, ie. every string index in pg_catalog. But name is not locale-aware --- it just uses strcmp(). AFAIR there aren't any locale-dependent indexes in the system catalogs. So in principle you could hack pg_control, restart the postmaster, and then reindex every locale-dependent index. Hacking pg_control would be the hard part; you'll never get the CRC right if you do it manually. Possibly pg_resetxlog could be adapted to the purpose. I'd suggest single user mode at least, and make backups! Yup, a filesystem backup would be a *real* good idea. Not to mention testing the procedure on a toy installation. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Case Studio II
Hi, I use it and can recommend it. It is actively developed/maintained (the updates are free). There is very basic support of 7.3 schemas in the new version for instance... And the development team is very responsive, if you don't like or missing something write them... kuba On Sun, 2 Feb 2003 [EMAIL PROTECTED] wrote: Has anyone seriously tried out this package? It looks like a cheaper variant on ERWin, with the merit of having some PostgreSQL support. It only runs on WinTel, which is somewhat unfortunate, but I haven't gotten the sort of diagramming I have been looking for out of AutoDoc, so I'd be game to look at something pricey, assuming it is useful. -- http://cbbrowne.com/info/linux.html Rules of the Evil Overlord #50. My main computers will have their own special operating system that will be completely incompatible with standard IBM and Macintosh powerbooks. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] ecpg problem ...
hi,
i think that ecpg is only text preprocessor. it doesn't understand the c
semantics - it goes from the top to the end of the file row by row and
sees your declaration twice.
kuba
On Tue, 12 Nov 2002, Marc G. Fournier wrote:
if (ic_flag == 1) {
/*only select those non-IC/Spyder nodes that has full update set*/
EXEC SQL DECLARE full_dyn_node CURSOR FOR
SELECT node_name FROM NODE
WHERE dynamic_community = 'f' AND ic_flag='n' AND machine_type!=22
AND node_id != 0 AND NODE_NAME != :nodename;
}
else{
EXEC SQL DECLARE full_dyn_node CURSOR FOR
SELECT node_name FROM NODE
WHERE dynamic_community = 'f'
AND node_id != 0 AND NODE_NAME != :nodename; (line#493)
}
the above code generates the following error:
The compiler complains:
../subapi.pgc:493: ERROR: cursor full_dyn_node already defined
since its envelop'd in an if/else clause, shouldn't it work?
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] another optimizer question
hi,
can anyone explain me why there are different query plans for select ...
from ... where y!=x and select ... from ... where yx or yx for
integers, please?
see the details below...
thanks,
kuba
db_cen7=# analyze;
ANALYZE
db_cen7=# \d ts19
Table ts19
Column | Type | Modifiers
---+--+
ts19pk___ | integer | not null default
nextval('ts19_ts19pkseq'::text)
ts19datum | timestamp with time zone | not null
ts19zavaz | integer | not null
ts19cislo | integer | not null
ts19text_ | character varying(65536) | not null
ts19idpri | integer | not null
Indexes: ts19_ts19zavaz_idx
Primary key: ts19_pkey
db_cen7=# explain analyze select * from ts19 where ts19zavaz != 7 order by
ts19pk___ desc limit 10;
NOTICE: QUERY PLAN:
Limit (cost=89635.63..89635.63 rows=1 width=38) (actual
time=50868.17..50868.18 rows=10 loops=1)
- Sort (cost=89635.63..89635.63 rows=1 width=38) (actual
time=50868.16..50868.17 rows=11 loops=1)
- Seq Scan on ts19 (cost=0.00..89635.62 rows=1 width=38)
(actual time=95.99..50852.34 rows=300 loops=1)
Total runtime: 50868.27 msec
db_cen7=# explain analyze select * from ts19 where ts19zavaz 7 or
ts19zavaz 7 order by ts19pk___ desc limit 10;
NOTICE: QUERY PLAN:
Limit (cost=4.04..4.04 rows=1 width=38) (actual time=1118.28..1118.29
rows=10 loops=1)
- Sort (cost=4.04..4.04 rows=1 width=38) (actual
time=1118.27..1118.28 rows=11 loops=1)
- Index Scan using ts19_ts19zavaz_idx, ts19_ts19zavaz_idx on
ts19 (cost=0.00..4.03 rows=1 width=38) (actual time=0.03..1117.58
rows=300 loops=1)
Total runtime: 1118.40 msec
the runtime times depends on the machine load but generally the second
query is much faster...
more info:
db_cen7=# select count(*) from ts19;
count
-
4190527
(1 row)
db_cen7=# select distinct(ts19zavaz) from ts19;
ts19zavaz
---
3
7
(2 rows)
db_cen7=# select count(*) from ts19 where ts19zavaz = 3;
count
---
300
(1 row)
db_cen7=# select version();
version
---
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
---(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
